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(