EasyPoi模板导出（横向导出）

前言

有时会出现列表中动态列的情况，导出时就需要横向导出了。

一、引入包

引入easypoi的相关包

二、代码实现

代码如下（示例）：

@Override
    public void export(RawMaterialMonthConst rawMaterialMonthConst, HttpServletRequest request, HttpServletResponse response) {
        String fileName = "";
        String templatePath = "";
        List<Map<String, Object>> list = Lists.newArrayList();
        if(StringUtils.isNotBlank(rawMaterialMonthConst.getExportType())){
            // 原材料消耗  使用id
            if(StringUtils.equals("1", rawMaterialMonthConst.getExportType())){
                list = findRawMonthConstData(rawMaterialMonthConst);
                fileName = "原材耗用.xlsx";
                templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator +  "month/exportRawCost.xlsx";
            }
            // 消耗按强度 使用强度id
            if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
                list = findYfMonthConstList(rawMaterialMonthConst);
                fileName = "研发耗用.xlsx";
                templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator +  "month/exportYfCost.xlsx";
            }
            // 消耗按配比 使用配比id
            if(StringUtils.equals("3", rawMaterialMonthConst.getExportType())){
                list = findNailMonthConstList(rawMaterialMonthConst);
                fileName = "甲供材耗用.xlsx";
                templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator +  "month/exportNailCost.xlsx";
            }
            if(CollectionUtil.isNotEmpty(list)){
                int num = 1;
                for(Map<String, Object> le : list){
                    le.put("numIndex", num);
                    num++;
                }
            }
            StudyTopicSetting setting = new StudyTopicSetting();
            setting.setCompanyId(rawMaterialMonthConst.getCompanyId());
            List<StudyTopicSetting> cloumns  = findCloumnData(setting);

            // 添加合计金额、累计金额
            StudyTopicSetting set = new StudyTopicSetting();
            set.setTopicName("合计金额");
            cloumns.add(set);
            StudyTopicSetting set2 = new StudyTopicSetting();
            set2.setTopicName("累计金额");
            cloumns.add(set2);
            StudyTopicSetting set3 = new StudyTopicSetting();
            set3.setTopicName("备注");
            cloumns.add(set3);

            exportCommon(rawMaterialMonthConst, list, fileName, templatePath, response, cloumns);
        }
    }

    public void exportCommon(RawMaterialMonthConst rawMaterialMonthConst, List<Map<String, Object>> list, String fileName, String templatePath, HttpServletResponse response, List<StudyTopicSetting> cloumns){
        String companyIdHg = baseAPI.translateDict("sys_dept_id", "1");
        String companyIdWy = baseAPI.translateDict("sys_dept_id", "2");
        if(StringUtils.isBlank(companyIdHg)){
            throw new JeecgBootException("请联系管理员配置sys_dept_id字典");
        }
        if(StringUtils.isBlank(companyIdWy)){
            throw new JeecgBootException("请联系管理员配置sys_dept_id字典");
        }
        String name = "";
        if(StringUtils.equals(companyIdHg, rawMaterialMonthConst.getCompanyId())){
            name = "测试1";
        }
        if(StringUtils.equals(companyIdWy, rawMaterialMonthConst.getCompanyId())){
            name = "测试2";
        }

       // 定义导出内容
        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("name", name);
        paramMap.put("startTime", rawMaterialMonthConst.getStartTime());
        paramMap.put("endTime", rawMaterialMonthConst.getEndTime());
        paramMap.put("time", DateUtil.format(DateUtil.parse(rawMaterialMonthConst.getEndTime()),"yyyy年MM月"));
        if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
        	//设置导出的表头列
            paramMap.put("cloumns", cloumns);
            if(CollectionUtil.isNotEmpty(cloumns)){
                int num = 1;
                for(StudyTopicSetting omap : cloumns){
                // 设置列对应的数据，列表查询的数据 为key1 key2......keyn  这里设置值 导表格上就会显示t.key1......
                    omap.setMaterialName("t.key"+num);
                    // 由于动态列在固定列的中间显示 而动态列直接添加的话 会覆盖掉后边的固定列 所以改成往后拼接的方式拼接固定列
                    if(StringUtils.equals("合计金额", omap.getTopicName())){
                        omap.setMaterialName("t.sum");
                    }
                    if(StringUtils.equals("累计金额", omap.getTopicName())){
                        omap.setMaterialName("t.total");
                    }
                    if(StringUtils.equals("备注", omap.getTopicName())){
                        omap.setMaterialName("t.remark");
                    }
                    num++;
                }
            }
        }
        if(StringUtils.equals("3", rawMaterialMonthConst.getExportType())){
            Map map = getStatic(rawMaterialMonthConst);
            paramMap.put("monthSlSum",map.get("monthSlSum"));
            paramMap.put("monthAmountSum",map.get("monthAmountSum"));
            paramMap.put("slSum",map.get("slSum"));
            paramMap.put("amountSum",map.get("amountSum"));
        }
        paramMap.put("list", list);
        // 增加一个空的list防止模板空指针异常
        if(paramMap.get("list") == null){
            ArrayList<String> emptyList = new ArrayList<>();
            emptyList.add("");
            paramMap.put("list",emptyList);
        }
        File folder = new File(upLoadPath + File.separator + TEMPLE_URL + File.separator + "month");
        // 文件夹不存在则创建文件夹
        if(!folder.exists()){
            folder.mkdirs();
        }
        // 文件不存在 则联系管理员添加导出模板
        File file = new File(templatePath);
        if(!file.exists()){
            throw new JeecgBootException("导出模板不存在，请联系管理员在【" + templatePath + "】路径添加导出模板！");
        }
        try {
            TemplateExportParams exportParams = new TemplateExportParams(templatePath);
            exportParams.setColForEach(true);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, paramMap);
            int[] relationColl = new int[]{1};
            Map<Integer,int[]> mergeMap = new HashMap<>();
            mergeMap.put(1, relationColl);
            PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0),mergeMap,1);
            // 自定义标题和时间 - 研发耗用
            // 由于动态列 标题写死不能动态的合并单元格，所以下边内容为设置标题的样式 及根据动态列数自动合并单元格
            if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
                CellStyle headStyle = workbook.createCellStyle();
                Font headFont = workbook.createFont();
                headFont.setFontHeightInPoints((short) 12);
                headFont.setBold(true);
                headStyle.setFont(headFont);
                headStyle.setAlignment(HorizontalAlignment.CENTER);
                headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                Sheet sheet = workbook.getSheetAt(0);
                // 定义表头行
                Row headRow = sheet.createRow(0);
                headRow.setHeightInPoints(30);

                // 创建表头单元格
                Cell headCell = headRow.createCell(0);
                headCell.setCellValue(DateUtil.format(DateUtil.parse(rawMaterialMonthConst.getEndTime()),"yyyy年MM月")+"生产材料研发耗用表");
                headCell.setCellStyle(headStyle);

                // 合并表头单元格
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2 + cloumns.size());
                sheet.addMergedRegion(region);

                // 设置表头单元格边框
                RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

                // 第二标题

                Sheet sheet2 = workbook.getSheetAt(0);
                CellStyle rowStyle = workbook.createCellStyle();
                Font rowFont = workbook.createFont();
                rowFont.setFontHeightInPoints((short) 12);
                rowFont.setBold(false);
                rowStyle.setFont(rowFont);
                rowStyle.setAlignment(HorizontalAlignment.CENTER);
                rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//                rowStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                rowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                // 开始到结束时间
                Row timeRow = sheet2.createRow(1);
                timeRow.setHeightInPoints(30);

                Cell timeCell = timeRow.createCell(2 + cloumns.size());
                timeCell.setCellValue(rawMaterialMonthConst.getStartTime() + " - " + rawMaterialMonthConst.getEndTime());
                timeCell.setCellStyle(rowStyle);
                rowStyle.setBorderRight(BorderStyle.THIN);

                Cell titleCell = timeRow.createCell(0);
                titleCell.setCellValue("编制单位：测试-"+name+"项目部");
                titleCell.setCellStyle(rowStyle);

                CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 3);
                sheet2.addMergedRegion(region2);

                RegionUtil.setBorderTop(BorderStyle.THIN, region2, sheet2);
                RegionUtil.setBorderBottom(BorderStyle.THIN, region2, sheet2);
                RegionUtil.setBorderLeft(BorderStyle.THIN, region2, sheet2);
                RegionUtil.setBorderRight(BorderStyle.THIN, region2, sheet2);

            }
            response.addHeader("filename", URLEncoder.encode(fileName,"utf-8"));
            response.addHeader("Access-Control-Expose-Headers","filename");
            response.setHeader("Content-disposition", "attachment; fileName=" + URLEncoder.encode(fileName, "utf-8"));
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }