动态列的excel导出

动态列的excel导出

由于上次写的动态列的excel导入时挖下的坑。

上一篇在这里：动态列的excel导入

所以说这个咱们挖的坑，就必须咱们来填了它。
于是这次我们来写动态列的导出吧。

先看效果

表结构

---

表结构是一对多的，非动态列的部分是一，动态部分是多，如果你是单纯的列表转列可以不关注主表，只关注多表的部分，道理都是一样的。
（图中的表结构与测试效果图不一致，给个示例，达到效果能理解就行了）

---

依赖

我们这里依然使用的是easyexcel来做导出

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

接下来看代码

---

在parseFactorList()方法中将多表的属性名称转成多列表头名称的列表
重点在于 .registerWriteHandler(new DynamicMergeStrategy(excelVoList,factorList)) ,使用自定义的合并策略,画出需要导出的excel的样式

---

@Override
    public void exportList(HttpServletResponse response, ListDto dto) throws IOException, NoSuchFieldException, IllegalAccessException {
        List<UniDto> excelVoList = uniDao.listDto(dto);

        if(CollectionUtil.isEmpty(excelVoList)) {
            return;
        }

		// 将多表的属性名称转成多列表头名称的列表
        List<String> factorList = parseFactorList(excelVoList);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=全部数据.xlsx");

        EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new DynamicMergeStrategy(excelVoList,factorList)) // 使用自定义的合并策略,画出需要导出的excel的样式
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("运单列表")
                .doWrite(factorList);
    }

	private <T> List<String> parseFactorList(List<T> excelVoList) throws IllegalAccessException, NoSuchFieldException {
        Set<String> set = new TreeSet<>();
        for (T t : excelVoList) {
            Class<?> clazz = UniPmsPriceDto.class;
            Field factorListField = clazz.getDeclaredField("factorList");
            factorListField.setAccessible(true);

            List<UniDtl> factorList = (List<UniDtl>) factorListField.get(t);
            if(CollectionUtil.isEmpty(factorList)) {
                continue;
            }

            for (UniDtl uniDtl : factorList) {
                set.add(uniDtl.getPriceFactor());
            }
        }

        return new ArrayList<>(set);
    }

自定义合并策略(重点)

---

先用 titleHandle() 方法,将标题画出来,

for (int i = 0; i < factorList.size(); i++) {
            Cell celli = row0.createCell(25 + i);
            celli.setCellValue(factorList.get(i));
            celli.setCellStyle(cellStyle);
            setCellBorder(celli, BorderStyle.THIN, IndexedColors.BLACK.getIndex());
        }

在这段代码中,将前面获取到的标题名称列表遍历设置成标题

在使用 valueHandle() 方法,将数据填充到excel中

下面是完整代码⬇

---

public class PmsPriceSeaDynamicMergeStrategy implements RowWriteHandler {
    private List<UniPmsPriceSeaDto> excelList;
    private List<String> factorList;

    public PmsPriceSeaDynamicMergeStrategy(List<UniPmsPriceSeaDto> excelList, List<String> factorList) {
        this.excelList = excelList;
        this.factorList = factorList;
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        Sheet sheet = writeSheetHolder.getSheet();
        Row row0 = sheet.createRow(0);
        titleHandle(sheet,row0,factorList);

        for (int i = 0; i < excelList.size(); i++) {
            UniPmsPriceSeaDto uniPmsPriceSeaDto = excelList.get(i);
            Row rowValue = sheet.createRow(i + 1);

            valueHandle(uniPmsPriceSeaDto, rowValue, factorList);
        }
    }

    /**
     * 创建标题
     * @param sheet
     * @param row0
     * @param factorList
     */
    private void titleHandle(Sheet sheet, Row row0, List<String> factorList) {
        Workbook workbook = sheet.getWorkbook();
		// 创建字体
        Font font = workbook.createFont();
        font.setBold(true); // 加粗
        font.setFontHeightInPoints((short)14); // 字体高度（大小）

        CellStyle cellStyle = workbook.createCellStyle();
        // 内容居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font); // 设置字体

        // 设置单元格的背景颜色为灰色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式为实心

        Cell cell19 = row0.createCell(20);
        cell19.setCellValue("备注");
        sheet.setColumnWidth(20, 6000);
        cell19.setCellStyle(cellStyle);
        // 设置边框
        setCellBorder(cell19, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

        Cell cell20 = row0.createCell(21);
        cell20.setCellValue("创建人");
        cell20.setCellStyle(cellStyle);
        sheet.setColumnWidth(21, 5000);
        setCellBorder(cell20, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

        Cell cell21 = row0.createCell(22);
        cell21.setCellValue("创建时间");
        cell21.setCellStyle(cellStyle);
        sheet.setColumnWidth(22, 6000);
        setCellBorder(cell21, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

        Cell cell22 = row0.createCell(23);
        cell22.setCellValue("更新人");
        cell22.setCellStyle(cellStyle);
        sheet.setColumnWidth(23, 5000);
        setCellBorder(cell22, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

        Cell cell23 = row0.createCell(24);
        cell23.setCellValue("更新时间");
        cell23.setCellStyle(cellStyle);
        sheet.setColumnWidth(24, 6000);
        setCellBorder(cell23, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

		// 设置动态列标题列
        for (int i = 0; i < factorList.size(); i++) {
            Cell celli = row0.createCell(25 + i);
            celli.setCellValue(factorList.get(i));
            celli.setCellStyle(cellStyle);
            setCellBorder(celli, BorderStyle.THIN, IndexedColors.BLACK.getIndex());
        }
    }

    // 设置单元格边框
    public static void setCellBorder(Cell cell, BorderStyle borderStyle, short borderColor) {
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setBorderTop(borderStyle);
        cellStyle.setBorderRight(borderStyle);
        cellStyle.setBorderBottom(borderStyle);
        cellStyle.setBorderLeft(borderStyle);

        cellStyle.setTopBorderColor(borderColor);
        cellStyle.setRightBorderColor(borderColor);
        cellStyle.setBottomBorderColor(borderColor);
        cellStyle.setLeftBorderColor(borderColor);

        cell.setCellStyle(cellStyle);
    }
    
    private void valueHandle(UniPmsPriceSeaDto uniPmsPriceSeaDto, Row rowValue, List<String> factorList) {
     
        Cell cell19 = rowValue.createCell(20);
        cell19.setCellValue(uniPmsPriceSeaDto.getRemarks());

        Cell cell20 = rowValue.createCell(21);
        cell20.setCellValue(uniPmsPriceSeaDto.getInsertedBy());

        Cell cell21 = rowValue.createCell(22);
        cell21.setCellValue(uniPmsPriceSeaDto.getInsertTime());

        Cell cell22 = rowValue.createCell(23);
        cell22.setCellValue(uniPmsPriceSeaDto.getUpdatedBy());

        Cell cell23 = rowValue.createCell(24);
        cell23.setCellValue(uniPmsPriceSeaDto.getUpdateTime());

        List<UniPmsPriceDtl> factorListValue = uniPmsPriceSeaDto.getFactorList();
        if(CollectionUtil.isEmpty(factorListValue)) {
            return;
        }

        Map<String, BigDecimal> factorMap = factorListValue.stream().collect(Collectors.toMap(item -> item.getPriceFactor(), UniPmsPriceDtl::getAmtCostPrice));
        
        // 填充动态列的值
        for (int i = 0; i < factorList.size(); i++) {
            String factor = factorList.get(i);
            BigDecimal amt = DecimalUtils.getDecimal(factorMap.get(factor));

            Cell celli = rowValue.createCell(25 + i);
            celli.setCellValue(DecimalUtils.eqZero(amt) ? "" : String.valueOf(amt));
        }

    }
}

下一篇我们来填上一篇挖合并单元格和样式的坑

（相别容易见时难，别后相思独凄然，千山万水总是情，点个关注行不行）