这次开任务使用的是EasyExcel导出Excel文档,但是在任务过程中,生成的文档的格式要求并不符合产品和测试的期望值,如图:
想着自己地位低下,也无能力反驳。只好硬着头皮查阅资料来达到他们的想要的需求结果。如图:
经过百度工程师的各种挑战实验和查阅。终于让我实现了上述的要求。代码如下:
Excel文档的自动列宽设置
public class Custemhandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
//因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 20;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public Custemhandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
if(columnWidth<COLUMN_WIDTH){
columnWidth =columnWidth*2;
}
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth* 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
Excel的字体,样式,背景色的设置
public static HorizontalCellStyleStrategy getStyleStrategy(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
// 字体样式
headWriteFont.setFontName("Frozen");
headWriteCellStyle.setWriteFont(headWriteFont);
//自动换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
// 字体样式
contentWriteFont.setFontName("Calibri");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
调用方式
这部分是因为我的Excel都是自定义开头数据,所以用的是List。
EasyExcel.write(outputStream).needHead(true)
.head(getAssignmentTotalExcelSheet0Title())
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(new Custemhandler())
.registerWriteHandler(EasyExcelUtil.getStyleStrategy())
.sheet(0, "Assignment Total Report")
.doWrite(getAssignmentTotalSheet0Data(list));
下面部分是用上实体类的调用
public static void write(OutputStream outputStream, Class<?> clazz, String sheetName, List writeList){
EasyExcel.write(outputStream).needHead(true)
.head(clazz)
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(new Custemhandler())
.registerWriteHandler(EasyExcelUtil.getStyleStrategy())
.sheet(0, sheetName)
.doWrite(writeList);
}
实体类说明
@ExcelProperty(value = "Tenant") //表示Excel 显示的表头
private String tenant;
@ExcelProperty(value = "Locale")
private String locale;
参考说明:
如有疑问,可以参考以下文档:
https://www.yuque.com/easyexcel/doc/easyexcel (EasyExcel 官网)
https://www.cnblogs.com/Hizy/p/11825886.html (Excel的字体,样式,背景色的设置参考)
https://blog.csdn.net/qq_37361535/article/details/106050623(Excel文档的自动列宽设置参考)