1 设置web 输出流:
fileName 为文件名称
/**
* web 输出流
*
* @param fileName
* @param response
* @return
* @throws Exception
*/
private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()) + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
2 定义ExcelWriter:
ExcelWriter writer = null;
try {
writer = EasyExcel.write(getOutputStream(“这是文件名称”, response)).excelType(ExcelTypeEnum.XLSX).build();
writer.finish();
} catch (Exception ex) {
log.warn("导出异常!");
if (writer != null) {
writer.finish();
}
}
2.3 定义excel 表头:
List<List<String>> header // 根据业务需求定义表头
2.4 定义excel 表格内容:
List<List<String>> data // 根据业务需求定义表格内容 可以泛型为Object
2.5 定义要合并的列下标:
如定义第1列需要进行合并
List<Integer> set = new ArrayList<>();
set.add(0);
2.6 定义WriteSheet:
1: 写入第一个sheet
WriteSheet sheet = EasyExcel.writerSheet(1, “这是sheet的名称”)).head(header)
//开启列的合并
.registerWriteHandler(new MergeStrategyTravelRankConfig(data.size(), set, Boolean.TRUE))
// 开启列的宽度计算
.registerWriteHandler(new AutoWidthStyleStrategy())
.build();
// 写入sheet
writer.write(data, sheet);
完整方法:
ExcelWriter writer = null;
try {
List<List<String>> data =;// 根据业务需求定义表格内容 可以泛型为Object
//要合并的列
List<Integer> set = new ArrayList<>();
set.add(1);
writer = EasyExcel.write(getOutputStream(“这是文件名称”, response)).excelType(ExcelTypeEnum.XLSX).build();
List<List<String>> header =; // 根据业务需求定义表头
WriteSheet sheet = EasyExcel.writerSheet(1,“这是sheet的名称”).head(header)
//开启列的合并
.registerWriteHandler(new MergeStrategyTravelRankConfig(data.size(), set, Boolean.TRUE))
// 开启列的宽度计算
.registerWriteHandler(new AutoWidthStyleStrategy())
.build();
writer.write(data, sheet);
writer.finish();
} catch (Exception ex) {
log.warn("导出异常!");
if (writer != null) {
writer.finish();
}
}
2.7 自定义行,列合并方法:
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description TODO
* @Version 1.0
*/
public class MergeStrategyTravelRankConfig extends AbstractMergeStrategy {
// 合并的列编号,从0开始,指定的index或自己按字段顺序数
private List<Integer> mergeCellIndex = new ArrayList<>();
// 数据集大小,用于区别结束行位置
private Integer maxRow = 0;
//是否开启行合并
private Boolean isMergeRow;
private Integer startRowNum = 1;
private MergeStrategyTravelRankConfig() {
}
public MergeStrategyTravelRankConfig(Integer maxRow, List<Integer> mergeCellIndex, Boolean isMergeRow) {
this.mergeCellIndex = mergeCellIndex;
this.maxRow = maxRow;
this.isMergeRow = isMergeRow;
}
// 记录上一次合并的信息
private Map<Integer, MergeRange> lastRow = new HashMap<>();
private int lastRowIndex = 0;
private String lastRowValue = "";
private Map<String, MergeRange> mapData = new HashMap<>();
// 每行每列都会进入
@Override
protected void merge(Sheet sheet, Cell cell, Head head, int relativeRowIndex) {
int currentCellIndex = cell.getColumnIndex();
int currentRowIndex = cell.getRowIndex();
// 判断该列是否需要合并
if (mergeCellIndex.contains(currentCellIndex)) {
String currentCellValue = cell.getStringCellValue();
if (!lastRow.containsKey(currentCellIndex)) {
// 记录首行起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
int preCellIndex = currentCellIndex - 1;
if (preCellIndex >= 0) {
// 处理列合并起始位置
MergeRange mergeRangePre = lastRow.get(preCellIndex);
if (mergeRangePre != null) {
if (currentCellValue.equals(mergeRangePre.lastValue) && currentCellIndex == mergeCellIndex.size() - 1) {
MergeRange cellMerge = mapData.get(currentCellValue);
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, cellMerge.startCell, currentCellIndex));
} else if (currentCellValue.equals(mergeRangePre.lastValue) && !mapData.containsKey(currentCellValue)) {
mapData.put(currentCellValue, mergeRangePre);
}
}
}
lastRowIndex = currentRowIndex;
lastRowValue = currentCellValue;
return;
}
//行合并
if (null != isMergeRow && isMergeRow) {
if (!currentCellValue.equals(lastRowValue) && !currentCellValue.contains("其中")) {
// 触发上一行的列合并
// 简易写法---直接取上一行最后一个cell 的单元格
MergeRange mergeRangePreRowCell = lastRow.get(mergeCellIndex.size() - 1);
if (mergeRangePreRowCell.startCell != mergeRangePreRowCell.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRangePreRowCell.startRow, mergeRangePreRowCell.endRow, mergeRangePreRowCell.startCell, mergeRangePreRowCell.endCell));
// 复原
lastRow.put(mergeCellIndex.size() - 1, new MergeRange(mergeRangePreRowCell.lastValue, mergeRangePreRowCell.startRow, mergeRangePreRowCell.endRow, mergeCellIndex.size() - 1, mergeRangePreRowCell.endCell));
}
}
}
//对比.
MergeRange mergeRange = lastRow.get(currentCellIndex);
if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
// 更新当前列起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
if (!currentCellValue.contains("其中") && !mapData.containsKey(currentCellValue)) {
mapData.put(currentCellValue, lastRow.get(currentCellIndex));
}
int preCellIndex = currentCellIndex - 1;
if (preCellIndex >= 0) {
// 处理列合并起始位置
MergeRange mergeRangePre = lastRow.get(preCellIndex);
if (mergeRangePre != null) {
if (currentCellValue.equals(mergeRangePre.lastValue) && currentCellIndex == mergeCellIndex.size() - 1) {
MergeRange cellMerge = mapData.get(currentCellValue);
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, cellMerge.startCell, currentCellIndex));
}
}
}
// 结束的位置触发下合并.
// 同行同列不能合并,会抛异常
if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
}
}
// 合并行 + 1
mergeRange.endRow += 1;
// 结束的位置触发下最后一次没完成的合并
if (relativeRowIndex == maxRow - startRowNum) {
MergeRange lastMergeRange = lastRow.get(currentCellIndex);
// 同行同列不能合并
if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
sheet.addMergedRegionUnsafe(
new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
}
}
lastRowIndex = currentRowIndex;
lastRowValue = currentCellValue;
}
}
class MergeRange {
int startRow;
int endRow;
int startCell;
int endCell;
String lastValue;
MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
this.startRow = startRow;
this.endRow = endRow;
this.startCell = startCell;
this.endCell = endCell;
this.lastValue = lastValue;
}
}
}
2.8 自定义列宽方法:
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;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description TODO
* @Version 1.0
*/
public class AutoWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(8);
public AutoWidthStyleStrategy() {
}
@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 = this.cache.get(writeSheetHolder.getSheetNo());
if (CollectionUtils.isEmpty(maxColumnWidthMap)) {
maxColumnWidthMap = new HashMap<>(16);
this.cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
} else if (columnWidth < 12) {
columnWidth = 12;
}
Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
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 = 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 + 3;
default:
return -1;
}
}
}
}
}