前言
近期，笔者面临一项新的需求：实现一个自定义表格导出功能，该功能能够将Excel文件的样式与用户自定义的表格样式相匹配。在评估了多种工具后，决定采用easyExcel来完成这项任务。然而，笔者发现easyExcel的官方示例并未涵盖一些关键功能，例如使用RGB值来分别设置单元格的背景和字体颜色。
通过广泛的研究和实践，终于找到了解决方案。现在，笔者将分享一段实用的代码示例，供您参考。请注意，这仅是一个参考示例，具体的实现可能需要根据您的具体需求进行调整。
依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.16</version>
</dependency>
示例
public class TestFileUtil {
public static String getPath(){
return "D:\testExcel\";
}
}
@Test
public void customHandlerWrite() {
String fileName = TestFileUtil.getPath() + "customHandlerWrite" + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName)
// 富文本 不加这个会导致字体样式失效
.inMemory(true)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(new ColumnStyleStrategy())
.registerWriteHandler(new RowStyleStrategy())
.sheet("模板")
.doWrite(dataList());
}
private List<List<Object>> dataList() {
List<List<Object>> list = new ArrayList<List<Object>>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<Object>();
data.add("字符串");
if (i % 3 != 0) {
data.add(new Date());
data.add(0.56);
} else {
data.add("字符串");
data.add("");
}
list.add(data);
}
return list;
}
/**
* 自定义拦截器。
*
*/
public class CustomCellWriteHandler implements CellWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
@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) {
// 这里可以对cell进行任何操作
LOGGER.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
// 筛选单元格
if (cell.getRowIndex() > 0) {
// 拿到poi的workbook
// 这里千万记住 想办法能复用的地方把他缓存起来 一个表格最多创建6W个样式
// 不同单元格尽量传同一个 cellStyle
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置rgb颜色
byte[] bgRgb = new byte[]{(byte) 57, 126, (byte) 240};
// 设置居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
XSSFCellStyle xssfCellColorStyle = (XSSFCellStyle) cellStyle;
xssfCellColorStyle.setFillForegroundColor(new XSSFColor(bgRgb, null));
cell.setCellStyle(xssfCellColorStyle);
// // 设置字体RGB颜色
cell.setCellType(CellType.STRING);
String stringCellValue = cell.getStringCellValue();
XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
Font font = workbook.createFont();
byte[] ftRgb = new byte[]{(byte) 255, 0, 0};
XSSFFont xssfFont = (XSSFFont) font;
xssfFont.setColor(new XSSFColor(ftRgb, null));
// 设置字体大小
BigDecimal fonsize = new BigDecimal("16.5").divide(new BigDecimal("1.5")).multiply(new BigDecimal("2"));
font.setFontHeightInPoints(Short.parseShort(fonsize.toString()));
// 设置粗体
font.setBold(true);
// 从哪到哪，你想设置成什么样的字体都行startIndex，endIndex，这里设置全部
richString.applyFont(0, stringCellValue.length(), xssfFont);
// 再设置回每个单元格里
cell.setCellValue(richString);
}
}
}
public class ColumnStyleStrategy extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
Sheet sheet = writeSheetHolder.getSheet();
int columnIndex = cell.getColumnIndex();
if(columnIndex >= 1){
// 列宽
sheet.setColumnWidth(columnIndex, 9000);
}else {
// 列宽
sheet.setColumnWidth(columnIndex, 3000);
}
}
}
public class RowStyleStrategy extends AbstractRowHeightStyleStrategy {
@Override
// 头行高
protected void setHeadColumnHeight(Row row, int i) {
int rowNum = row.getRowNum();
row.setHeightInPoints(36f);
}
@Override
// 内容行高
protected void setContentColumnHeight(Row row, int i) {
int rowNum = row.getRowNum();
if(rowNum%2==0){
row.setHeightInPoints(72f);
}
}
}
效果如下所示
后言
此文仅为示例，后续会在需求完成后更新更完整的版本。
