POI之excel固定模板导出
一、简介
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。
二、excel模板
三、项目中maven依赖
<!--poi依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<!--poi依赖-->
四、Excel模板操作代码
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.*;
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 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 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 插入的行数量
* @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();
// 读取需要插入的数据
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(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.getCellType() == CellType.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