java實(shí)現(xiàn)excel自定義樣式與字段導(dǎo)出詳細(xì)圖文教程
更新時(shí)間：2023年09月26日 11:04:03 作者：qq_40415205
最近接到一個(gè)需求,客戶不滿意原本導(dǎo)出的csv文件,想要導(dǎo)出Excel文件,下面這篇文章主要給大家介紹了關(guān)于java實(shí)現(xiàn)excel自定義樣式與字段導(dǎo)出詳細(xì)圖文教程
前言
java 功能中，有一個(gè)功能是大家經(jīng)常做的，就是excel導(dǎo)出，簡(jiǎn)單的excel導(dǎo)出 可以直接用阿里的easyExcel添加注解自動(dòng)導(dǎo)出來某些固定字段就行了，這個(gè)是比較簡(jiǎn)單的導(dǎo)出，本文就不作過多贅述 這篇文章主要是針對(duì)，某些頁面的導(dǎo)出，比如說按照頁面上的表格的樣式導(dǎo)出數(shù)據(jù) 類似于下面圖片這樣的，主要應(yīng)用于報(bào)表，自定義的樣式之類的excel導(dǎo)出 本文導(dǎo)出 總匯，標(biāo)品，定開三個(gè)sheet的數(shù)據(jù)，有則導(dǎo)出，無則不用導(dǎo)出導(dǎo)出的excel需要的pom結(jié)構(gòu)
最后導(dǎo)出的請(qǐng)求接口字段
最后導(dǎo)出的內(nèi)容如下
<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、第一步 控制層
/**
* 根據(jù)報(bào)價(jià)單id導(dǎo)出報(bào)表
*
* @param response
* @param id
*/
@PostMapping("/exportPriceDetail/{id}")
@ApiOperation(value = "根據(jù)報(bào)價(jià)單id導(dǎo)出報(bào)表")
@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("報(bào)價(jià)單", "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、第二步 邏輯層
主要生成水印和處理要導(dǎo)出的，模塊的邏輯
@SneakyThrows
public void exportPriceDetail(HttpServletResponse response, Long id, List<ExportMenuReq> menus) {
UserDTO user = AuthenticationContext.getUser();
Watermark watermark = new Watermark();
watermark.setContent(user.getUserAccount()+"@XX集團(tuán)"+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());
}
}
//根據(jù)ID獲取列，如果用名稱遇到名稱一樣的數(shù)據(jù)，就會(huì)因?yàn)閗ey一樣導(dǎo)致只有一個(gè)列表，并且兩個(gè)列表value都放入一個(gè)key
openContentColumnNames.put(menu.getId().toString(), columnNames);
}
}
exportPriceDetail(id, sheetNames, openContentColumnNames, openContentDevelopmentColumnNames, excelWriter);
}2.1：writeCollectInfo（）方法，組裝總匯的導(dǎo)出數(shù)據(jù)
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);
//標(biāo)題
Map<String, String> params = new HashMap<>();
String priceName = StringUtils.isNotBlank(collectInfoVo.getPriceName()) ? collectInfoVo.getPriceName() : "報(bào)價(jià)單";
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);
//聯(lián)系人信息
String contactInfo = String.format("聯(lián)系人: %s\r\n聯(liá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);
//最后三列顯示小計(jì)
Integer lastCol = headSize - 5;
//標(biāo)品
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 -> {
// 設(shè)置功能清單
if (StringUtils.isNotBlank(standard.getProductListUrl())) {
WriteCellData<String> hyperlink = new WriteCellData<>("點(diǎn)擊查看");
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);
//小計(jì)
//最后三列顯示小計(jì)
writeSubtotal(excelWriter, writeSheet, standardInfo.getTaxPrice(), standardInfo.getTaxUpperPrice(), standardInfo.getNotTaxTotalPrice(), standardInfo.getNotTaxTotalUpperPrice(), lastCol, tableNo++);
}
//第三方產(chǎn)品
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());
// 設(shè)置功能清單
tripartiteInfoExcelList.stream().forEach(tripartiteInfoExcelVO -> {
// 設(shè)置功能清單
if (StringUtils.isNotBlank(tripartiteInfoExcelVO.getProductListUrl())) {
WriteCellData<String> hyperlink = new WriteCellData<>("點(diǎn)擊查看");
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);
//小計(jì)
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, openingInfo, lastCol, tableNo++);
}
// 運(yùn)維管理費(fèi)
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, serviceInfo, lastCol, tableNo++);
}
//服務(wù)器費(fèi)用
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, serverInfo, lastCol, tableNo++);
}
//項(xiàng)目管理費(fèi)
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, managementInfoVO, lastCol, tableNo++);
}
//項(xiàng)目駐場(chǎng)費(fèi)
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, residentInfo, lastCol, tableNo++);
}
//項(xiàng)目差旅費(fèi)
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);
//小計(jì)
writeSubtotal(excelWriter, writeSheet, travelChargeInfo, lastCol, tableNo++);
}
//培訓(xùn)管理費(fèi)
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);
//小計(jì)
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);
//寫服務(wù)費(fèi)用
writeServiceTax(excelWriter, writeSheet, collectInfoVo,tableNo++);
//寫空行
excelWriter.write(Arrays.asList(""), writeSheet, newLineInfoTable);
//寫總計(jì)
//計(jì)算折后價(jià)
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導(dǎo)出的表格模塊比較多，所以實(shí)體類也比較多，本文就舉一個(gè)對(duì)象 標(biāo)品和培訓(xùn)服務(wù) 的例子，其他實(shí)體類類似
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("標(biāo)品信息")
@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 = "產(chǎn)品名稱",index = 0)
private String productName;
@ExcelProperty(value = "類型", converter = ProductTypeConvert.class, index = 1)
private Byte type;
@ExcelProperty(value = "產(chǎn)品單價(jià)", index = 2)
private String productPriceDes;
@ExcelProperty(value = "產(chǎn)品數(shù)量",index = 3)
private String productNumDes;
@ExcelProperty(value = "產(chǎn)品原總價(jià)(元)",converter = MoneyConvert.class,index = 4)
@NumberFormat("#0.00")
private Long oldTotalPrice;
@ExcelProperty(value = "產(chǎn)品折后總價(jià)(元)",converter = MoneyConvert.class,index = 5)
@NumberFormat("#0.00")
private Long currentTotalPrice;
@ExcelProperty(value = "稅率", converter = TaxRateConvert.class, index = 6)
private Double taxRate;
@ExcelProperty(value = "產(chǎn)品功能清單",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;
/**
* 產(chǎn)品功能說明
*/
private String productListUrl;
}@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("培訓(xùn)服務(wù)")
@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 {
/**
* 培訓(xùn)類型
*/
@ExcelProperty(value = {"培訓(xùn)服務(wù)","類型"},index = 0)
private String type;
@ExcelProperty(value = {"培訓(xùn)服務(wù)","類型"},index = 1)
private String hide1;
/**
* 培訓(xùn)次數(shù)
*/
@ExcelProperty(value = {"培訓(xùn)服務(wù)","培訓(xùn)次數(shù)"},index = 2)
private Double times;
@ExcelProperty(value = {"培訓(xùn)服務(wù)","培訓(xùn)次數(shù)"},index = 3)
private String hide2;
/**
* 費(fèi)用
*/
@ExcelProperty(value = {"培訓(xùn)服務(wù)","培訓(xùn)單次費(fèi)用(元/次)"},converter = MoneyConvert.class, index = 4)
@NumberFormat("#0.00")
private Long cost;
/**
* 培訓(xùn)服務(wù)費(fèi)用
*/
@JsonSerialize(using = MoneySerializer.class)
@ExcelProperty(value = {"培訓(xùn)服務(wù)","培訓(xùn)總費(fèi)用(元)"},converter = MoneyConvert.class, index = 5)
@NumberFormat("#0.00")
private Long totalPrice;
@ExcelProperty(value = {"培訓(xùn)服務(wù)", "稅率"}, converter = TaxRateConvert.class, index = 6)
private Double taxRate;
/**
* 備注
*/
@ExcelProperty(value = {"培訓(xùn)服務(wù)","備注"},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 = {"培訓(xùn)服務(wù)","備注"},index = 8)
private String remark1;
}2.2：小計(jì)代碼快
/**
* 寫入小計(jì)
*
* @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);
}
/**
* 寫入小計(jì)
*
* @param excelWriter
* @param writeSheet
* @param taxPrice 含稅價(jià)
* @param taxUpperPrice 含稅價(jià)大寫
* @param notTaxTotalPrice 不含稅價(jià)
* @param notTaxTotalUpperPrice 不含稅價(jià)大寫
* @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));
//不含稅價(jià)
SubtotalExcelVO notTaxTotal = SubtotalExcelVO.builder()
.name("小計(jì):")
.priceType("不含稅價(jià)")
.priceStr("￥"+notTaxTotalPriceStr)
.upperPriceStr(notTaxTotalUpperPrice)
.build();
//含稅價(jià)
SubtotalExcelVO taxTotal = SubtotalExcelVO.builder()
.name("小計(jì)")
.priceType("含稅價(jià)")
.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：寫入總合計(jì)
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));
//不含稅價(jià)
SummaryExcelVO notTaxSummary = SummaryExcelVO.builder()
.priceDetail("報(bào)價(jià)單描述:" + StringUtils.trimToEmpty(collectInfoVo.getPriceDetail()))
.type("總合計(jì):")
.priceType("不含稅價(jià)")
.totalPriceStr("￥"+notTaxPrice)
.upperTotalPriceStr(collectInfoVo.getNotTaxTotalUpperPrice())
.build();
//含稅價(jià)
String priceStr = String.format("%.2f",summaryPrice.divide(new BigDecimal(100), 2, BigDecimal.ROUND_HALF_UP));
SummaryExcelVO taxSummary = SummaryExcelVO.builder()
.priceDetail("報(bào)價(jià)單描述:" + StringUtils.trimToEmpty(collectInfoVo.getPriceDetail()))
.type("總合計(jì):")
.priceType("含稅價(jià)")
.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、導(dǎo)出標(biāo)品的sheet
/**
* 導(dǎo)出標(biāo)品內(nèi)容
*
* @param standardInfo
* @param excelWriter
*/
private void writeStandardDetail(StandardInfoVO standardInfo, ExcelWriter excelWriter) {
if (standardInfo != null && CollectionUtils.isNotEmpty(standardInfo.getStandards())) {
WriteSheet writeSheet = EasyExcel.writerSheet("標(biāo)品").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 -> {
// 設(shè)置功能清單
if (StringUtils.isNotBlank(standardDetailExcelVO.getProductListUrl())) {
WriteCellData<String> hyperlink = new WriteCellData<>("點(diǎn)擊查看");
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);
// 總計(jì)
//StandardDetailExcelVO total = new StandardDetailExcelVO();
//total.setProductName("總計(jì):");
//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、導(dǎo)出定開的內(nèi)容
@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)) {
//查詢動(dòng)態(tài)表頭格式
PriceOpenContentExcel priceOpenContentExcel = iPriceOpenContentExcelService.selectByPriceOpenId(openingVO.getId());
List<OpenDetailDevelopmentResult> openDetailDevelopmentResults = JSON.parseArray(priceOpenContentExcel.getDynamicHead(), OpenDetailDevelopmentResult.class);
//根據(jù)動(dòng)態(tài)表頭動(dòng)態(tài)生成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);
//獲取合并單元格并根據(jù)排除列重新計(jì)算合并單元格坐標(biāo)
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);
//總列數(shù)-研發(fā)項(xiàng)列數(shù)-備注一列
//Integer lastCol = CollectionUtils.size(columnNames) - CollectionUtils.size(developmentColumnNames) - (excludeColumnFieldNames.contains("remark")?0:1) - 1;
if (CollectionUtils.size(columnNames) - 4 >= 0) {
//小計(jì)
writeDynamicHeadSubtotal(excelWriter, writeSheet, baseVo, CollectionUtils.size(columnNames), CollectionUtils.size(columnNames) - 4, tableNo++);
}
}
}
4.1：導(dǎo)出定開的小計(jì)
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("小計(jì):");
}
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("小計(jì):");
}
notTaxTotal.add("不含稅價(jià)");
notTaxTotal.add("￥"+notTaxTotalPriceStr);
notTaxTotal.add(baseVo.getNotTaxTotalUpperPrice());
dataList.add(notTaxTotal);
List<String> taxTotal = ListUtils.newArrayList();
for (int i = 0; i <= lastCol; i++) {
taxTotal.add("小計(jì):");
}
taxTotal.add("含稅價(jià)");
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();
}至此，導(dǎo)出內(nèi)容完畢
總結(jié)
到此這篇關(guān)于java實(shí)現(xiàn)excel自定義樣式與字段導(dǎo)出的文章就介紹到這了,更多相關(guān)java實(shí)現(xiàn)excel自定義樣式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
使用java自帶des加密算法實(shí)現(xiàn)文件加密和字符串加密
這篇文章主要介紹了使用java自帶des加密算法實(shí)現(xiàn)文件加密和字符串加密的示例,需要的朋友可以參考下2014-03-03
JFreeChart簡(jiǎn)單實(shí)現(xiàn)光滑曲線繪制
這篇文章主要為大家詳細(xì)介紹了JFreeChart簡(jiǎn)單實(shí)現(xiàn)光滑曲線的繪制，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-06-06
這篇文章主要介紹了Java中的內(nèi)存模型JMM詳細(xì)解讀,Java?對(duì)內(nèi)存的抽象模型如下,每個(gè)線程都有一塊自己的私有內(nèi)存(也稱為工作內(nèi)存),當(dāng)線程使用變量時(shí),會(huì)把主內(nèi)存里面的變量復(fù)制到工作內(nèi)存,線程讀寫變量時(shí)操作的是自己工作內(nèi)存中的變量,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了Mybatis-Plus實(shí)現(xiàn)批量新增與批量更新以及出現(xiàn)的問題，文章中有詳細(xì)的代碼示例，感興趣的同學(xué)可以參考一下2023-04-04
本文主要是教大家如何將GraphicsMagick命令行工具集成到Springboot項(xiàng)目中，便可以使用Java進(jìn)行圖片處理相關(guān)開發(fā)。2021-05-05
Jackson忽略字段實(shí)現(xiàn)對(duì)字段進(jìn)行序列化和反序列化
在使用?Jackson?進(jìn)行序列化和反序列化時(shí),有時(shí)候需要對(duì)某些字段進(jìn)行過濾,以便在?JSON?數(shù)據(jù)中不包含某些敏感信息,下面就一起來了解一下Jackson忽略字段實(shí)現(xiàn)對(duì)字段進(jìn)行序列化和反序2023-10-10
這篇文章主要為大家詳細(xì)介紹了struts2如何自定義MVC框架，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-10-10
這篇文章主要介紹了java 并發(fā)中的原子性與可視性實(shí)例詳解的相關(guān)資料,原子性是說一個(gè)操作是否可分割。可見性是說操作結(jié)果其他線程是否可見。需要的朋友可以參考下2017-07-07
詳解Java TCC分布式事務(wù)實(shí)現(xiàn)原理
這篇文章主要介紹了詳解Java TCC分布式事務(wù)實(shí)現(xiàn)原理，對(duì)分布式事務(wù)感興趣的同學(xué)，一定要看一下2021-04-04
SpringBoot整合JWT實(shí)戰(zhàn)教程
JWT（JSON?Web?Token）是一種用于身份驗(yàn)證和授權(quán)的開放標(biāo)準(zhǔn)（RFC?7519），它使用JSON格式傳輸信息，可以在不同系統(tǒng)之間安全地傳遞數(shù)據(jù)，這篇文章主要介紹了SpringBoot整合JWT實(shí)戰(zhàn)教程,需要的朋友可以參考下2023-06-06