com.gitee.beiding.template_excel.TemplateUtils Maven / Gradle / Ivy
package com.gitee.beiding.template_excel;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressBase;
import org.apache.poi.xssf.usermodel.*;
import java.lang.reflect.Field;
import java.util.*;
//数据处理单元
class TemplateUtils {
//主要考虑两件事情
/*
1. 模板和填充数据的一对多关系
2. 单元格合并问题的处理
处理工分为两步
1.根据模板和填充数据的一对多的关系填充目标数据,同时对跨行的数据生成合并单元(只考虑跨行不考虑跨列,使用map进行返回)
2.利用生成的合并单元对原有合并单元进行平移或延伸
最终要对所有跨行跨界进行最大合并
*/
static void copySheetDataWithData(XSSFSheet fromsheet, XSSFSheet newsheet, Map>> data) {
if (data == null) {
data = emptyMap;
}
int lastrow = fromsheet.getLastRowNum();
int currentLine = 0;
List mergedRegions = fromsheet.getMergedRegions();
Map> equalsCra = new HashMap<>();
Map> crossCra = new HashMap<>();
for (CellRangeAddress cellAddresses : mergedRegions) {
int firstRow = cellAddresses.getFirstRow();
//首行相等的
equalsCra.computeIfAbsent(firstRow, b -> new HashMap<>()).put(cellAddresses.getFirstColumn(), cellAddresses);
//穿过的
for (int i = cellAddresses.getFirstRow(); i <= cellAddresses.getLastRow(); i++) {
crossCra.computeIfAbsent(i, k -> new HashMap<>()).put(cellAddresses.getFirstColumn(), cellAddresses);
}
}
Map>> addressMap = new HashMap<>();
/*
出现了空白换行
*/
// 拷贝行并填充数据
for (int i = 0; i <= lastrow; i++) {//当切换数据源时,null值有必要进行处理吗?
Map> colCRA = new HashMap<>();
Map hasCell = equalsCra.get(i);
//上一行的数据
Map preRowData = null;
//模板行
XSSFRow templateRow = fromsheet.getRow(i);
//数据
List> datas = data.get(i);
if (datas == null || datas.size() == 0) {
if (hasCell != null) {
for (Integer col : hasCell.keySet()) {
CellRangeAddress c = hasCell.get(col);
colCRA.put(col, Collections.singletonList(new CellRangeAddress(currentLine, currentLine, c.getFirstColumn(), c.getLastColumn())));
}
addressMap.put(i, colCRA);
}
//复制当前行的样式
if (templateRow != null) {
XSSFRow row = newsheet.createRow(currentLine);
copyRowStyle(templateRow, row, 0, templateRow.getLastCellNum());
}
currentLine++;
continue;
}
//不需要对第一行进行特殊处理
for (Map rowData : datas) {
int row = currentLine++;
XSSFRow newRow = newsheet.createRow(row);
Map addresses = copyRowOrMerge(templateRow, newRow, rowData, preRowData, hasCell);
if (addresses.size() > 0) {//进行合并操作
addresses.forEach((col, c) -> {
List absent = colCRA.computeIfAbsent(col, k -> new ArrayList<>());
absent.add(c);
});
}
preRowData = rowData;
}
if (colCRA.size() > 0) {
addressMap.put(i, colCRA);
}
}
//处理合并
handleCreatedCellRangeAddress(addressMap);
int size = newsheet.getMergedRegions().size();
for (int i = 0; i < size; i++) {
newsheet.removeMergedRegion(0);
}
List addresses = handleCreatedCellRangeAddressAndOriginalCellRange(addressMap, equalsCra, crossCra);
//添加单元格合并是一个耗时的操作
for (CellRangeAddress address : addresses) {
//TODO 不需要安全校验
newsheet.addMergedRegionUnsafe(address);
}
}
//查看对应位置是否具有相应的单元格合并
/*
多行的跨界不能重复
*/
//根据数据跨行对已经生成的单元格再进行一次规整
private static List handleCreatedCellRangeAddressAndOriginalCellRange(Map>> cr, Map> eqs, Map> crosses) {
List r = new ArrayList<>();
//生成的相一定包含创建的
for (Integer row : cr.keySet()) {
Map> createCol = cr.get(row);
Map eqCol = eqs.get(row);
Map crossCol = eqs.get(row);
for (Integer col : createCol.keySet()) {
//获取当前列扩展开的所有跨界
List addresses = createCol.get(col);
//这种情形通常是一个单元格中具有唯一的值
if (addresses.size() == 1) {//没有重复,允许多行跨界
CellRangeAddress cellAddresses = addresses.get(0);
CellRangeAddress eq;
if (eqCol != null && (eq = eqCol.get(col)) != null) {
int c = eq.getLastRow() - eq.getFirstRow();
if (c == 0) {
c = cellAddresses.getLastRow() - cellAddresses.getFirstRow();
}
//只是对元素进行平移即可
eq.setFirstRow(cellAddresses.getFirstRow());
eq.setLastRow(eq.getFirstRow() + c);
r.add(eq);
} else {
r.add(cellAddresses);
}
} else {//有重复,禁止多行跨界
CellRangeAddress eq;
if (eqCol != null && (eq = eqCol.get(col)) != null) {
if (eq.getLastRow() - eq.getFirstRow() > 0) {
throw new RuntimeException("多行合并单元禁止重复");
}
for (CellRangeAddress address : addresses) {
CellRangeAddress copy = eq.copy();
copy.setFirstRow(address.getFirstRow());
copy.setLastRow(address.getLastRow());
r.add(copy);
}
} else {
r.addAll(addresses);
}
}
}
}
return r;
}
/*
不同行不同列
*/
private static void handleCreatedCellRangeAddress(Map>> map) {
map.forEach((r, cols) -> {
cols.forEach((col, list) -> {
cols.put(col, handleSameColCellRangeAddress(list));
});
});
}
private static List handleSameColCellRangeAddress(List list) {
List r = new ArrayList<>();
list.sort(Comparator.comparingInt(CellRangeAddressBase::getFirstRow));
//list中是不同列中对应的单元格
CellRangeAddress pre = list.get(0);
r.add(pre);
for (int i = 1; i < list.size(); i++) {
CellRangeAddress address = list.get(i);
if (pre.getLastRow() == address.getFirstRow()) {
pre.setLastRow(address.getLastRow());
} else {
r.add(address);
pre = address;
}
}
return r;
}
static Map copyRowOrMerge(XSSFRow templateRow, XSSFRow targetRow, Map targetData, Map preRowData, Map hasCellRA) {
targetRow.setHeight(templateRow.getHeight());
if (targetData == null) {
targetData = emptyMap;
}
if (preRowData == null) {
preRowData = emptyMap;
}
//本行中所生成的所有的跨行元素
Map r = new HashMap<>();
//遍历本行中所有列 j
for (int j = templateRow.getFirstCellNum(); j <= templateRow.getLastCellNum(); j++) {
XSSFCell templateCell = templateRow.getCell((short) j);
Object cellData = targetData.get(j);
if (cellData == null) {
//只复制样式即可
if (templateCell != null) {
copyCellStyle(templateCell, targetRow.createCell(j));
}
continue;
}
//添加合并单元
if (preRowData.get(j) == cellData) {
CellRangeAddress address = new CellRangeAddress(targetRow.getRowNum() - 1, targetRow.getRowNum(), j, j);
r.put(j, address);
//复制样式
copyCellStyle(templateCell, targetRow.createCell(j));
} else {
//总是给其添加一个单行单元格的合并
copyCellWithData(templateCell, targetRow.createCell((short) j), cellData);
if (hasCellRA != null) {
CellRangeAddress cellAddresses = hasCellRA.get(j);
if (cellAddresses != null) {
CellRangeAddress address = new CellRangeAddress(targetRow.getRowNum(), targetRow.getRowNum(), j, j);
r.put(j, address);
}
}
}
}
return r;
}
private static void copyRowStyle(XSSFRow fromRow, XSSFRow newRow, int from, int to) {
for (int i = from; i <= to; i++) {
XSSFCell fromCell = fromRow.getCell(i);
if (fromCell == null) {
continue;
}
XSSFCell newCell = newRow.getCell(i);
if (newCell == null) {
newCell = newRow.createCell(i);
}
copyCellStyle(fromCell, newCell);
}
}
//复制使用单元格数据
static void copyCellWithData(XSSFCell fromCell, XSSFCell newCell, Object data) {
copyCellStyle(fromCell, newCell);
if (data == null) {
return;
}
if (data instanceof Boolean) {
newCell.setCellValue((Boolean) data);
} else if (data instanceof Number) {
Number v = (Number) data;
newCell.setCellValue(v.doubleValue());
//newCell.setCellValue(100);
} else if (data instanceof Date) {
newCell.setCellValue((Date) data);
} else {//如果是一个json对象
if (data instanceof Picture) {
Picture picture = (Picture) data;
byte[] d = picture.getData();
if (d != null) {
XSSFSheet sheet = newCell.getSheet();
XSSFWorkbook workbook = sheet.getWorkbook();
XSSFClientAnchor anchor = new XSSFClientAnchor(picture.getDx1(), picture.getDy1(), picture.getDx2(), picture.getDy2(), newCell.getColumnIndex(), newCell.getRowIndex(), newCell.getColumnIndex() + picture.getCol(), newCell.getRowIndex() + picture.getRow());
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
patriarch.createPicture(anchor, workbook.addPicture(picture.getData(), picture.getFormat()));
}
} else {
newCell.setCellValue(data.toString());
}
}
}
//复制单元格样式
private static void copyCellStyle(XSSFCell fromCell, XSSFCell newCell) {
XSSFCellStyle cellStyle = (XSSFCellStyle) fromCell.getCellStyle().clone();
XSSFCellStyle style = newCell.getCellStyle();
try {
field.set(cellStyle, field.get(style));
} catch (Exception e) {
e.printStackTrace();
}
newCell.setCellStyle(cellStyle);
CellType cType = fromCell.getCellType();
newCell.setCellType(cType);
}
private static Field field;
static {
Class c = XSSFCellStyle.class;
try {
field = c.getDeclaredField("_stylesSource");
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
field.setAccessible(true);
}
private static Map emptyMap = new HashMap();
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy