/** * 取单元格所在的合并区域,如果返回空,则说明没有在合并区域 * * @param cell -- 指定的单元格 * @return */ private static CellRangeAddress getMergedRegion(HSSFCell cell) { HSSFSheet sheet = cell.getSheet(); CellRangeAddress range = null; int mergedNum = sheet.getNumMergedRegions(); for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); if (range.getFirstColumn() == cell.getColumnIndex() && range.getFirstRow() == cell.getRowIndex()) { return range; } } return null; }
/** * 添加临时表格内容 * * @param mainSheet -- 原表单内容 * @param subSheet -- 临时表单内容 * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) { if (mainSheet == null || subSheet == null) return null; // 判断报表是否允许输出 if (!isAllowOut(mainSheet)) return mainSheet; // 原报表的最后一行 int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; // 复制表格中的图片 copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); // 设置以合并的单元格 CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; // int k = 0; // 设置相关参数 mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); // 取模板中的单元格,与来源表单位置相同 int row = sourcell.getRowIndex(); orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { // 取模板中的类型赋值 descell.setCellType(orgcell.getCellType()); // 取模板中的样式赋值 descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ; } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
/** * Собирает информацию о некоторой области из указанного листа шаблона excel. * * @param sheet шаблон листа отчета в котором находятся данные для данной области. * @param top номер верхней строки (начиная с 0) относящейся к указанной области. * @param height количество строк в области. Должно быть как минимум 1. * @param palette реестр всех стилей используемых в данном отчете. */ public Area(final HSSFSheet sheet, final int top, final int height, final StylePalette palette) { if (sheet == null || top < 0 || height < 1) throw new IllegalArgumentException("Illegal area arguments"); rows = new ArrayList<Row>(); regions = new ArrayList<Region>(); final int bottom = top + height - 1; int lastColumn = 0; for (int i = top; i <= bottom; i++) { final Row rm = new Row(); rows.add(rm); final HSSFRow row = sheet.getRow(i); if (row == null) { rm.setHeight(sheet.getDefaultRowHeight()); continue; } rm.setHeight(row.getHeight()); rm.setHidden(row.getZeroHeight()); lastColumn = Math.max(lastColumn, row.getLastCellNum()); for (int j = 0; j <= row.getLastCellNum(); j++) { final HSSFCell cell = row.getCell(j); if (cell == null) { rm.getCells().add(null); } else { rm.getCells().add(new Cell(cell, palette)); } } } this.columnsCount = lastColumn + 1; final int regcount = sheet.getNumMergedRegions(); for (int i = 0; i < regcount; i++) { final CellRangeAddress src = sheet.getMergedRegion(i); if (src.getFirstRow() >= top && src.getLastRow() <= bottom) { final Region dst = new Region( this, src.getFirstColumn(), src.getFirstRow() - top, src.getLastColumn(), src.getLastRow() - top); regions.add(dst); } else if (src.getFirstRow() < top && src.getLastRow() >= top) throw new IllegalArgumentException( "Illegal region {top:" + top + ", height:" + height + "} bounds: conflict with region {top:" + src.getFirstRow() + ", bottom:" + src.getLastRow() + "}"); if (src.getFirstRow() >= top && src.getFirstRow() <= bottom && src.getLastRow() > bottom) throw new IllegalArgumentException( "Illegal region {top:" + top + ", height:" + height + "} bounds: conflict with region {top:" + src.getFirstRow() + ", bottom:" + src.getLastRow() + "}"); } this.hidden = false; }