Java使用EsayExcel导出模板，涉及多个sheet

怼烎@ 2024-03-17 19:56 17阅读 0赞

#### 需求：利用excel模板导出，有两个sheet，其中一个sheet涉及固定字段导出和多数   据(List)导出 ####

#### 1、模板 ####

sheet1：

![f5623b0122224fbda8b26477501a255e.png][]

sheet2：

![af7ad72bb27b4c82a76e4f038e02d16e.png][]

#### 2、主要代码 ####

public static void outputExcelForTunnelMonitor(HttpServletResponse response, EngineerTunnelMonitorExcelVo engineerTunnelMonitor){
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + "问题清单" + ".xlsx");
            // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
            // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
    
            //获取文件路径
            String url="excelMode/周边.xlsx";
            if(null!=engineerTunnelMonitor.getTestPart() && 1==engineerTunnelMonitor.getTestPart()) {
                url="excelMode/拱顶.xlsx";
            }
            ClassPathResource resource= new ClassPathResource(url);
            String templateFileName = null;
            try {
                templateFileName = resource.getFile().getPath();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
    
            ExcelWriter excelWriter = null;
            try {
                excelWriter = EasyExcel.write(response.getOutputStream())
                        .withTemplate(templateFileName)
                        .build();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"量测记录表").build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            //sheet1数据(list)
            excelWriter.fill(engineerTunnelMonitor.getEngineerTunnelMonitorRecordList(), fillConfig, writeSheet);
    
            //sheet1(表头固定数据)
            Map<String, Object> map = new HashMap<>();
            map.clear();
            map.put("pointName", engineerTunnelMonitor.getPointName());
            map.put("constructionName", engineerTunnelMonitor.getConstructionName());
            map.put("wallRockLevel", engineerTunnelMonitor.getWallRockLevel());
            map.put("fractureSurfaceMileage", engineerTunnelMonitor.getFractureSurfaceMileage());
            map.put("buildTime", engineerTunnelMonitor.getBuildTime());
            map.put("constructMethod", engineerTunnelMonitor.getConstructMethod());
            map.put("observationMethod", engineerTunnelMonitor.getObservationMethod());
            map.put("testCode", engineerTunnelMonitor.getTestCode());
            excelWriter.fill(map, writeSheet);
    
            //sheet2固定数据
            Map<String, Object> map2 = new HashMap<>();
            EngineerTunnelMonitorAnalyseExcelVo engineerTunnelMonitorAnalyse = engineerTunnelMonitor.getEngineerTunnelMonitorAnalyse();
            map2.clear();
            map2.put("name", engineerTunnelMonitorAnalyse.getName());
            map2.put("constructionName", engineerTunnelMonitorAnalyse.getConstructionName());
            map2.put("fractureSurfaceMileage", engineerTunnelMonitorAnalyse.getFractureSurfaceMileage());
            map2.put("testPart", engineerTunnelMonitorAnalyse.getTestPart());
            map2.put("wallRockLevel", engineerTunnelMonitorAnalyse.getWallRockLevel());
            map2.put("testCode", engineerTunnelMonitorAnalyse.getTestCode());
            map2.put("firstTime", engineerTunnelMonitorAnalyse.getFirstTime());
            map2.put("lastTime", engineerTunnelMonitorAnalyse.getLastTime());
            map2.put("dataTaskTime", engineerTunnelMonitorAnalyse.getDataTaskTime());
            map2.put("regressionEquation", engineerTunnelMonitorAnalyse.getRegressionEquation());
            map2.put("coefficientAssociation", engineerTunnelMonitorAnalyse.getCoefficientAssociation());
            map2.put("displacementLevel", engineerTunnelMonitorAnalyse.getDisplacementLevel());
            map2.put("rate", engineerTunnelMonitorAnalyse.getRate());
            map2.put("conclusion", engineerTunnelMonitorAnalyse.getConclusion());
            excelWriter.fill(map2, EasyExcel.writerSheet(1,"回归分析表").build());
    
            excelWriter.finish();
        }

[f5623b0122224fbda8b26477501a255e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b4fadf3621b9494bbe08ba6b59c0cacb.png
[af7ad72bb27b4c82a76e4f038e02d16e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/031542d70c83466d8874c63f08ff1faf.png