java 实现 excel 自定义样式和字段导出

冷不防 2024-03-17 14:18 67阅读 0赞

java 功能中，有一个功能是大家经常做的，就是excel导出，简单的excel导出 可以直接用阿里的easyExcel添加注解自动导出来某些固定字段就行了，这个是比较简单的导出，本文就不作过多赘述  
这篇文章主要是针对，某些页面的导出，比如说按照页面上的表格的样式导出数据 类似于下面图片这样的，主要应用于报表，自定义的样式之类的excel导出 本文导出 总汇，标品，定开三个sheet的数据，有则导出，无则不用导出  
  
导出的excel需要的pom结构

**最后导出的请求接口字段**   
![b2f7ca8a634941e3bc342b5bcba2abf0.jpeg][]

**最后导出的内容如下**  
![6452477772d641388d1df381d0cbc705.jpeg][]

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.2.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.4</version>
            </dependency>

![ae89cb8e401644c8aa8a0dfb7bc9163a.png][]

**1 第一步 控制层**

/**
         * 根据报价单id导出报表
         *
         * @param response
         * @param id
         */
        @PostMapping("/exportPriceDetail/{id}")
        @ApiOperation(value = "根据报价单id导出报表")
        @SneakyThrows
        public void exportPriceDetail(HttpServletResponse response, @PathVariable("id") Long id, @RequestBody List<ExportMenuReq> menus) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String encodeFileName = URLEncoder.encode("报价单", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodeFileName + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            priceDetailExportService.exportPriceDetail(response, id,menus);
        }

2.第二步 逻辑层  
主要生成水印和处理要导出的，模块的逻辑

@SneakyThrows
        public void exportPriceDetail(HttpServletResponse response, Long id, List<ExportMenuReq> menus) {
            UserDTO user = AuthenticationContext.getUser();
            Watermark watermark = new Watermark();
            watermark.setContent(user.getUserAccount()+"@XX集团"+user.getErpId());
            watermark.setWidth(500);
            watermark.setHeight(200);
            watermark.setYAxis(200);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).inMemory(true).registerWriteHandler(new CustomerWaterMarkHandler(watermark)).build();
            List<String> sheetNames = new ArrayList<>();
            Map<String, List<String>> openContentColumnNames = new HashMap<>();
            Map<String, List<String>> openContentDevelopmentColumnNames = new HashMap<>();
            for (ExportMenuReq menu : menus) {
                sheetNames.add(menu.getName());
                if (CollectionUtils.isNotEmpty(menu.getChildren())) {
                    List<String> columnNames = openContentColumnNames.get(menu.getName()) != null ? openContentColumnNames.get(menu.getName()) : new ArrayList<String>();
                    List<ExportMenuReq> openContents = menu.getChildren();
                    for (ExportMenuReq openContent : openContents) {
                        if (Constants.DEVELOPMENT_INFO.equals(openContent.getName()) && CollectionUtils.isNotEmpty(openContent.getChildren())) {
                            List<ExportMenuReq> developmentColumns = openContent.getChildren();
                            List<String> developmentColumnNames = developmentColumns.stream().map(tree -> tree.getName() + Constants.DEVELOPMENT_SUFFIX).collect(Collectors.toList());
                            columnNames.addAll(developmentColumnNames);
                            openContentDevelopmentColumnNames.put(menu.getName(), developmentColumnNames);
                        } else {
                            columnNames.add(openContent.getName());
                        }
                    }
                    //根据ID获取列，如果用名称遇到名称一样的数据，就会因为key一样导致只有一个列表，并且两个列表value都放入一个key
                    openContentColumnNames.put(menu.getId().toString(), columnNames);
                }
            }
            exportPriceDetail(id, sheetNames, openContentColumnNames, openContentDevelopmentColumnNames, excelWriter);
        }

2.1 writeCollectInfo（）方法，组装总汇的导出数据

private void writeCollectInfo(CollectInfoVo collectInfoVo, ExcelWriter excelWriter) {
            if (collectInfoVo == null) {
                return;
            }
            WriteSheet writeSheet = EasyExcel.writerSheet("总汇").build();
            int tableNo = 0;
            Integer headSize = 9;
            //有效期
            String priceDataInfo = getPriceDataInfo(collectInfoVo.getPriceData(), collectInfoVo.getExpirationDay());
            ContactInfoExcelVO priceDataInfoVO = ContactInfoExcelVO.builder()
                    .contactInfo(priceDataInfo)
                    .build();
            WriteTable priceDataInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.FALSE).registerWriteHandler(new ColumnMergeStrategy(0, headSize - 1)).head(ContactInfoExcelVO.class).build();
            excelWriter.write(Arrays.asList(priceDataInfoVO), writeSheet, priceDataInfoTable);
    
            //标题
            Map<String, String> params = new HashMap<>();
            String priceName = StringUtils.isNotBlank(collectInfoVo.getPriceName()) ? collectInfoVo.getPriceName() : "报价单";
            params.put("priceName", priceName);
            WriteTable priceNameTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new HeadWriteHandler(params)).head(PriceBillBaseExcelVO.class).build();
            excelWriter.write(Collections.emptyList(), writeSheet, priceNameTable);
    
            //联系人信息
            String contactInfo = String.format("联系人: %s\r\n联系方式: %s", StringUtils.trimToEmpty(collectInfoVo.getContactName()), StringUtils.trimToEmpty(collectInfoVo.getMobile()));
            ContactInfoExcelVO contactInfoExcelVO = ContactInfoExcelVO.builder()
                    .contactInfo(contactInfo)
                    .build();
            WriteTable contactInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.FALSE).registerWriteHandler(new ColumnMergeStrategy(0, headSize - 1)).head(ContactInfoExcelVO.class).build();
            excelWriter.write(Arrays.asList(contactInfoExcelVO), writeSheet, contactInfoTable);
    
            //最后三列显示小计
            Integer lastCol = headSize - 5;
            //标品
            StandardInfoVO standardInfo = collectInfoVo.getStandardInfo();
            if (standardInfo != null && CollectionUtils.isNotEmpty(standardInfo.getStandards())) {
                List<StandardVO> standards = standardInfo.getStandards();
                List<StandardExcelVO> standardExcelVOS = standards.stream().map(standInfoConverter::toExcel).collect(Collectors.toList());
                standardExcelVOS.stream().forEach(standard -> {
                    // 设置功能清单
                    if (StringUtils.isNotBlank(standard.getProductListUrl())) {
                        WriteCellData<String> hyperlink = new WriteCellData<>("点击查看");
                        HyperlinkData hyperlinkData = new HyperlinkData();
                        hyperlinkData.setAddress(standard.getProductListUrl());
                        hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
                        hyperlink.setHyperlinkData(hyperlinkData);
                        standard.setProductListUrlHyperlink(hyperlink);
                    }
                });
                WriteTable standardTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).head(StandardExcelVO.class).build();
                excelWriter.write(standardExcelVOS, writeSheet, standardTable);
                //小计
                //最后三列显示小计
                writeSubtotal(excelWriter, writeSheet, standardInfo.getTaxPrice(), standardInfo.getTaxUpperPrice(), standardInfo.getNotTaxTotalPrice(), standardInfo.getNotTaxTotalUpperPrice(), lastCol, tableNo++);
            }
            //第三方产品
            TripartiteCollectVO priceTripartiteInfo = collectInfoVo.getPriceTripartiteInfo();
            if (priceTripartiteInfo != null && priceTripartiteInfo.getTripartiteInfoVOS() != null) {
                List<TripartiteInfoVO> tripartiteInfo = priceTripartiteInfo.getTripartiteInfoVOS();
                List<TripartiteInfoExcelVO> tripartiteInfoExcelList = tripartiteInfo.stream().map(tripartiteInfoConvert::toExcel).collect(Collectors.toList());
                // 设置功能清单
                tripartiteInfoExcelList.stream().forEach(tripartiteInfoExcelVO -> {
                    // 设置功能清单
                    if (StringUtils.isNotBlank(tripartiteInfoExcelVO.getProductListUrl())) {
                        WriteCellData<String> hyperlink = new WriteCellData<>("点击查看");
                        HyperlinkData hyperlinkData = new HyperlinkData();
                        hyperlinkData.setAddress(tripartiteInfoExcelVO.getProductListUrl());
                        hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
                        hyperlink.setHyperlinkData(hyperlinkData);
                        tripartiteInfoExcelVO.setProductListUrlHyperlink(hyperlink);
                    }
                });
    
                WriteTable tripartiteInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE)
                        .head(TripartiteInfoExcelVO.class).build();
                excelWriter.write(tripartiteInfoExcelList, writeSheet, tripartiteInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, priceTripartiteInfo, lastCol, tableNo++);
            }
    
            //定开
            OpeningCollectVO openingInfo = collectInfoVo.getOpeningInfo();
            if (openingInfo != null && CollectionUtils.isNotEmpty(openingInfo.getOpeningVOS())) {
                List<OpeningVO> openingVOS = openingInfo.getOpeningVOS();
                List<OpeningExcelVO> openingExcelVOS = openingVOS.stream().map(openingConverter::toExcel).collect(Collectors.toList());
                WriteTable openingTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(7, 8)).head(OpeningExcelVO.class).build();
                excelWriter.write(openingExcelVOS, writeSheet, openingTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, openingInfo, lastCol, tableNo++);
            }
    
            // 运维管理费
            ServiceInfoCollectVO serviceInfo = collectInfoVo.getServiceInfo();
            if (serviceInfo != null && serviceInfo.getServiceInfoVO() != null) {
                ServiceInfoVO serviceInfoVO = serviceInfo.getServiceInfoVO();
                ServiceInfoExcelVO serviceInfoExcelVO = serviceInfoConvert.toExcel(serviceInfoVO);
                WriteTable serviceInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).head(ServiceInfoExcelVO.class).build();
                excelWriter.write(Arrays.asList(serviceInfoExcelVO), writeSheet, serviceInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, serviceInfo, lastCol, tableNo++);
            }
    
            //服务器费用
            ServerInfoCollectVO serverInfo = collectInfoVo.getServerInfo();
            if (serverInfo != null && CollectionUtils.isNotEmpty(serverInfo.getServerInfoVOS())) {
                List<ServerInfoVO> serverInfos = serverInfo.getServerInfoVOS();
                List<ServerInfoExcelVO> serverInfoExcelVOS = serverInfos.stream().map(serverInfoConvert::toExcel).collect(Collectors.toList());
                WriteTable serverInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).head(ServerInfoExcelVO.class).build();
                excelWriter.write(serverInfoExcelVOS, writeSheet, serverInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, serverInfo, lastCol, tableNo++);
            }
    
            //项目管理费
            ManagementInfoCollectVO managementInfoVO = collectInfoVo.getManagementInfo();
            if (managementInfoVO != null && managementInfoVO.getManagementInfoVO() != null) {
                ManagementInfoVO managementInfo = managementInfoVO.getManagementInfoVO();
                ManagementInfoExcelVO managementInfoExcelVO = managementInfoConvert.toExcel(managementInfo);
                WriteTable managementInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).registerWriteHandler(new ColumnMergeStrategy(2, 3)).head(ManagementInfoExcelVO.class).build();
                excelWriter.write(Arrays.asList(managementInfoExcelVO), writeSheet, managementInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, managementInfoVO, lastCol, tableNo++);
            }
    
    
            //项目驻场费
            ResidentInfoCollectVO residentInfo = collectInfoVo.getResidentInfo();
            if (residentInfo != null && CollectionUtils.isNotEmpty(residentInfo.getResidentInfoVOS())) {
                List<ResidentInfoVO> residentInfos = residentInfo.getResidentInfoVOS();
                List<ResidentInfoExcelVO> residentInfoExcelVOS = residentInfos.stream().map(residentInfoConvert::toExcel).collect(Collectors.toList());
                WriteTable residentInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).head(ResidentInfoExcelVO.class).build();
                excelWriter.write(residentInfoExcelVOS, writeSheet, residentInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, residentInfo, lastCol, tableNo++);
            }
    
    
            //项目差旅费
            TravelChargeInfoCollectVO travelChargeInfo = collectInfoVo.getTravelChargeInfo();
            if (travelChargeInfo != null && CollectionUtils.isNotEmpty(travelChargeInfo.getTravelChargeInfoVOS())) {
                List<TravelChargeInfoVO> travelChargeInfos = travelChargeInfo.getTravelChargeInfoVOS();
                List<TravelChargeInfoExcelVO> travelChargeInfoExcelVOS = travelChargeInfos.stream().map(travelChargeInfoConvert::toExcel).collect(Collectors.toList());
                WriteTable travelChargeInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).head(TravelChargeInfoExcelVO.class).build();
                excelWriter.write(travelChargeInfoExcelVOS, writeSheet, travelChargeInfoTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, travelChargeInfo, lastCol, tableNo++);
            }
            //培训管理费
            TrainModuleCollectVO trainModuleInfo = collectInfoVo.getTrainModuleInfo();
            if (trainModuleInfo != null && CollectionUtils.isNotEmpty(trainModuleInfo.getTrainModuleVOS())) {
                List<TrainModuleVO> trainModuleInfos = trainModuleInfo.getTrainModuleVOS();
                List<TrainModuleExcelVO> trainModuleExcelVOS = trainModuleInfos.stream().map(trainModuleConvert::toExcel).collect(Collectors.toList());
                WriteTable trainModuleTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).registerWriteHandler(new ColumnMergeStrategy(0, 1))
                        .registerWriteHandler(new ColumnMergeStrategy(7, 8)).registerWriteHandler(new ColumnMergeStrategy(2, 3)).head(TrainModuleExcelVO.class).build();
                excelWriter.write(trainModuleExcelVOS, writeSheet, trainModuleTable);
                //小计
                writeSubtotal(excelWriter, writeSheet, trainModuleInfo, lastCol, tableNo++);
            }
            //写空行
            WriteTable newLineInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.FALSE).registerWriteHandler(new ColumnMergeStrategy(0, 8)).head(TravelChargeInfoExcelVO.class).build();
            excelWriter.write(Arrays.asList(""), writeSheet, newLineInfoTable);
    
            //写服务费用
            writeServiceTax(excelWriter, writeSheet, collectInfoVo,tableNo++);
    
            //写空行
            excelWriter.write(Arrays.asList(""), writeSheet, newLineInfoTable);
    
            //写总计
            //计算折后价
            BigDecimal totalDiscountPrice = collectInfoVo.getTaxTotalPrice() != null ? new BigDecimal(collectInfoVo.getTaxTotalPrice()) : new BigDecimal("0");
            if (collectInfoVo.getTaxTotalPrice() != null && collectInfoVo.getPriceDiscount() == null && collectInfoVo.getDiscountTotalPrice() == null) {
                totalDiscountPrice = new BigDecimal(collectInfoVo.getTaxTotalPrice());
            } else if (collectInfoVo.getTaxTotalPrice() != null && collectInfoVo.getPriceDiscount() != null) {
                totalDiscountPrice = new BigDecimal(collectInfoVo.getTaxTotalPrice()).multiply(collectInfoVo.getPriceDiscount()).divide(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
            } else if (collectInfoVo.getDiscountTotalPrice() != null) {
                totalDiscountPrice = new BigDecimal(collectInfoVo.getDiscountTotalPrice());
            }
            String totalUpperDiscountPrice = NumberChineseFormatter.format(totalDiscountPrice.divide(BigDecimal.valueOf(100), 2, BigDecimal.ROUND_HALF_UP).doubleValue(),
                    true,
                    true);
            writeSummaryTotal(excelWriter, writeSheet, collectInfoVo, totalDiscountPrice, totalUpperDiscountPrice, tableNo++);
        }

2.1导出的表格模块比较多，所以实体类也比较多，本文就举一个对象 标品和培训服务 的例子，其他实体类类似

@Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @ApiModel("标品信息")
    @HeadRowHeight(value = 33)
    @ContentRowHeight(value = 30)
    @ColumnWidth(value = 20)
    @HeadStyle(fillBackgroundColor = 64)
    @HeadFontStyle(bold = BooleanEnum.TRUE, fontHeightInPoints = 10, fontName = "微软雅黑")
    @ContentFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, borderTop = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ExcelIgnoreUnannotated
    public class StandardDetailExcelVO {
    
        @ExcelProperty(value = "产品名称",index = 0)
        private String productName;
    
        @ExcelProperty(value = "类型", converter = ProductTypeConvert.class, index = 1)
        private Byte type;
    
        @ExcelProperty(value = "产品单价", index = 2)
        private String  productPriceDes;
    
        @ExcelProperty(value = "产品数量",index = 3)
        private String productNumDes;
    
        @ExcelProperty(value = "产品原总价(元)",converter = MoneyConvert.class,index = 4)
        @NumberFormat("#0.00")
        private Long oldTotalPrice;
    
        @ExcelProperty(value = "产品折后总价(元)",converter = MoneyConvert.class,index = 5)
        @NumberFormat("#0.00")
        private Long currentTotalPrice;
    
        @ExcelProperty(value = "税率", converter = TaxRateConvert.class, index = 6)
        private Double taxRate;
    
        @ExcelProperty(value = "产品功能清单",index = 7)
        @ContentFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑",color = 12)
        private WriteCellData<String> productListUrlHyperlink;
    
        @ExcelProperty(value = "备注",index = 8)
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderTop = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
        private String remark;
    
        /**
         * 产品功能说明
         */
        private String productListUrl;
    
    }

@Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @ApiModel("培训服务")
    @HeadRowHeight(value = 33)
    @ContentRowHeight(value = 30)
    @ColumnWidth(value = 20)
    @HeadStyle(fillBackgroundColor = 27)
    @HeadFontStyle(bold = BooleanEnum.FALSE, fontHeightInPoints = 10, fontName = "微软雅黑")
    @ContentFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, borderTop = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    public class TrainModuleExcelVO {
    
        /**
         * 培训类型
         */
        @ExcelProperty(value = {"培训服务","类型"},index = 0)
        private String type;
    
        @ExcelProperty(value = {"培训服务","类型"},index = 1)
        private String hide1;
    
        /**
         * 培训次数
         */
        @ExcelProperty(value = {"培训服务","培训次数"},index = 2)
        private Double times;
    
        @ExcelProperty(value = {"培训服务","培训次数"},index = 3)
        private String hide2;
    
        /**
         * 费用
         */
        @ExcelProperty(value = {"培训服务","培训单次费用(元/次)"},converter = MoneyConvert.class, index = 4)
        @NumberFormat("#0.00")
        private Long cost;
    
        /**
         * 培训服务费用
         */
        @JsonSerialize(using = MoneySerializer.class)
        @ExcelProperty(value = {"培训服务","培训总费用(元)"},converter = MoneyConvert.class, index = 5)
        @NumberFormat("#0.00")
        private Long totalPrice;
    
        @ExcelProperty(value = {"培训服务", "税率"}, converter = TaxRateConvert.class, index = 6)
        private Double taxRate;
    
        /**
         * 备注
         */
        @ExcelProperty(value = {"培训服务","备注"},index = 7)
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderTop = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
        private String remark;
    
        @ExcelProperty(value = {"培训服务","备注"},index = 8)
        private String remark1;
    }

2.2小计代码快

/**
         * 写入小计
         *
         * @param excelWriter
         * @param writeSheet
         * @param baseVo
         * @param tableNo
         */
        private void writeSubtotal(ExcelWriter excelWriter, WriteSheet writeSheet, CollectBaseVo baseVo, Integer lastCol, Integer tableNo) {
            writeSubtotal(excelWriter, writeSheet, baseVo.getTaxPrice(), baseVo.getTaxUpperPrice(), baseVo.getNotTaxTotalPrice(), baseVo.getNotTaxTotalUpperPrice(), lastCol, tableNo);
        }
    
        /**
         * 写入小计
         *
         * @param excelWriter
         * @param writeSheet
         * @param taxPrice              含税价
         * @param taxUpperPrice         含税价大写
         * @param notTaxTotalPrice      不含税价
         * @param notTaxTotalUpperPrice 不含税价大写
         * @param tableNo
         */
        private void writeSubtotal(ExcelWriter excelWriter, WriteSheet writeSheet, Long taxPrice, String taxUpperPrice, Long notTaxTotalPrice, String notTaxTotalUpperPrice, Integer lastCol, Integer tableNo) {
            String taxPriceStr = String.format("%.2f",new BigDecimal(taxPrice).divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            String notTaxTotalPriceStr = String.format("%.2f",new BigDecimal(notTaxTotalPrice).divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            //不含税价
            SubtotalExcelVO notTaxTotal = SubtotalExcelVO.builder()
                    .name("小计:")
                    .priceType("不含税价")
                    .priceStr("￥"+notTaxTotalPriceStr)
                    .upperPriceStr(notTaxTotalUpperPrice)
                    .build();
            //含税价
            SubtotalExcelVO taxTotal = SubtotalExcelVO.builder()
                    .name("小计")
                    .priceType("含税价")
                    .priceStr("￥"+taxPriceStr)
                    .upperPriceStr(taxUpperPrice)
                    .build();
            TableMergeProperty tableMergeProperty = new TableMergeProperty(0, 1, 0, lastCol);
            WriteTable totalTable = EasyExcel.writerTable()
                    .needHead(Boolean.FALSE)
                    .head(SubtotalExcelVO.class)
                    .tableNo(tableNo)
                    .registerWriteHandler(new SubtotalMergeStrategy(Arrays.asList(tableMergeProperty)))
                    .registerWriteHandler(new ColumnMergeStrategy(7, 8))
                    .registerWriteHandler(new SubtotalCellStyleStrategy())
                    .build();
            excelWriter.write(Arrays.asList(notTaxTotal, taxTotal), writeSheet, totalTable);
        }

2.3写入总合计

private void writeSummaryTotal(ExcelWriter excelWriter, WriteSheet writeSheet, CollectInfoVo collectInfoVo, BigDecimal summaryPrice, String upperSummaryPrice, Integer tableNo) {
            BigDecimal totalNotTaxPrice = collectInfoVo.getNotTaxTotalPrice() != null ? new BigDecimal(collectInfoVo.getNotTaxTotalPrice()) : new BigDecimal("0");
            String notTaxPrice = String.format("%.2f",totalNotTaxPrice.divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            //不含税价
            SummaryExcelVO notTaxSummary = SummaryExcelVO.builder()
                    .priceDetail("报价单描述:" + StringUtils.trimToEmpty(collectInfoVo.getPriceDetail()))
                    .type("总合计:")
                    .priceType("不含税价")
                    .totalPriceStr("￥"+notTaxPrice)
                    .upperTotalPriceStr(collectInfoVo.getNotTaxTotalUpperPrice())
                    .build();
            //含税价
            String priceStr = String.format("%.2f",summaryPrice.divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            SummaryExcelVO taxSummary = SummaryExcelVO.builder()
                    .priceDetail("报价单描述:" + StringUtils.trimToEmpty(collectInfoVo.getPriceDetail()))
                    .type("总合计:")
                    .priceType("含税价")
                    .totalPriceStr("￥"+priceStr)
                    .upperTotalPriceStr(upperSummaryPrice)
                    .build();
    
            TableMergeProperty priceDetailMergeProperty = new TableMergeProperty(0, 1, 0, 3);
            TableMergeProperty typeMergeProperty = new TableMergeProperty(0, 1, 4, 4);
            List<TableMergeProperty> tableMergeProperties = Arrays.asList(priceDetailMergeProperty, typeMergeProperty);
            WriteTable totalTable = EasyExcel.writerTable()
                    .needHead(Boolean.FALSE)
                    .head(SummaryExcelVO.class)
                    .tableNo(tableNo)
                    .registerWriteHandler(new SubtotalCellStyleStrategy())
                    .registerWriteHandler(new ColumnMergeStrategy(7, 8))
                    .registerWriteHandler(new SubtotalMergeStrategy(tableMergeProperties))
                    .build();
            excelWriter.write(Arrays.asList(notTaxSummary, taxSummary), writeSheet, totalTable);
        }

3,导出标品的sheet

/**
         * 导出标品内容
         *
         * @param standardInfo
         * @param excelWriter
         */
        private void writeStandardDetail(StandardInfoVO standardInfo, ExcelWriter excelWriter) {
            if (standardInfo != null && CollectionUtils.isNotEmpty(standardInfo.getStandards())) {
                WriteSheet writeSheet = EasyExcel.writerSheet("标品").build();
                int tableNo = 0;
                if (!standardInfo.getIsExport()) {
                    //有效期
                    String priceDataInfo = getPriceDataInfo(standardInfo.getPriceData(), standardInfo.getExpirationDay());
                    ContactInfoExcelVO priceDataInfoVO = ContactInfoExcelVO.builder()
                            .contactInfo(priceDataInfo)
                            .build();
                    WriteTable priceDataInfoTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.FALSE).registerWriteHandler(new ColumnMergeStrategy(0, 6)).head(ContactInfoExcelVO.class).build();
                    excelWriter.write(Arrays.asList(priceDataInfoVO), writeSheet, priceDataInfoTable);
                }
                List<StandardDetailExcelVO> standardDetailExcelVOS = standardInfo.getStandards().stream()
                        .map(standInfoConverter::toDetailExcel).collect(Collectors.toList());
                standardDetailExcelVOS.stream().forEach(standardDetailExcelVO -> {
                    // 设置功能清单
                    if (StringUtils.isNotBlank(standardDetailExcelVO.getProductListUrl())) {
                        WriteCellData<String> hyperlink = new WriteCellData<>("点击查看");
                        standardDetailExcelVO.setProductListUrlHyperlink(hyperlink);
                        HyperlinkData hyperlinkData = new HyperlinkData();
                        hyperlink.setHyperlinkData(hyperlinkData);
                        hyperlinkData.setAddress(standardDetailExcelVO.getProductListUrl());
                        hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
                    }
                });
                WriteTable writeTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.TRUE).head(StandardDetailExcelVO.class).build();
                excelWriter.write(standardDetailExcelVOS, writeSheet, writeTable);
                //    总计
                //StandardDetailExcelVO total = new StandardDetailExcelVO();
                //total.setProductName("总计:");
                //total.setCurrentTotalPrice(standardInfo.getStandardTotalPrice());
                //writeTotal(excelWriter, writeSheet, total, tableNo++, 0, 3);
                //writeStandardSubtotal(excelWriter,writeSheet,standardInfo.getStandardTotalPrice(),standardInfo.getStandardTotalUpperPrice(),tableNo++);
    
                writeSubtotal(excelWriter, writeSheet, standardInfo.getTaxPrice(), standardInfo.getTaxUpperPrice(), standardInfo.getNotTaxTotalPrice(), standardInfo.getNotTaxTotalUpperPrice(), 4, tableNo++);
    
    
            }
    
        }

4导出定开的内容

@SneakyThrows
        private void writeDynamicOpeningInfos(OpeningInfoVO openingVO, CollectBaseVo baseVo, List<String> columnNames, List<String> developmentColumnNames, ExcelWriter excelWriter) {
            List<ModuleInfoVo> moduleInfos = openingVO.getModuleInfos();
            if (CollectionUtils.isNotEmpty(moduleInfos)) {
                //查询动态表头格式
                PriceOpenContentExcel priceOpenContentExcel = iPriceOpenContentExcelService.selectByPriceOpenId(openingVO.getId());
                List<OpenDetailDevelopmentResult> openDetailDevelopmentResults = JSON.parseArray(priceOpenContentExcel.getDynamicHead(), OpenDetailDevelopmentResult.class);
                //根据动态表头动态生成class
                Map<String, String> params = new HashMap<>();
                Class<? extends OpeningDetailExcelVo> openingDetailExcelVoClazz = assembleTemplateClass(openDetailDevelopmentResults, params);
    
                List<OpeningDetailExcelVo> openingDetailExcelVos = moduleInfos.stream().map(openingConverter::toDetailExcel).collect(Collectors.toList());
                List<? extends OpeningDetailExcelVo> detailExcelVos = BeanUtil.copyToList(openingDetailExcelVos, openingDetailExcelVoClazz);
                assembleDynamicFieldsAndCalculateTotal(detailExcelVos, openingDetailExcelVoClazz);
                //获取排除的列
                List<Field> excludeColumnField = getExcludeColumnFields(openingDetailExcelVoClazz, columnNames);
                //获取合并单元格并根据排除列重新计算合并单元格坐标
                ExcelMergeStrategy excelMergeStrategy = calculateMergeProperty(priceOpenContentExcel, excludeColumnField);
                List<String> excludeColumnFieldNames = excludeColumnField.stream().map(Field::getName).collect(Collectors.toList());
                WriteSheet writeSheet = EasyExcel.writerSheet(openingVO.getOpeningName()).registerWriteHandler(new HeadWriteHandler(params)).excludeColumnFieldNames(excludeColumnFieldNames).head(openingDetailExcelVoClazz).build();
                int tableNo = 0;
                WriteTable writeTable = EasyExcel.writerTable().tableNo(tableNo++).needHead(Boolean.FALSE).registerWriteHandler(excelMergeStrategy).build();
                excelWriter.write(detailExcelVos, writeSheet, writeTable);
                //总列数-研发项列数-备注一列
                //Integer lastCol = CollectionUtils.size(columnNames) - CollectionUtils.size(developmentColumnNames) - (excludeColumnFieldNames.contains("remark")?0:1) - 1;
                if (CollectionUtils.size(columnNames) - 4 >= 0) {
                    //小计
                    writeDynamicHeadSubtotal(excelWriter, writeSheet, baseVo, CollectionUtils.size(columnNames), CollectionUtils.size(columnNames) - 4, tableNo++);
                }
            }
        }

4.1导出定开的小计

private void writeDynamicHeadSubtotal(ExcelWriter excelWriter, WriteSheet writeSheet, CollectBaseVo baseVo, Integer headSize, Integer lastCol, Integer tableNo) {
    
            Long taxPrice = baseVo.getTaxPrice();
            String taxPriceStr = String.format("%.2f",new BigDecimal(taxPrice).divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            Long notTaxTotalPrice = baseVo.getNotTaxTotalPrice();
            String notTaxTotalPriceStr = String.format("%.2f",new BigDecimal(notTaxTotalPrice).divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
            List<List<String>> headList = ListUtils.newArrayList();
            List<String> head = ListUtils.newArrayList();
            for (int i = 0; i <= lastCol; i++) {
                head.add("小计:");
            }
            head.add("金额类型");
            head.add("金额");
            head.add("大写金额");
            headList.add(head);
            List<List<String>> dataList = ListUtils.newArrayList();
            List<String> notTaxTotal = ListUtils.newArrayList();
            for (int i = 0; i <= lastCol; i++) {
                notTaxTotal.add("小计:");
            }
            notTaxTotal.add("不含税价");
            notTaxTotal.add("￥"+notTaxTotalPriceStr);
            notTaxTotal.add(baseVo.getNotTaxTotalUpperPrice());
            dataList.add(notTaxTotal);
            List<String> taxTotal = ListUtils.newArrayList();
            for (int i = 0; i <= lastCol; i++) {
                taxTotal.add("小计:");
            }
            taxTotal.add("含税价");
            taxTotal.add("￥"+taxPriceStr);
            taxTotal.add(baseVo.getTaxUpperPrice());
            dataList.add(taxTotal);
            TableMergeProperty tableMergeProperty = new TableMergeProperty(0, 1, 0, lastCol);
    
            SubtotalRepeatMergeStrategy subtotalRepeatMergeStrategy = new SubtotalRepeatMergeStrategy(Arrays.asList(tableMergeProperty));
            WriteTable totalTable = EasyExcel.writerTable()
                    .needHead(Boolean.FALSE)
                    .head(headList)
                    .tableNo(tableNo)
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 33, (short) 30))
                    .registerWriteHandler(subtotalRepeatMergeStrategy)
                    .registerWriteHandler(new SubtotalCellStyleStrategy())
                    .build();
            excelWriter.write(dataList, writeSheet, totalTable);
            subtotalRepeatMergeStrategy.setFirstRow();
        }

至此，导出内容完毕

[b2f7ca8a634941e3bc342b5bcba2abf0.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/785ba4852b9e44e595b63d1cc389113a.png
[6452477772d641388d1df381d0cbc705.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6a7e0953072a4b09bd7b2dd6568fd1ab.png
[ae89cb8e401644c8aa8a0dfb7bc9163a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/24878e311b0a47748ce1507ae8c875c2.png