使用SpringBoot把数据库的数据生成Excel报表，并保存到本地

1.jar:

<!--web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!--excel报表-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<!--mybatis-plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.2</version>
</dependency>
<!-- mysql驱动&ndash;&gt;-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<!-- 数据连接池 druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

2.配置本地保存地址:

 3.controller:

public void getUserInfoEx(HttpServletResponse response) {
    List<Map<String, Object>> userList = null;
    ArrayList<String> titleKeyList = null;
    Map<String, String> titleMap = null;
    try {
        userList = empService.queryUserInfoResultListMap();
        titleKeyList = new ColumnTitleMap().getTitleKeyList();
        titleMap = new ColumnTitleMap().getColumnTitleMap();
        exportDataService.exportDataToEx(response, titleKeyList, titleMap, userList);
    } catch (Exception e) {
        log.info("Exception: " + e.toString());
    }
}

4.uitl:

/*写入文件的上级目录*/
    @Value("${fileWritePath}")
    private String fileWritePath;

    /*生成Excel表格*/
    public void expoerDataExcel(ArrayList<String> titleKeyList,
                                Map<String, String> titleMap, List<Map<String, Object>> srcList) throws IOException {

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String xlsFileName = dateFormat.format(date) + ".xlsx";

        Workbook wb = new SXSSFWorkbook(100);   //创建Excel文件
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        for (int k = 0; k < srcList.size(); k++) {
            Map<String, Object> srcMap = srcList.get(k);
            //写入300000条后切换到下个工作表
            if (rowNo % 100000 == 0) {
                wb.createSheet("工作簿" + (rowNo / 100000));//创建新的sheet对象
                sheet = wb.getSheetAt(rowNo / 100000);        //动态指定当前的工作表
                pageRowNo = 0;      //新建了工作表,重置工作表的行号为0

                nRow = sheet.createRow(pageRowNo++);  // 定义表头
                // 列数 titleKeyList.size()
                for (int i = 0; i < titleKeyList.size(); i++) {
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;

            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 行，获取cell值
            for (int j = 0; j < titleKeyList.size(); j++) {
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }

        File writeFile = new File(fileWritePath + "/" + xlsFileName);
        System.out.println(fileWritePath + "/" + xlsFileName);

        writeFile.createNewFile();
        FileOutputStream out = new FileOutputStream(writeFile);
        wb.write(out);
        wb.close();
        out.flush();
        out.close();

//        response.setContentType("application/vnd.ms-excel;charset=utf-8");
//        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
//        response.flushBuffer();
//        OutputStream outputStream = response.getOutputStream();
//        wb.write(response.getOutputStream());
//        wb.close();
//        outputStream.flush();
//        outputStream.close();
    }

5.ExcelSerivce:

@Resource
private ExportExcelUtil exportExcelUtil;

/*导出用户数据表*/
public void exportDataToEx(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String, Object>> src_list) {
    try {
        exportExcelUtil.expoerDataExcel(titleKeyList, titleMap, src_list);
    } catch (Exception e) {
        log.error("Exception: " + e.toString());
    }
}

6.tDao(用来键值对应):

/**
 * @ClassName ColumnTitleMap
 * @Description 数据导出，生成excel文件时的列名称
 * @Author qiwen
 * @Data 2020/8/4 15:59
 */

private Map<String, String> columnTitleMap = new HashMap<String, String>();
private ArrayList<String> titleKeyList = new ArrayList<String>();

public ColumnTitleMap() {
    initUserInfoColu();
    initUserInfoTitleKeyList();
}

/**
 * mysql用户表需要导出字段--显示名称对应集合
 */
private void initUserInfoColu() {
    columnTitleMap.put("emp_id", "员工ID");
    columnTitleMap.put("emp_name", "员工名字");
    columnTitleMap.put("emp_age", "员工年龄");

}

/**
 * mysql用户表需要导出字段集
 */
private void initUserInfoTitleKeyList() {
    titleKeyList.add("emp_id");
    titleKeyList.add("emp_name");
    titleKeyList.add("emp_age");
}