java 功能中，有一个功能是大家经常做的，就是excel导出，简单的excel导出 可以直接用阿里的easyExcel添加注解自动导出来某些固定字段就行了，这个是比较简单的导出，本文就不作过多赘述 这篇文章主要是针对，某些页面的导出，
java 功能中，有一个功能是大家经常做的，就是excel导出，简单的excel导出 可以直接用阿里的easyExcel添加注解自动导出来某些固定字段就行了，这个是比较简单的导出，本文就不作过多赘述
这篇文章主要是针对，某些页面的导出，比如说按照页面上的表格的样式导出数据 类似于下面图片这样的，主要应用于报表，自定义的样式之类的excel导出 本文导出 总汇，标品，定开三个sheet的数据，有则导出，无则不用导出
导出的excel需要的pom结构
最后导出的请求接口字段 
最后导出的内容如下
com.alibaba easyexcel 3.2.1 org.apache.poi ooxml-schemas 1.4 
1 第一步 控制层
@PostMapping("/exportPriceDetail/{id}") @apiOperation(value = "根据报价单id导出报表") @SneakyThrows public void exportPriceDetail(httpservletResponse response, @PathVariable("id") Long id, @RequestBody List 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); } 第二步 逻辑层
主要生成水印和处理要导出的，模块的逻辑
@SneakyThrows public void exportPriceDetail(HttpServletResponse response, Long id, List 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 sheetNames = new ArrayList<>(); Map> openContentColumnNames = new HashMap<>(); Map> openContentDevelopmentColumnNames = new HashMap<>(); for (ExportMenuReq menu : menus) { sheetNames.add(menu.getName()); if (CollectionUtils.isNotEmpty(menu.getChildren())) { List columnNames = openContentColumnNames.get(menu.getName()) != null ? openContentColumnNames.get(menu.getName()) : new ArrayList(); List openContents = menu.getChildren(); for (ExportMenuReq openContent : openContents) { if (Constants.DEVELOPMENT_INFO.equals(openContent.getName()) && CollectionUtils.isNotEmpty(openContent.getChildren())) { List developmentColumns = openContent.getChildren(); List 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 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 standards = standardInfo.getStandards(); List standardExcelVOS = standards.stream().map(standInfoConverter::toExcel).collect(Collectors.toList()); standardExcelVOS.stream().forEach(standard -> { // 设置功能清单 if (StringUtils.isNotBlank(standard.getProductListUrl())) { WriteCellData 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 tripartiteInfo = priceTripartiteInfo.getTripartiteInfoVOS(); List tripartiteInfoExcelList = tripartiteInfo.stream().map(tripartiteInfoConvert::toExcel).collect(Collectors.toList()); // 设置功能清单 tripartiteInfoExcelList.stream().forEach(tripartiteInfoExcelVO -> { // 设置功能清单 if (StringUtils.isNotBlank(tripartiteInfoExcelVO.getProductListUrl())) { WriteCellData 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 openingVOS = openingInfo.getOpeningVOS(); List 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 serverInfos = serverInfo.getServerInfoVOS(); List 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 residentInfos = residentInfo.getResidentInfoVOS(); List 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 travelChargeInfos = travelChargeInfo.getTravelChargeInfoVOS(); List 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 trainModuleInfos = trainModuleInfo.getTrainModuleVOS(); List 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)@ExcelIgnoreUnannotatedpublic 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 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小计代码快 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); } 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 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 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 standardDetailExcelVOS = standardInfo.getStandards().stream() .map(standInfoConverter::toDetailExcel).collect(Collectors.toList()); standardDetailExcelVOS.stream().forEach(standardDetailExcelVO -> { // 设置功能清单 if (StringUtils.isNotBlank(standardDetailExcelVO.getProductListUrl())) { WriteCellData 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 columnNames, List developmentColumnNames, ExcelWriter excelWriter) { List moduleInfos = openingVO.getModuleInfos(); if (CollectionUtils.isNotEmpty(moduleInfos)) { //查询动态表头格式 PriceOpenContentExcel priceOpenContentExcel = iPriceOpenContentExcelService.selectByPriceOpenId(openingVO.getId()); List openDetailDevelopmentResults = jsON.parseArray(priceOpenContentExcel.getDynamicHead(), OpenDetailDevelopmentResult.class); //根据动态表头动态生成class Map params = new HashMap<>(); Class openingDetailExcelVoClazz = assembleTemplateClass(openDetailDevelopmentResults, params); List openingDetailExcelVos = moduleInfos.stream().map(openingConverter::toDetailExcel).collect(Collectors.toList()); List detailExcelVos = BeanUtil.copyToList(openingDetailExcelVos, openingDetailExcelVoClazz); assembleDynamicFieldsAndCalculateTotal(detailExcelVos, openingDetailExcelVoClazz); //获取排除的列 List excludeColumnField = getExcludeColumnFields(openingDetailExcelVoClazz, columnNames); //获取合并单元格并根据排除列重新计算合并单元格坐标 ExcelMergeStrategy excelMergeStrategy = calculateMergeProperty(priceOpenContentExcel, excludeColumnField); List 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> headList = ListUtils.newArrayList(); List head = ListUtils.newArrayList(); for (int i = 0; i <= lastCol; i++) { head.add("小计:"); } head.add("金额类型"); head.add("金额"); head.add("大写金额"); headList.add(head); List> dataList = ListUtils.newArrayList(); List 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 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(); } 至此，导出内容完毕 来源地址：https://blog.csdn.net/qq_40415205/article/details/131514939
--结束END--
本文标题: java 实现 excel 自定义样式和字段导出
本文链接: https://lsjlt.com/news/408181.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-04-01
2024-04-03
2024-04-03
2024-01-21
2024-01-21
2024-01-21
2024-01-21
2023-12-23
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0