SpringBoot实现快速导出Excel

一. SpringBoot实现快速导出Excel

工作中经常遇到导出Ecxel功能，这里就给出完整示例代码，可以直接使用。

1. 引入依赖

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>3.17</version>
</dependency>

2. ExcelSheetSettingEnum
   定义Excel的一些配置信息

public enum ExcelSheetSettingEnum {
 REPORT_TEST("report_test", "Excel文件名称", new String[]{"Sheet名称1", "Sheet名称2"}, new String[]{"标题1", "标题2"}, new String[][]{
 {"字段名称A", "字段名称B", "字段名称C", "字段名称D"},
 {"字段名称A", "字段名称B", "字段名称C", "字段名称D"}
 }),
 REPORT_TEST2("report_test2", "Excel文件名称", new String[]{"标题1", "标题2"}),
 REPORT_TEST3("report_test3", "Excel文件名称")
 ;
 ExcelSheetSettingEnum(String code, String filename) {
 this.code = code;
 this.filename = filename;
 }
 ExcelSheetSettingEnum(String code, String filename, String[] titles) {
 this.code = code;
 this.filename = filename;
 this.titles = titles;
 }
 ExcelSheetSettingEnum(String code, String filename, String[] sheetnames, String[] titles, String[][] headers) {
 this.code = code;
 this.filename = filename;
 this.sheetnames = sheetnames;
 this.titles = titles;
 this.headers = headers;
 }
 /** 代码标识(必选) */
 private String code;
 /** 代码标识(必选) */
 private String filename;
 /** Sheet名称(可选) */
 private String[] sheetnames;
 /** Sheet标题(可选) */
 private String[] titles;
 /** 表头名称(可选) */
 private String[][] headers;
 // Getter & Setter
}

3. ExcelView
   定义Excel视图，继承自AbstractXlsxView或者AbstractXlsView, 需要实现一个abstract方法buildExcelDocument用于创建Sheet，构造Excel数据。

继承关系如下：

/**
 * Excel视图
 *
 * 支持多个Sheet, Sheet名称、标题和表头不是必须的
 * AbstractPdfView和AbstractXlsxView原理大致相同
 *
 * @author mengday zhang
 */
public class ExcelView extends AbstractXlsxView {
 @Override
 protected void buildExcelDocument(Map<String, Object> map, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
 ExcelSheetSettingEnum setting = (ExcelSheetSettingEnum) map.get("ExcelSheetSetting");
 // 设置文件名称
 String filename = setting.getFilename();
 filename = new String(filename.getBytes("UTF-8"),"ISO8859-1");
 response.setHeader(HttpHeaders.CONTENT_DISPOSITION,"attachment;filename=" + filename + ".xlsx");
 List<List<List<String>>> sheets = (List<List<List<String>>>) map.get("data");
 for (int i = 0; i < sheets.size(); i++) {
 // 创建sheet
 String[] sheetNames = setting.getSheetnames();
 String sheetName = "Sheet" + (i + 1);
 if (sheetNames != null && sheetNames.length > 0) {
 sheetName = sheetNames[i];
 }
 Sheet sheet = workbook.createSheet(sheetName);
 // 如果标题不为空的话，将表格的第一行作为标题行，并合并第一行的N个单元格
 int index = 0;
 String[] titles = setting.getTitles();
 String[][] headerss = setting.getHeaders();
 List<List<String>> rowsForTable = sheets.get(i);
 if (titles != null && titles.length > 0) {
 // 合并标题单元格 下标从0开始 起始行号，终止行号， 起始列号，终止列号
 CellRangeAddress region = new CellRangeAddress(0, 0, 0, rowsForTable.get(0).size() - 1);
 sheet.addMergedRegion(region);
 Row titleRow = sheet.createRow(index++);
 Cell titleCell = titleRow.createCell(0);
 CellStyle cellStyle = workbook.createCellStyle();
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 Font font = workbook.createFont();
 font.setFontName("黑体");
 font.setBold(true);
 font.setFontHeightInPoints((short) 15);
 cellStyle.setFont(font);
 titleCell.setCellStyle(cellStyle);
 titleCell.setCellValue(titles[i]);
 }
 // 创建表头行
 if (headerss != null && headerss.length > 0) {
 Row headerRow = sheet.createRow(index++);
 String[] headers = headerss[i];
 for(int j = 0; j < headers.length; j++) {
 headerRow.createCell(j).setCellValue(headers[j]);
 }
 }
 // 创建数据行
 AtomicInteger rowIndex = new AtomicInteger(index);
 rowsForTable.forEach(rowList -> {
 Row row = sheet.createRow(rowIndex.getAndIncrement());
 AtomicInteger x = new AtomicInteger();
 rowList.forEach(cell ->
 row.createCell(x.getAndIncrement()).setCellValue(cell)
 );
 });
 }
 }
}

4. controller
   mvc方法中方法的返回值是ModelAndView, 当代码执行new ModelAndView(excelView, map)时会执行ExcelView#buildExcelDocument的方法

@RestController
@RequestMapping("/excel")
public class ExcelController {
 @RequestMapping("/export")
 public ModelAndView export(){
 List<List<String>> sheet1 = Arrays.asList(
 Arrays.asList("1", "11", "111", "1111"),
 Arrays.asList("2", "22", "222", "2222"),
 Arrays.asList("3", "33", "333", "3333")
 );
 List<List<String>> sheet2 = Arrays.asList(
 Arrays.asList("4", "44", "444", "4444"),
 Arrays.asList("5", "55", "555", "5555"),
 Arrays.asList("6", "66", "666", "6666")
 );
 List<List<List<String>>> sheets = Arrays.asList(sheet1, sheet2);
 Map<String, Object> map = new HashMap<>();
 map.put("ExcelSheetSetting", ExcelSheetSettingEnum.REPORT_TEST2);
 map.put("data", sheets);
 ExcelView excelView = new ExcelView();
 return new ModelAndView(excelView, map);
 }
}