Java操作excel表格,除了运用POI技术,阿里开发一个工具简易操作EasyExcel,接下来我们来实战操作下自定义动态化导出excel,自定义动态化为自定义标题,合并单元格
准备环境
POM.XML
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
实战代码
package com.example.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import jdk.nashorn.internal.runtime.JSONFunctions;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import org.springframework.util.NumberUtils;
import java.util.*;
import java.util.stream.Collectors;
/**
* describe: 导出Excel 表格
*
* @author
* @date
*/
@Slf4j
public class WriteExcelUtils {
public static void main(String[] args) {
List<String> titleList = new ArrayList<>();
titleList.add("客户名称");
titleList.add("客户编号");
titleList.add("供应商名称");
titleList.add("合同名称");
titleList.add("合同编号");
titleList.add("要合并");
List<String> mergeTitles = new ArrayList<>();
mergeTitles.add("客户名称");
mergeTitles.add("供应商名称");
mergeTitles.add("合同编号");
List<List<Object>> dataList = new ArrayList<>();
for (int i = 0; i< 10; i++) {
List<Object> objList = new ArrayList<>();
objList.add("客户名称");
objList.add("客户编号"+i);
objList.add("供应商名称"+i);
objList.add("合同名称"+i);
objList.add("合同编号");
objList.add("要合并"+i);
dataList.add(objList);
}
WriteExcelUtils writeExcelUtils = new WriteExcelUtils();
writeExcelUtils.customDynamicExport("测试导出excel" + Math.random(),titleList, mergeTitles, dataList);
}
/**
* 自定义动态导出excel
*
* @param fileName 文件名称 不带后缀
* @param titleList excel标题名称
* @param mergeTitles 需要合并的标题列
* @param dataList 导出的数据 按顺序
*/
public void customDynamicExport(String fileName, List<String> titleList,
List<String> mergeTitles, List<List<Object>> dataList) {
fileName = fileName + ".xlsx";
// 导出的数据转换为Map数据结构, k -> 标题,v -> 此标题下的数据按顺序
Map<String, List<Object>> dataMap = new HashMap<>(titleList.size());
for (int i = 0 ; i < titleList.size(); i++) {
// 当前列数据
List<Object> currectTitleDatas = new ArrayList<>();
for (int j = 0; j < dataList.size(); j++) {
currectTitleDatas.add(dataList.get(j).get(i));
}
dataMap.put(titleList.get(i), currectTitleDatas);
}
// 获取标题信息
List<List<String>> headTitleInfo = excelTitle(titleList);
// 获取数据信息
List<List<Object>> excelDataInfo = excelData(dataList);
// 获取合并单元格信息
List<LoopMergeStrategy> mergeStrategies = mergeCells(titleList, mergeTitles, dataMap);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(fileName);
if (!CollectionUtils.isEmpty(mergeStrategies)) {
log.info("开始进行合并单元格操作 mergeStrategies ===> {}", mergeStrategies);
mergeStrategies.forEach(m -> {
excelWriterBuilder.registerWriteHandler(m);
});
}
excelWriterBuilder.head(headTitleInfo).sheet("模板")
// 当然这里数据也可以用 List<List<String>> 去传入
.doWrite(excelDataInfo);
}
/**
* 获取excel标题
*
* @param titleList
* @return
*/
private List<List<String>> excelTitle(List<String> titleList) {
if (CollectionUtils.isEmpty(titleList)) {
log.error("====> 导出excel标题为空");
throw new RuntimeException();
}
List<List<String>> excelTitleList = new ArrayList<>(titleList.size());
titleList.forEach(k -> {
List<String> titles = new ArrayList<>(1);
titles.add(k);
excelTitleList.add(titles);
});
return excelTitleList;
}
private List<List<Object>> excelData(List<List<Object>> dataList) {
if (CollectionUtils.isEmpty(dataList)) {
log.error("===> 导出excel数据为空");
throw new RuntimeException();
}
return dataList;
}
private List<LoopMergeStrategy> mergeCells(List<String> titleList, List<String> mergeTitles, Map<String, List<Object>> dataMap) {
if (CollectionUtils.isEmpty(titleList)) {
log.error("====> 导出excel标题为空");
throw new RuntimeException();
}
if (CollectionUtils.isEmpty(mergeTitles)) {
log.warn("该次导出无合并单元格操作");
return new ArrayList<>();
}
// 将需要合并的项转换为Map,以便获取合并位置
Map<String, String> mergeTitleMap = mergeTitles.stream().collect(Collectors.toMap(k -> k, k -> k));
// 获取合并位置
Map<String, Integer> mergeLocationMap = new HashMap<>(mergeTitles.size());
for (int i = 0; i < titleList.size(); i++) {
if (Objects.nonNull(mergeTitleMap.get(titleList.get(i)))) {
mergeLocationMap.put(titleList.get(i), i);
}
}
List<LoopMergeStrategy> mergeStrategyList = new ArrayList<>();
mergeTitles.forEach(k -> {
// 合并数据所在的列数
Integer columnIndex = mergeLocationMap.get(k);
List<Object> mergeDatas = dataMap.get(k);
// 需要合并的数量
Integer currectMergeNum = 1;
for (int m = 0; m < mergeDatas.size(); m++) {
// 是否存在合并环节
Boolean isMerge = false;
if (m != 0 && Objects.equals(mergeDatas.get(m), mergeDatas.get(m - 1))) {
isMerge = true;
currectMergeNum = currectMergeNum + 1;
if (m == mergeDatas.size() - 1) {
isMerge = false;
}
}
if (!isMerge && currectMergeNum > 1) {
// 合并的行数
Integer eachRow = currectMergeNum;
mergeStrategyList.add(new LoopMergeStrategy(eachRow, columnIndex));
currectMergeNum = 1;
}
}
});
return mergeStrategyList;
}
}