public void setSheetForPrint(Workbook workbook)throws Exception{/*** 处理顺序:* 1 确定现有页数,并保存 以待用于删除* 2 巡查工作簿1 获取终结列数和分割次数* 3 巡查工作簿1 并将其内容进行分割复制* 4 用第1步保存的页序号进行删除*//*1 确定现有页数,并保存 以待用于删除*/Integer[] deleteSheetIndexArr = new Integer[workbook.getNumberOfSheets()];// 保存页数int deleteSheetIndexArrIndex = 0;for (Sheet sheet : workbook){int sheetIndex = workbook.getSheetIndex(sheet);deleteSheetIndexArr[deleteSheetIndexArrIndex] = sheetIndex;deleteSheetIndexArrIndex++;}/*2 巡查工作簿1 获取终结列数和分割次数*/// 获取工作簿Sheet sheet = workbook.getSheetAt(0);//设置关键字 变量 并巡查工作簿String keyWord1 = "xxxx";String keyWord2 = "yyyy";String keyWordLastColumn = "页";String keyWordLastRow = "The Below is Blank";String keyWordLastRow2 = "以下空白";//设置变量int lastColumn = 0; //最后一列int newSheetNum = 0; //新增页数int lastRowNum = sheet.getLastRowNum(); // 最后一行List<int[]> deleteRow = new ArrayList<>();//开始巡查工作簿for (Row row : sheet){Cell cell = row.getCell(0); //获取第0列单元格// 查找关键字if (PoiExcelUtils.getCellValue(cell).contains(keyWord1)){// 如果查找到关键字1int nextRowIndex = cell.getRowIndex() +1;Row nextRow = sheet.getRow(nextRowIndex);Cell nextRowCell = nextRow.getCell(0);if (PoiExcelUtils.getCellValue(nextRowCell).contains(keyWord2)){//如果查找到关键字2 则查找关键字3if (lastColumn == 0){ // 如果未获取到最后一列for (Cell tempCell : row){if (PoiExcelUtils.getCellValue(tempCell).contains(keyWordLastColumn)){// 如果查找到关键字3 则返回信息lastColumn = tempCell.getColumnIndex();}}}// 从key1上一行开始 保存这三两行 并增加计数器int[] indexArray = new int[]{cell.getRowIndex()-1,nextRowIndex};deleteRow.add(indexArray);newSheetNum++;}}// 如果检测到最后一行的标记 则跳出循环for (Cell cellT :row ){if (PoiExcelUtils.getCellValue(cellT).contains(keyWordLastRow)){lastRowNum = row.getRowNum() +1;break;}}}/*3 巡查工作簿1 并将其内容进行分割复制*/// 首先获取合并单元格信息Map<Integer,List<Integer[]>> mergedDic = new HashMap<>(); // 单元格合并字典for (int i = 0; i < sheet.getNumMergedRegions(); i++) {CellRangeAddress mergedRegion = sheet.getMergedRegion(i);Integer firstRow = mergedRegion.getFirstRow();Integer lastRow = mergedRegion.getLastRow();Integer firstColumn = mergedRegion.getFirstColumn();Integer LastColumnT = mergedRegion.getLastColumn();//获取并设置字典List<Integer[]> tempList = mergedDic.containsKey(firstRow) ? mergedDic.get(firstRow) : new ArrayList<>();Integer[] mergedArr = new Integer[4];mergedArr[0] = firstRow;mergedArr[1] = lastRow;mergedArr[2] = firstColumn;mergedArr[3] = LastColumnT;tempList.add(mergedArr);mergedDic.put(firstRow,tempList);}int newSheetIndex = 0; //新sheet索引int newSheetRow = 0;Sheet newSheet = workbook.createSheet();//计算器FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();// 开始新建sheet页for (Row row : sheet){// 获取界限if (newSheetIndex > deleteRow.size() || row.getRowNum() >= lastRowNum){break;}int[] sheetIndex = newSheetIndex == deleteRow.size() ? null : deleteRow.get(newSheetIndex);//判断是否是新标签页的第一行if (sheetIndex != null && row.getRowNum() >= sheetIndex[0] && row.getRowNum() < sheetIndex[1]){continue;}if (sheetIndex != null && row.getRowNum() == sheetIndex[1]){newSheetIndex++; //进入新工作簿newSheetRow = 0; //重置行数if (newSheetIndex > deleteRow.size()){break;}newSheet = workbook.createSheet(); // 新建工作簿continue;}if (newSheetRow == 0){// 如果是第一行 设置列宽for (Cell cell : sheet.getRow(0)){int columWidth = sheet.getColumnWidth(cell.getColumnIndex());newSheet.setColumnWidth(cell.getColumnIndex(),columWidth);if (cell.getColumnIndex() == lastColumn) break;}}// 巡查并创建单元格// 创建新的一行Row targetRow = newSheet.createRow(newSheetRow);for (Cell cell : row){//判断是否结束复制if (cell.getRowIndex() == lastColumn) break;// 如果不结束 获取当前 并复制targetRow.setHeightInPoints(row.getHeightInPoints());Cell targetCell = targetRow.createCell(cell.getColumnIndex());}// 本行复制结束 新页面行数+1newSheetRow++;}// 新建结束// 清空计数器newSheetIndex = 0;newSheetRow = 0;newSheet = workbook.getSheetAt(deleteSheetIndexArr.length);//开始合并单元格for (Row row : sheet){// 获取界限if (newSheetIndex > deleteRow.size() || row.getRowNum() >= lastRowNum){break;}int[] sheetIndex = newSheetIndex == deleteRow.size() ? null : deleteRow.get(newSheetIndex);//判断是否是新标签页的第一行if (sheetIndex != null && row.getRowNum() >= sheetIndex[0] && row.getRowNum() < sheetIndex[1]){continue;}if (sheetIndex != null && row.getRowNum() == sheetIndex[1]){newSheetIndex++; //进入新工作簿newSheetRow = 0; //重置行数if (newSheetIndex > deleteRow.size()){break;}newSheet = workbook.getSheetAt(workbook.getSheetIndex(newSheet)+1); // 获取工作簿continue;}// 巡查并合并单元格 设置单元格格式if (mergedDic.containsKey(row.getRowNum())){// 如果本行具备合并单元格List<Integer[]> mergedList = mergedDic.get(row.getRowNum());for (Integer[] tempArr : mergedList){int firstRow = tempArr[0] - (row.getRowNum() - newSheetRow);int lastRow = tempArr[1]- (row.getRowNum() - newSheetRow);int firstColumn = tempArr[2];int LastColumnT = tempArr[3];CellRangeAddress targetMergedRegion = new CellRangeAddress(firstRow, lastRow,firstColumn, LastColumnT);newSheet.addMergedRegion(targetMergedRegion);}}//遍历并设置单元格格式Row targetRow = newSheet.getRow(newSheetRow);for(Cell cell :row){Cell targetCell = targetRow.getCell(cell.getColumnIndex());if (targetCell != null){CellStyle sourceCellStyle = cell.getCellStyle();CellStyle targetCellStyle = newSheet.getWorkbook().createCellStyle();targetCellStyle.cloneStyleFrom(sourceCellStyle);targetCell.setCellStyle(targetCellStyle);}}// 本行修改结束 新页面行数+1newSheetRow++;}// 清空计数器newSheetIndex = 0;newSheetRow = 0;newSheet = workbook.getSheetAt(deleteSheetIndexArr.length);//开始巡查for (Row row : sheet){// 开始巡查工作簿// 获取界限if (newSheetIndex > deleteRow.size() || row.getRowNum() >= lastRowNum){break;}int[] sheetIndex = newSheetIndex == deleteRow.size() ? null : deleteRow.get(newSheetIndex);//判断是否是新标签页的第一行//判断是否结束复制if (sheetIndex != null && row.getRowNum() >= sheetIndex[0] && row.getRowNum() < sheetIndex[1]){continue;}if (sheetIndex != null && row.getRowNum() == sheetIndex[1]){newSheetIndex++; //进入新工作簿newSheetRow = 0; //重置行数if (newSheetIndex > deleteRow.size()){break;}newSheet = workbook.getSheetAt(workbook.getSheetIndex(newSheet)+1); // 获取工作簿continue;}// 巡查并复制单元格// 创建新的一行Row targetRow = newSheet.getRow(newSheetRow);for (Cell cell : row){//判断是否结束复制if (cell.getRowIndex() == lastColumn) break;// 如果不结束 获取当前 并复制Cell targetCell = targetRow.getCell(cell.getColumnIndex());String cellValueSt = getCellValue(cell,evaluator);if (!"".equals(cellValueSt)){targetCell.setCellValue(getCellValue(cell,evaluator));targetCell.setCellStyle(cell.getCellStyle());}}// 本行复制结束 新页面行数+1newSheetRow++;}/*4 用第1步保存的页序号进行删除*/Arrays.sort(deleteSheetIndexArr,Comparator.reverseOrder());for (int deleteSheetIndex : deleteSheetIndexArr){workbook.removeSheetAt(deleteSheetIndex);}}/*** 获取单元格内容* @param cell* @return* @throws Exception*/private String getCellValue (Cell cell,FormulaEvaluator evaluator)throws Exception{String cellValue = "";if (cell == null){return cellValue;}// 获取单元格类型CellType cellType = cell.getCellTypeEnum();CellType formula = CellType.FORMULA;if (cellType.equals(formula)){// 检查单元格结果类型CellValue cellValue1 = evaluator.evaluate(cell);if (cellValue1.getCellTypeEnum() == CellType.NUMERIC) {cellValue = String.valueOf(cellValue1.getNumberValue());// 处理数值结果} else if (cellValue1.getCellTypeEnum() == CellType.STRING) {cellValue = cellValue1.getStringValue();// 处理字符串结果}}else{cellValue = PoiExcelUtils.getCellValue(cell);}return cellValue;}
PoiExcelUtils.getCellValue(cell) 就是普通的根据类型获取单元格内容的方法,就不写了,第一步和第四步不需要的话就删掉