package com.example.workflow.controller;
import com.alibaba.excel.EasyExcel;
import com.example.workflow.model.Member;
import com.example.workflow.strategy.ExcelFillCellMergeStrategy;
import com.example.workflow.strategy.MergeStrategy;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@RestController
public class ExcelController {
@RequestMapping("/export")
public void exportMembers1(HttpServletResponse response) throws IOException {
List<Member> members =
Arrays.asList(
new Member("1","测试部","zhangsan","18"),
new Member("1","测试部","zhangsan","19"),
new Member("1","测试部","zhangsan","29"),
new Member("2","测试部","lisi","18"),
new Member("3","测试部","wangwu","18"),
new Member("4","开发部","zhaoliu","18"),
new Member("5","开发部","maqi","18"),
new Member("5","开发部","m11aqi","18")
);
// 设置文本内省
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename=demo.xlsx");
EasyExcel.write(response.getOutputStream(), Member.class).
//自定义多级表头
head(getTitles()).
//需要合并单元格的索引
registerWriteHandler(new ExcelFillCellMergeStrategy(0,2,new ArrayList<>())).
sheet("成员列表").doWrite(members);
}
private List<List<String>> getTitles() {
List<String> title1 = Arrays.asList("ID");
List<String> title2 = Arrays.asList("部门");
List<String> title3 = Arrays.asList("信息","用户名");
List<String> title4 = Arrays.asList("信息","年龄");
return Arrays.asList(title1,title2,title3,title4);
}
}
@Data
@ColumnWidth(20)
@NoArgsConstructor
@AllArgsConstructor
public class Member {
@ExcelProperty(value = "ID",index = 0)
private String id;
@ExcelProperty(value = "部门",index = 1)
private String depart;
@ExcelProperty(value = "用户名",index = 2)
private String name;
@ExcelProperty(value = "年龄",index = 3)
private String age;
}
条件合并
package com.example.workflow.strategy;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
// 需要从第几行开始合并，0表示第1行
private final int mergeRowIndex;
// 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列
private final int mergeColumnRegion;
private final List<Integer> ignoreColumn;
public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion, List<Integer> ignoreColumn) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnRegion = mergeColumnRegion;
this.ignoreColumn = ignoreColumn;
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 隐藏id列
// writeSheetHolder.getSheet().setColumnHidden(0, true);
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (!ignoreColumn.contains(curColIndex) && curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnRegion; i++) {
if (curColIndex <= mergeColumnRegion) {
mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并：当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
*
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
// 当前行的第一个Cell
Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
Object curFirstData = curFirstCell.getCellType() == CellType.STRING.getCode() ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
// 上一行的第一个Cell
Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Object preFirstData = preFirstCell.getCellType() == CellType.STRING.getCode() ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();
// 当前cell
Object data = cell.getCellType() == CellType.STRING.getCode() ? cell.getStringCellValue() : cell.getNumericCellValue();
// 上面的Cell
Cell upCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object upData = upCell.getCellType() == CellType.STRING.getCode() ? upCell.getStringCellValue() : upCell.getNumericCellValue();
// 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列 且上一行值相同
if (curFirstData.equals(preFirstData) && data.equals(upData)) {
Sheet sheet = writeSheetHolder.getSheet();
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);
}
}
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
}
直接合并
package com.example.workflow.strategy;
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.*;
public class MergeStrategy extends AbstractMergeStrategy {
// 合并的列编号，从0开始，指定的index或自己按字段顺序数
private Set<Integer> mergeCellIndex = new HashSet<>();
// 数据集大小，用于区别结束行位置
private Integer maxRow = 0;
// 禁止无参声明
private MergeStrategy() {
}
public MergeStrategy(Integer maxRow, int... mergeCellIndex) {
Arrays.stream(mergeCellIndex).forEach(item -> {
this.mergeCellIndex.add(item);
});
this.maxRow = maxRow;
}
// 记录上一次合并的信息
private Map<Integer, MergeRange> lastRow = new HashMap<>();
private Set<String> nos = new HashSet<>();
// 每行每列都会进入，绝对不要在这写循环
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int currentCellIndex = cell.getColumnIndex();
// 判断该行是否需要合并
if (mergeCellIndex.contains(currentCellIndex)) {
//当前单元格的值
String currentCellValue = cell.getStringCellValue();
int currentRowIndex = cell.getRowIndex();
//lastRow put(0,new) put(1,new) put
if (!lastRow.containsKey(currentCellIndex)) {
// 记录首行起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
return;
}
//有上行这列的值了，拿来对比.
MergeRange mergeRange = lastRow.get(currentCellIndex);
if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
// 结束的位置触发下合并.
// 同行同列不能合并，会抛异常
if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
}
// 更新当前列起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
}
// 合并行 + 1
mergeRange.endRow += 1;
// 结束的位置触发下最后一次没完成的合并
if (relativeRowIndex.equals(maxRow - 1)) {
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));
}
}
}
}
}
class MergeRange {
public int startRow;
public int endRow;
public int startCell;
public int endCell;
public String lastValue;
public 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;
}
}
网友评论