一、EasyExcel多数据导出到多Sheet工作表
outputStream = response.getOutputStream();
// 创建ExcelWriter对象
com.alibaba.excel.ExcelWriter writer = EasyExcel
.write(outputStream)
//自定义合并单元格策略
.registerWriteHandler(new CustomMergeStrategy())
//自定义样式
.registerWriteHandler(ExcelExportUtil.defaultStylePolicyPolicy())
.build();
List<EventTask1Template> list1 = new ArrayList<>();
list1.add(new EventTask1Template("张三", "20", "男"));
list1.add(new EventTask1Template("张三", "02", "女"));
list1.add(new EventTask1Template("李四", "25", "女"));
WriteSheet sheet1 = EasyExcel.writerSheet("sheet1").head(EventTask1Template.class).build();
writer.write(list1,sheet1);
List<EventTask2Template> list2 = new ArrayList<>();
list2.add(new EventTask2Template("王五", "20", "男"));
list2.add(new EventTask2Template("王五", "02", "女"));
list2.add(new EventTask2Template("赵六", "25", "女"));
WriteSheet sheet2 = EasyExcel.writerSheet("sheet2").head(EventTask2Template.class).build();
writer.write(list2,sheet2);
// 完成写入操作
writer.finish();
WriteSheet sheet1 = EasyExcel.writerSheet("sheet1").head(EventTask1Template.class).build();
这里的head可以用List<List<String>>来自定义动态的表头
自定义合并策略(自行扩充内容)
package com.netintech.core.easyexcel.mergeStrategy;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class CustomMergeStrategy extends AbstractMergeStrategy {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public CustomMergeStrategy() {
}
public CustomMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int curRowIndex = cell.getRowIndex(); //获取当前行数
int curColIndex = cell.getColumnIndex(); //获取当前列
sheet.setColumnWidth(curColIndex,5120);
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(sheet, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 单元格向上合并
*
* @param sheet 工作簿
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(Sheet sheet, Cell cell, int curRowIndex, int curColIndex) {
//获取当前列数据
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//获取上一行单元格
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
//获取上一行数据
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//curColIndex 可指定哪一行为对比列,也可以通过配置合并列进行对比
// 获取每一行第一列数据和上一行第一列数据进行比较,如果相等合并
Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool && bool) {
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
private void mergeCells(Sheet sheet, int columnIndex, int startRowIndex, int endRowIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
二、默认样式策略
/**
* 默认样式策略策略
*
* @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);
}