java使用easyexcel+poi实现excel模板多sheet页导出

         使用easyexcel+poi实现excel模板，动态sheet页导出需求。

一、使用的依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.1</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.0</version>
</dependency>

二、需求

        sheet0页需求是导出的数据为固定的样式。sheet1页的需求是导出多条数据。基于此需求，考虑的是用easyexcel模版导出的方式实现此需求较为简单。

sheet0页样式

sheet1页样式

 三、模版的设计

easyexcel官方文档

 四、问题点

         在多公司的情况下，第二页需要动态多sheet页，比如两个公司的就需要两个sheet1页，并导出每个公司下的人员详情。因为easyexcel不支持动态sheet页，要是重写导出又比较复杂，综合考虑使用poi+easyexcel一起实现多公司的导出。

 五、功能的实现

        导出模版在项目中的位置

导出excel实例代码

import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * @author 小红花
 * @version 1.0
 * @date 2023/12/28
 * @description mytest
 */
@RestController
@RequestMapping("test")
@Slf4j
public class MyTestController {
 
 
    @PostMapping(value = "export")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        long startTime = System.currentTimeMillis();
        System.out.println("导出开始时间:" + startTime + "ms");
 
        //设置输出流格式以及文件名：
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("计提表-导出", "UTF-8");
 
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
 
        ExcelWriter excelWriter = null;
        InputStream stream = null;
        ByteArrayOutputStream baos = null;
        try {
            //1.找出模板文件，并转化为输入流;
            ClassPathResource classPathResource = new ClassPathResource("export/test/jitinew.xlsx");
            stream = classPathResource.getStream();
            baos = new ByteArrayOutputStream();
            OutputStream outputStream = response.getOutputStream();
            //2.数据准备
            //sheet0页数据
            Map<String, Object> sheet0Data = new HashMap<>();
            sheet0Data.put("deptName", "测试部门");
            sheet0Data.put("settlementCycle", "2023-12");
            sheet0Data.put("contractName", "测试合同名称");
            sheet0Data.put("contractNum", "12345678");
            sheet0Data.put("predictMoneyWithoutRate", "20000.00");
            sheet0Data.put("predictMoney", "21000.00");
            sheet0Data.put("attendanceDays", "21");
            sheet0Data.put("companyNames", "测试供应商");
 
            //动态sheet页数据
            List<Map<String, Object>> sheet1Data = new ArrayList<>();
            for (int i = 0; i < 2; i++) {
                BigDecimal serveDaysAll = BigDecimal.ZERO;
                BigDecimal usrServicePriceAll = BigDecimal.ZERO;
                BigDecimal coefficientAssessPriceAll = BigDecimal.ZERO;
                BigDecimal predictPriceWithoutRateAll = BigDecimal.ZERO;
                BigDecimal predictPriceAll = BigDecimal.ZERO;
 
                //准备sheet1页集合数据
                List<Map<String, Object>> sheetListData = new ArrayList<>();
                for (int j = 0; j < 5; j++) {
                    Map<String, Object> sheetList = new HashMap<>();
                    BigDecimal serveDays = new BigDecimal(21);
                    BigDecimal usrServicePrice = new BigDecimal(10000);
                    BigDecimal coefficientAssessPrice = new BigDecimal(20000);
                    BigDecimal predictPriceWithoutRate = new BigDecimal(30000);
                    BigDecimal predictPrice = new BigDecimal(31000);
 
                    sheetList.put("month", "2023-12");
                    sheetList.put("realName", "测试人员" + i + j);
                    sheetList.put("personNum", "1000" + i + j);
                    sheetList.put("deptName", "测试部门");
                    sheetList.put("projName", "测试项目");
                    sheetList.put("serveName", "测试");
                    sheetList.put("levelName", "中级");
                    sheetList.put("serveDays", serveDays);
                    sheetList.put("usrServicePrice", usrServicePrice);
                    sheetList.put("coefficientAssessPrice", coefficientAssessPrice);
                    sheetList.put("predictPriceWithoutRate", predictPriceWithoutRate);
                    sheetList.put("predictPrice", predictPrice);
 
                    serveDaysAll = NumberUtil.add(serveDaysAll, serveDays);
                    usrServicePriceAll = NumberUtil.add(usrServicePriceAll, usrServicePrice);
                    coefficientAssessPriceAll = NumberUtil.add(coefficientAssessPriceAll, coefficientAssessPrice);
                    predictPriceWithoutRateAll = NumberUtil.add(predictPriceWithoutRateAll, predictPriceWithoutRate);
                    predictPriceAll = NumberUtil.add(predictPriceAll, predictPrice);
 
                    sheetListData.add(sheetList);
                }
 
                //准备sheet1页基础数据
                Map<String, Object> sheet1Map = new HashMap<>();
                sheet1Map.put("title", "测试sheet" + i);
                sheet1Map.put("companyName", "测试公司" + i);
                sheet1Map.put("deptName", "2023-12");
                sheet1Map.put("serveDaysAll", serveDaysAll);
                sheet1Map.put("usrServicePriceAll", usrServicePriceAll);
                sheet1Map.put("coefficientAssessPriceAll", coefficientAssessPriceAll);
                sheet1Map.put("predictPriceWithoutRateAll", predictPriceWithoutRateAll);
                sheet1Map.put("predictPriceAll", predictPriceAll);
 
                sheet1Map.put("sheetListData", sheetListData);
                sheet1Data.add(sheet1Map);
            }
 
            //3.根据sheet1集合长度，复制模版sheet1页
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            for (int i = 1; i < sheet1Data.size(); i++) {
                //开始复制sheet1页 ,新的sheet以sheet2开始。（复制sheet页名字不可重复）
                workbook.cloneSheet(1, "sheet" + (i + 1));
            }
            //把workbook写到流里
            workbook.write(baos);
            byte[] bytes = baos.toByteArray();
            stream = new ByteArrayInputStream(bytes);
            excelWriter = EasyExcel.write(outputStream).withTemplate(stream).build();
 
            //4.填充模版数据
            //写入sheet0
            WriteSheet sheetDataWrite = EasyExcel.writerSheet(0).build();
            excelWriter.fill(sheet0Data, sheetDataWrite);
 
            //写入动态sheet页基础数据
            for (int i = 0; i < sheet1Data.size(); i++) {
                //获取sheeti写入对象
                sheetDataWrite = EasyExcel.writerSheet(i + 1).build();
                //获取数据
                Map<String, Object> sheet1Map = sheet1Data.get(i);
                List<Map<String, Object>> sheetListData = (List<Map<String, Object>>) sheet1Map.get("sheetListData");
                //写入excel
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter.fill(sheetListData, fillConfig, sheetDataWrite);
                excelWriter.fill(sheet1Map, sheetDataWrite);
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("code", 500);
            map.put("message", "下载文件失败:" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        } finally {
            //关流
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (baos != null) {
                baos.close();
            }
            if (stream != null) {
                stream.close();
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println("导出结束时间:" + endTime + "ms");
        System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
    }
}

 实现效果如图