在做Excel文件导入导出的时候，我通常会用EasyExcel来进行处理，因为它重写了POI对Excel的解析，解决了Apache POI高耗内存的问题，同时能够保证很高的读写速度，集成起来也很简单，所以导出都推荐EasyExcel。它不仅效率高，里面还提供了很多Handler使得我们可以对Excel中的格式及样式进行灵活的自定义设置，这里列举一些我们能够常用的Hander，方便开发时使用。
1. 自适应列宽
自动的列宽其实很常用，当我们导出一个基础表格时，希望根据内容来自适应宽度，方便用户直观的查看，但同时因为有些列内容太长，我们也希望能够定义一个最大的宽度，所以我们也需要在计算宽度的时候做一些处理。
/**
* Excel自定义列宽处理程序
*/
public class CustomCellWriteWidthHandler extends AbstractColumnWidthStyleStrategy {
//固定最大宽度
Integer fixedWidth;
//固定宽度
private Map<Integer, Map<Integer, Integer>> sheetCache;
public CustomCellWriteWidthHandler() {
this.sheetCache = new HashMap<>();
}
public CustomCellWriteWidthHandler(Integer fixedWidth) {
this.fixedWidth = fixedWidth;
this.sheetCache = new HashMap<>();
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
if(fixedWidth!=null){
Integer columnWidth = this.fixedWidth;
if (columnWidth >= 0) {
if (columnWidth > 40) {
columnWidth = 40;
}
}
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 280);
}else {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = sheetCache.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
sheetCache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 40) {
columnWidth = 40;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 280);
}
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
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;
}
}
}
}
}
2. 自适应行高
自适应行高其实这里也是通过计算来设置行高，那么最主要的还是如何来计算行高，因为我们要考虑单元格文本的换行。这里假使我使我们定义了一个默认行高，那么当我们知道了列宽，那么剩下的只要知道单元格内容的行数再乘以行高就可以得出单元格的高度。下面的实例代码不太精确，实际使用还需要再调试下。
/**
* 自适应行高
*/
public class CustomCellWriteHeightHandler extends AbstractRowHeightStyleStrategy {
//默认高度
final Integer defaultHeight = 280;
//列宽
Integer cellWidth;
public CustomCellWriteHeightHandler() {
}
public CustomCellWriteHeightHandler(Integer cellWidth) {
this.cellWidth = cellWidth;
}
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
row.setHeight((short) 500);
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
// 默认为 1行高度
Integer maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellTypeEnum()) {
case STRING:
if (cell.getStringCellValue().indexOf("\n") != -1) {
if (cellWidth != null) {
String[] strs = cell.getStringCellValue().split("\n");
int realHeight = Stream.of(strs).map(o -> ((o.getBytes().length + cellWidth - 1) / cellWidth)).reduce(0, Integer::sum);
maxHeight = Math.max(maxHeight, realHeight);
} else {
int length = cell.getStringCellValue().split("\n").length;
maxHeight = Math.max(maxHeight, length);
}
}
break;
default:
break;
}
}
row.setHeight((short) (maxHeight > 1 ? maxHeight * defaultHeight : maxHeight * 400));
}
}
3. 设置单元格为下拉框
设置下拉框还是比较常用的，我们需要指定起始行到截止行数的数据内容用于选择。这里你可以选择使用继承自CellWriteHandler，也可以选择继承自SheetWriteHandler，二者最主要的区别是前者只能应用到已经写入行的单元格中，对于没有写入行的单元格是无法设置的，而后者则可以应用到整个Sheet的列上。下面的propValueDescMapping其实你可以直接使用Sheet或者List，Key和Value其实是一样的。
/**
* 设置单元格下拉框处理程序
*/
public class CustomCellWriteSelectHandler implements SheetWriteHandler {
int firstRow;
int lastRow;
//表头的PropNames
String[] dataFieldNames;
/**
* 枚举翻译,Map<PropName, Map<EnumKey, EnumValue>>
*/
Map<String, Map<String,Object>> propValueDescMapping;
public CustomCellWriteSelectHandler(String[] dataFieldNames, Map<String, Map<String, Object>> propValueDescMapping) {
this.dataFieldNames = dataFieldNames;
this.propValueDescMapping = propValueDescMapping;
}
public CustomCellWriteSelectHandler(int firstRow, int lastRow, String[] dataFieldNames, Map<String, Map<String, Object>> propValueDescMapping) {
this.firstRow = firstRow;
this.lastRow = lastRow;
this.dataFieldNames = dataFieldNames;
this.propValueDescMapping = propValueDescMapping;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Map<Integer,String []> mapDropDown = new HashMap<>();
int index= 0;
for(String columnPropName: dataFieldNames){
if(propValueDescMapping.containsKey(columnPropName) && propValueDescMapping.get(columnPropName).size()>0){
mapDropDown.put(index,propValueDescMapping.get(columnPropName).entrySet().stream().map(o->o.getValue()).toArray(String[]::new));
}
index++;
}
Sheet sheet = writeSheetHolder.getSheet();
///开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
/***起始行、终止行、起始列、终止列**/
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, entry.getKey(), entry.getKey());
/***设置下拉框数据**/
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
/***处理Excel兼容性问题**/
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
}
4. 设置单元格为文本格式
设置文本格式也很常用，如单元格的内容是金额，数字时，我们就必须要对整个列进行设置了。这里同样使用了SheetWriteHandler集成，是因为我也要应用到整个Sheet中，而单元格的其他格式在使用时在网上也可以查到，就不在赘述。
/**
* 设置Excel列的格式
*/
public class CustomSheetWriteHandler implements SheetWriteHandler {
//需要设置为文本的单元格的ColumnIndex, 从0开始计数
private List<Integer> columnIndexList;
public CustomSheetWriteHandler(List<Integer> columnIndexList) {
this.columnIndexList = columnIndexList;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 获得sheet页
Sheet sheet = writeSheetHolder.getSheet();
// 获得Workbook 工作蒲
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 创建一个样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置这个样式的格式为文本格式，‘@’代表文本
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));
// 设置居右对齐
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
// 设置样式给指定列
for(Integer columnIndex:columnIndexList){
//给所有给定索引的列设置该样式
sheet.setDefaultColumnStyle(columnIndex,cellStyle);
}
}
}
5. 创建Excel文件，并添加默认样式
/**
* Excel操作通用类
*/
public class EasyExcelUtils
/**
* 指定文件，并设置策略
* @param fileNamePath
* @return
*/
public static ExcelWriter buildDynamicHeadExcelWriter(String fileNamePath, WriteHandler ...writeHandlers) {
ExcelWriterBuilder builder = EasyExcel.write(fileNamePath).registerWriteHandler(EasyExcelUtils.createStyleStrategy());
if(writeHandlers!=null) {
for (WriteHandler handler : writeHandlers) {
if (handler != null) {
builder.registerWriteHandler(handler);
}
}
}else{
//自适应宽度
builder.registerWriteHandler(new CustomCellWriteWidthHandler());
}
ExcelWriter excelWriter = builder.build();
return excelWriter;
}
/**
* 定义固定样式
* @return
*/
public static HorizontalCellStyleStrategy createStyleStrategy(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为蓝色
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("微软雅黑");
headWriteFont.setFontHeightInPoints((short)11);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); //底边框
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //左边框
contentWriteCellStyle.setBorderRight(BorderStyle.THIN); //右边框
contentWriteCellStyle.setBorderTop(BorderStyle.THIN); //顶边框
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("微软雅黑");
// 字体大小
contentWriteFont.setFontHeightInPoints((short)10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是： 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
//创建自定义的Handler
WriteHandler[] writeHandlers = new WriteHandler[]{
new CustomCellWriteWidthHandler(25), //自适应列宽策略
new CustomCellWriteHeightHandler(25), //自适应行高策略
new CustomCellWriteSelectHandler(4, spinnerRowIndex+1000, excelConfig.getDataFieldNames(), map), //列下拉菜单策略
new CustomSheetWriteHandler(textIndex)
ExcelWriter excelWriter = EasyExcelUtils.buildDynamicHeadExcelWriter(excelFileInfo.getFileNamePath(), writeHandlers);
//每个文件中sheet索引
int sheetIndex = 0;
//sheet名称
String sheetName = "默认表格"
//表头
List<List<String>> head= new ArrayList<>();
//...省略添加表头...
WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex, sheetName).head(head).build();
//...省略创建records中间代码...
excelWriter.write(records, writeSheet);
//关闭当前文件写入
excelWriter.finish();
以上示例代码仅供参考。