package com.dy.pipIrrGlobal.excel;
|
|
import com.alibaba.excel.metadata.Head;
|
import com.alibaba.excel.metadata.data.WriteCellData;
|
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
|
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
|
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
|
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
|
import com.alibaba.excel.write.metadata.style.WriteFont;
|
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
|
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
|
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
|
import org.apache.poi.xssf.usermodel.XSSFColor;
|
import org.springframework.util.PropertyPlaceholderHelper;
|
|
import java.util.List;
|
import java.util.Objects;
|
import java.util.Properties;
|
|
/**
|
* @author ZhuBaoMin
|
* @date 2024-08-26 14:18
|
* @LastEditTime 2024-08-26 14:18
|
* @Description
|
*/
|
|
public class ReportExportCellWriteHandler extends AbstractVerticalCellStyleStrategy {
|
private String title;
|
|
PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${", "}");
|
|
public ReportExportCellWriteHandler(String title) {
|
this.title = title;
|
}
|
|
// 动态导入标题
|
@Override
|
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
|
Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
|
if (head != null) {
|
List<String> headNameList = head.getHeadNameList();
|
if (CollectionUtils.isNotEmpty(headNameList)) {
|
Properties properties = new Properties();
|
properties.setProperty("title", title);
|
headNameList.replaceAll(value -> propertyPlaceholderHelper.replacePlaceholders(value, properties));
|
}
|
}
|
}
|
|
// 设置标题样式，包含字体、背景
|
@Override
|
protected void setHeadCellStyle(CellWriteHandlerContext context) {
|
// 获取和创建CellStyle
|
WriteCellData<?> cellData = context.getFirstCellData();
|
CellStyle originCellStyle = cellData.getOriginCellStyle();
|
Cell cell = context.getCell();
|
|
if (Objects.isNull(originCellStyle)) {
|
originCellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
|
}
|
|
((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap()));
|
|
// 设置指定列背景色
|
//if(cell.getColumnIndex() == 10
|
//) {
|
// ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(237, 237, 237), new DefaultIndexedColorMap()));
|
//}
|
|
// 设置背景颜色
|
originCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
// 由于在FillStyleCellWriteHandler，会把OriginCellStyle和WriteCellStyle合并，会以WriteCellStyle样式为主，所有必须把WriteCellStyle设置的背景色清空
|
// 具体合并规则看WriteWorkbookHolder.createCellStyle方法
|
WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
|
writeCellStyle.setFillForegroundColor(null);
|
// 必须设置OriginCellStyle
|
cellData.setOriginCellStyle(originCellStyle);
|
|
WriteFont headWriteFont = new WriteFont();
|
if (cell.getRowIndex() == 0) {
|
headWriteFont.setFontHeightInPoints((short)14);
|
headWriteFont.setFontName("Arial");
|
headWriteFont.setBold(true);
|
}else if (cell.getRowIndex() == 1) {
|
headWriteFont.setFontHeightInPoints((short)12);
|
headWriteFont.setFontName("Arial");
|
headWriteFont.setBold(false);
|
}
|
|
cellData.getWriteCellStyle().setWriteFont(headWriteFont);
|
}
|
|
// 设置内容公示、内容字体样式、背景样式
|
@Override
|
protected void setContentCellStyle(CellWriteHandlerContext context) {
|
|
// 获取和创建CellStyle
|
WriteCellData<?> cellData = context.getFirstCellData();
|
CellStyle originCellStyle = cellData.getOriginCellStyle();
|
Cell cell = context.getCell();
|
|
if (Objects.isNull(originCellStyle)) {
|
originCellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
|
}
|
// 设置背景颜色
|
((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 192, 0), new DefaultIndexedColorMap()));
|
//System.out.println("Go to row: " + context.getCell().getRowIndex() + ", column: " + context.getCell().getColumnIndex());
|
|
if (cell.getRowIndex() >= 2){
|
((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(237, 237, 237), new DefaultIndexedColorMap()));
|
// 票面总价
|
//if (cell.getColumnIndex() == 10){
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("I" + actualCellRowNum +"+J" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "票面总价写入公式完成");
|
//}else if (cell.getColumnIndex() == 12){
|
// // 支付票款
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("K" + actualCellRowNum +"-L" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "支付票款写入公式完成");
|
//}else if (cell.getColumnIndex() == 15){
|
// // 支付总额
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("M" + actualCellRowNum +"+N" + actualCellRowNum +"+O" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "支付总额写入公式完成");
|
//}else if (cell.getColumnIndex() == 17){
|
// // 实际利润
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("L" + actualCellRowNum +"+Q" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "实际利润写入公式完成");
|
//}else if (cell.getColumnIndex() == 18){
|
// // 应收款
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("P" + actualCellRowNum +"+R" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "应收款写入公式完成");
|
//}else if (cell.getColumnIndex() == 20){
|
// // 未收款
|
// int actualCellRowNum = context.getCell().getRowIndex() + 1;
|
// context.getCell().setCellFormula("S" + actualCellRowNum +"-T" + actualCellRowNum);
|
// System.out.println("第" + context.getCell().getRowIndex() + "行,第" + context.getCell().getColumnIndex() + "未收款写入公式完成");
|
//}else if (cell.getColumnIndex() >= 2){
|
// ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(189, 215, 238), new DefaultIndexedColorMap()));
|
//}else if (cell.getColumnIndex() < 2){
|
// ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 192, 0), new DefaultIndexedColorMap()));
|
//}
|
}
|
|
originCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
originCellStyle.setBorderLeft(BorderStyle.THIN); //左边框线
|
originCellStyle.setBorderTop(BorderStyle.THIN); //顶部框线
|
originCellStyle.setBorderRight(BorderStyle.THIN); //右边框线
|
originCellStyle.setBorderBottom(BorderStyle.THIN); //底部框线
|
// 由于在FillStyleCellWriteHandler，会把OriginCellStyle和WriteCellStyle合并，会以WriteCellStyle样式为主，所有必须把WriteCellStyle设置的背景色清空
|
// 具体合并规则看WriteWorkbookHolder.createCellStyle方法
|
WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
|
writeCellStyle.setFillForegroundColor(null);
|
writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
|
writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
// 必须设置OriginCellStyle
|
cellData.setOriginCellStyle(originCellStyle);
|
|
WriteFont headWriteFont = new WriteFont();
|
headWriteFont.setFontHeightInPoints((short)12);
|
headWriteFont.setFontName("宋体");
|
headWriteFont.setBold(false);
|
headWriteFont.setColor(IndexedColors.BLACK.getIndex());
|
|
cellData.getWriteCellStyle().setWriteFont(headWriteFont);
|
}
|
}
|
