Java|Java springmvc Excel(poi) 的导入导出（上传下载） JavaspringmvcExcel(poi)的导入导出（

maven-poi

org.apache.poi poi 3.15 org.apache.poi poi-ooxml 3.15 org.apache.commons commons-lang3 3.3.2

springmvc 文件上传限制

上传excel 并解析excel

//excel文件上传 @RequestMapping(value = "https://www.it610.com/fileUpload") public StringfileUpload(HttpServletRequest request,MultipartFile fileUpload) throws IOException { XSSFWorkbook workbook=null; File file = new File("xx"); FileInputStream fileInputStream = null; byte[] bytes= fileUpload.getBytes(); OutputStream output = new FileOutputStream(file); BufferedOutputStream bufferedOutput = new BufferedOutputStream(output); bufferedOutput.write(bytes); fileInputStream = new FileInputStream(file); file.deleteOnExit(); //删除文件 fileUpload=null; BufferedInputStream bis = new BufferedInputStream(fileInputStream); //创建工作簿 workbook= new XSSFWorkbook(bis); //注意07的版本使用这个 //按索引读取sheet表 XSSFSheet sheet = workbook.getSheetAt(0); //取得所有的行数 int rowNum = sheet.getLastRowNum(); for (int i = 1; i <= rowNum; i++) { //读取1行 XSSFRow row = sheet.getRow(i); XSSFCell cell=null; //读取每行的列数 //short cellNum = row.getLastCellNum(); Employee employee = new Employee(); cell= row.getCell(1); employee.setEname(getCellValue( cell)); Department department = new Department(); cell= row.getCell(2); department.setDid(Integer.parseInt(getCellValue(cell))); cell= row.getCell(3); department.setDname(getCellValue(cell)); cell= row.getCell(4); department.setDaddress(getCellValue(cell)); employee.setDno(department); iEmployeeService.insertEmployee(employee); }return "index"; }//excel 单元格格式 private String getCellValue(XSSFCell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // 数字 // 如果为时间格式的内容 if (DateUtil.isCellDateFormatted(cell)) { //注：format格式 yyyy-MM-dd SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.format(cell.getDateCellValue()); } else { //有小数保留两位，位数不够自动忽略 return new DecimalFormat("#.##").format(cell.getNumericCellValue()); } case Cell.CELL_TYPE_STRING: // 字符串 return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: // Boolean return cell.getBooleanCellValue() + ""; case Cell.CELL_TYPE_FORMULA: // 公式 return cell.getCellFormula() + ""; case Cell.CELL_TYPE_BLANK: // 空值 return ""; case Cell.CELL_TYPE_ERROR: // 故障 return "非法字符"; default: return "未知类型"; }}

构建excel 并下载

//导出文件导出的文件不能直接导入，需要另存为xlsx @RequestMapping("/fileDownload") public void fileDownload(HttpServletResponse response) throws IOException { //创建工作簿，SXSSFWorkbook支持大数据量的导出 SXSSFWorkbook workbook = new SXSSFWorkbook(); //创建sheet表 Sheet sheet = workbook.createSheet("康舍司测试表"); List employees = iEmployeeService.selectEmployee(0, 0); //创建标题 Row rowTitle = sheet.createRow(0); Cell cellTitle=null; cellTitle = rowTitle.createCell(0); cellTitle.setCellValue("eid"); cellTitle = rowTitle.createCell(1); cellTitle.setCellValue("ename"); cellTitle = rowTitle.createCell(2); cellTitle.setCellValue("did"); cellTitle = rowTitle.createCell(3); cellTitle.setCellValue("dname"); cellTitle = rowTitle.createCell(4); cellTitle.setCellValue("daddress"); for (int i = 0; i < employees.size(); i++) { //创建行 Row row = sheet.createRow(i+1); Cell cell =null; //创建单元格cell = row.createCell(0); cell.setCellValue(employees.get(i).getEid()); cell = row.createCell(1); cell.setCellValue(employees.get(i).getEname()); cell = row.createCell(2); cell.setCellValue(employees.get(i).getDno().getDid()); cell = row.createCell(3); cell.setCellValue(employees.get(i).getDno().getDname()); cell = row.createCell(4); cell.setCellValue(employees.get(i).getDno().getDaddress()); } ServletOutputStream outputStream = response.getOutputStream(); response.reset(); //清空输出六response.setCharacterEncoding("UTF-8"); response.setContentType("application/force-download"); //response.setContentType("application/octet-stream; charset=UTF-8"); response.setHeader("Content-Disposition","attachment; filename=fileOut.xlsx"); response.setHeader("Pargam","no-cache"); response.setHeader("Cache-Control","no-cache"); workbook.write(outputStream); workbook.close(); }

问题：通过SXSSFWorkbook构建的excel (xlsx) 表格，导出后，该文件导入无法解析
【Java|Java springmvc Excel(poi) 的导入导出（上传下载）】原因：导出的excel文件头中有问题
解决方法：将导出文件，另存为xlsx，就可以，重新上传