easyExcel自定义单元格样式

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.Test;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import lombok.Data;

/**
*@author:think_toomuch
*@Time:2022-10-31 19:51:06
*/
public class TestEasyExcel {
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty(value = "*姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "学校", index = 2)
        private String school;
    }

    /**
     * 测试导出模板
     * 1. 标题指定某列标红色字段
     * 2. 标题指定某列加批注
     */
    @Test
    public void testExport1() throws FileNotFoundException {
        // 输出流
        OutputStream outputStream = new FileOutputStream(new File("F:\\test.xlsx"));
        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        // 指定标红色的列
        List<Integer> columns = Arrays.asList(0, 1);
        // 指定批注
        HashMap<Integer, String> annotationsMap = new HashMap<>();
        annotationsMap.put(0,"第一列标题批注");
        annotationsMap.put(1,"第二列标题批注");
        TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap);
        EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
    }
    public static void main(String [] args) throws FileNotFoundException {
    	TestEasyExcel testEasyExcel=new TestEasyExcel();
    	testEasyExcel.testExport1();
    }
}

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
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.chinacreator.bdg.standard.service.utils.LogOutUtil;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.util.HashMap;
import java.util.List;

/**
 * @author:think_toomuch
 * @Time:2022-10-31 17:41:26
 */
public class TitleHandler implements CellWriteHandler {
	private static final LogOutUtil LOGGER = new LogOutUtil(TitleHandler.class);

	// 操作列
	private List<Integer> columnIndexs;
	// 颜色
	private Short colorIndex;
	// 批注<列的下标,批注内容>
	private HashMap<Integer, String> annotationsMap;

	public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap) {
		this.columnIndexs = columnIndexs;
		this.colorIndex = colorIndex;
		this.annotationsMap = annotationsMap;
	}

	public TitleHandler(List<Integer> columnIndexs, Short colorIndex) {
		this.columnIndexs = columnIndexs;
		this.colorIndex = colorIndex;
	}

	@Override
	public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
			Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
	}

	@Override
	public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
			Head head, Integer relativeRowIndex, Boolean isHead) {
	}

	@Override
	public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
		if (isHead) {
			// 设置列宽
			Sheet sheet = writeSheetHolder.getSheet();
//            sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
//            writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
			Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
			Drawing<?> drawing = sheet.createDrawingPatriarch();
//
//			// 设置标题字体样式
//			WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//			WriteFont headWriteFont = new WriteFont();
//			headWriteFont.setFontName("宋体");
//			headWriteFont.setFontHeightInPoints((short) 14);
//			headWriteFont.setBold(true);
//			if (CollectionUtils.isNotEmpty(columnIndexs) && colorIndex != null
//					&& columnIndexs.contains(cell.getColumnIndex())) {
//				// 设置字体颜色
//				System.err.println("设置颜色" + colorIndex);
//				headWriteFont.setColor(colorIndex);
//			}
//			headWriteCellStyle.setWriteFont(headWriteFont);
//			headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//			CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
//			cell.setCellStyle(cellStyle);
			// 设置字体富文本格式
			// xlsx格式，如果是老版本格式的话就用 HSSFRichTextString
			XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
			Font font = workbook.createFont();
			font.setColor(Font.COLOR_RED);
			// 从哪到哪，你想设置成什么样的字体都行startIndex，endIndex
			richString.applyFont(0, 1, font);
			// 再设置回每个单元格里
			cell.setCellValue(richString);
			if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
				// 批注内容
				String context = annotationsMap.get(cell.getColumnIndex());
				// 创建绘图对象
				Comment comment = drawing.createCellComment(
						new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
				comment.setString(new XSSFRichTextString(context));
				cell.setCellComment(comment);
			}
		}
	}

	@Override
	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
		// TODO Auto-generated method stub

	}
}

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.chinacreator.bdg.standard.service.utils.LogOutUtil;

/**
 * @author:think_toomuch
 * @Time:2022-10-31 17:04:18
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {
	private static final LogOutUtil LOGGER = new LogOutUtil(CustomSheetWriteHandler.class);

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
		// 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
		CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
		DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
		DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] { "测试1", "测试2" });
		DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
		writeSheetHolder.getSheet().addValidationData(dataValidation);
	}
}

import java.io.OutputStream;
import java.util.List;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

/**
 * @author:think_toomuch
 * @Time:2022-10-31 19:50:22
 */
public class EasyExcelUtil {
	/**
	 * 导出excel
	 * 
	 * @param outputStream      输出流
	 * @param dataList          导出的数据
	 * @param classT            模板类
	 * @param sheetName         sheetName
	 * @param cellWriteHandlers 样式处理类
	 */
	public static void writeExcelWithModel(OutputStream outputStream, List<? extends Object> dataList,
			Class<? extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {

		// 头的策略
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		// 单元格策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		// 初始化表格样式
		HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
				contentWriteCellStyle);
		//这里inMemory不设置为true表头第一个显示红色不生效不知道为什么，但是设置为true可能导致oom
		ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).inMemory(true)
				.sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustomSheetWriteHandler());
		if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
			for (int i = 0; i < cellWriteHandlers.length; i++) {
				excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
			}
		}
		// 开始导出
		excelWriterSheetBuilder.doWrite(dataList);
	}
}