Ejemplo n.º 1
0
  /**
   * 取单元格所在的合并区域,如果返回空,则说明没有在合并区域
   *
   * @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;
  }
Ejemplo n.º 2
0
  /**
   * 添加临时表格内容
   *
   * @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;
  }
Ejemplo n.º 3
0
  /**
   * Собирает информацию о некоторой области из указанного листа шаблона 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;
  }