前言
表格的导入、导出可谓开发过程中经常会碰到的功能。然后这种模版化的东西并不需要每次都去编码一次，因此我就整理了一个Excel的万能处理类。能够实现兼容2003、2007的各种Excel格式的导入导出功能，使用起来也非常的方面，适用于所有业务场景，下面会有案例讲解
Excel导入、导出工具类源码：
/** * 操作Excel的处理器类(含导入和导出功能) * * @author fangshixiang * @description // * @date 2018/11/13 21:17 */ public class ExcelHandler { private Workbook workbook; /** * 构造函数 调用者只需要传入excel的文件流,并告知Excel版本即可（使用得最多） * * @param is the is * @param version the version * @throws IOException the io exception */ public ExcelHandler(InputStream is, WorkbookVersion version) throws IOException { if (version == WorkbookVersion.XLS) { workbook = new HSSFWorkbook(is); } else { workbook = new XSSFWorkbook(is); } } /** * 需要导出的时候 调用这个构造函数 可以创建一个Excel文件 并且调用对应的方法 * * @param version */ public ExcelHandler(WorkbookVersion version) { if (version == WorkbookVersion.XLS) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } } /** * 也支持调用者外部解析 然后把workbook直接传给我就行 * <p> * 一般用于导出，外部建立好传进来就成 * * @param workboook the workboook */ public ExcelHandler(Workbook workboook) { this.workbook = workboook; } /** * excel的版本 */ public enum WorkbookVersion { XLS, XLSX } /** * 行类型 是否是表头 */ public enum RowType { HEADER, NORMER } //静态方法 start// /** * 根据后缀判断是否为 Excel 文件，后缀匹配xls和xlsx * * @param pathName the pathName * @return boolean boolean */ public static boolean isExcel(String pathName) { if (pathName == null) { return false; } //return pathName.endsWith(".xls") || pathName.endsWith(".xlsx"); return getExcelVersion(pathName) != null; } /** * 获取该文件的Excel版本 * * @param pathName the path name * @return the excel version */ public static WorkbookVersion getExcelVersion(String pathName) { if (isExcel2003(pathName)) { return WorkbookVersion.XLS; } else if (isExcel2007(pathName)) { return WorkbookVersion.XLSX; } else { return null; } } /** * 是否是xls格式的Excel文件 * * @param pathName the file path * @return the boolean */ public static boolean isExcel2003(String pathName) { return pathName.matches("^.+\\.(?i)(xls)$"); } /** * 是否是xlsx格式的Excel文件 * * @param pathName the file path * @return the boolean */ public static boolean isExcel2007(String pathName) { return pathName.matches("^.+\\.(?i)(xlsx)$"); } //静态方法 end// /** * 读取 Excel 第一页所有数据（第一个sheet，一般导入都只有一个sheet，所以这个最常用） * * @return list list */ public List<List<String>> read() { return read(0, 0, getRowCount(0) - 1); } /** * 读取指定sheet 页所有数据 * * @param sheetIx 指定 sheet 页，从 0 开始 * @return list list */ public List<List<String>> read(int sheetIx) { return read(sheetIx, 0, getRowCount(sheetIx) - 1); } /** * 读取指定sheet 页指定行数据 * * @param sheetIx 指定 sheet 页，从 0 开始 * @param start 指定开始行，从 0 开始 * @param end 指定结束行，从 0 开始 * @return list list */ public List<List<String>> read(int sheetIx, int start, int end) { Sheet sheet = workbook.getSheetAt(sheetIx); List<List<String>> list = new ArrayList<>(); if (end > getRowCount(sheetIx)) { end = getRowCount(sheetIx); } //int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数（第一行总列数决定了列的总数） //兼容操作：防止第一列没填，所以最多往下找10列 再没有就报错吧 找到就当作表头 int cols = 0; //优化 为了表面有的人可能第一列空着 所以此处改为找第一个不为null的行为止（遍历10行吧） for (int i = 0; i < 10; i++) { Row row = sheet.getRow(i); if (row != null) { cols = row.getLastCellNum(); break; } } if (cols == 0) { throw new RuntimeException("请把表格的前10行填充数据(表头)~"); } for (int i = start; i <= end; i++) { List<String> rowList = new ArrayList<>(); Row row = sheet.getRow(i); for (int j = 0; j < cols; j++) { if (row == null) { rowList.add(null); continue; } rowList.add(getCellValueToString(row.getCell(j))); } list.add(rowList); } return list; } /** * 将数据写入到 Excel 默认第一页中，从第1行开始写入（导出可以使用） * * @param rowData 数据 * @return boolean boolean */ public boolean write(List<List<String>> rowData) { return write(0, rowData, 0); } /** * 将数据写入到 Excel 新创建的 Sheet 页（导出） * * @param rowData 数据 * @param sheetName 长度为1-31，不能包含后面任一字符: ：\ / ? * [ ] * @param isNewSheet the is new sheet * @return boolean boolean * @throws IOException the io exception */ public boolean write(List<List<String>> rowData, String sheetName, boolean isNewSheet) { Sheet sheet = null; if (isNewSheet) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int sheetIx = workbook.getSheetIndex(sheet); return write(sheetIx, rowData, 0); } /** * 将数据追加到sheet页最后 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowData 数据 * @param isAppend 是否追加,true 追加，false 重置sheet再添加 * @return boolean boolean */ public boolean write(int sheetIx, List<List<String>> rowData, boolean isAppend) { if (isAppend) { return write(sheetIx, rowData, getRowCount(sheetIx)); } else { // 清空再添加 clearSheet(sheetIx); return write(sheetIx, rowData, 0); } } /** * 把该workBook写进resp，提供下载的请求头 * <p> * 提示浏览器下载此文件 * * @param response resp * @param fileName excel文件的名字 * @return boolean */ public boolean flushIntoResponseForDownload(HttpServletResponse response, String fileName) { fileName = fileName == null ? "test.xlsx" : fileName; try { response.setContentType("application/vnd.ms-excel"); //response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8")); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException(e); } return true; } /** * 将数据写入到 Excel 指定 Sheet 页指定开始行中,指定行后面数据向后移动 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowData 数据 * @param startRow 指定开始行，从 0 开始 * @return boolean boolean */ public boolean write(int sheetIx, List<List<String>> rowData, int startRow) { Sheet sheet = workbook.getSheetAt(sheetIx); int dataSize = rowData.size(); if (getRowCount(sheetIx) > 0) { // 如果小于等于0，则一行都不存在 sheet.shiftRows(startRow, getRowCount(sheetIx), dataSize); } for (int i = 0; i < dataSize; i++) { Row row = sheet.createRow(i + startRow); for (int j = 0; j < rowData.get(i).size(); j++) { Cell cell = row.createCell(j); cell.setCellValue(rowData.get(i).get(j) + ""); } } return true; } ///下为设置Excel的样式... /** * 给一行设置样式（每个单元格都一样） * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex the row index * @param style the style * @return boolean */ public boolean setRowStyle(int sheetIx, int rowIndex, CellStyle style) { Sheet sheet = workbook.getSheetAt(sheetIx); Row row = sheet.getRow(rowIndex); int columnCount = row.getLastCellNum(); for (int i = 0; i < columnCount; i++) { sheet.autoSizeColumn(i, true);// 设置列宽度自适应 sheet.setColumnWidth(i, 4000); //拿到这个cell 设置一样的样式 Cell cell = row.getCell(i); cell.setCellStyle(style); } return true; } /** * 设置cell 样式 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex the row index * @param colIndex 指定列，从 0 开始 * @param style the style * @return style style */ public boolean setCellStyle(int sheetIx, int rowIndex, int colIndex, CellStyle style) { Sheet sheet = workbook.getSheetAt(sheetIx); // sheet.autoSizeColumn(colIndex, true);// 设置列宽度自适应 sheet.setColumnWidth(colIndex, 4000); Cell cell = sheet.getRow(rowIndex).getCell(colIndex); cell.setCellStyle(style); return true; } /** * 建立一个样式（比如给标题给出内置特殊样式 type就传1即可） * * @param rowType 参考枚举@RowType * @return cell style */ public CellStyle makeStyle(RowType rowType) { CellStyle style = workbook.createCellStyle(); //内容居中 style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); //针对时间格式 进行特殊处理一下 DataFormat format = workbook.createDataFormat(); style.setDataFormat(format.getFormat("@")); // // 内容样式 设置单元格内容格式是文本 //设置边框样式 style.setBorderTop(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); // 文字样式 Font font = workbook.createFont(); if (rowType == RowType.HEADER) { //颜色样式 style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); //前景颜色 style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());//背景色 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充方式 font.setBold(true); //标题文字加粗 font.setFontHeight((short) 300); font.setColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex()); } else if (rowType == RowType.NORMER) { font.setFontHeight((short) 150); } style.setFont(font); return style; } /** * 合并单元格（这个功能很高级） * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param firstRow 开始行 * @param lastRow 结束行 * @param firstCol 开始列 * @param lastCol 结束列 */ public void region(int sheetIx, int firstRow, int lastRow, int firstCol, int lastCol) { Sheet sheet = workbook.getSheetAt(sheetIx); sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } /** * 指定行是否为空 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定开始行，从 0 开始 * @return true 不为空，false 不行为空 */ public boolean isRowNull(int sheetIx, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); return sheet.getRow(rowIndex) == null; } /** * 创建行，若行存在，则清空 * * @param sheetIx 指定 sheet 页，从 0 开始 * @param rowIndex the row index * @return boolean boolean */ public boolean createRow(int sheetIx, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); sheet.createRow(rowIndex); return true; } /** * 指定单元格是否为空 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定开始行，从 0 开始 * @param colIndex 指定开始列，从 0 开始 * @return true 行不为空，false 行为空 */ public boolean isCellNull(int sheetIx, int rowIndex, int colIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); if (!isRowNull(sheetIx, rowIndex)) { return false; } Row row = sheet.getRow(rowIndex); return row.getCell(colIndex) == null; } /** * 创建单元格 * * @param sheetIx 指定 sheet 页，从 0 开始 * @param rowIndex 指定行，从 0 开始 * @param colIndex 指定创建列，从 0 开始 * @return true 列为空，false 行不为空 */ public boolean createCell(int sheetIx, int rowIndex, int colIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); Row row = sheet.getRow(rowIndex); row.createCell(colIndex); return true; } /** * 返回sheet 中的行数 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @return row count */ public int getRowCount(int sheetIx) { Sheet sheet = workbook.getSheetAt(sheetIx); if (sheet.getPhysicalNumberOfRows() == 0) { return 0; } return sheet.getLastRowNum() + 1; } /** * 返回所在行的列数 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @return 返回 -1 表示所在行为空 */ public int getColumnCount(int sheetIx, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); Row row = sheet.getRow(rowIndex); return row == null ? -1 : row.getLastCellNum(); } /** * 设置row 和 column 位置的单元格值 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @param colIndex 指定列，从0开始 * @param value 值 * @return value at */ public boolean setValueAt(int sheetIx, int rowIndex, int colIndex, String value) { Sheet sheet = workbook.getSheetAt(sheetIx); sheet.getRow(rowIndex).getCell(colIndex).setCellValue(value); return true; } /** * 返回 row 和 column 位置的单元格值 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @param colIndex 指定列，从0开始 * @return value at */ public String getValueAt(int sheetIx, int rowIndex, int colIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); return getCellValueToString(sheet.getRow(rowIndex).getCell(colIndex)); } /** * 重置指定行的值 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowData 数据 * @param rowIndex 指定行，从0开始 * @return row value */ public boolean setRowValue(int sheetIx, List<String> rowData, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); Row row = sheet.getRow(rowIndex); for (int i = 0; i < rowData.size(); i++) { row.getCell(i).setCellValue(rowData.get(i)); } return true; } /** * 返回指定行的值的集合 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @return row value */ public List<String> getRowValue(int sheetIx, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); Row row = sheet.getRow(rowIndex); List<String> list = new ArrayList<>(); if (row == null) { list.add(null); } else { for (int i = 0; i < row.getLastCellNum(); i++) { list.add(getCellValueToString(row.getCell(i))); } } return list; } /** * 返回列的值的集合 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @param colIndex 指定列，从0开始 * @return column value */ public List<String> getColumnValue(int sheetIx, int rowIndex, int colIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); List<String> list = new ArrayList<>(); for (int i = rowIndex; i < getRowCount(sheetIx); i++) { Row row = sheet.getRow(i); if (row == null) { list.add(null); continue; } list.add(getCellValueToString(sheet.getRow(i).getCell(colIndex))); } return list; } /** * 获取excel 中sheet 总页数 * * @return sheet count */ public int getSheetCount() { return workbook.getNumberOfSheets(); } /** * 创建一个sheet */ public void createSheet() { workbook.createSheet(); } /** * 设置sheet名称，长度为1-31，不能包含后面任一字符: ：\ / ? * [ ] * * @param sheetIx 指定 Sheet 页，从 0 开始，// * @param name the name * @return sheet name */ public boolean setSheetName(int sheetIx, String name) { workbook.setSheetName(sheetIx, name); return true; } /** * 获取 sheet名称 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @return sheet name */ public String getSheetName(int sheetIx) { Sheet sheet = workbook.getSheetAt(sheetIx); return sheet.getSheetName(); } /** * 获取sheet的索引，从0开始 * * @param name sheet 名称 * @return -1表示该未找到名称对应的sheet */ public int getSheetIndex(String name) { return workbook.getSheetIndex(name); } /** * 删除指定sheet * * @param sheetIx 指定 Sheet 页，从 0 开始 * @return boolean boolean */ public boolean removeSheetAt(int sheetIx) { workbook.removeSheetAt(sheetIx); return true; } /** * 删除指定sheet中行，改变该行之后行的索引 * * @param sheetIx 指定 Sheet 页，从 0 开始 * @param rowIndex 指定行，从0开始 * @return boolean boolean */ public boolean removeRow(int sheetIx, int rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIx); sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1); Row row = sheet.getRow(getRowCount(sheetIx) - 1); sheet.removeRow(row); return true; } /** * 设置sheet 页的索引 * * @param sheetname Sheet 名称 * @param sheetIx the sheet ix */ public void setSheetOrder(String sheetname, int sheetIx) { workbook.setSheetOrder(sheetname, sheetIx); } /** * 清空指定sheet页（先删除后添加并指定sheetIx） * * @param sheetIx 指定 Sheet 页，从 0 开始 * @return boolean boolean */ public boolean clearSheet(int sheetIx) { String sheetname = getSheetName(sheetIx); removeSheetAt(sheetIx); workbook.createSheet(sheetname); setSheetOrder(sheetname, sheetIx); return true; } /** * 对外提供关闭流的方法（使用完后记得关流） */ public void close() { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("关流失败~", e); } } /** * 转换单元格的类型为String 默认的 <br> * 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4), * CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0), * CELL_TYPE_STRING(1) * * @param cell the cell * @return cell value to string */ private String getCellValueToString(Cell cell) { String strCell = ""; if (cell == null) { return null; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: // 如果数字过长且不是日期格式，则防止当数字过长时以科学计数法显示 cell.setCellType(HSSFCell.CELL_TYPE_STRING); strCell = cell.toString(); break; case Cell.CELL_TYPE_STRING: strCell = cell.getStringCellValue(); break; default: break; } return strCell; } @Override public String toString() { return "共有 " + getSheetCount() + "个sheet 页！"; } /** * 可以把指定的sheet等toString输出 * * @param sheetIx the sheet ix * @return the string */ public String toString(int sheetIx) { return "第 " + (sheetIx + 1) + "个sheet 页，名称： " + getSheetName(sheetIx) + "，共 " + getRowCount(sheetIx) + "行！"; } /** * 把Workbook可以提供给外部访问 * * @return Workbook */ public Workbook getWorkbook() { return workbook; } }
依赖的jar为：
<!-- POI --> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
为何不使用POI4.0？
2018年09月 Apache POI 4.0.0 发布了。此版本特点是一些新功能和众多 bug 修复
删除了对 Java 6 和 7 的支持，使 Java 8 成为支持的最低版本
需要新的 OOXML schema（1.4），因为不兼容的 XMLBeans 加载不再通过 POIXMLTypeLoader
按照惯例，这种大版本出来之后，都会很快的出几个修复版本，所以等一段时间再更新吧（要是大家都这么想，都不更新，发现不了bug，那就尴尬了😂）
注意：
POI4.0版本，向下不兼容，类都有变化。
POI4.0版本，向下不兼容，类都有变化。
POI4.0版本，向下不兼容，类都有变化。
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
使用案例
导入Excel（建议POST请求，可以有JSON返回值）：
@ApiOperation("测试导入") @PostMapping("/{id}/import") Object testImport(@PathVariable Integer id, @RequestParam MultipartFile file) throws IOException { String fileName = file.getOriginalFilename(); if (!ExcelHandler.isExcel(fileName)) { throw new RuntimeException("导入只支持Excel表格~"); } InputStream is = file.getInputStream(); //把流交给Excel工具类进行解析 ExcelHandler excelHandler = new ExcelHandler(is, ExcelHandler.getExcelVersion(fileName)); //excel表格数据 解析成了List的数据结构 非常友好了 List<List<String>> read = excelHandler.read(); //关流 excelHandler.close(); return read; }
结果如下：
导出Excel（get请求，并且有对应的响应头，需要注意）：
@ApiOperation("测试导出") @GetMapping("/{id}/export") void testExport(@PathVariable Integer id, HttpServletResponse response) { List<List<String>> data = new ArrayList<>(); data.add(new ArrayList<String>() {{ add("fang"); add("shi"); add("xiang"); }}); data.add(new ArrayList<String>() {{ add("fang22"); add("shi22"); add("xiang22"); }}); ExcelHandler excelHandler = new ExcelHandler(ExcelHandler.WorkbookVersion.XLSX); //给创建一个sheet 并且吧数据写进去 创建一个新的sheet excelHandler.write(data, "sheet名称", true); //定制一些样式 一般只需要维护表头即可 excelHandler.setRowStyle(0, 0, excelHandler.makeStyle(ExcelHandler.RowType.HEADER)); //写入数据后 把数据再写到response即可实现导出了 excelHandler.flushIntoResponseForDownload(response, "测试导出.xlsx"); //关流 excelHandler.close(); }
Excel正常导出如下：
Tips（需要注意的是）：
响应头为：
Content-Disposition: attachment;filename=%E6%B5%8B%E8%AF%95%E5%AF%BC%E5%87%BA.xlsx Content-Type: application/vnd.ms-excel Date: Wed, 14 Nov 2018 07:09:05 GMT Transfer-Encoding: chunked
显然不再是application/json了。所以需要注意：如果你在控制台发现了这种异常（但不会影响导出Excel）：
org.springframework.web.HttpMediaTypeNotAcceptableException: Could not find acceptable representation at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:306) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestResponseBodyMethodProcessor.handleReturnValue(RequestResponseBodyMethodProcessor.java:180) ~[spring-webmvc-5.1.2.RELEASE.jar:5.1.2.RELEASE] at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue(HandlerMethodReturnValueHandlerComposite.java:82) ~[spring-web-5.1.2.RELEASE.jar:5.1.2.RELEASE] ,,,
原因就是你的MVC方法是@ResponseBody返回的，解析器使用不当导致。处理办法为：
返回值改为void（推荐）
去掉 produces = "text/html; charset=UTF-8"这段代码或者设置produces=application/json;charset=UTF-8
最后
此工具方法使用起来非常方便，与业务无关，希望能帮助到大家
