一. ExcelTemplateAbstract(样式处理器抽象类)
import cn.hutool.core.map.MapUtil;
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.Data;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Data
public abstract class ExcelTemplateAbstract1 implements SheetWriteHandler,CellWriteHandler,RowWriteHandler {
// 标题字段
private List<String> heads =new ArrayList<>();
// 示例内容
private List<String> data =new ArrayList<>();
// 下拉框数据
private Map<Integer, String[]> mapDropDown =new HashMap<>();
// 备注
private Map<Integer, String> headerRemarkMap =new HashMap<>();
public ExcelTemplateAbstract1(){
init();
}
public abstract void init();
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (context.getHead() == null) {
return;
}
if (context.getHead()) {
setHeadCellStyle(context);
} else {
setContentCellStyle(context);
}
}
@Override
public void afterRowDispose(RowWriteHandlerContext context) {
if (BooleanUtils.isTrue(context.getHead()) && MapUtil.isNotEmpty(headerRemarkMap)) {
Sheet sheet = context.getWriteSheetHolder().getSheet();
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
headerRemarkMap.forEach((index, value) -> {
// 在第一行 第二列创建一个批注
Comment comment =
drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, index.shortValue(), 0, (short)2, 1));
// 输入批注信息
comment.setString(new XSSFRichTextString(value));
// 将批注添加到单元格对象中
sheet.getRow(0).getCell(index).setCellComment(comment);
});
}
}
/**
* 设置表头样式
* @param context
*/
public void setHeadCellStyle(CellWriteHandlerContext context) {
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWrapped(true);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setLocked(true);
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("仿宋_GB2312");
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
context.getRow().setHeightInPoints(40);
}
/**
* 设置内容样式
* @param context
*/
public void setContentCellStyle(CellWriteHandlerContext context) {
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setColor(IndexedColors.RED.getIndex());
headWriteFont.setFontName("仿宋_GB2312");
headWriteFont.setFontHeightInPoints((short)14);
contentWriteCellStyle.setWriteFont(headWriteFont);
DataFormatData dataFormatData = new DataFormatData();
dataFormatData.setFormat("@");
contentWriteCellStyle.setDataFormatData(dataFormatData);
WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
context.getRow().setHeightInPoints(20);
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//获取工作簿
Sheet sheet = writeSheetHolder.getSheet();
///开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//设置下拉框
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
/*起始行、终止行、起始列、终止列 起始行为1即表示表头不设置**/
CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
/*设置下拉框数据**/
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
sheet.addValidationData(dataValidation);
}
}
}
二. Demo
2.1 DemoExcelTemplate（具体模板样式类）
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.List;
@Data
@EqualsAndHashCode(callSuper = true)
public class DemoExcelTemplate extends ExcelTemplateAbstract{
@Override
public void init() {
// 标题
getHeads().addAll(List.of("序号","名字","年龄"));
// 示例内容
getData().addAll(List.of("1", "小明", "18"));
//第三列添加下拉值
getMapDropDown().put(2, new String[]{"1", "2","3"});
// 第二列标题添加备注
getHeaderRemarkMap().put(1,"这是一个名字");
}
}
2.2 单元测试
@Test
@SneakyThrows
public void demo() {
final FileOutputStream fileOutputStream = new FileOutputStream(new File("/data/123.xlsx"));
DemoExcelTemplate demoExcelTemplate = new DemoExcelTemplate();
final List<List<String>> headList = demoExcelTemplate.getHeads().stream().map(List::of).collect(Collectors.toList());
EasyExcel.write(fileOutputStream)
.head(headList)
.registerWriteHandler(demoExcelTemplate)
.sheet("模板")
.doWrite(List.of(demoExcelTemplate.getData()));
}
三. 下拉值过多处理
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 获取工作簿
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet mainSheet = writeSheetHolder.getSheet();
// 设置下拉框
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
int columnIndex = entry.getKey(); // 列index
String[] values = entry.getValue(); // 下拉值
// 创建子工作表
String newSheetName = "Dropdown"+columnIndex;
Sheet subSheet = workbook.createSheet(newSheetName);
// 开始设置下拉框
DataValidationHelper helper = mainSheet.getDataValidationHelper();
// 将值列表写入子工作表
int startRow = 0; // 起始行
for (int i = 0; i < values.length; i++) {
Row row = subSheet.getRow(startRow + i);
if (row == null) {
row = subSheet.createRow(startRow + i);
}
Cell cell = row.createCell(0); // 写入第一列
cell.setCellValue(values[i]);
}
// 创建引用子工作表中的值列表的公式
String formula = String.format("'%s'!$A$1:$A$",newSheetName) + values.length;
// 创建数据验证约束
DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
// 设置数据验证范围
CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
// 创建数据验证
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 添加数据验证到主工作表
mainSheet.addValidationData(dataValidation);
}
}
