功能列表
- 导出Excel返回文件的File
- 导出到浏览器
- 导出到浏览器(动态字段)
- 导出数据到多Sheet表 -> 浏览器
- 读取表格标题和数据
内置已实现动态列宽和行高
public class EasyExcelUtils {
private static final Logger log = LoggerFactory.getLogger(EasyExcelUtils.class);
/**
* 导出Excel返回文件的File
*
* @Param data
* @param clazz
* @param fileName
*/
public static <T> File exportReturnToFile(List<T> data, Class<T> clazz, String fileName) {
File file = null;
//名称拆分前后缀
//1.判断文件名是否有扩展
try {
if (StringUtils.isBlank(fileName)) {
file = File.createTempFile(fileName, "");
} else {
String[] parts = fileName.split("\\.");
String extension = "." + parts[parts.length - 1];
String name = fileName.substring(0, fileName.length() - extension.length());
file = File.createTempFile(name, extension);
}
} catch (IOException e) {
log.error("文件生成失败,文件名异常.异常信息:{}", e.getMessage());
throw new RuntimeException("文件生成失败,文件名异常");
}
//导出
EasyExcel.write(file, clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(defaultStylePolicyPolicy())
.sheet()
.doWrite(data);
return file;
}
/**
* EasyExce导出到浏览器(类模板方式)
*
* @param fileName 文件名
* @param response 响应对象
* @param data 数据集
* @param clazz 实体类模板
* @param <T> 泛型
*/
public static <T> void export(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz) throws IOException {
exportPublic(fileName, response, data, clazz, null);
}
/**
* EasyExce导出到浏览器(类模板方式)
*
* @param fileName 文件名
* @param response 响应对象
* @param data 数据集
* @param clazz 实体类模板
* @param excludeColumnFieldNames 忽略指定列导出根据字段名
* @param <T> 泛型
*/
public static <T> void export(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {
exportPublic(fileName, response, data, clazz, excludeColumnFieldNames);
}
/**
* export公共
*
* @param fileName 文件名
* @param response 响应对象
* @param data 数据集
* @param clazz 实体类模板
* @param excludeColumnFieldNames 忽略指定列导出根据字段名
* @param <T> 泛型
* @throws IOException
*/
private static <T> void exportPublic(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {
setResponse(response, fileName);
//导出
EasyExcel.write(response.getOutputStream(), clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(defaultStylePolicyPolicy())
.registerWriteHandler(new CustomCellWriteWidthConfig())
// .registerWriteHandler(new CustomCellWriteHeightConfig())
.sheet()
.excludeColumnFieldNames(excludeColumnFieldNames)
.doWrite(data);
}
/**
* EasyExce导出到浏览器(动态标题(字段)方式)
*
* @param fileName 文件名
* @param response 响应对象
* @param heads 标题(字段)
* @param data 数据
* @throws IOException
*/
public static void export(String fileName, HttpServletResponse response, List<List<String>> heads, List<List<Object>> data) throws IOException {
setResponse(response, fileName);
//导出
EasyExcel.write(response.getOutputStream())
.head(heads)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(defaultStylePolicyPolicy())
.sheet()
.doWrite(data);
}
/**
* 导出失败返回错误模板
*
* @param response 响应对象
* @param pathName 模板路径
* @param msg 异常消息
* @throws IOException
*/
public static void exportError(HttpServletResponse response, String pathName, String msg) throws IOException {
setResponse(response, "导出失败");
InputStream resourceAsStream = EasyExcelUtils.class.getResourceAsStream(pathName);
Map<String, String> map = new HashMap<>();
map.put("msg", msg);
//导出
EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).sheet().doFill(map);
}
/**
* EasyExce导出Excel到多Sheet表 -> 浏览器
*
* @param fileName 文件名
* @param response 响应对象
* @param sheetNameList 已经排序过的sheet名称列表
* @param data 数据集 key为sheet名称,value为数据集合
* @param clazz 实体类模板
* @param excludeColumnFieldNames 忽略指定列导出根据字段名
* @throws IOException
*/
public static <T> void exportToMultipleSheets(String fileName, HttpServletResponse response, List<String> sheetNameList, Map<String, List<T>> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {
setResponse(response, fileName);
// 创建ExcelWriter对象
ExcelWriter writer = EasyExcel
.write(response.getOutputStream())
.registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
.registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高（根据自己情况选择使用，我这里没用到）*/
//自定义样式
.registerWriteHandler(EasyExcelUtils.defaultStylePolicyPolicy())
.build();
//创建工作表
sheetNameList.forEach(sheetName -> {
WriteSheet sheet = EasyExcel.writerSheet(sheetName).excludeColumnFieldNames(excludeColumnFieldNames).head(clazz).build();
List<T> valueList = data.get(sheetName);
if (CollUtil.isEmpty(valueList)) throw new RuntimeException("查询不到对应sheet数据,请检查数据!");
writer.write(valueList, sheet);
});
//导出
writer.finish();
}
/**
* 设置响应对象
*
* @param response
* @param fileName
*/
private static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + ".xlsx");
}
/**
* 获取标题和数据
* 标题 listTitle
* 数据 listBody
*
* @param file 文件
* @Return 返回包含标题和数据的Map
* @throws IOException 当发生I/O错误时抛出
*/
public static Map<String, Object> readExcelTitleAndBody(MultipartFile file) throws IOException {
InputStream inputStream = file.getInputStream();
List<String> listTitle = new ArrayList<>(4);
List<Map<String, String>> listBody = new ArrayList<>();
Integer count = 0;
AnalysisEventListener<Map<Integer, String>> listener = new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// 在这里处理标题行的数据
// headMap是一个Map，键为列的索引，值为列的标题
// 你可以在这里获取到标题行的数据
if (listTitle.size() == 0) {
headMap.forEach((k, v) -> {
listTitle.add(v);
});
}
}
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
// 这里是读取到每一行数据时的处理逻辑
Map<String, String> map = new HashMap<>();
data.forEach((k, v) -> {
map.put(listTitle.get(k), v);
});
listBody.add(map);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 读取完成后的处理逻辑
}
};
EasyExcel.read(inputStream, listener).sheet().doRead();
inputStream.close();
Map<String, Object> resMap = new HashMap<>();
resMap.put("listTitle", listTitle);
resMap.put("listBody", listBody);
// 返回标题行的数据
return resMap;
}
/**
* 默认样式策略策略
*
* @return
*/
public static HorizontalCellStyleStrategy defaultStylePolicyPolicy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置列宽
// 背景设置为浅蓝色
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//设置水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体为微软雅黑
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体为微软雅黑
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 通用xlsx模板下载
*
* @param response
* @param fileName
*/
public static void templateDownload(HttpServletResponse response, String fileName) {
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
response.setContentType("multipart/form-data");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
InputStream inputStream = EasyExcelUtils.class.getResourceAsStream("/static/template/" + fileName);
if (inputStream == null) {
return;
}
OutputStream os = response.getOutputStream();
byte[] b = new byte[2048];
int length;
while ((length = inputStream.read(b)) > 0) {
os.write(b, 0, length);
}
os.close();
inputStream.close();
} catch (IOException e) {
log.error(e.getMessage());
}
}
}
/**
* 自适应列宽
*/
class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map<String, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetName() + writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
// 单元格文本长度大于60换行
if (columnWidth >= 0) {
if (columnWidth > 60) {
columnWidth = 60;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 计算长度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> 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:
// 换行符（数据需要提前解析好）
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
/**
* 自适应行高
*/
class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 300;
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
// 默认为 1行高度
int maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellTypeEnum() == CellType.STRING) {
String value = cell.getStringCellValue();
int len = value.length();
int num = 0;
if (len > 50) {
num = len % 50 > 0 ? len / 50 : len / 2 - 1;
}
if (num > 0) {
for (int i = 0; i < num; i++) {
value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
}
}
if (value.contains("\n")) {
int length = value.split("\n").length;
maxHeight = Math.max(maxHeight, length) + 1;
}
}
}
row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
}
}
/**
* 合并行策略
*/
class MergeRowStrategy implements RowWriteHandler {
/**
* 需要合并的依据的列下表数组（从 0 开始）,例如传{1,2}则判断第二列和第三列是否相同,相同则合并mergeColumnIndexList对应列
*/
private final int[] mergeByColumn;
/**
* 需合并的列下标数组（从 0 开始）
*/
private final int[] mergeColumnIndexList;
/**
* 是否清空冗余内容
*/
private boolean isCleanSurplusContent = true;
/**
* 合并 Key
*/
private String mergeKey = null;
/**
* 合并起始行下标
*/
private int mergeStartRowIndex;
/**
* @param mergeByColumn 需要合并的依据的列下表数组
* @param mergeColumnIndexList 需合并的列下标数组
*/
public MergeRowStrategy(int[] mergeByColumn, int[] mergeColumnIndexList) {
this.mergeByColumn = mergeByColumn;
this.mergeColumnIndexList = mergeColumnIndexList;
}
@Override
public void afterRowDispose(RowWriteHandlerContext context) {
if (context.getHead() || context.getRelativeRowIndex() == null) {
return;
}
//需要合并的依据组合
StringJoiner sj = new StringJoiner("&-&");
for (int index : mergeByColumn) {
sj.add(context.getRow().getCell(index).getStringCellValue());
}
if (context.getRelativeRowIndex() == 0) {
this.mergeKey = sj.toString();
this.mergeStartRowIndex = context.getRowIndex();
return;
}
String currentKey = sj.toString();
if (!Objects.equals(currentKey, this.mergeKey)) {
this.mergeKey = currentKey;
this.mergeStartRowIndex = context.getRowIndex();
return;
} else {
//清空当前要合并单元格的内容
for (Integer index : mergeColumnIndexList) {
context.getWriteSheetHolder().getSheet().getRow(context.getRowIndex()).getCell(index).setCellValue("");
}
}
for (Integer columnIndex : mergeColumnIndexList) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(
mergeStartRowIndex,
context.getRowIndex(),
columnIndex,
columnIndex);
context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(cellRangeAddress);
}
}
}
[复制链接]
发表于 2024-7-29 13:03
|
发表于 2024-7-29 13:44
