rongzidanbao/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelFillUtils.java

package com.ruoyi.common.utils.poi;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelFillUtils {

    //    public static final String FILL_EXPRESSION_REGEX = "\\{\\.\\w+\\}";
    private static final String FILL_EXPRESSION_REGEX = "\\{\\.[\\p{L}\\p{M}\\S]+\\}";

    /**
     * 给定模板，指定某个页签，将数据填充到模板中的指定页签，并在模板所在目录生成新的副本文件。
     * @param template 模板文件地址
     * @param sheetName 页签名称
     * @param data 待填充的数据,数据格式如下
     *                  [
     *                      {"colName1":v1 ,"colName2":v2...},
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ]
     * @return 新生成的副本文件的地址
     *
     * */
    public static String fillOneSheet(String template ,String sheetName , List<Map<String,Object>> data) {
        return fillOneSheet(template ,FileUtils.getAvailableFullName(template) ,sheetName ,data);
    }

    /**
     * 给定模板，指定某个页签，将数据填充到模板中的指定页签，并将数据导入到指定文件上。
     * @param template 模板文件地址
     * @param outputFile 新生成的文件的地址
     * @param sheetName 页签名称
     * @param data 待填充的数据,数据格式如下
     *                  [
     *                      {"colName1":v1 ,"colName2":v2...},
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ]
     * @return 新生成的副本文件的地址
     *
     * */
    public static String fillOneSheet(String template , String outputFile,String sheetName , List<Map<String,Object>> data){

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(template))) {

            fill(workbook,sheetName,data);//填充数据
            refreshFormula(workbook);//刷新公式

            try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
                workbook.write(outputStream);
            }catch (Exception e){
                throw new RuntimeException(e);
            }

        }catch (IOException e){
            throw new RuntimeException(e);
        }

        return outputFile;
    }

    /**
     * 给定模板,将数据填充到模板中的多个页签，并在模板所在目录生成新的副本文件。
     * @param template 模板文件地址
     * @param datas 待填充的数据集,数据格式如下
     *              {
     *                  "SheetName1":[
     *                      {"colName1":v1 ,"colName2":v2...},
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ],
     *                  "SheetName2":[
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ],
     *                  ...
     *              }
     *
     * */
    public static String fillMultipleSheet(String template ,Map<String,List<Map<String,Object>>> datas) {
        return fillMultipleSheet(template ,FileUtils.getAvailableFullName(template) ,datas);
    }

    /**
     * 给定模板,将数据填充到模板中的多个页签，并将数据导入到指定文件上。
     * @param template 模板文件地址
     * @param datas 待填充的数据集,数据格式如下
     *              {
     *                  "SheetName1":[
     *                      {"colName1":v1 ,"colName2":v2...},
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ],
     *                  "SheetName2":[
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      {"colName1":v1 ,"colName2":v2...}
     *                      ,...
     *                  ],
     *                  ...
     *              }
     *
     * */
    public static String fillMultipleSheet(String template ,String outputFile ,Map<String,List<Map<String,Object>>> datas){

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(template))) {

            datas.forEach( (sheetName ,data)-> fill(workbook,sheetName,data) );//填充数据
            refreshFormula(workbook);//刷新公式

            try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
                workbook.write(outputStream);
            }catch (Exception e){
                throw new RuntimeException(e);
            }

        }catch (IOException e){
            throw new RuntimeException(e);
        }

        return outputFile;
    }

    private static Workbook fill(Workbook workbook ,String sheetName ,List<Map<String,Object>> data) {

        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new RuntimeException(String.format("sheet [%s] does not exist.",sheetName));
        }

        //找到所有的表达式单元格
        Map<String,Cell> expressionCellMap = new HashMap<>();
        for( int i = 0 ;i < sheet.getPhysicalNumberOfRows() ;i++){
            Row row = sheet.getRow(i);
            for( int j = 0 ;j< row.getPhysicalNumberOfCells() ;j++){
                Cell cell = row.getCell(j);
                cell.setCellType(CellType.STRING);
                if( !Objects.isNull(cell) && isFillExpression(cell.getStringCellValue()) ){//判断该单元格是否是填充公式
                    expressionCellMap.put(getColNameFromEx(cell.getStringCellValue()),cell);
                }
            }
        }

        //填充数据
        for(int i = 0 ;i< data.size() ;i++){
            Map<String,Object> dataRow = data.get(i);
            for (Map.Entry<String,Object> entry : dataRow.entrySet()){
                String colName = entry.getKey();
                Object value = entry.getValue();
                if(expressionCellMap.containsKey(colName)){

                    Cell cell = expressionCellMap.get(colName);//公式所在的单元格
                    int rowID = cell.getRowIndex() + i;
                    int colId = cell.getColumnIndex();

                    Row fillRow = sheet.getRow(rowID);
                    fillRow = Objects.isNull(fillRow) ? sheet.createRow(rowID) : fillRow;
                    Cell fillCell = fillRow.getCell(colId);
                    //创建的新单元格需要复制公式单元格的格式
                    fillCell = Objects.isNull(fillCell) ? CellUtil.createCell(fillRow,colId,"", cell.getCellStyle()) : fillCell;

                    if ( value instanceof String){
                        fillCell.setCellValue( String.valueOf(value) );
                    }else if( value instanceof Number ){
                        fillCell.setCellValue( ((Number)value).doubleValue() );
                    }else{
                        throw new RuntimeException(String.format("Unsupported data type [%s].",value.getClass().toString()));
                    }

                }
            }
        }

        return workbook;
    }

    private static void refreshFormula(Workbook workbook){
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
    }

    private static boolean isFillExpression(String ex){
        if (ex.isEmpty()) return false;
        return Pattern.matches(FILL_EXPRESSION_REGEX ,ex);
    }

    private static String getColNameFromEx(String ex){
        if (!isFillExpression(ex)) throw new RuntimeException("Illegal expression " + ex );
        return ex.substring(2,ex.length() - 1);
    }

}