Excel文档导出和导入

2 Excel文档导出 response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")); 2.1 3 Excel文档导入 3.1 界面: var htmlStr = ""; htmlStr += '<div style="width:600px;" >'; htmlStr += '<div style="display:block; padding-bottom:20px;" align="center" >'; htmlStr += '<form id = "userExcelFileFormId" action="/SSMPro/uploadExcel" method="post" >'; htmlStr += '<input type="file" id="userExcelFile" name="userExcelFile" onchange="ValidateFileType()" >'; htmlStr += ' <input type="submit" value="导入" /> '; htmlStr += '</form>'; htmlStr += '</table>'; htmlStr += '</div>'; htmlStr += '</div>'; $.layer({ type : 1, title : '导入用户', area : [ 'auto', 'auto' ], page : { html : htmlStr } }); function ValidateFileType() { var array = new Array(); // 得到上传的Excel表格的名称 var excelName = $("#userExcelFile").val(); // 拆分 array = excelName.split("."); var suffix = array[array.length - 1]; if (suffix != "xlsx" && suffix != "xls") { alert("您选择的不是excel文档，请重新选择"); var file = document.getElementById("userExcelFile"); file.value = ""; } } /** * 使用ajax提交表单，要引入jquryForm的js文件 */ function submitFrom() { var options = { beforeSubmit : showRequest, success : showResponse, resetForm : true, dataType : 'json' }; $("#userExcelFileFormId").submit(function() { $(this).ajaxSubmit(options); return false; }); } function showRequest(){ } function showResponse(responseText) { if (responseText.isSuccess == true) { alert('导入成功'); window.location = "/SSMProject/user/userMana"; } } @RequestMapping("/uploadExcel") public void uploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { Map<String, Object> resultMap = new HashMap<>(); try { // 把request对象转换成Spring的request对象 MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; // 获取上传的文件列表 Map<String, MultipartFile> fileMap = multiRequest.getFileMap(); // 遍历 for (Map.Entry<String, MultipartFile> entry : fileMap.entrySet()) { // 获取上传的excel文件 MultipartFile file = entry.getValue(); // 得到文件输入流 InputStream inputStream = file.getInputStream(); // 创建excel文档对象 Workbook workbook = WorkbookFactory.create(inputStream); // 读取文档的内容 List<Map<String, Object>> list = this.readExcel(workbook); // 把读取到的内容插入到数据库中 userService.insertData(list); // 返回标识 resultMap.put("isSuccess", true); } } catch (Exception e) { e.printStackTrace(); resultMap.put("isSuccess", false); } Gson gson = new Gson(); String responseContent = gson.toJson(resultMap); this.flushResponse(response, responseContent); } /** * 读取sheet中的内容 * @param workbook * @return */ private List<Map<String, Object>> readExcel(Workbook workbook) { // 得到SimpleReadParameter对象，封装了插入的字段名和读取文档的下标 SimpleReadParameter srp = this.getSimpleReadParameter(); String[] fieldNames = srp.getFieldsId(); int startIndex = srp.getStartIndex(); ExcelUtil excelUtil = new ExcelUtil(); List<Map<String, Object>> list = new ArrayList<>(); // 判断是否有工作单元 if (workbook != null && workbook.getNumberOfSheets() > 0) { // 得到sheet对象 Sheet sheet = workbook.getSheetAt(0); // 通过工具类来读取sheet中的内容 List<Map<String, Object>> sheetData = excelUtil.readSimple(sheet, startIndex, fieldNames); if (sheetData != null && sheetData.size() > 0) { list.addAll(sheetData); } } return list; } /** * 得到要插入到数据库的字段名和起始下标 * * @return */ private SimpleReadParameter getSimpleReadParameter() { SimpleReadParameter simpleReadParameter = new SimpleReadParameter(); StringBuffer sbBuffer = new StringBuffer(); sbBuffer.append("userChName,").append("mobilePhone,").append("email,").append("userSex,").append("userName,") .append("orgId,"); String[] filedNames = sbBuffer.toString().split(","); int startIndex = 2; simpleReadParameter.setFieldsId(filedNames); simpleReadParameter.setStartIndex(startIndex); return simpleReadParameter; } } 导出 @RequestMapping("/exportUserExcel") public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { String fileName = ""; // 获取传过来的参数 Map<String, Object> param = this.getParam(request); Object fileNameObj = param.get("fileName"); // 创建一个默认的日期来拼接文件名 Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String defaultDate = sdf.format(date); if (fileNameObj != null && !("").equals(fileNameObj.toString())) { String fileChName = fileNameObj.toString(); // 截取文件名 if (fileChName.endsWith(".xls") || fileChName.endsWith(".xlsx")) { fileChName = fileChName.substring(0, fileChName.lastIndexOf(".")); } fileName = fileChName + defaultDate; } else { fileName = defaultDate; } // 统一确定后缀 fileName = fileName + ".xls"; OutputStream outputStream = null; try { // 得到一个输出流 outputStream = response.getOutputStream(); // 创建一个excel文档对象 Workbook wb = new HSSFWorkbook(); // 设置响应头 // Content-Disposition 的作用，当Content-Type 的类型为要下载的类型时 , // 这个信息头会告诉浏览器这个文件的名字或类型。 response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")); // Excel导出操作 this.exprotUserExcel(wb); // 把文档对象输出 wb.write(outputStream); outputStream.flush(); } catch (Exception e) { e.printStackTrace(); if (outputStream != null) { outputStream.close(); } } } // Excel导出操作 private void exprotUserExcel(Workbook wb) { // 得到输入excel文档数据的对象 SimpleExportParameter parameters = this.getSimpleExportParamter(); // 创建sheet对象 Sheet sheet = wb.createSheet(); ExcelUtil util = new ExcelUtil(); // 填充excel文档内容 util.simpleExport(wb, sheet, parameters); } //填充SimpleExportParameter对象 private SimpleExportParameter getSimpleExportParamter() { // 从数据库查出的属性要与下面的属性对应（用“,”分隔，先后顺序与数据库查出结果的顺序一致） String filedIds = "userChName,userSex,mobilePhone,provinceName,cityName,contryName,userBirthday"; // 中文名称 String filedName = "姓名,性别,电话,省份,地市,区县,生日"; // 列宽 String widthsStr = "20,20,20,20,20,20,20"; // 从数据库中查询数据 List<Map<String, Object>> dataList = userService.queryExprotData(); // 设置标题 String title = "千锋员工信息"; // 设置sheet名称 String sheetName = "员工sheet"; String[] ids = filedIds.split(","); String[] names = filedName.split(","); String[] widths = widthsStr.split(","); // 给对象赋值 SimpleExportParameter sep = new SimpleExportParameter(); sep.setTitle(title); sep.setTitleEn(sheetName); sep.setFieldsId(ids); sep.setFieldsName(names); sep.setWidths(widths); sep.setDataList(dataList); return sep; } }