import com.alibaba.fastjson.JSONObject;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;
/**
* Excel 匹配模板处理工具 通配包含 ${}和#{}
* ${} 字段模板 将${}及里面的内容更换成指定json的内容
* #{} 集合模板 将#{}及里面的内容更换成指定json的内容
* compile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
* compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
* compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'
* @Description
* @Author 不爱写代码的码农
* @Date 2020/7/13 14:03
*/
@Slf4j
public class ExcelProcessUtils {
private final static Pattern fieldPatternAll = Pattern.compile("\\$\\{(.+?)}");
private final static Pattern listPatternAll = Pattern.compile("\\#\\{(.+?)}");
private final static String fieldMatchAll = "\\$\\{[^\\}]+\\}";
private final static String listMatchAll = "\\#\\{[^\\}]+\\}";
/**
* 行复制功能
*
* @param wb 工作簿
* @param fromRow 从哪行开始
* @param toRow 目标行
* @param copyValueFlag true则连同cell的内容一起复制
*/
public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
toRow.setHeight(fromRow.getHeight());
for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
Cell tmpCell = cellIt.next();
Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb, tmpCell, newCell, copyValueFlag);
}
Sheet worksheet = fromRow.getSheet();
for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
(toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
worksheet.addMergedRegionUnsafe(newCellRangeAddress);
}
}
}
/**
* 复制单元格
*
* @param wb wb
* @param srcCell 被复制cell
* @param distCell 复制cell
* @param copyValueFlag true则连同cell的内容一起复制
*/
private static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
CellStyle newStyle = wb.createCellStyle();
CellStyle srcStyle = srcCell.getCellStyle();
newStyle.cloneStyleFrom(srcStyle);
newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));
// 样式
distCell.setCellStyle(newStyle);
// 内容
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
// 不同数据类型处理
CellType srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (copyValueFlag) {
if (srcCellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == CellType.STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == CellType.BLANK) {
} else if (srcCellType == CellType.BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == CellType.ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == CellType.FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
}
}
}
/**
* cell处理
*
* @param cell 处理的cell
* @param jsonObject 所有数据
*/
public static void cellProcess(Cell cell, JSONObject jsonObject, List<ExcelProcessDto> excelProcessDtos, Row row) {
String content = getCellStringValue(cell);
if (fieldPatternAll.matcher(content).find()) {
// 匹配到字段
String cellValue = fieldProcess(content, jsonObject);
cell.setCellValue(cellValue);
} else if (listPatternAll.matcher(content).find()) {
// 匹配到列表
String listName = ExcelProcessUtils.getListName(content);
int rowNum = row.getRowNum();
ExcelProcessDto excelProcessDto = new ExcelProcessDto();
if (CollectionUtils.isEmpty(excelProcessDtos)) {
excelProcessDto.setListName(listName);
excelProcessDto.setRowNum(rowNum);
excelProcessDtos.add(excelProcessDto);
return;
}
for
Excel 匹配模板处理工具
最新推荐文章于 2023-02-08 21:50:56 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)