SpringBoot如何实现导出Excel表格

我们查到的数据是list，如何将这些数据导出到Excel表格中呢

我这里查到的list数据是 List monthReportModels

一、导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.6</version>
</dependency>

二、生成表格
根据数据以及一些属性，生成表格，并设置表格的一些属性

 public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
    // 创建一个HSSFWorkbook，对应一个Excel文件
    if (workbook == null) {
        workbook = new HSSFWorkbook();
    }
    // 在workbook中添加一个sheet,对应Excel文件中的sheet
    HSSFSheet sheet = workbook.createSheet(sheetName);
    // 在sheet中添加表头第0行
    HSSFRow row = sheet.createRow(0);
    // 创建单元格，并设置值表头 设置表头居中
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 声明列对象
    HSSFCell cell = null;
    // 创建标题
    for (int i = 0; i < title.length; i++) {
        cell = row.createCell(i);
        cell.setCellValue(title[i]);
        cell.setCellStyle(cellStyle);
    }
    // 创建内容
    for (int i = 0; i < values.length; i++) {
        row = sheet.createRow(i + 1);
        for (int j = 0; j < values[i].length; j++) {
            // 将内容按顺序赋给对应的列对象
            row.createCell(j).setCellValue(values[i][j]);
        }
    }
    return workbook;

}

三、设置发送响应流

public void setResponseHeader(HttpServletResponse response, String fileName) {
  try {
        fileName = new String(fileName.getBytes(), "ISO8859-1");
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

四、处理list数据
将list数据处理成二维数组，设置Excel标题，以及Excel文件名，Excel的Sheet名，以供生成表格

public void exportExcel(HttpServletResponse response, List<MonthReportModel> monthReportModels) {
    // Excel标题
    String[] title = {"姓名", "违纪次数", "违纪原因", "请假次数", "请假类型", "请假累计时间（小时）", "请假原因描述"};
    // Excel文件名
    String fileName =  "请假统计.xls";
    // sheet名
    String sheetName = "请假统计";
    // 将数据放到数组中
    String[][] content = new String[monthReportModels.size()][title.length];
    for (int i = 0; i < monthReportModels.size(); i++) {
        MonthReportModel monthReportModel = monthReportModels.get(i);
        content[i][0] = monthReportModel.getUserName();
        content[i][1] = monthReportModel.getDisobedientNum();
        content[i][2] = monthReportModel.getDisobedientReason();
        content[i][3] = monthReportModel.getLeaveNum();
        content[i][4] = monthReportModel.getLeaveType();
        content[i][5] = monthReportModel.getLeaveTime() + "小时";
        content[i][6] = monthReportModel.getLeaveReason();
    }
    // 导出Excel
    try {
        HSSFWorkbook hssfWorkbook = getHSSFWorkbook(sheetName, title, content, null);
        setResponseHeader(response, fileName);
        OutputStream outputStream = response.getOutputStream();
        hssfWorkbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}