Java Poi 简单模板生成数据

应项目需求，初次学习使用。。。
没有文案，具体见示例

package com.sinoyd.util;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.*;

/**
 * 该类实现了基于模板的导出,很多规则仅做定制化用
 * 遍历数据使用_开头表示来代替
 * 如果要替换信息，需要传入一个Map，这个map中存储着要替换信息的值，在excel中通过#来开头
 */
public class ExcelTemplate {
    private static final String[] Vars = {"_data1", "_data2", "_data3", "_data4"};//遍历字符串
    private Map<String, List<Integer>> Location = new HashMap<>();//遍历字符串行每个元素绝对位置起始row(行),起始col(列)
    private Map<String, List<Integer>> Location_Full = new HashMap<>();//遍历字符串行每个元素扩展后绝对位置起始row(行),,起始col(列)
    private Map<String, CellStyle> Styles = new HashMap<>();//遍历字符串行每个元素样式
    private static final String Var_1 = "_data1";//上左
    private static final String Var_2 = "_data2";//上右
    private static final String Var_3 = "_data3";//下左
    private static final String Var_4 = "_data4";//下右
    private static final int First_Row = 0;
    private static final int Last_Row = 1;
    private static final int First_Column = 2;
    private static final int Last_Column = 3;
    private static ExcelTemplate et = new ExcelTemplate();
    private Workbook wb;
    private Sheet sheet;

    private ExcelTemplate() {
    }

    public static ExcelTemplate getInstance() {
        return et;
    }

    public ExcelTemplate readTemplateByPath(String path) {
        try {
//            InputStream resourceAsStream = ExcelTemplate.class.getResourceAsStream(path);//类地址
            File file = new File(path);
            wb = WorkbookFactory.create(file);
            initTemplate();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException("InvalidFormatException, please check.");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("The template is not exist, please check.");
        }
        return this;
    }

    /**
     * 初始化模板信息
     */
    private void initTemplate() {
        sheet = wb.getSheetAt(0);
        int sheetMergeCount = sheet.getNumMergedRegions();
        int firstRow, lastRow, firstCol, lastCol;
        Row row;
        Cell cell;

        for (String var : Vars) {
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress range = sheet.getMergedRegion(i);
                firstRow = range.getFirstRow();
                lastRow = range.getLastRow();
                firstCol = range.getFirstColumn();
                lastCol = range.getLastColumn();
                row = sheet.getRow(firstRow);
                cell = row.getCell(firstCol);
                if (Cell.CELL_TYPE_STRING == cell.getCellType() && var.equals(cell.getStringCellValue().trim())) {
                    Location.put(var, Arrays.asList(firstRow, lastRow, firstCol, lastCol));
                    Styles.put(var, cell.getCellStyle());
                    break;
                }
            }
        }
    }

    /**
     * 添加行
     *
     * @param dataSize 数据大小
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param basics   需要copy行的字段->改变Location_Full
     * @param extras   跟随copy变化的字段->改变Location
     */
    private void addRow(int dataSize, int firstRow, int lastRow, List<String> basics, List<String> extras) {
        int difRow = lastRow - firstRow + 1;
        int addRow = dataSize > 0 ? (difRow * dataSize - difRow) : 0;
        int beginRow = lastRow + 1;
        int endRow = sheet.getLastRowNum();
        shiftRows(beginRow, endRow, addRow);

        //位置改变
        List<Integer> loc;
        int first_row, last_row, first_col, last_col;
        for (String basic : basics) {
            loc = Location.get(basic);
            first_row = firstRow;
            last_row = lastRow + addRow;
            first_col = loc.get(First_Column);
            last_col = loc.get(Last_Column);
            Location_Full.put(basic, Arrays.asList(first_row, last_row, first_col, last_col));
        }
        for (String extra : extras) {
            loc = Location.get(extra);
            first_row = loc.get(First_Row) + addRow;
            last_row = loc.get(Last_Row) + addRow;
            first_col = loc.get(First_Column);
            last_col = loc.get(Last_Column);
            Location.put(extra, Arrays.asList(first_row, last_row, first_col, last_col));
        }
    }

    /**
     * 插入行
     *
     * @param startRow 起始行
     * @param endRow   结束行
     * @param rows     插入的行数
     */
    private void shiftRows(int startRow, int endRow, int rows) {
        sheet.shiftRows(startRow, endRow, rows, true, false);
        for (int i = 0; i < rows; i++) {
            Row sourceRow = sheet.getRow(startRow - 1);
            Row targetRow = sheet.createRow(startRow++);
            targetRow.setHeight(sourceRow.getHeight());
            Cell sourceCell;
            Cell targetCell;
            for (int m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                targetCell.setCellType(sourceCell.getCellType());
            }
        }
    }

    /**
     * 设置单元格数据
     *
     * @param map 原始数据
     */
    private void setCell(Map<String, List<Object>> map) {
        for (String var : Vars) {
            List<Object> list = map.get(var);
            List<Integer> loc = Location.get(var);
            CellStyle cellStyle = Styles.get(var);
            int firstRow = loc.get(First_Row);
            int lastRow = loc.get(Last_Row);
            int firstCol = loc.get(First_Column);
            int lastCol = loc.get(Last_Column);
            int difRow = lastRow - firstRow;
            int endRow = difRow == 0 ? Location_Full.get(var).get(Last_Row) + 1 : Location_Full.get(var).get(Last_Row);
            int index = 0;
            setCellValue(list, index++, firstRow, firstCol, cellStyle);
            for (int i = firstRow + difRow + 1; i < endRow; i += difRow + 1) {
                setCellValue(list, index++, i, firstCol, cellStyle);
                sheet.addMergedRegion(new CellRangeAddress(i, i + difRow, firstCol, lastCol));
            }
        }
    }

    /**
     * 设定单元格值
     *
     * @param list      原始数据
     * @param index     下标
     * @param rowIndex  当前行
     * @param colIndex  当前列
     * @param cellStyle 样式
     */
    private void setCellValue(List<Object> list, int index, int rowIndex, int colIndex, CellStyle cellStyle) {
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("");//初始化,只考虑数字和字符串类型
        if (list != null && list.size() > index) {
            Object object = list.get(index);
            if (object != null) {
                if (object instanceof String)
                    cell.setCellValue((String) object);
                if (object instanceof Number)
                    cell.setCellValue(((Number) object).doubleValue());
            }
        }
    }

    /**
     * 合并随动单元格
     *
     * @param leftExtra  左侧标志
     * @param rightExtra 右侧标志
     */
    private void mergeRow(String leftExtra, String rightExtra) {
        List<Integer> left = Location.get(leftExtra);
        List<Integer> right = Location.get(rightExtra);
        int rowEnd = Location_Full.get(leftExtra).get(Last_Row);
        int leftFirstRow = left.get(First_Row), leftFirstCol = 0, leftLastCol = left.get(First_Column) - 1;
        int rightFirstRow = left.get(First_Row), rightFirstCol = left.get(Last_Column) + 1, rightLastCol = right.get(First_Column) - 1;
        mergedRegion(leftFirstRow, rowEnd, leftFirstCol, leftLastCol);
        mergedRegion(rightFirstRow, rowEnd, rightFirstCol, rightLastCol);
    }

    /**
     * 操作合并单元格
     *
     * @param firstRow 希望合并的开始行
     * @param lastRow  希望合并的结束行
     * @param firstCol 希望合并的开始列
     * @param lastCol  希望合并的结束列
     */
    private void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstRow() == firstRow && mergedRegion.getFirstColumn() == firstCol) {
                sheet.removeMergedRegion(i);
                break;
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 添加遍历数据
     */
    public void replaceCellData(Map<String, List<Object>> map) {
        List<String> top = Arrays.asList(Var_1, Var_2);
        List<String> down = Arrays.asList(Var_3, Var_4);

        int topSize = map.get(Var_1) == null ? 0 : map.get(Var_1).size();
        int topFirstRow = Location.get(Var_1).get(First_Row);
        int topLastRow = Location.get(Var_1).get(Last_Row);
        addRow(topSize, topFirstRow, topLastRow, top, down);

        int downSize = map.get(Var_3) == null ? 0 : map.get(Var_3).size();
        int downFirstRow = Location.get(Var_3).get(First_Row);
        int downLastRow = Location.get(Var_3).get(Last_Row);
        addRow(downSize, downFirstRow, downLastRow, down, new ArrayList<>());

        setCell(map);//添加cell数据合并循环变量单元格
        mergeRow(Var_1, Var_2);//同一行合并随动单元格
        mergeRow(Var_3, Var_4);//同一行合并随动单元格
    }

    /**
     * 根据map替换相应的常量，通过Map中的值来替换#开头的值
     */
    public void replaceFinalData(Map<String, String> datas) {
        if (datas == null) return;
        for (Row row : sheet) {
            for (Cell c : row) {
                if (c.getCellType() != Cell.CELL_TYPE_STRING) continue;
                String str = c.getStringCellValue().trim();
                if (str.startsWith("#")) {
                    if (datas.containsKey(str.substring(1))) {
                        c.setCellValue(datas.get(str.substring(1)));
                    }
                }
            }
        }
    }

    /**
     * 生成文件
     */
    public void writeToFile(String filepath) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filepath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("写入的文件不存在" + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("写入数据失败" + e.getMessage());
        } finally {
            if (fos != null)
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }
}


测试用例
    @Test
    public void contextLoads() {
        ExcelTemplate et = ExcelTemplate.getInstance().readTemplateByPath("/excel/in.xlsx");
        Map<String, List<Object>> map = new HashMap<>();
        //日期需要excel单元格是日期格式,或转成字符串
        map.put("_data1", Arrays.asList("a1-1", "a1-2", "a1-3", "a1-4", "a1-5"));
        map.put("_data2", Arrays.asList("a2-1", "a2-2", "a2-3", "a2-4", "a2-5"));
        map.put("_data3", Arrays.asList("a3-1", "a3-2", "a3-3", "a3-4", "a3-5"));
        map.put("_data4", Arrays.asList("a4-1", "a4-2", "a4-3", "a4-4", "a4-5"));
//        map.put("_data4", Arrays.asList(1, 4, -1, 4, 2));
//        map.put("_data4", Arrays.asList(new Date(1573606975), new Date(1573610575), new Date(1573614175), new Date(1573617775), new Date(1573621375)));
        Map<String, String> datas = new HashMap<>();
        datas.put("name", "暗帝天下第一");
        datas.put("time", LocalDateTime.now().toString());

        et.replaceCellData(map);
        et.replaceFinalData(datas);
        et.writeToFile("/excel/out.xlsx");
    }

模板及输出路径在项目运行的相对盘；