占位符导入模板excel, 再导出xlsx
1、引入包
`<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>`
2、工具类
<details>
package com.ly.education.trainingResource.server.utils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.format.CellFormatType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.aspectj.weaver.ast.Test;
import org.springframework.core.io.ClassPathResource;
import java.io.*;
import java.util.*;
import java.util.function.Predicate;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
* 使用一个已经存在的Excel作为模板，可以对当前的模板Excel进行修改操作，
* 然后重新输出为流，或者存入文件系统当中。
*
*
* @Description: excel模板操作
*
* */
public class ExcelTemplate {
private String path;
private Workbook workbook;
private Sheet[] sheets;
private Sheet sheet;
private Throwable ex;
private List<Cell> cellList = null;
private Pattern doublePattern = Pattern.compile("^[0-9]+[.]{0,1}[0-9]*[dD]{0,1}$");
/**
* 通过模板Excel的路径初始化
* */
public ExcelTemplate(String path) {
this.path = path;
init();
}
public ExcelTemplate(InputStream is) {
init(is);
}
private void init(){
ClassPathResource classPathResource = new ClassPathResource(path);
// File file = new File(path);
// if (file.exists() && (path == null
// || (!path.endsWith(".xlsx") && !path.endsWith(".xls")))) {
// ex = new IOException("错误的文件格式");
// } else{
try (InputStream is = classPathResource.getInputStream()){
workbook = WorkbookFactory.create(is);
sheets = new Sheet[workbook.getNumberOfSheets()];
for(int i = 0;i < sheets.length;i++){
sheets[i] = workbook.getSheetAt(i);
}
if(sheets.length > 0) {
sheet = sheets[0];
}
sheet.setForceFormulaRecalculation(true);
} catch (EncryptedDocumentException e) {
ex = e;
} catch (IOException | InvalidFormatException e) {
ex = e;
}
// }
}
private void init(InputStream is){
try {
workbook = WorkbookFactory.create(is);
sheets = new Sheet[workbook.getNumberOfSheets()];
for(int i = 0;i < sheets.length;i++){
sheets[i] = workbook.getSheetAt(i);
}
if(sheets.length > 0) {
sheet = sheets[0];
}
sheet.setForceFormulaRecalculation(true);
} catch (EncryptedDocumentException e) {
ex = e;
} catch (IOException | InvalidFormatException e) {
ex = e;
}
}
private boolean initSheet(int sheetNo){
if(!examine() || sheetNo < 0 || sheetNo > workbook.getNumberOfSheets() - 1) {
return false;
}
int sheetNum = workbook.getNumberOfSheets();
sheets = new Sheet[sheetNum];
for(int i = 0;i < sheetNum;i++){
if(i == sheetNo) {
sheet = workbook.getSheetAt(i);
}
sheets[i] = workbook.getSheetAt(i);
}
sheet = workbook.getSheetAt(sheetNo);
sheet.setForceFormulaRecalculation(true);
return true;
}
/**
* 验证模板是否可用
* @return true-可用 false-不可用
* */
public boolean examine(){
if(ex == null && workbook != null) {
return true;
}
return false;
}
private boolean examineSheetRow(int index){
if(index < 0 || index > sheet.getLastRowNum()) {
return false;
}
return true;
}
/**
* 使用一个已经存在的row作为模板，
* 从sheet[sheetNo]的toRowNum行开始插入这个row模板的副本
*
* @param sheetNo 需要操作的Sheet的编号
* @param fromRowStartIndex 模板row区域的开始索引
* @param fromRowEndIndex 模板row区域的结束索引
* @param toRowIndex 开始插入的row索引值
* @param copyNum 复制的数量
* @param delRowTemp 是否删除模板row区域
* @return int 插入的行数量
* @throws IOException
* */
public int addRowByExist(int sheetNo,int fromRowStartIndex, int fromRowEndIndex,int toRowIndex, int copyNum,boolean delRowTemp)
throws IOException {
LinkedHashMap<Integer, LinkedList<String>> map = new LinkedHashMap<>();
for(int i = 1;i <= copyNum;i++){
map.put(i,new LinkedList<>());
}
return addRowByExist(sheetNo,fromRowStartIndex,fromRowEndIndex,toRowIndex,map,delRowTemp);
}
/**
* 使用一个已经存在的row作为模板，
* 从sheet[sheetNo]的toRowNum行开始插入这个row模板的副本,
* 并且使用areaValue从左至右，从上至下的替换掉
* row区域中值为 ${} 的单元格的值
*
* @param sheetNo 需要操作的Sheet的编号
* @param fromRowIndex 模板行的索引
* @param toRowIndex 开始插入的row索引
* @param areaValues 替换模板row区域的${}值
* @return int 插入的行数量
* @throws IOException
* */
public int addRowByExist(int sheetNo,int fromRowIndex, int toRowIndex,
LinkedHashMap<Integer,LinkedList<String>> areaValues)
throws IOException {
return addRowByExist(sheetNo,fromRowIndex,fromRowIndex,toRowIndex,areaValues,true);
}
/**
* 使用一个已经存在的行区域作为模板，
* 从sheet的toRowNum行开始插入这段行区域,
* areaValue会从左至右，从上至下的替换掉row区域
* 中值为 ${} 的单元格的值
*
* @param sheetNo 需要操作的Sheet的编号
* @param fromRowStartIndex 模板row区域的开始索引
* @param fromRowEndIndex 模板row区域的结束索引
* @param toRowIndex 开始插入的row索引
* @param areaValues 替换模板row区域的${}值
* @param delRowTemp 是否删除模板row区域
* @return int 插入的行数量
* // 第一个参数，需要操作的sheet的索引
* // 第二个参数，需要复制的区域的第一行索引（占位符所在的第一行）
* // 第三个参数，需要复制的区域的最后一行索引（占位符所在的最后一行）
* // 第四个参数，需要插入的位置的索引(占位符的下一行)
* // 第五个参数，填充行区域中${}的值
* // 第六个参数，是否需要删除原来的区域
* // 需要注意的是，行的索引一般要减一
* @throws IOException
* */
public int addRowByExist(int sheetNo,int fromRowStartIndex, int fromRowEndIndex,int toRowIndex,
LinkedHashMap<Integer,LinkedList<String>> areaValues, boolean delRowTemp)
throws IOException {
exception();
if(!examine()
|| !initSheet(sheetNo)
|| !examineSheetRow(fromRowStartIndex)
|| !examineSheetRow(fromRowEndIndex)
|| fromRowStartIndex > fromRowEndIndex) {
return 0;
}
int areaNum;List<Row> rows = new ArrayList<>();
if(areaValues != null){
int n = 0,f = areaValues.size() * (areaNum = (fromRowEndIndex - fromRowStartIndex + 1));
// 在插入前腾出空间，避免新插入的行覆盖原有的行
shiftAndCreateRows(sheetNo,toRowIndex,f);
// 读取需要插入的数据
for (Integer key:areaValues.keySet()){
List<Row> temp = new LinkedList<>();
// 插入行
for(int i = 0;i < areaNum;i++){
int num = areaNum * n + i;
Row toRow = sheet.getRow(toRowIndex + num);
Row row;
if(toRowIndex >= fromRowEndIndex) {
row = copyRow(sheetNo,sheet.getRow(fromRowStartIndex + i),sheetNo,toRow,true,true);
} else {
row = copyRow(sheetNo,sheet.getRow(fromRowStartIndex + i + f),sheetNo,toRow,true,true);
}
temp.add(row);
}
// 使用传入的值覆盖${}或者N${}
replaceMark(temp,areaValues.get(key));
rows.addAll(temp);
n++;
}
if(delRowTemp){
if(toRowIndex >= fromRowEndIndex) {
removeRowArea(sheetNo,fromRowStartIndex,fromRowEndIndex);
} else {
removeRowArea(sheetNo,fromRowStartIndex + f,fromRowEndIndex + f);
}
}
}
return rows.size();
}
/**
* 使用一个已经存在的列区域作为模板，
* 从sheet的toColumnIndex列开始插入这段列区域,
* areaValue会从上至下，从左至右的替换掉列区域
* 中值为 ${} 的单元格的值
*
* @param sheetNo 需要操作的Sheet的编号
* @param fromColumnStartIndex 模板列区域的开始索引
* @param fromColumnEndIndex 模板列区域的结束索引
* @param toColumnIndex 开始插入的列索引
* @param areaValues 替换模板列区域的${}值
* @param delColumnTemp 是否删除模板列区域
* @return int 插入的列数量
* @throws IOException
* */
public int addColumnByExist(int sheetNo,int fromColumnStartIndex, int fromColumnEndIndex,int toColumnIndex,
LinkedHashMap<Integer,LinkedList<String>> areaValues, boolean delColumnTemp)
throws IOException{
exception();
if(!examine()
|| !initSheet(sheetNo)
|| fromColumnStartIndex > fromColumnEndIndex
|| toColumnIndex < 0) {
return 0;
}
// 合并区域的列的数量
int areaNum;
List<Integer> n = new ArrayList<>();
n.add(0);
if(areaValues != null){
int f = areaValues.size() * (areaNum = (fromColumnEndIndex - fromColumnStartIndex + 1));
// 创建空白的列
shiftAndCreateColumns(sheetNo,toColumnIndex-1,f);
// 获取所有合并区域
// List<CellRangeAddress> crds = sheet.getMergedRegions();
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = sheet.getNumMergedRegions();
// 读取需要插入的数据
for (Integer key:areaValues.keySet()){
for(int i = 0;i < areaNum;i++){
// 获取插入的位置
int position = toColumnIndex + n.get(0) * areaNum + i;
// 插入的列的位置是在复制区域之后
if(toColumnIndex >= fromColumnStartIndex) {
copyColumn(sheetNo,fromColumnStartIndex + i,sheetNo,position,true);
}
// 插入的列的位置是在复制区域之前
else {
copyColumn(sheetNo,fromColumnStartIndex + i + f,sheetNo,position,true);
}
}
// 复制源列的合并区域到新添加的列
if(sheetMergeCount>0) {
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress crd = sheet.getMergedRegion(i);
// 列偏移量
int offset = toColumnIndex - fromColumnStartIndex + areaNum * n.get(0);
// 合并区域的宽度
int rangeAreaNum = crd.getLastColumn() - crd.getFirstColumn() + 1;
// 原合并区域的首列
int firstColumn = crd.getFirstColumn();
// 需要添加的合并区域首列
int addFirstColumn = firstColumn + offset;
// 根据插入的列的位置是在复制区域之前还是之后
// firstColumn和addFirstColumn分配不同的值
firstColumn = toColumnIndex >= fromColumnStartIndex ? firstColumn : firstColumn - f;
addFirstColumn = toColumnIndex >= fromColumnStartIndex ? addFirstColumn : toColumnIndex + areaNum * n.get(0);
if(firstColumn >= fromColumnStartIndex && firstColumn < fromColumnEndIndex){
if ((firstColumn + rangeAreaNum - 1) > fromColumnEndIndex) {
rangeAreaNum = fromColumnEndIndex - firstColumn + 1;
}
if(rangeAreaNum > areaNum){
mergedRegion(sheetNo,
crd.getFirstRow(),
crd.getLastRow(),
addFirstColumn,
addFirstColumn + areaNum - 1);
}
else {
mergedRegion(sheetNo,
crd.getFirstRow(),
crd.getLastRow(),
addFirstColumn,
addFirstColumn + rangeAreaNum - 1);
}
}
}
}
/*if(crds != null){
crds.forEach(crd -> {
// 列偏移量
int offset = toColumnIndex - fromColumnStartIndex + areaNum * n.get(0);
// 合并区域的宽度
int rangeAreaNum = crd.getLastColumn() - crd.getFirstColumn() + 1;
// 原合并区域的首列
int firstColumn = crd.getFirstColumn();
// 需要添加的合并区域首列
int addFirstColumn = firstColumn + offset;
// 根据插入的列的位置是在复制区域之前还是之后
// firstColumn和addFirstColumn分配不同的值
firstColumn = toColumnIndex >= fromColumnStartIndex ? firstColumn : firstColumn - f;
addFirstColumn = toColumnIndex >= fromColumnStartIndex ? addFirstColumn : toColumnIndex + areaNum * n.get(0);
if(firstColumn >= fromColumnStartIndex && firstColumn < fromColumnEndIndex){
if ((firstColumn + rangeAreaNum - 1) > fromColumnEndIndex) {
rangeAreaNum = fromColumnEndIndex - firstColumn + 1;
}
if(rangeAreaNum > areaNum){
mergedRegion(sheetNo,
crd.getFirstRow(),
crd.getLastRow(),
addFirstColumn,
addFirstColumn + areaNum - 1);
}
else {
mergedRegion(sheetNo,
crd.getFirstRow(),
crd.getLastRow(),
addFirstColumn,
addFirstColumn + rangeAreaNum - 1);
}
}
});
}*/
// 填充${}
List<String> fillValues = new ArrayList<>(areaValues.get(key));
if (fillValues == null || fillValues.size() == 0){
n.replaceAll(i -> i + 1);
continue;
}
List<Cell> needFillCells;
initCellList(sheetNo);
needFillCells = cellList;
// 获取所有的值为${}单元格
needFillCells = needFillCells.stream().filter(c -> {
// if(c != null && c.getCellTypeEnum() == CellType.STRING){
if(c != null && c.getCellType() == Cell.CELL_TYPE_STRING){
if ("${}".equals(c.getStringCellValue()) || "N${}".equals(c.getStringCellValue())) {
return true;
}
}
return false;
}).collect(Collectors.toList());
if (needFillCells == null){
n.replaceAll(i -> i + 1);
continue;
}
// 所有的${}单元格按照列从小到大，行从小到大的顺序排序
needFillCells.sort((c1,c2) -> {
if (c1 == null && c2 == null) {
return 0;
}
if (c1 == null) {
return 1;
}
if (c2 == null) {
return -1;
}
if(c1.getColumnIndex() > c2.getColumnIndex()) {
return 1;
} else if(c1.getColumnIndex() < c2.getColumnIndex()) {
return -1;
} else {
if(c1.getRowIndex() > c2.getRowIndex()) {
return 1;
} else if(c1.getRowIndex() < c2.getRowIndex()) {
return -1;
} else {
return 0;
}
}
});
needFillCells
.stream()
.filter(c -> {
if(c == null) {
return false;
}
// 筛选出当前需要填充的单元格
return c.getColumnIndex() >= toColumnIndex + areaNum * n.get(0)
&& c.getColumnIndex() <= toColumnIndex + areaNum * (n.get(0) + 1);
}).forEach(c -> {
if(fillValues.size() > 0){
// 设置为列的首行，再移除掉首行的值
String value = fillValues.stream().filter(Objects::nonNull).findFirst().orElse("");
if (doublePattern.matcher(value == null ? "": value).find()){
c.setCellValue(Double.parseDouble(value));
}
else {
c.setCellValue(value);
}
CellStyle cellStyle = c.getCellStyle();
cellStyle.setWrapText(true);
c.setCellStyle(cellStyle);
fillValues.remove(0);
}
});
n.replaceAll(i -> i + 1);
}
if(delColumnTemp){
if(toColumnIndex >= fromColumnStartIndex) {
removeColumnArea(sheetNo,fromColumnStartIndex,fromColumnEndIndex);
} else {
removeColumnArea(sheetNo,fromColumnStartIndex + f,fromColumnEndIndex + f);
}
}
}
return n.get(0);
}
/**
* 使用一个已经存在的列区域作为模板，
* 从sheet的toColumnIndex列开始插入这段列区域
*
* @param sheetNo 需要操作的Sheet的编号
* @param fromColumnStartIndex 模板列区域的开始索引
* @param fromColumnEndIndex 模板列区域的结束索引
* @param toColumnIndex 开始插入的列索引
* @param copyNum 复制数量
* @param delColumnTemp 是否删除模板列区域
* @return int 插入的列数量
* @throws IOException
* */
public int addColumnByExist(int sheetNo,int fromColumnStartIndex, int fromColumnEndIndex,int toColumnIndex,
int copyNum, boolean delColumnTemp)
throws IOException{
LinkedHashMap<Integer, LinkedList<String>> map = new LinkedHashMap<>();
for(int i = 1;i <= copyNum;i++){
map.put(i,new LinkedList<>());
}
return addColumnByExist(sheetNo,fromColumnStartIndex,fromColumnEndIndex,toColumnIndex,map,delColumnTemp);
}
/**
* 填充Excel当中的变量
*
* @param fillValues 填充的值
* @return int 受影响的变量数量
* @throws IOException
**/
public int fillVariable(Map<String,String> fillValues) throws IOException {
return fillVariable(0,fillValues);
}
/**
* 填充Excel当中的变量
*
* @param sheetNo 需要操作的Sheet的编号
* @param fillValues 填充的值
* @return int 受影响的变量数量
* 例：
* // 创建需要填充替换的值
* // ${参数名}
* // Map<String, String> fillValues = new HashMap<>();
* // //项目名称
* // fillValues.put("projectName", "测试项目");
* @throws IOException
**/
public int fillVariable(int sheetNo,Map<String,String> fillValues)
throws IOException {
exception();
if(!examine()
|| sheetNo < 0
|| sheetNo > sheets.length - 1
|| fillValues == null
|| fillValues.size() == 0) {
return 0;
}
// 验证${}格式
Pattern pattern = Pattern.compile("(\\$\\{[^\\}]+})");
// 把所有的${}按Cell分类，也就是说如果一个Cell中存在两个${}，
// 这两个变量的Cell应该一样
Map<Cell,Map<String,String>> cellVal = new HashMap<>();
List<Integer> ns = new ArrayList<>();
ns.add(0);
fillValues.forEach((k,v) ->{
// 找到变量所在的单元格
Cell cell = findCells(sheetNo,s -> {
if(s == null || "".equals(s)) {
return false;
}
Matcher matcher = pattern.matcher(s);
while(matcher.find()){
String variable = matcher.group(1);
if(variable != null
&& formatParamCode(variable).equals(k.trim())) {
return true;
}
}
return false;
}).stream().findFirst().orElse(null);
if(cell != null){
Map<String,String> cellValMap = cellVal.get(cell);
if(cellValMap == null) {
cellValMap = new HashMap<>();
}
cellValMap.put(k,v);
cellVal.put(cell,cellValMap);
ns.replaceAll(n -> n + 1);
}
});
cellVal.forEach((k,v) -> {
String cellValue = k.getStringCellValue();
String value = composeMessage(cellValue,v);
Matcher matcher = doublePattern.matcher(value == null ? "": value);
if (matcher.find()){
k.setCellValue(Double.parseDouble(value));
}
else {
k.setCellValue(value);
}
CellStyle cellStyle = k.getCellStyle();
cellStyle.setWrapText(true);
k.setCellStyle(cellStyle);
});
return ns.get(0);
}
/**
* 指定cell插入图片
*
* @param sheetNo 需要操作的Sheet的编号
* @param imageBytes 图片字节流
* @param imgType 图片类型（举例：png 传入 Workbook.PICTURE_TYPE_PNG作为参数）
* @param startRow 开始行
* @param endRow 结束行
* @param startCol 开始列
* @param endCol 结束列
* */
public void insertPicture(int sheetNo,byte[] imageBytes,int imgType,int startRow,int endRow,int startCol,int endCol) throws IOException {
exception();
if (!initSheet(sheetNo)) {
return;
}
Drawing patriarch = sheet.createDrawingPatriarch();
ClientAnchor anchor = null;
if (sheet instanceof XSSFSheet) {
anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) startCol, startRow, (short) endCol, endRow);
} else {
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) startCol, startRow, (short) endCol, endRow);
}
anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(imageBytes, imgType));
if (sheet instanceof XSSFSheet) {
List<XSSFShape> shapes = ((XSSFDrawing)patriarch).getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
picture.getPreferredSize();
}
}
else {
List<HSSFShape> list = ((HSSFSheet)sheet).getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
// picture.getClientAnchor();
picture.getAnchor();
picture.getPictureData();
}
}
}
}
/**
* 根据行坐标和列坐标定位到单元格，填充单元格
*
* @param sheetNo 需要操作的Sheet的编号
* @param rowIndex 行坐标
* @param columnIndex 列坐标
* @param value 填充的值
* @return boolean 是否成功
* @throws IOException
**/
public boolean fillByCoordinate(int sheetNo,int rowIndex,int columnIndex,String value)
throws IOException {
exception();
if(!initSheet(sheetNo)) {
return false;
}
Row row = sheet.getRow(rowIndex);
if(row == null) {
return false;
}
Cell cell = row.getCell(columnIndex);
if(cell == null) {
return false;
}
if (doublePattern.matcher(value == null ? "": value).find()){
cell.setCellValue(Double.parseDouble(value));
}
else{
cell.setCellValue(value);
}
return true;
}
/**
* 根据断言predicate查找sheet当中符合条件的cell
*
* @param sheetNo 需要操作的Sheet的编号
* @param predicate 筛选的断言
* @return List<Cell> 符合条件的Cell
* */
public List<Cell> findCells(int sheetNo,Predicate<String> predicate){
Objects.requireNonNull(predicate);
initCellList(sheetNo);
return cellList.stream()
.map(c -> {
if(c != null && c.getCellType() == Cell.CELL_TYPE_STRING) {
return c.getStringCellValue();
}
return null;
})// Cell流转换为String流
.filter(predicate)
.map(s -> cellList.stream().filter(c -> {
if(c != null && c.getCellType() == Cell.CELL_TYPE_STRING
&& s.equals(c.getStringCellValue())) {
return true;
}
return false;
}).findFirst().orElse(null))// String流重新转换位Cell流
.filter(c -> c != null)
.collect(Collectors.toList());
}
/**
* 根据断言predicate查找sheet当中符合条件的Row
*
* @param sheetNo 需要操作的Sheet的编号
* @param predicate 筛选的断言
* @return List<Row> 符合条件的Row
* */
public List<Row> findRows(int sheetNo,Predicate<Row> predicate){
if(!examine() || !initSheet(sheetNo)) {
return null;
}
List<Row> rows = new ArrayList<>();
for(int i = sheet.getFirstRowNum();i <= sheet.getLastRowNum();i++){
Row row = sheet.getRow(i);
if(predicate.test(row)) {
rows.add(row);
}
}
return rows;
}
/**
* 提取变量中的值，比如 formatParamCode("${1234}"),
* 会得到结果1234
*
* @param paramCode 需要提取的字符串
* @return String
* */
private String formatParamCode(String paramCode){
if(paramCode == null) {
return "";
}
return paramCode.replaceAll("\\$", "")
.replaceAll("\\{", "")
.replaceAll("\\}", "");
}
/**
* 使用paramData当中的值替换data当中的变量
*
* @param data 需要提取的字符串
* @param paramData 需要替换的值
* @return String
* */
private String composeMessage(String data, Map<String,String> paramData){
String regex = "\\$\\{(.+?)\\}";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(data == null ? "": data);
StringBuffer msg = new StringBuffer();
while (matcher.find()) {
String key = matcher.group(1);// 键名
String value = paramData.get(key);// 键值
if(value == null) {
value = "";
} else {
value = value.replaceAll("\\$", "\\\\\\$");
}
matcher.appendReplacement(msg, value);
}
matcher.appendTail(msg);
return msg.toString();
}
/**
* 计算excel公式中的单元格的列和行加上数值后的结果
*
* @param isColumn 是否是行的计算
* @param value 原始值
* @param addNum 添加的数量
* @return String
* */
private String addRowOrColumnIndex(boolean isColumn,String value,int addNum){
value = value == null ? "" : value;
if(isColumn){
if (!Pattern.compile("^[A-Z]+$").matcher(value).find()) {
return value;
}
char[] cs = value.toCharArray();
int cardinal = 0;
// 组合转换为数字
for (int i = cs.length - 1; i >= 0; i--) {
cardinal += Math.pow(26,cs.length - 1 - i) * (cs[i] - 64);
}
// 加上添加后的数值
cardinal += addNum;
// 不能为0
cardinal = cardinal <= 0 ? 1 : cardinal;
// 是否需要向前借一位
boolean borrowBit = false;
Stack<Character> stack = new Stack<>();
// 数字转换为组合
while (true && cardinal > 0){
int mode = cardinal % 26;
// 如果到达了第一位
if(cardinal >= 1 && cardinal < 26){
// 是否需要借位
if (borrowBit) {
mode -= 1;
}
// 首位借位之后必须大于0才能添加
if (mode > 0) {
stack.add((char)(mode + 64));
}
break;
}
cardinal -= mode;
cardinal /= 26;
if (borrowBit){
if (mode != 0) {
mode -= 1;
}
// 如果借位的时候，发现本身也为0，需要向前再借位
else{
mode = 25;
borrowBit = true;
stack.add((char)(mode + 64));
continue;
}
}
if (mode == 0){
mode = 26;
borrowBit = true;
}
else {
borrowBit = false;
}
stack.add((char)(mode + 64));
}
int size = stack.size();
char[] chars = new char[size];
for (int j = size - 1; j >= 0; j--) {
chars[size - 1 - j] = stack.get(j);
}
return new String(chars);
}
else {
if(!Pattern.compile("^[0-9]+$").matcher(value).find()) {
return value;
}
try{
int intValue = Integer.parseInt(value);
intValue += addNum;
if (intValue <= 0) {
return "1";
}
return Integer.toString(intValue);
}catch (NumberFormatException e){
return value;
}
}
}
/**
* 修改公式里面单元格参数的坐标
*
* @param formula 公式
* @param index 第几个单元格参数
* @param rowAddNum 给行添加的数量
* @param columnAddNum 给列添加的数量
* @return String
* */
private String composeFormula(String formula,int index,
int rowAddNum,int columnAddNum){
String regex = "[A-Z]+[0-9]+";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(formula == null ? "" : formula);
List<String> valueList = new LinkedList<>();
String oldFormula = formula;
while(matcher.find()){
String value = matcher.group();
valueList.add(value);
formula = formula.replaceFirst(value,"@&");
}
if (index >= 0 && index < valueList.size()){
String value = valueList.get(index);
Matcher columnMatcher = Pattern.compile("[A-Z]+").matcher(value);
String newValue = value;
if (columnMatcher.find()){
String columnIndex = columnMatcher.group();
String rowIndex = value.replaceAll(columnIndex,"");
columnIndex = addRowOrColumnIndex(true,columnIndex,columnAddNum);
rowIndex = addRowOrColumnIndex(false,rowIndex,rowAddNum);
newValue = columnIndex + rowIndex;
}
valueList.set(index,newValue);
}
String[] spilts = formula.split("@&");
if (spilts.length == 0){
if (valueList.size() == 1) {
return valueList.get(0);
}
return oldFormula;
}
StringBuffer newFormula = new StringBuffer();
int position = 0;
for (int i = 0; i < spilts.length; i++) {
newFormula.append(spilts[i]);
if (position < valueList.size()){
newFormula.append(valueList.get(position++));
}
}
return newFormula.toString();
}
/**
* 获取单元格里面公式的变量数量
* 例如公式 SUM(AP40:AV40)，含有两个单元格变量 AP40和AV40，
* 使用此方法会返回2
*
* @param cell 需要操作的单元格
* @return int 单元格变量的数量
* */
public int getFormulaVariableNum(Cell cell){
if (cell == null || cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
return 0;
}
String formula = cell.getCellFormula();
Matcher matcher = Pattern.compile("[A-Z]+[0-9]+").matcher(formula == null ? "" : formula);
int count = 0;
while(matcher.find()){
count++;
}
return count;
}
/**
* 修改单元格的公式的参数
* excel的所有列按照如下规则分布，
* A,B,C,D...Z,AA,AB...AZ,BA,BB...BZ...以此类推，
* 你可以看成是一个关于A,B,C...Z的排列组合问题
*
* 举例：
* 单元格cell的公式为 SUM(AP40:AV40) 是求单元格 AP40 到 AV40的单元格的和，
* 其中AP40中的AP表示单元格的列坐标，40表示横坐标，AV40类推。
* 如果使用方法 composeCellFormula(cell,0,2,5)，则cell的公式会修改为 SUM(AU42:AV40)
*
* @param cell 需要修改的单元格
* @param index 第几个单元格参数
* @param rowAddNum 给行添加的数量
* @param columnAddNum 给列添加的数量
* @return String
* */
public void composeCellFormula(Cell cell,int index,
int rowAddNum,int columnAddNum){
if (cell == null || cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
return;
}
if (cell instanceof HSSFCell) {
throw new IllegalArgumentException("4.1.1及之前的版本的POI的处理xls文件的公式单元格有bug，建议换成xlsx文件，" +
"或者去官网查看哪个版本修复了这个bug(https://bz.apache.org/bugzilla/show_bug.cgi?id=64517)，换成此版本POI。" +
"如果引用的POI版本已经是修复了此BUG的版本的POI，可以删掉这个异常提示！");
}
String formula = cell.getCellFormula();
cell.setCellFormula(composeFormula(formula,index,rowAddNum,columnAddNum));
}
// 初始化cellList
private void initCellList(int sheetNo){
cellList = new ArrayList<>();
if(examine() && !initSheet(sheetNo)) {
return;
}
int rn = sheet.getLastRowNum();
for(int i = 0;i <= rn;i++){
Row row = sheet.getRow(i);
if(row != null){
short cn = row.getLastCellNum();
for (int j = 0;j < cn;j++){
cellList.add(row.getCell(j));
}
}
}
}
/**
* 替换掉所有行区域中的所有 ${} 标记
* valueList对rows中${}替换的顺序是：
* 从左至右，从上到下
*
* @param rows 行区域
* @param vl 替换的值
* */
private void replaceMark(List<Row> rows,List<String> vl){
if (rows == null || vl == null) {
return;
}
List<String> valueList = new ArrayList<>(vl);
rows.forEach(r -> {
if(r != null){
r.forEach(c -> {
if (c != null){
if (c.getCellType() == Cell.CELL_TYPE_STRING){
if("${}".equals(Optional.ofNullable(c.getStringCellValue()).orElse("").trim())){
if(valueList == null) {
return;
}
String value = valueList.stream().filter(Objects::nonNull).findFirst().orElse(null);
c.setCellValue(value);
CellStyle cellStyle = c.getCellStyle();
cellStyle.setWrapText(true);
c.setCellStyle(cellStyle);
if(value != null) {
valueList.remove(valueList.indexOf(value));
}
}
else if("N${}".equals(Optional.ofNullable(c.getStringCellValue()).orElse("").trim())){
if(valueList == null) {
return;
}
String value = valueList.stream().filter(Objects::nonNull).findFirst().orElse(null);
Matcher matcher = doublePattern.matcher(value == null ? "" : value);
if (matcher.find()){
c.setCellValue(Double.parseDouble(value));
CellStyle cellStyle = c.getCellStyle();
cellStyle.setWrapText(true);
c.setCellStyle(cellStyle);
if(value != null) {
valueList.remove(valueList.indexOf(value));
}
}
else {
throw new IllegalArgumentException("N${} 所替换的内容只能为数字,非法参数\"" + value + "\"");
}
}
}
}
});
}
});
}
/**
* 复制Row到sheet中的另一个Row
*
* @param fromSheetNo 复制的行所在的sheet
* @param fromRow 需要复制的行
* @param toSheetNo 粘贴的行所在的sheet
* @param toRow 粘贴的行
* @param copyValueFlag 是否需要复制值
* @param needMerged 是否需要合并单元格
*/
private Row copyRow(int fromSheetNo,Row fromRow, int toSheetNo,Row toRow, boolean copyValueFlag,boolean needMerged) {
if(fromSheetNo < 0 || fromSheetNo > workbook.getNumberOfSheets()
|| toSheetNo < 0 || toSheetNo > workbook.getNumberOfSheets()) {
return null;
}
if (fromRow == null) {
return null;
}
if(toRow == null){
Sheet sheet = workbook.getSheetAt(toSheetNo);
if(sheet == null) {
return null;
}
toRow = sheet.createRow(fromRow.getRowNum());
if(toRow == null) {
return null;
}
}
// 设置高度
toRow.setHeight(fromRow.getHeight());
// 遍历行中的单元格
for(Cell c:fromRow){
Cell newCell = toRow.createCell(c.getColumnIndex());
copyCell(c, newCell, copyValueFlag);
}
// 如果需要合并
if(needMerged){
Sheet fromSheet = workbook.getSheetAt(fromSheetNo);
Sheet toSheet = workbook.getSheetAt(toSheetNo);
// 遍历行当中的所有的合并区域
// List<CellRangeAddress> crds = fromSheet.getMergedRegions();
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = fromSheet.getNumMergedRegions();
if(sheetMergeCount>0){
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress crd = null;
try {
crd = sheet.getMergedRegion(i);
// 如果当前合并区域的首行为复制的源行
if(crd.getFirstRow() == fromRow.getRowNum()) {
// 创建对应的合并区域
CellRangeAddress newCellRangeAddress = new CellRangeAddress(
toRow.getRowNum(),
(toRow.getRowNum() + (crd.getLastRow() - crd.getFirstRow())),
crd.getFirstColumn(),
crd.getLastColumn());
// 添加合并区域
safeMergedRegion(toSheetNo,newCellRangeAddress);
}//This worksheet does not contain merged regions
}catch (Exception e){
e.printStackTrace();
}
}
}
}
return toRow;
}
/**
* 复制sheet中列的另一列
*
* @param fromSheetNo 复制的行所在的sheet
* @param fromColumnIndex 需要复制的行索引
* @param toSheetNo 粘贴的行所在的sheet
* @param toColumnIndex 粘贴的行
* @param copyValueFlag 是否需要复制值
*/
private void copyColumn(int fromSheetNo,int fromColumnIndex,int toSheetNo,
int toColumnIndex,boolean copyValueFlag) {
if(fromSheetNo < 0 || fromSheetNo > workbook.getNumberOfSheets()
|| toSheetNo < 0 || toSheetNo > workbook.getNumberOfSheets()) {
return;
}
Sheet fromSheet = workbook.getSheetAt(fromSheetNo);
Sheet toSheet = workbook.getSheetAt(toSheetNo);
for(int i = 0;i <= fromSheet.getLastRowNum();i++){
Row fromRow = fromSheet.getRow(i);
Row toRow = toSheet.getRow(i);
if(fromRow == null) {
continue;
}
if(toRow == null) {
toRow = toSheet.createRow(i);
}
if(toRow == null) {
continue;
}
// 设置高度
toRow.setHeight(fromRow.getHeight());
Cell srcCell = fromRow.getCell(fromColumnIndex);
Cell distCell = toRow.getCell(toColumnIndex);
if(srcCell == null) {
continue;
}
if(distCell == null) {
distCell = toRow.createCell(toColumnIndex);
}
// 设置列宽
toSheet.setColumnWidth(toColumnIndex,fromSheet.getColumnWidth(fromColumnIndex));
copyCell(srcCell,distCell,copyValueFlag);
}
}
/**
* 删除sheet的一行
* @param fromSheetNo 行所在的sheet
* @param deleteRow 需要删除的行 从0开始数
*/
public synchronized void deleteRow(int fromSheetNo,int deleteRow){
if(fromSheetNo < 0 || fromSheetNo > workbook.getNumberOfSheets() ) {
return;
}
Sheet fromSheet = workbook.getSheetAt(fromSheetNo);
if(fromSheet.getRow(deleteRow) != null) {
fromSheet.removeRow(fromSheet.getRow(deleteRow));
if(deleteRow < sheet.getLastRowNum()) {
sheet.shiftRows(deleteRow + 1, sheet.getLastRowNum(), -1);
}
}
}
/**
* 复制Cell到sheet中的另一个Cell
*
* @param srcCell 需要复制的单元格
* @param distCell 粘贴的单元格
* @param copyValueFlag true则连同cell的内容一起复制
*/
private void copyCell(Cell srcCell, Cell distCell, boolean copyValueFlag) {
if (srcCell == null || distCell == null) {
return;
}
// 获取源单元格的样式
CellStyle srcStyle = srcCell.getCellStyle();
// 复制样式
distCell.setCellStyle(srcStyle);
// 复制评论
if(srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
// 不同数据类型处理
int srcCellType = srcCell.getCellType();
if(copyValueFlag) {
if(srcCellType == Cell.CELL_TYPE_NUMERIC) {
if(DateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if(srcCellType == Cell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if(srcCellType == Cell.CELL_TYPE_BLANK) {
} else if(srcCellType == Cell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if(srcCellType == Cell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if(srcCellType == Cell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else {
}
}
/*CellType srcCellType = srcCell.getCellTypeEnum();
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());
} else {
}
}*/
}
/**
* 合并单元格区域，本方法是安全的操作，在出现合并冲突的时候，
* 分割合并区域，然后最大限度的合并冲突区域
*
* 使用此方法而不是采用addMergedRegion()和
* addMergedRegionUnsafe()合并单元格区间，
* 因为此方法会自行解决合并区间冲突，避免报错或者生成
* 无法打开的excel
*
* @param sheetNo 需要操作的Sheet的编号
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
* */
public void mergedRegion(int sheetNo,int firstRow, int lastRow, int firstCol, int lastCol){
if(firstRow > lastRow || firstCol > lastCol) {
return;
}
CellRangeAddress address = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);
safeMergedRegion(sheetNo,address);
}
/**
* 合并单元格区域，本方法是安全的操作，在出现合并冲突的时候，
* 分割合并区域，然后最大限度的合并冲突区域
*
* @param sheetNo 需要操作的Sheet的编号
* @param rangeAddress 合并的单元格区域
* */
private void safeMergedRegion(int sheetNo,CellRangeAddress rangeAddress){
if(!examine() || !initSheet(sheetNo) || rangeAddress == null) {
return;
}
// 获取所有合并的区域
/* List<CellRangeAddress> crds = sheet.getMergedRegions();
if(crds == null) {
return;
}*/
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = sheet.getNumMergedRegions();
if(sheetMergeCount<=0){
return;
}
// 获取描述单元格区域的坐标，
// 在首行和首列，坐标等于行编号，
// 在末行和末列，坐标等于行编号加1
int firstRow = rangeAddress.getFirstRow();
int lastRow = rangeAddress.getLastRow() + 1;
int firstColumn = rangeAddress.getFirstColumn();
int lastColumn = rangeAddress.getLastColumn() + 1;
// 查找冲突的单元格区域
CellRangeAddress conflictRange = null;
Boolean checkFlagConflit = false;
for (int i = 0; i < sheetMergeCount; i++) {
if(checkFlagConflit){
continue;
}
CellRangeAddress crd = sheet.getMergedRegion(i);
// 获取单元格区域的坐标
int cFirstRow = crd.getFirstRow();
int cLastRow = crd.getLastRow() + 1;
int cFirstColumn = crd.getFirstColumn();
int cLastColumn = crd.getLastColumn() + 1;
// 每个合并单元格区域看成一个长方形
// 计算两个长方形中心的X坐标的距离
float xDistance = (float)(lastColumn + firstColumn)/2
- (float)(cLastColumn + cFirstColumn)/2;
// 每个合并单元格区域看成一个长方形
// 计算两个长方形中心的Y坐标的距离
float yDistance = (float)(lastRow + firstRow)/2
- (float)(cLastRow + cFirstRow)/2;
// 获取距离的绝对值
xDistance = xDistance >= 0 ? xDistance : -xDistance;
yDistance = yDistance >= 0 ? yDistance : -yDistance;
// 如果两个合并区域相交了，返回true
if(xDistance < ((float)(lastColumn - firstColumn)/2 + (float)(cLastColumn - cFirstColumn)/2)
&& yDistance < ((float)(lastRow - firstRow)/2 + (float)(cLastRow - cFirstRow)/2)) {
checkFlagConflit = true;
conflictRange = crd;
}
}
/*CellRangeAddress conflictRange = crds.stream()
.filter(crd -> {
// 获取单元格区域的坐标
int cFirstRow = crd.getFirstRow();
int cLastRow = crd.getLastRow() + 1;
int cFirstColumn = crd.getFirstColumn();
int cLastColumn = crd.getLastColumn() + 1;
// 每个合并单元格区域看成一个长方形
// 计算两个长方形中心的X坐标的距离
float xDistance = (float)(lastColumn + firstColumn)/2
- (float)(cLastColumn + cFirstColumn)/2;
// 每个合并单元格区域看成一个长方形
// 计算两个长方形中心的Y坐标的距离
float yDistance = (float)(lastRow + firstRow)/2
- (float)(cLastRow + cFirstRow)/2;
// 获取距离的绝对值
xDistance = xDistance >= 0 ? xDistance : -xDistance;
yDistance = yDistance >= 0 ? yDistance : -yDistance;
// 如果两个合并区域相交了，返回true
if(xDistance < ((float)(lastColumn - firstColumn)/2 + (float)(cLastColumn - cFirstColumn)/2)
&& yDistance < ((float)(lastRow - firstRow)/2 + (float)(cLastRow - cFirstRow)/2)) {
return true;
}
return false;
})
.findFirst()
.orElse(null);*/
// 如果没有查找到冲突的区域，直接合并
if(conflictRange == null){
if(examineRange(rangeAddress)) {
sheet.addMergedRegion(rangeAddress);
}
}
// 如果合并区域冲突了，分离新增的合并区域
List<CellRangeAddress> splitRangeAddr = splitRangeAddress(conflictRange,rangeAddress);
if(splitRangeAddr != null) {
splitRangeAddr.forEach(sra -> safeMergedRegion(sheetNo,sra));
}
}
/**
* 如果插入的目标合并区域target和sheet中已存在的合并区域source冲突，
* 把target分割成多个合并区域，这些合并区域都不会和source冲突
*
* @param source 已经存在的合并单元格区域
* @param target 新增的合并单元格区域
* @return target分离之后的合并单元格列表
* */
private List<CellRangeAddress> splitRangeAddress(CellRangeAddress source,CellRangeAddress target){
List<CellRangeAddress> splitRangeAddr = null;
if(source == null || target == null) {
return null;
}
// 获取source区域的坐标
int sFirstRow = source.getFirstRow();
int sLastRow = source.getLastRow() + 1;
int sFirstColumn = source.getFirstColumn();
int sLastColumn = source.getLastColumn() + 1;
// 获取target区域的坐标
int tFirstRow = target.getFirstRow();
int tLastRow = target.getLastRow() + 1;
int tFirstColumn = target.getFirstColumn();
int tLastColumn = target.getLastColumn() + 1;
while(true){
if(splitRangeAddr == null) {
splitRangeAddr = new ArrayList<>();
}
// 如果target被切分得无法越过source合并区域，退出循环
if(tFirstRow >= sFirstRow && tLastRow <= sLastRow
&& tFirstColumn >= sFirstColumn && tLastColumn <= sLastColumn) {
break;
}
// 只考虑Y坐标，当source的最大Y坐标sLastRow在开区间(tFirstRow,tLastRow)
if(sLastRow > tFirstRow && sLastRow < tLastRow){
CellRangeAddress address =
new CellRangeAddress(sLastRow,tLastRow - 1,tFirstColumn,tLastColumn - 1);
tLastRow = sLastRow;
if(examineRange(address)) {
splitRangeAddr.add(address);
}
}
// 只考虑Y坐标，当source的最小Y坐标sFirstRow在开区间(tFirstRow,tLastRow)
if(sFirstRow > tFirstRow && sFirstRow < tLastRow){
CellRangeAddress address =
new CellRangeAddress(tFirstRow,sFirstRow - 1,tFirstColumn,tLastColumn - 1);
tFirstRow = sFirstRow;
if(examineRange(address)) {
splitRangeAddr.add(address);
}
}
// 只考虑X坐标，当source的最小X坐标sFirstColumn在开区间(tFirstColumn,tLastColumn)
if(sFirstColumn > tFirstColumn && sFirstColumn < tLastColumn){
CellRangeAddress address =
new CellRangeAddress(tFirstRow,tLastRow - 1,tFirstColumn,sFirstColumn - 1);
tFirstColumn = sFirstColumn;
if(examineRange(address)) {
splitRangeAddr.add(address);
}
}
// 只考虑X坐标，当source的最大X坐标sLastColumn在开区间(tFirstColumn,tLastColumn)
if(sLastColumn > tFirstColumn && sLastColumn < tLastColumn){
CellRangeAddress address =
new CellRangeAddress(tFirstRow,tLastRow - 1,sLastColumn,tLastColumn - 1);
tLastColumn = sLastColumn;
if(examineRange(address)) {
splitRangeAddr.add(address);
}
}
}
return splitRangeAddr;
}
// 检查合并区域
private boolean examineRange(CellRangeAddress address){
if(address == null || !examine()) {
return false;
}
int firstRowNum = address.getFirstRow();
int lastRowNum = address.getLastRow();
int firstColumnNum = address.getFirstColumn();
int lastColumnNum = address.getLastColumn();
if(firstRowNum == lastRowNum && firstColumnNum == lastColumnNum) {
return false;
}
return true;
}
private void exception() throws EncryptedDocumentException, IOException {
if(ex != null){
if(ex instanceof EncryptedDocumentException) {
throw new EncryptedDocumentException("无法读取的加密文件");
} else if(ex instanceof IOException) {
throw new IOException(ex);
} else {
return;
}
}
}
/**
* 获取sheet的所有有合并行的 CellRangeAddress
* @param sheetNo sheet编号
* @return
*/
public List<CellRangeAddress> hasMergeCell(int sheetNo){
List<CellRangeAddress> list = new ArrayList<>();
Sheet fromSheet = workbook.getSheetAt(sheetNo);
int countMerge = fromSheet.getNumMergedRegions();
for (int fi = 0; fi<countMerge; fi++ ) {
list.add( fromSheet.getMergedRegion(fi));
}
return list;
}
/**
* 重新设置sheet的所有合并行
* 因为当使用了 excel.addRowByExist 这个方法，里面有调用shiftAndCreateRows方法。 这个是有副作用，会使
* 后面下移的行的合并单元格会部分消失，所以需要手动恢复合并行的列。
* @param sheetNo sheet编号
* @param listMergeRow sheet需要合并行的 CellRangeAddress
* @param fromAddRow 从这一行开始下移
* @param addTotalRow 一共下移多少行
*/
public void addMergeRowCell(int sheetNo,List<CellRangeAddress> listMergeRow,int fromAddRow,int addTotalRow){
Sheet fromSheet = workbook.getSheetAt(sheetNo);
for (CellRangeAddress cel : listMergeRow){
if(cel.getFirstRow()>=fromAddRow){
// 如果合并的行在下移的那一行后面，则要重新计算
cel.setFirstRow(cel.getFirstRow()+addTotalRow-1);
cel.setLastRow(cel.getLastRow()+addTotalRow-1);
}
fromSheet.addMergedRegion(cel);
}
}
/**
* 把sheet[sheetNo]当中所有的行从startRow位置开始，
* 全部下移moveNum数量的位置，并且在腾出的空间当中创建新行
*
* 应该使用本方法而不是采用sheet.shiftRows()和sheet.createRow()，
* 主要是因为插入一段行的时候会进行如下步骤：
* 第一：使用shiftRows腾出空间
* 第二：使用createRow(position)从position开始创建行
* 但是这样，后面下移的行的合并单元格会部分消失，
* 并且新创建的行的合并单元格并没有消失，这是因为sheet当中的
* 大于position的CellRangeAddress并没有跟着下移。
* 而使用本方法下移并且在中间自动插入行，新插入的行不会含有任何合并单元格，
* 并且原来的合并单元格也不会消失。
*
* @param sheetNo 需要操作的Sheet的编号
* @param startRow 移动的Row区间的起始位置
* @param moveNum 移动的行数
* */
public synchronized void shiftAndCreateRows(int sheetNo,int startRow,int moveNum){
if(!examine() || !initSheet(sheetNo))
return;
// 复制当前需要操作的sheet到一个临时的sheet
Sheet tempSheet = workbook.cloneSheet(sheetNo);
// 获取临时sheet在workbook当中的索引
int tempSheetNo = workbook.getSheetIndex(tempSheet);
// 得到临时sheet的第一个row的索引
int firstRowNum = tempSheet.getFirstRowNum();
// 得到临时sheet的最后一个row的索引
int lastRowNum = tempSheet.getLastRowNum();
if(!clearSheet(sheetNo)){
return;
}
if (startRow <= lastRowNum){
for(int i= firstRowNum;i <= lastRowNum - firstRowNum + moveNum + 1;i++) {
sheet.createRow(i);
}
}
else {
for(int i= firstRowNum;i <= startRow + moveNum + 1;i++) {
sheet.createRow(i);
}
}
for(int i= firstRowNum;i <= lastRowNum;i++){
if(i < startRow) {
copyRow(tempSheetNo,tempSheet.getRow(i),sheetNo,sheet.getRow(i),true,true);
}
// 到达需要插入的索引的位置，需要留出moveNum空间的行
else {
copyRow(tempSheetNo,tempSheet.getRow(i),sheetNo,sheet.getRow(i + moveNum),true,true);
}
}
settingColumnWidth(tempSheetNo,sheetNo);
// 删除临时的sheet
workbook.removeSheetAt(tempSheetNo);
}
/**
* 把sheet[sheetNo]当中所有的列从startColumn位置开始，
* 全部右移moveNum数量的位置，并且在腾出的空间当中创建新列
*
* @param sheetNo 需要操作的Sheet的编号
* @param startColumn 移动的列区间的起始位置
* @param moveNum 移动的列数
* */
public synchronized void shiftAndCreateColumns(int sheetNo,int startColumn,int moveNum){
if(!examine() || !initSheet(sheetNo))
return;
// 复制当前需要操作的sheet到一个临时的sheet
Sheet tempSheet = workbook.cloneSheet(sheetNo);
// 获取临时sheet在workbook当中的索引
int tempSheetNo = workbook.getSheetIndex(tempSheet);
// 得到临时sheet的第一个row的索引
int firstRowNum = tempSheet.getFirstRowNum();
// 得到临时sheet的最后一个row的索引
int lastRowNum = tempSheet.getLastRowNum();
if(!clearSheet(sheetNo)){
return;
}
for(int i = firstRowNum;i <= lastRowNum;i++){
Row row = tempSheet.getRow(i);
if(row != null){
int addNum = row.getLastCellNum() + moveNum;
for(int j = 0;j < moveNum;j++){
row.createCell(addNum);
}
for(int j = 0;j <= row.getLastCellNum();j++){
if(j <= startColumn) {
copyColumn(tempSheetNo,j,sheetNo,j,true);
} else {
copyColumn(tempSheetNo,j,sheetNo,j + moveNum,true);
}
}
}
}
/*List<CellRangeAddress> crds = tempSheet.getMergedRegions();
if(crds == null) {
return;
}*/
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = sheet.getNumMergedRegions();
if(sheetMergeCount<=0){
return;
}
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress crd = sheet.getMergedRegion(i);
int firstColumn;
int lastColumn;
if((lastColumn = crd.getLastColumn()) <= startColumn) {
safeMergedRegion(sheetNo,crd);
} else if((firstColumn = crd.getFirstColumn()) <= startColumn){
if(lastColumn > startColumn){
CellRangeAddress range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),firstColumn,startColumn);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),
startColumn + moveNum + 1,lastColumn + moveNum);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
}
}
else if(firstColumn > startColumn){
CellRangeAddress range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),
firstColumn + moveNum,lastColumn + moveNum);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
}
}
/*crds.forEach(crd -> {
int firstColumn;
int lastColumn;
if((lastColumn = crd.getLastColumn()) <= startColumn) {
safeMergedRegion(sheetNo,crd);
} else if((firstColumn = crd.getFirstColumn()) <= startColumn){
if(lastColumn > startColumn){
CellRangeAddress range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),firstColumn,startColumn);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),
startColumn + moveNum + 1,lastColumn + moveNum);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
}
}
else if(firstColumn > startColumn){
CellRangeAddress range = new CellRangeAddress(crd.getFirstRow(),crd.getLastRow(),
firstColumn + moveNum,lastColumn + moveNum);
if(examineRange(range)) {
safeMergedRegion(sheetNo,range);
}
}
});*/
// 删除临时的sheet
workbook.removeSheetAt(tempSheetNo);
}
/**
* 移除掉行区域
*
* @param sheetNo 需要操作的Sheet的编号
* @param startRow 起始行
* @param endRow 结束行
* */
public synchronized void removeRowArea(int sheetNo,int startRow,int endRow){
if(!examine() || !initSheet(sheetNo) || startRow > endRow)
return;
// 复制当前需要操作的sheet到一个临时的sheet
Sheet tempSheet = workbook.cloneSheet(sheetNo);
// 获取临时sheet在workbook当中的索引
int tempSheetNo = workbook.getSheetIndex(tempSheet);
// 得到临时sheet的第一个row的索引
int firstRowNum = tempSheet.getFirstRowNum();
// 得到临时sheet的最后一个row的索引
int lastRowNum = tempSheet.getLastRowNum();
// 清空sheet
if(!clearSheet(sheetNo)){
return;
}
int delNum = endRow - startRow + 1;
for(int i = firstRowNum;i <= lastRowNum;i++){
Row fromRow = tempSheet.getRow(i);
Row toRow = sheet.createRow(i);
if(i < startRow) {
copyRow(tempSheetNo,fromRow,sheetNo,toRow,true,false);
} else {
copyRow(tempSheetNo,tempSheet.getRow(i + delNum),sheetNo,toRow,true,false);
}
}
/*List<CellRangeAddress> crds = tempSheet.getMergedRegions();
if(crds == null) {
return;
}*/
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = sheet.getNumMergedRegions();
if(sheetMergeCount<=0){
return;
}
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress crd = sheet.getMergedRegion(i);
if(crd != null){
int firstMergedRow = crd.getFirstRow();
int lastMergedRow = crd.getLastRow();
int firstMergedColumn = crd.getFirstColumn();
int lastMergedClolunm = crd.getLastColumn();
if(lastMergedRow < startRow) {
safeMergedRegion(sheetNo,crd);
} else if(lastMergedRow >= startRow){
if(lastMergedRow <= endRow){
if(firstMergedRow < startRow){
mergedRegion(sheetNo,firstMergedRow,startRow - 1,firstMergedColumn,lastMergedClolunm);
}
}
else if(lastMergedRow > endRow){
if(firstMergedRow < startRow){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
else if(firstMergedRow >= startRow && firstMergedRow <= endRow){
mergedRegion(sheetNo,endRow + 1 - delNum,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
else if(firstMergedRow > endRow){
mergedRegion(sheetNo,firstMergedRow - delNum,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
}
}
}
}
/*crds.forEach(crd -> {
if(crd != null){
int firstMergedRow = crd.getFirstRow();
int lastMergedRow = crd.getLastRow();
int firstMergedColumn = crd.getFirstColumn();
int lastMergedClolunm = crd.getLastColumn();
if(lastMergedRow < startRow) {
safeMergedRegion(sheetNo,crd);
} else if(lastMergedRow >= startRow){
if(lastMergedRow <= endRow){
if(firstMergedRow < startRow){
mergedRegion(sheetNo,firstMergedRow,startRow - 1,firstMergedColumn,lastMergedClolunm);
}
}
else if(lastMergedRow > endRow){
if(firstMergedRow < startRow){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
else if(firstMergedRow >= startRow && firstMergedRow <= endRow){
mergedRegion(sheetNo,endRow + 1 - delNum,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
else if(firstMergedRow > endRow){
mergedRegion(sheetNo,firstMergedRow - delNum,lastMergedRow - delNum,firstMergedColumn,lastMergedClolunm);
}
}
}
}
});*/
settingColumnWidth(tempSheetNo,sheetNo);
// 删除临时的sheet
workbook.removeSheetAt(tempSheetNo);
}
/**
* 移除掉列区域
*
* @param sheetNo 需要操作的Sheet的编号
* @param startCol 起始列
* @param endCol 结束列
* */
public synchronized void removeColumnArea(int sheetNo,int startCol,int endCol){
if(!examine() || !initSheet(sheetNo) || startCol > endCol)
return;
// 复制当前需要操作的sheet到一个临时的sheet
Sheet tempSheet = workbook.cloneSheet(sheetNo);
// 获取临时sheet在workbook当中的索引
int tempSheetNo = workbook.getSheetIndex(tempSheet);
// 得到临时sheet的第一个row的索引
int firstRowNum = tempSheet.getFirstRowNum();
// 得到临时sheet的最后一个row的索引
int lastRowNum = tempSheet.getLastRowNum();
if(!clearSheet(sheetNo)){
return;
}
for(int i = firstRowNum;i <= lastRowNum;i++){
Row row = tempSheet.getRow(i);
if(row != null){
for(int j = 0;j < row.getLastCellNum();j++){
// 到达删除区间之前正常复制
if(j < startCol) {
copyColumn(tempSheetNo,j,sheetNo,j,true);
}
// 到达删除区间后，跳过区间长度复制
else {
copyColumn(tempSheetNo,j + endCol - startCol + 1,sheetNo,j,true);
}
}
}
}
/*List<CellRangeAddress> crds = tempSheet.getMergedRegions();
if(crds == null) {
return;
}*/
//获取合并单元格的总数，并循环每一个合并单元格，
int sheetMergeCount = sheet.getNumMergedRegions();
if(sheetMergeCount<=0){
return;
}
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress crd = sheet.getMergedRegion(i);
if (crd != null) {
int delColNum = endCol - startCol + 1;
int firstMergedRow = crd.getFirstRow();
int lastMergedRow = crd.getLastRow();
int firstMergedColumn = crd.getFirstColumn();
int lastMergedClolunm = crd.getLastColumn();
if(lastMergedClolunm < startCol) {
safeMergedRegion(sheetNo,crd);
} else if(lastMergedClolunm >= startCol){
if(lastMergedClolunm <= endCol){
if(firstMergedColumn < startCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn,startCol - 1);
}
}
else if(lastMergedClolunm > endCol){
if(firstMergedColumn < startCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn,lastMergedClolunm - delColNum);
}
else if(firstMergedColumn >= startCol && firstMergedColumn <= endCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,endCol + 1 - delColNum,lastMergedClolunm - delColNum);
}
else if(firstMergedColumn > endCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn - delColNum,lastMergedClolunm -delColNum);
}
}
}
}
}
/*crds.forEach(crd -> {
int delColNum = endCol - startCol + 1;
int firstMergedRow = crd.getFirstRow();
int lastMergedRow = crd.getLastRow();
int firstMergedColumn = crd.getFirstColumn();
int lastMergedClolunm = crd.getLastColumn();
if(lastMergedClolunm < startCol) {
safeMergedRegion(sheetNo,crd);
} else if(lastMergedClolunm >= startCol){
if(lastMergedClolunm <= endCol){
if(firstMergedColumn < startCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn,startCol - 1);
}
}
else if(lastMergedClolunm > endCol){
if(firstMergedColumn < startCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn,lastMergedClolunm - delColNum);
}
else if(firstMergedColumn >= startCol && firstMergedColumn <= endCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,endCol + 1 - delColNum,lastMergedClolunm - delColNum);
}
else if(firstMergedColumn > endCol){
mergedRegion(sheetNo,firstMergedRow,lastMergedRow,firstMergedColumn - delColNum,lastMergedClolunm -delColNum);
}
}
}
});*/
// 删除临时的sheet
workbook.removeSheetAt(tempSheetNo);
}
private void settingColumnWidth(int sourceSheetNo,int sheetNo){
if(sourceSheetNo < 0 || sourceSheetNo > workbook.getNumberOfSheets() ||
sheetNo < 0 || sheetNo > workbook.getNumberOfSheets()) {
return;
}
List<Row> rows = new ArrayList<>();
for(int i = sheet.getFirstRowNum();i <= sheet.getLastRowNum();i++){
Row row = sheet.getRow(i);
if(row != null) {
rows.add(row);
}
}
Row maxColumnRow = rows.stream().max((r1,r2) -> {
if (r1 == null && r2 == null) {
return 0;
}
if (r1 == null) {
return 1;
}
if (r2 == null) {
return -1;
}
if (r1.getLastCellNum() == r2.getLastCellNum()) {
return 0;
}
if (r1.getLastCellNum() > r2.getLastCellNum()) {
return 1;
} else {
return -1;
}
}).filter(r -> r != null).orElse(null);
if(maxColumnRow != null){
int maxColumn = maxColumnRow.getLastCellNum();
for (int i = 0; i < maxColumn; i++) {
workbook.getSheetAt(sheetNo).setColumnWidth(i,workbook.getSheetAt(sourceSheetNo).getColumnWidth(i));
}
}
}
/**
* 清除掉sheet，清除不是删除，只是会清除所有
* 的列的值和和合并单元格
*
* @param sheetNo 需要操作的Sheet的编号
* @return boolean true-成功 false-失败
* */
public synchronized boolean clearSheet(int sheetNo){
if(!examine()) {
return false;
}
int sheetNum;
if(sheetNo < 0 || sheetNo > (sheetNum = workbook.getNumberOfSheets())) {
return false;
}
for(int i = 0;i < sheetNum;i++){
if(i == sheetNo){
String sheetName = workbook.getSheetName(i);
workbook.removeSheetAt(i);
workbook.createSheet(sheetName);
}
if(i > sheetNo){
int offset = i - sheetNo;
String sheetName = workbook.getSheetName(i-offset);
Sheet newSheet = workbook.cloneSheet(i-offset);
workbook.removeSheetAt(i-offset);
workbook.setSheetName(workbook.getSheetIndex(newSheet),sheetName);
}
}
if(!initSheet(sheetNo)) {
return false;
}
return true;
}
/**
* 存储Excel
*
* @param path 存储路径
* @throws IOException
*/
public void save(String path) throws
IOException {
exception();
if(!examine())
return;
try (FileOutputStream fos = new FileOutputStream(path)){
workbook.write(fos) ;
}
}
/**
* 返回Excel的字节数组
*
* @return byte[]
*/
public byte[] getBytes(){
if(!examine())
return null;
try(ByteArrayOutputStream ops = new ByteArrayOutputStream()){
workbook.write(ops);
return ops.toByteArray();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 返回Workbook
*
* @return Workbook
* @throws IOException
* */
public Workbook getWorkbook()
throws IOException {
exception();
return workbook;
}
/**
* @return sheet的数量
* */
public int getSheetNum(){
return workbook.getNumberOfSheets();
}
/**
* 返回sheet的行数量
*
* @param sheetNo 需要操作的Sheet的编号
* @return int 行数量
* */
public int getSheetRowNum(int sheetNo){
if(!examine() || !initSheet(sheetNo))
return 0;
return sheets[sheetNo].getLastRowNum();
}
/**
* 设置excel的缩放率
*
* @param zoomFz 缩放率分子
* @param zoomFm 缩放率分母
* */
public void setZoom(int zoomFz, int zoomFm){
if(!examine() || !initSheet(workbook.getSheetIndex(sheet)))
return;
for (int i = 0; i < sheets.length; i++) {
//设置放大属性(Zoom被明确为一个分数，例如下面的75%使用3作为分子，4作为分母)
// sheets[i].setZoom(3,4);
sheets[i].setZoom(zoomFz,zoomFm);
}
}
@Override
public boolean equals(Object o){
if(o == null)
return false;
if(o == this)
return true;
if(!(o instanceof ExcelTemplate))
return false;
if(examine() ^ ((ExcelTemplate)o).examine())
return false;
return Objects.equals(path,((ExcelTemplate)o).path);
}
@Override
public int hashCode(){
int hash = Objects.hashCode(path);
return hash >>> 16 ^ hash;
}
@Override
public String toString(){
return "ExcelTemplate from " + path + " is " +
(examine() ? "effective" : "invalid");
}
}
`
3、调用
点击查看代码
package com.ly.education.trainingResource.server.service.impl;
import com.alibaba.fastjson.JSONObject;
import com.ly.education.commons.util.Encodes;
import com.ly.education.trainingResource.api.dto.ExcelTemplateDto;
import com.ly.education.trainingResource.api.dto.ExcelTemplateSetDto;
import com.ly.education.trainingResource.api.dto.WordTemplateDto;
import com.ly.education.trainingResource.api.dto.xmsb.ProjectApplyDto;
import com.ly.education.trainingResource.api.exception.ServiceException;
import com.ly.education.trainingResource.api.vo.PublicCodeVo;
import com.ly.education.trainingResource.api.vo.WordTemplateVo;
import com.ly.education.trainingResource.api.vo.xmsb.ProjectApplyVo;
import com.ly.education.trainingResource.server.mapper.WordPrintMapper;
import com.ly.education.trainingResource.server.service.ExcelPrintService;
import com.ly.education.trainingResource.server.service.WordPrintService;
import com.ly.education.trainingResource.server.service.xmsb.ProjectApplyService;
import com.ly.education.trainingResource.server.utils.ExcelTemplate;
import com.ly.spring.boot.pagehelper.dto.PageQueryParam;
import com.ly.spring.boot.pagehelper.vo.Page;
import com.ly.spring.boot.uuid.UUIDStringGenerator;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTJc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.Clob;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import static com.ly.education.trainingResource.server.service.impl.WordPrintServiceImpl.clobToString;
/**
* 项目管理_评审模板 ReviewTemplate
*
* @author Heaven
*/
@Slf4j
@Service
public class ExcelPrintServiceImpl implements ExcelPrintService {
@Autowired
private WordPrintMapper wordPrintMapper;
@Autowired
private ProjectApplyService projectApplyService;
@Autowired
private UUIDStringGenerator uuIDStringGenerator;
@Autowired
private HttpServletResponse response;
@Autowired
private HttpServletRequest request;
@Override
public void testExcelPrint(PageQueryParam<ProjectApplyDto> pageQueryParam) {
// ExcelTemplateDto excelTemplateDto = new ExcelTemplateDto();
ExcelTemplateSetDto excelTemplateDto = new ExcelTemplateSetDto();
// wordPrintMapper.getRowWordPrint()
// String filePAth = "D:/opt/jwxt/file/uploadfile/ly-edu-core-svc-thx/3.四川轻化工大学教学成果奖申报项目汇总表.xlsx";
String filePAth = "D:/opt/jwxt/file/uploadfile/ly-edu-core-svc-thx/测试.xlsx";
// 创建Word模板文件:在开始代码编写之前，我们需要准备一个Word模板文件，模板文件中的文本可以是固定的，也可以使用占位符来表示需要动态填充的内容
//读取Word模板文件
// 我们需要使用Apache POI库来读取Word模板文件，并将其加载到内存中进行后续的操作。代码如下
// 设置文件存储路径（全局根路径/相对路径）
// String fileInPath = FilenameUtils
// .normalize(getAttachmentBasePath().concat(File.separator).concat(relativePath));
FileInputStream fis = null;
try {
// File sPath = new File("D:/opt/jwxt/file/uploadfile/ly-edu-core-svc-thx/1.四川轻化工大学研究生教学成果奖申报书(修改).docx");
// File sPath = new File("D:/opt/jwxt/file/uploadfile/ly-edu-core-svc-thx/2.四川轻化工大学研究生教学成果奖申报简表（修改） - 副本.docx");
// File sPath = new File("D:" + filePAth.replace("\\", "/"));
// File sPath = new File( FilenameUtils.normalize(filePAth));
File sPath = new File(filePAth);
fis = new FileInputStream(sPath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
// HWPFDocument 支持doc XWPFDocument支持docx
//初始化工具
// ExcelTemplate excel = new ExcelTemplate(filePAth);// 这种读的类路径
ExcelTemplate excel = new ExcelTemplate(fis);// 这种读任意流
// 获取 sheet的所有合并行，到时重新合并下
List<CellRangeAddress> listMergeRow = excel.hasMergeCell(0);
/*javaBean转map*/
/* Map<String, Object> map = getSevenInfo(affairSevenKnowledge);
rows = (LinkedHashMap<Integer, LinkedList<String>>) map.get("rows");*/
List<Map<String,Object>> dbData_listBgRow = new ArrayList<>();
// TODO: 从数据库查另外单独处理Row行的sql
// 使用一个Map来存储所有的行区域，
// 每个行区域对应Map的一个键
// LinkedHashMap<Integer, LinkedList<String>> rows = new LinkedHashMap<>();
// 创建第一个行区域里面填充的值，ExcelTemplate会按从左至右，
// 从上往下的顺序，挨个填充区域里面的${}，所以创建的时候注意顺序就好
List<List<Map<String, Object>>> dealList = new ArrayList<>();
dealList = handDbRowToExcelRowData( dbData_listBgRow, dealList);
// rows$ : 处理excel自增行，填充${xx0} ${xx1} .....
LinkedHashMap<Integer, LinkedList<String>> rows$ = new LinkedHashMap<>();
rows$ = handleAdd$Row( rows$, dealList );
// 处理单独变量 fillValues为 ${里面的占位符名称} 如：成果名称：研究生教学
Map<String, String> fillValues = new HashMap<>();
// TODO: 从数据库查另外单独处理的sql
List<Map<String, Object>> fillValuesOtherFromDb = new ArrayList<>();
fillValues = handleFillValues( fillValues, fillValuesOtherFromDb, dealList);
// excel的rows
Map<String, Object> map = new HashMap<>();
// 处理单独变量
// fillValues.put("projectName", "测试项目");
// fillValues.put("money", "金额");
map.put("param",fillValues);
// 第一个参数，需要操作的sheet的索引
// 第二个参数，需要复制的区域的第一行索引（占位符所在的第一行），从0索引开始算的
// 第三个参数，需要复制的区域的最后一行索引（占位符所在的最后一行）
// 第四个参数，需要插入的位置的索引(占位符的下一行)
// 第五个参数，填充行区域中使用传入的值覆盖${}或者N${}的值
// 第六个参数，是否需要删除原来的区域
// 需要注意的是，行的索引一般要减一
int sheetNo = null == excelTemplateDto || null == excelTemplateDto.getSheetNo() ?0:excelTemplateDto.getSheetNo();
int fromRowStartIndex = null == excelTemplateDto || null == excelTemplateDto.getFromRowStartIndex() ?0:excelTemplateDto.getFromRowStartIndex();
int fromRowEndIndex = null == excelTemplateDto || null == excelTemplateDto.getFromRowEndIndex() ?0:excelTemplateDto.getFromRowEndIndex();
int toRowIndex = null == excelTemplateDto || null == excelTemplateDto.getToRowIndex() ?0:excelTemplateDto.getToRowIndex();
Boolean delRowTemp = null == excelTemplateDto || null == excelTemplateDto.getDelRowTemp() ?true:excelTemplateDto.getDelRowTemp();
try {
// excel.fillVariable(0, (Map<String, String>) map.get("param"));
// int i = excel.addRowByExist(0, 14, 14, 15, (LinkedHashMap<Integer, LinkedList<String>>) map.get("rows"), true);
// 先填充${}
fromRowStartIndex = 3;
fromRowEndIndex = 3;
delRowTemp = false;
for (int dataIndex = 1; dataIndex<= rows$.size(); dataIndex++ ) {
toRowIndex = fromRowStartIndex + dataIndex;
LinkedHashMap<Integer, LinkedList<String>> rows$Item = new LinkedHashMap<>();
rows$Item.put(dataIndex-1,rows$.get(dataIndex-1));
int i = excel.addRowByExist(sheetNo, fromRowStartIndex, fromRowEndIndex, toRowIndex, rows$Item, delRowTemp);
}
excel.fillVariable(sheetNo, (Map<String, String>) map.get("param"));
excel.deleteRow(sheetNo,fromRowStartIndex);
/*
int i = excel.addRowByExist(sheetNo, fromRowStartIndex, fromRowEndIndex, toRowIndex, rows, delRowTemp);
//没有关系就不需要合并了
if (i > 0) {
// excel.mergedRegion(0,16,16+i+1,1,1);
}
// excel.addRowByExist(0,14,14,15,rows,true);*/
// 因为上面使用了这个addRowByExist 方法，里面是去掉所有合并的原样式，所以需要重新合并下行
excel.addMergeRowCell(sheetNo,listMergeRow,fromRowStartIndex,rows$.size());
} catch (IOException e) {
e.printStackTrace();
}
// 第一个参数，需要操作的sheet的索引
// 第二个参数，替换sheet当中${<变量的值>}的值
//下载
try {
// 导出excel的xlsx文档
String fileDownName = "";
String fileName = fileDownName + "导出excel" + (null == excelTemplateDto || StringUtils.isBlank(excelTemplateDto.getExcelType()) ? ".xlsx":".xls");
// 清除buffer缓存
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
//指定下载名字
response.setHeader("Content-Disposition", "attachment; filename=" +
Encodes.urlEncode(fileName));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream fout = response.getOutputStream();
excel.getWorkbook().write(fout);
fout.close();
// try (OutputStream os = response.getOutputStream()) {
// os.write(excel.getBytes());
// os.flush();
// }
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 处理excel自增行后， 先用${占位符} 填充excel的cell
* @param rows$ excel自增每一行的 ${占位符} ，
* 第一行是 ${占位符0}
* 第二行是 ${占位符1} .....
* @param dealRowList 数据库的查出来要渲染excel表格行的数据
* @return
*/
public LinkedHashMap<Integer, LinkedList<String>> handleAdd$Row(LinkedHashMap<Integer, LinkedList<String>> rows$,
List<List<Map<String, Object>>> dealRowList ){
if(CollectionUtils.isNotEmpty(dealRowList)){
for(int i=0;i<dealRowList.size();i++){
LinkedList<String> list$ = new LinkedList();
List<Map<String, Object>> dealMapList = dealRowList.get(i);
if(CollectionUtils.isNotEmpty(dealMapList)){
for(int j=0;j<dealMapList.size();j++){
list$.add("${"+(String)dealMapList.get(j).get("字段编码")+"}");
}
}
rows$.put(i,list$);
}
}
return rows$;
}
/**
* 处理填充占位符的 具体值 用map存起来
* @param fillValues excel的值
* @param fillValuesOtherFromDb 数据库中其他映射字段的值
* @param dealRowList 数据库的查出来要渲染excel表格行的数据 映射值
* @return Map<String, String> key:为占位符的名称 value为值
*/
public Map<String, String> handleFillValues(Map<String, String> fillValues,
List<Map<String, Object>> fillValuesOtherFromDb,
List<List<Map<String, Object>>> dealRowList){
// 把数据库的先塞进去
if(CollectionUtils.isNotEmpty(fillValuesOtherFromDb)){
for (Map<String, Object> otherMap: fillValuesOtherFromDb){
if ("clob".equals((String) otherMap.get("字段类型"))) {
otherMap.put("字段值", null != otherMap.get("大字段值") ? StringUtils.substring(clobToString((Clob) otherMap.get("大字段值")), 0, 1333) : "");
} else {
otherMap.put("字段值", (String) otherMap.get("字段值"));
}
fillValues.put((String)otherMap.get("字段编码"), (String)otherMap.get("字段值"));
}
}
// 处理 自增行的数据塞进去
for(int i=0;i<dealRowList.size();i++){
LinkedList<String> list$ = new LinkedList();
List<Map<String, Object>> dealMapList = dealRowList.get(i);
if(CollectionUtils.isNotEmpty(dealMapList)){
for(int j=0;j<dealMapList.size();j++){
fillValues.put((String)dealMapList.get(j).get("字段编码"), (String)dealMapList.get(j).get("字段值"));
}
}
}
return fillValues;
}
/**
* 处理数据库的map数据，这些数据是用来填充 excel的行的数据
* @param dbData_listBgRow 数据库的map数据
* @param dealRowList excel需要的行数据
* @return
*/
public List<List<Map<String, Object>>> handDbRowToExcelRowData(List<Map<String,Object>> dbData_listBgRow,
List<List<Map<String, Object>>> dealRowList){
Map<String,Object> dbMap = new HashMap<>();
dbMap.put("字段编码","序号");
dbMap.put("字段值","1");
dbMap.put("大字段值","1");
// dbMap.put("字段类型","clob");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","1");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","成果名称");
dbMap.put("字段值","成果名称啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","1");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","主要完成人");
dbMap.put("字段值","主要完成人啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","1");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","序号");
dbMap.put("字段值","2");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","2");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","成果名称");
dbMap.put("字段值","222222成果名称啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","2");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","主要完成人");
dbMap.put("字段值","主要完成人22啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","2");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","序号");
dbMap.put("字段值","3");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","3");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","成果名称");
dbMap.put("字段值","3333成果名称啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","3");
dbData_listBgRow.add(dbMap);
dbMap = new HashMap<>();
dbMap.put("字段编码","主要完成人");
dbMap.put("字段值","主要完成人33啦啦啦啦");
dbMap.put("大字段值","1");
dbMap.put("字段类型","Integer");
dbMap.put("同一行","3");
dbData_listBgRow.add(dbMap);
Map<String, List<Map<String, Object>>> collect1 =
dbData_listBgRow.stream().collect(Collectors.groupingBy(k ->
//k.get("BQID").toString() + "->" + k.get("TYHBZ").toString()
k.get("同一行").toString()
));
collect1 = collect1.entrySet().stream()
.sorted(Map.Entry.comparingByKey()).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue,
(oldValue, newValue) -> oldValue, LinkedHashMap::new));
Iterator var4 = collect1.entrySet().iterator();
while (var4.hasNext()) {
Map.Entry<String, List<Map<String, Object>>> order = (Map.Entry) var4.next();
// String keyVar = order.getKey().substring(0, order.getKey().indexOf("->"));
dealRowList.add(order.getValue());
}
if(CollectionUtils.isNotEmpty(dealRowList)){
for(int i=0;i<dealRowList.size();i++){
List<Map<String, Object>> dealMapList = dealRowList.get(i);
if(CollectionUtils.isNotEmpty(dealMapList)){
for(int j=0;j<dealMapList.size();j++){
String mapName = (String)dealMapList.get(j).get("字段编码")+i;
dealMapList.get(j).remove("字段编码");
dealMapList.get(j).put("字段编码",mapName);// 将 字段编码：序号 变成 字段编码：序号0 序号1 序号2 ....
if ("clob".equals((String) dealMapList.get(j).get("字段类型"))) {
// 大字段的值转换成string
dealMapList.get(j).remove("字段值");
dealMapList.get(j).put("字段值", null != dealMapList.get(j).get("大字段值") ? StringUtils.substring(clobToString((Clob) dealMapList.get(j).get("大字段值")), 0, 1333) : "");
} else {
dealMapList.get(j).put("字段值", (String) dealMapList.get(j).get("字段值"));
}
}
}
}
}
return dealRowList;
}
}
dto类：
点击查看代码
package com.ly.education.trainingResource.api.dto;
import lombok.Data;
import java.io.Serializable;
/**
* 项目管理_评审模板xlsx导出 ReviewTemplate
* @author xujinhui
*
*/
@Data
public class ExcelTemplateSetDto implements Serializable {
/**
* 序列号
*/
private static final long serialVersionUID = -9003609292510835997L;
// 第一个参数，需要操作的sheet的索引
// 第二个参数，需要复制的区域的第一行索引（占位符所在的第一行）
// 第三个参数，需要复制的区域的最后一行索引（占位符所在的最后一行）
// 第四个参数，需要插入的位置的索引(占位符的下一行)
// 第五个参数，填充行区域中${}的值
// 第六个参数，是否需要删除原来的区域
// 需要注意的是，行的索引一般要减一
private Integer sheetNo ;// 第一个参数，需要操作的sheet的索引
private Integer fromRowStartIndex ;// 第二个参数，需要复制的区域的第一行索引（占位符所在的第一行）
private Integer fromRowEndIndex ;// 第三个参数，需要复制的区域的最后一行索引（占位符所在的最后一行）
private Integer toRowIndex ;// 第四个参数，需要插入的位置的索引(占位符的下一行)
private String delRowTempFlag ;// 第六个参数，是否需要删除原来的区域
private Boolean delRowTemp ;// 第六个参数，是否需要删除原来的区域
private String excelType ;// 导出类型是 xls还是xlsx
}
4 效果
模板：
使用${}替换后：
本文非常感谢这个老哥的素材提供哈：
https://www.cnblogs.com/lljh/p/13647113.html
在此基础加了自己的一些逻辑和修改。
posted on 2023-10-19 19:55 HeavenTang 阅读(87) 评论(0) 编辑 收藏 举报
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
2022-10-19 CountDownLatch