java复杂模板excel导出例子

package com.hanclouds.wx.mes.shuangxing.utils; 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.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; 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(){ try (InputStream is = new FileInputStream(path)){ 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; } /** * 使用一个已经存在的行区域作为模板， * 从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;