java 功能中，有一个功能是大家经常做的，就是excel导出，简单的excel导出 可以直接用阿里的easyExcel添加注解自动导出来某些固定字段就行了，这个是比较简单的导出，本文就不作过多赘述
这篇文章主要是针对，某些页面的导出，比如说按照页面上的表格的样式导出数据 类似于下面图片这样的，主要应用于报表，自定义的样式之类的excel导出 本文导出 总汇，标品，定开三个sheet的数据，有则导出，无则不用导出
导出的excel需要的pom结构
最后导出的请求接口字段
最后导出的内容如下
<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>
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();
}
至此，导出内容完毕
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
