头疼:自己一直没找到可以将数据追加写入到同一个文件办法,所以实现了多个sheet同时写入一个文件
写完的效果图:
pom.xml引入的easyexcel版本
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
一次写入一个sheet
@SuppressWarnings("rawtypes")
public static <T> void writeOne(String fileName, List list, Class<T> t) {
// 指定文件,自定义列宽,注册样式
EasyExcel.write(fileName).registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(createStyleStrategy())
// 这里放入动态头,定义Sheet名称
.head(t).sheet(t.getSimpleName())
//要写入的数据
.doWrite(list);
}
一次写入多个sheet
@SuppressWarnings("rawtypes")
public static void writeMore(String fileName, Map<Class, List> map) {
// 指定文件,自定义列宽,注册样式
ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(createStyleStrategy()).build();
int i = 0;
for (Class c : map.keySet()) {
WriteSheet writeSheet = EasyExcel.writerSheet(i++, c.getSimpleName()).head(c).build();
excelWriter.write(map.get(c), writeSheet);
}
//这一步很关键,不然文件会损坏
excelWriter.finish();
}
自定义列宽类
package com.entity;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
//为了使我们写入的列能够自定义宽度,而不是所有列等宽,我们增加一个CustomCellWriteHandler类来自适应列宽度。
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@SuppressWarnings("rawtypes")
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<Integer, Integer>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
@SuppressWarnings("rawtypes")
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
// 此处加5可以额外扩充列宽度
return cell.getStringCellValue().getBytes().length + 5;
} 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;
}
}
}
}
}
自定义表头样式类
private static HorizontalCellStyleStrategy createStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
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.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
支持追加:
implementation group: 'com.alibaba', name: 'easyexcel', version: '2.2.10'
public static <T> void writeOneSupportAppend(String fileName, List<T> list, Class<T> t) { File file = new File(fileName); if (file.exists()) { int i = fileName.lastIndexOf("."); String newFileName = fileName.substring(0, i) + "temp" + fileName.substring(i); ExcelWriter excelWriter = EasyExcel.write().withTemplate(file).registerWriteHandler(createStyleStrategy()).file(newFileName).autoCloseStream(false).build(); WriteSheet writeSheet = EasyExcel.writerSheet(t.getSimpleName()).build(); excelWriter.write(list, writeSheet); excelWriter.finish(); try { Path path = file.toPath(); boolean isDelete = Files.deleteIfExists(path); if (isDelete) { Path newPath = new File(newFileName).toPath(); Files.move(newPath, path.resolveSibling(fileName)); } else { logger.info("文件删除失败"); } } catch (IOException e) { e.printStackTrace(); } } else { // 指定文件,自定义列宽,注册样式 writeOne(fileName, list, t); }