ExcelExportUtil基于模板导出excel&POI导出excel

1.使用ExcelExportUtil基于模板导出excel数据，listToExcel

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.2.0</version>
</dependency>

第一步，导入依赖没啥好说的；

第二步，查询数据库或es数据组装成list对象；

第三步，填充Map<String, Object> excelMap = new HashMap<>();

数据key和excel模板对应，如果一次导出多个，可以用index区分；

public void ExcelExport(HttpServletResponse response,String params) {
        //excel参数
        Map<String, Object> excelMap = new HashMap<>();
        //resources下的doc包下的这个尾缀为xlsx的模板
        TemplateExportParams params = new TemplateExportParams(
                "doc/模板名称.xlsx", true);

        Workbook workBook = null;
        try {
            excelMap.put("startDate", "2022-01-01");
            excelMap.put("endDate", "2022-02-01");

            List<String> list = new ArrayList<String>(Arrays.asList("1","2"));//嗯 没错 这里就是要导出的数据
            excelMap.put("list", list);
            workBook = ExcelExportUtil.exportExcel(params, excelMap);
            response.flushBuffer();
            workBook.write(response.getOutputStream());
            response.setHeader("content-Type", "application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("模板名称.xlsx", "UTF-8"));
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("导出异常");
        }finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2.基于org.apache.poi的XSSFWorkbook导出

第一步，导入依赖

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

第二步，上图哈哈

 public void ExcelExport(HttpServletResponse response,String params) {
        Map<String,String> map = new HashMap<>();
        map.put("name","张三");
        map.put("age","1");
        map.put("adre","地址");
        List<Map<String,String>> list = new ArrayList<>();
        list.add(map);
        // 创建工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = workbook.createSheet("居民职工结算申报明细单");
        //第一行合并
        CellRangeAddress region1 = new CellRangeAddress(0, 0, 0,13);

        //第二行合并
        CellRangeAddress region2 = new CellRangeAddress(1, 1, 0,6);
        CellRangeAddress region21 = new CellRangeAddress(1, 1, 7,13);
        //第三行合并
        CellRangeAddress region3 = new CellRangeAddress(2, 2, 0,6);

        sheet.addMergedRegion(region1);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region21);
        sheet.addMergedRegion(region3);

        //创建第一行
        XSSFRow row0 = sheet.createRow(0);
        XSSFCell cell_00=row0.createCell(0);
        XSSFFont font = workbook.createFont();
        //字体大小
        font.setFontHeightInPoints((short)20);
        // 字体加粗
        font.setBold(true);
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 设置水平对齐的样式为居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 在样式用应用设置的字体
        style.setFont(font);
        cell_00.setCellStyle(style);
        cell_00.setCellValue("第一行一般是标题，这里是标题的值");

        //创建第二行
        XSSFRow row1 = sheet.createRow(1);
        //第一列
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setAlignment(HorizontalAlignment.LEFT);
        style1.setVerticalAlignment(VerticalAlignment.CENTER);
        initExcelCell(row1,style1,"第二行第一列的值",0);
        //第二列

        XSSFCellStyle style1_1 = workbook.createCellStyle();
        style1_1.setAlignment(HorizontalAlignment.RIGHT);
        style1_1.setVerticalAlignment(VerticalAlignment.CENTER);
        String startDate = "2022-01-01";
        String endDate = "2022-02-01";
        String reportDate = "日期：" + startDate.substring(0, 4) + "年" + startDate.substring(5, 7) + "月" + startDate.substring(8) + "日至" + endDate.substring(0, 4) + "年" + endDate.substring(5, 7) + "月" + endDate.substring(8) + "日";
        initExcelCell(row1, style1_1, reportDate, 7);

        //创建第三行
        XSSFRow row2 = sheet.createRow(2);
        //第一列
        initExcelCell(row2,style1,"企业名称:",0);
        //第二列
        initExcelCell(row2,style1,"单位：元",13);

        //创建第四行 表头
        XSSFRow row3 = sheet.createRow(3);
        XSSFCellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);

        List<String> cellList = Arrays.asList("0", "1", "2", "3", "4", "5", "6", "7",
                "8", "9", "10", "11", "12", "13");
        //初始化列
        for (int i = 0; i < cellList.size(); i++) {
            initExcelCell(row3, style2, cellList.get(i), i);
        }

        //用于底部数据填充找到行数
        int j=0;
        for(int i=0;i<list.size();i++){
            Map r=list.get(i);
            XSSFRow rown = sheet.createRow(4+i);

            //序号
            initExcelCell(rown, style2, i + 1 + "", 0);
            //姓名
            initExcelCell(rown, style2, r.get("name").toString(), 1);
            //年龄
            initExcelCell(rown, style2, r.get("age").toString(), 2);
            //地址
            initExcelCell(rown, style2, r.get("adree").toString(), 3);
            j++;
        }

        j+=4;
        //合计列
        CellRangeAddress regionx = new CellRangeAddress(j, j, 0,9);
        XSSFRow rowx = sheet.createRow(j);
        initExcelCell(rowx,style2,"合     计",0);
        //给合并单元格加边框
        setRegionBorder(BorderStyle.THIN, regionx,sheet);
        sheet.addMergedRegion(regionx);
        j++;
        //最后一行
        CellRangeAddress regionl = new CellRangeAddress(j, j, 0, 3);
        CellRangeAddress regionl1 = new CellRangeAddress(j, j, 4, 6);
        CellRangeAddress regionl2 = new CellRangeAddress(j, j, 7, 9);
        CellRangeAddress regionl3 = new CellRangeAddress(j, j, 10, 13);

        sheet.addMergedRegion(regionl);
        sheet.addMergedRegion(regionl1);
        sheet.addMergedRegion(regionl2);
        sheet.addMergedRegion(regionl3);
        XSSFCellStyle style3 = workbook.createCellStyle();
        style3.setAlignment(HorizontalAlignment.LEFT);
        style3.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFRow rowx1 = sheet.createRow(j);
        //制表日期
        initExcelCell(rowx1, style3, "制表日期： " + DateUtils.format(new Date()), 0);
        //制表人
        initExcelCell(rowx1,style3,"制表人：",4);
        sheet.setDefaultRowHeight((short)450);
        sheet.setColumnWidth(2, 12*256);
        sheet.setColumnWidth(3, 20*256);
        try {
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + java.net.URLEncoder.encode("jm.xlsx", "UTF-8"));
        } catch (Exception e) {
            e.printStackTrace();
            response.setContentType(MediaType.APPLICATION_JSON_VALUE);
            throw new RuntimeException("导出明细异常");
        }
    }

    private void initExcelCell(XSSFRow row, XSSFCellStyle cellStyle, String cellValue,int index) {
        XSSFCell cell = row.createCell(index);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    private  void setRegionBorder(BorderStyle border, CellRangeAddress region, Sheet sheet){
        RegionUtil.setBorderBottom(border,region, sheet);
        RegionUtil.setBorderLeft(border,region, sheet);
        RegionUtil.setBorderRight(border,region, sheet);
        RegionUtil.setBorderTop(border,region, sheet);
    }

就是这么简单 没了