Beispiel #1
0
  //  traversal cell
  public void traversalCell(String filePath) {
    try {
      Workbook workBook = null;
      try {
        workBook = new XSSFWorkbook(filePath); // 支持2007
      } catch (Exception ex) {
        workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
      }

      // 获得Excel中工作表个数
      System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

      // 循环每个工作表
      for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        // 创建工作表
        Sheet sheet = workBook.getSheetAt(i);

        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数

        System.out.println(
            "工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());

        if (rows > 0) {
          sheet.getMargin(Sheet.TopMargin);
          for (int r = 0; r < rows; r++) { // 行循环
            Row row = sheet.getRow(r);
            if (row != null) {

              int cells = row.getLastCellNum(); // 获得列数
              for (short c = 0; c < cells; c++) { // 列循环
                Cell cell = row.getCell(c);

                if (cell != null) {
                  String value = getCellData(cell);
                  System.out.println("第" + r + "行 " + "第" + c + "列:" + value);
                }
              }
            }
          }
        }

        // 查询合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
          System.out.println("第" + i + "个合并单元格");
          CellRangeAddress region = sheet.getMergedRegion(i);
          int row = region.getLastRow() - region.getFirstRow() + 1;
          int col = region.getLastColumn() - region.getFirstColumn() + 1;
          System.out.println("起始行:" + region.getFirstRow());
          System.out.println("起始列:" + region.getFirstColumn());
          System.out.println("所占行:" + row);
          System.out.println("所占列:" + col);
        }
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
 /**
  * @param srcSheet the sheet to copy.
  * @param destSheet the sheet to create.
  * @param srcRow the row to copy.
  * @param destRow the row to create.
  * @param styleMap -
  */
 public static void copyRow(
     XSSFSheet srcSheet,
     XSSFSheet destSheet,
     XSSFRow srcRow,
     XSSFRow destRow,
     Map<Integer, XSSFCellStyle> styleMap) {
   Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
   destRow.setHeight(srcRow.getHeight());
   for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
     XSSFCell oldCell = srcRow.getCell(j);
     XSSFCell newCell = destRow.getCell(j);
     if (oldCell != null) {
       if (newCell == null) {
         newCell = destRow.createCell(j);
       }
       copyCell(oldCell, newCell, styleMap);
       CellRangeAddress mergedRegion =
           getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
       if (mergedRegion != null) {
         CellRangeAddress newMergedRegion =
             new CellRangeAddress(
                 mergedRegion.getFirstRow(),
                 mergedRegion.getFirstColumn(),
                 mergedRegion.getLastRow(),
                 mergedRegion.getLastColumn());
         CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
         if (isNewMergedRegion(wrapper, mergedRegions)) {
           mergedRegions.add(wrapper);
           destSheet.addMergedRegion(wrapper.range);
         }
       }
     }
   }
 }
  public void testModifyArrayCells_removeRow() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());

    Row srow = scell.getRow();
    assertSame(srow, sheet.getRow(cra.getFirstRow()));
    sheet.removeRow(srow);
    assertNull(sheet.getRow(cra.getFirstRow()));

    // re-create the removed row and cell
    scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove rows with cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      int columnIndex = mcell.getColumnIndex();
      Row mrow = mcell.getRow();
      try {
        sheet.removeRow(mrow);
        fail("expected exception");
      } catch (IllegalStateException e) {
        String msg =
            "Row[rownum="
                + mrow.getRowNum()
                + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      assertSame(mrow, sheet.getRow(mrow.getRowNum()));
      assertSame(mcell, mrow.getCell(columnIndex));
      assertTrue(mcell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }
  }
  private void processCell(Element tr, XSSFCell cell) {

    int num = cell.getSheet().getNumMergedRegions();

    //		System.out.println(cell.getCTCell());
    for (int i = 0; i < num; i++) {

      CellRangeAddress c = cell.getSheet().getMergedRegion(i);

      System.out.println(c.getFirstColumn());
      ;
      System.out.println(c.getLastColumn());
      System.out.println(c.getFirstRow());
      System.out.println(c.getLastRow());
      System.out.println();
      System.out.println(cell.getRowIndex());
      System.out.println(cell.getColumnIndex());

      System.out.println("\n\n\n");

      //		System.out.println(cra);

    }

    //		System.exit(0);

    Element td = htmlDocumentFacade.createTableCell();
    Object value;
    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_BLANK:
        value = "\u00a0";
        break;
      case Cell.CELL_TYPE_NUMERIC:
        value = cell.getNumericCellValue();
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        value = cell.getBooleanCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        value = cell.getNumericCellValue();
        break;
      default:
        value = cell.getRichStringCellValue();
        break;
    }
    if (value instanceof XSSFRichTextString) {
      processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
      td.setTextContent(value.toString());
    } else {
      processCellStyle(td, cell.getCellStyle(), null);
      td.setTextContent(value.toString());
    }
    //		System.err.println(value);
    tr.appendChild(td);
  }
Beispiel #5
0
 /**
  * @param
  * @return void
  */
 private void fillMergedRegion(HSSFSheet sheet, CellRangeAddress address, HSSFCellStyle style) {
   for (int i = address.getFirstRow(); i <= address.getLastRow(); i++) {
     HSSFRow row = sheet.getRow(i);
     if (row == null) row = sheet.createRow(i);
     for (int j = address.getFirstColumn(); j <= address.getLastColumn(); j++) {
       HSSFCell cell = row.getCell(j);
       if (cell == null) {
         cell = row.createCell(j);
         if (style != null) cell.setCellStyle(style);
       }
     }
   }
 }
  /**
   * 取单元格所在的合并区域,如果返回空,则说明没有在合并区域
   *
   * @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;
  }
  /**
   * 输出图片到指定的单元格,参考POI例子中的ReportImageUtil类。
   *
   * @param cell -- 单元格
   * @param bytes -- 图片内容
   */
  public static void addImageToSheet(HSSFCell cell, byte[] bytes) {
    if (cell == null) {
      _log.showError("-----insertImageToSheet: cell is null!");
      return;
    }
    if (bytes == null || bytes.length == 0) {
      _log.showError("-----insertImageToSheet: bytes is null!");
      return;
    }

    // 取所在表单对象
    HSSFSheet sheet = cell.getSheet();

    // 取图片输出行与列
    int firstRow = cell.getRowIndex();
    int lastRow = cell.getRowIndex();
    int firstCol = cell.getColumnIndex();
    int lastCol = cell.getColumnIndex();
    // 取单元格所在的区域
    CellRangeAddress range = getMergedRegion(cell);
    if (range != null) {
      firstRow = range.getFirstRow();
      lastRow = range.getLastRow();
      firstCol = range.getFirstColumn();
      lastCol = range.getLastColumn();
    }
    _log.showDebug(
        "---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]");
    // 图片输出要比单元格的高与宽偏5个值,保留单元的边框,宽度1023表示填充满,高度255表示填充满
    HSSFClientAnchor anchor =
        new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow);
    anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE);

    // 取图片管理器,如果没有则创建
    HSSFPatriarch draw = sheet.getDrawingPatriarch();
    if (draw == null) {
      draw = sheet.createDrawingPatriarch();
    }

    // 插入新图片,返回的新图片序号无效
    sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
    // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号
    List<HSSFPicture> lsPicture = getAllPicture(sheet);
    int index = lsPicture.size() + 1;
    _log.showDebug("---------new image index=" + index);

    draw.createPicture(anchor, index);
  }
Beispiel #8
0
 public static void copyBlock(
     Sheet sheet,
     int startRow,
     int startCol,
     int endRow,
     int endCol,
     boolean copyStyle,
     int rowOffset,
     int colOffset,
     List<CellRangeAddress> mergedRegions) {
   for (int row = startRow; row <= endRow; row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = sheet.getRow(row + rowOffset);
     if (newRow == null) newRow = sheet.createRow(row + rowOffset);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     if (logger.isDebugEnabled()) {
       logger.debug("copy row {} to {}", row, row + rowOffset);
       logger.debug("Set row height :{}", newRow.getHeightInPoints());
     }
     for (int col = startCol; col <= endCol; col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col + colOffset);
       if (newCell == null) newCell = newRow.createCell(col + colOffset);
       copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
     }
   }
   for (int col = startCol; col <= endCol; col++) {
     if (sheet.getColumnWidth(col) >= 0)
       sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
   }
   if (mergedRegions != null) {
     for (CellRangeAddress cra : mergedRegions) {
       CellRangeAddress craNew =
           new CellRangeAddress(
               cra.getFirstRow() + rowOffset,
               cra.getLastRow() + rowOffset,
               cra.getFirstColumn() + colOffset,
               cra.getLastColumn() + colOffset);
       sheet.addMergedRegion(craNew);
     }
   }
 }
  /**
   * 添加临时表格内容
   *
   * @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;
  }
Beispiel #10
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;
  }
 /** @return the 0-based row of the first cell that contains this hyperlink */
 public int getFirstRow() {
   return _range.getFirstRow();
 }
  /** Adds in a Row to the given Sheet */
  public Row addRow(
      Workbook wb,
      SheetToAdd sheetToAdd,
      RowToAdd rowToAdd,
      int rowIndex,
      ReportData reportData,
      ReportDesign design,
      Map<String, String> repeatSections) {

    // Create a new row and copy over style attributes from the row to add
    Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
    Row rowToClone = rowToAdd.getRowToClone();
    try {
      CellStyle rowStyle = rowToClone.getRowStyle();
      if (rowStyle != null) {
        newRow.setRowStyle(rowStyle);
      }
    } catch (Exception e) {
      // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
      // rowStyle.  Mysteries of POI
    }
    newRow.setHeight(rowToClone.getHeight());

    // Iterate across all of the cells in the row, and configure all those that need to be
    // added/cloned
    List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();

    int totalCells = rowToClone.getPhysicalNumberOfCells();
    int cellsFound = 0;
    for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
      Cell currentCell = rowToClone.getCell(cellNum);
      log.debug("Handling cell: " + currentCell);
      if (currentCell != null) {
        cellsFound++;
      }
      // If we find that the cell that we are on is a repeating cell, then add the appropriate
      // number of cells to clone
      String repeatingColumnProperty =
          getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
      if (repeatingColumnProperty != null) {
        String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
        String dataSetName = dataSetSpanSplit[0];
        DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
        int numCellsToRepeat = 1;
        if (dataSetSpanSplit.length == 2) {
          numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
        }
        log.debug(
            "Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
        int repeatNum = 0;
        for (DataSetRow dataSetRow : dataSet) {
          repeatNum++;
          for (int i = 0; i < numCellsToRepeat; i++) {
            Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
            if (repeatNum == 1 && cell != null && cell != currentCell) {
              cellsFound++;
            }
            Map<String, Object> newReplacements =
                getReplacementData(
                    rowToAdd.getReplacementData(),
                    reportData,
                    design,
                    dataSetName,
                    dataSetRow,
                    repeatNum);
            cellsToAdd.add(new CellToAdd(cell, newReplacements));
            log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
          }
        }
        cellNum += numCellsToRepeat;
      } else {
        cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
        log.debug("Adding " + currentCell);
      }
    }

    // Now, go through all of the collected cells, and add them back in

    ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    for (int i = 0; i < cellsToAdd.size(); i++) {
      CellToAdd cellToAdd = cellsToAdd.get(i);
      Cell newCell = newRow.createCell(i);
      Cell cellToClone = cellToAdd.getCellToClone();
      if (cellToClone != null) {
        String contents = ExcelUtil.getCellContentsAsString(cellToClone);
        newCell.setCellStyle(cellToClone.getCellStyle());
        try {
          newCell.setCellFormula(cellToClone.getCellFormula());
        } catch (Exception e) {
          // Do nothing here.  I don't know why POI throw exceptions here when the cell is not a
          // formula, but this suppresses them...
        }

        int numFormattings =
            sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
        for (int n = 0; n < numFormattings; n++) {
          ConditionalFormatting f =
              sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
          for (CellRangeAddress add : f.getFormattingRanges()) {

            if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
                && add.getLastRow() == rowToClone.getRowNum()) {
              if (add.getFirstColumn() == cellToClone.getColumnIndex()
                  && add.getLastColumn() == cellToClone.getColumnIndex()) {
                ConditionalFormattingRule[] rules =
                    new ConditionalFormattingRule[f.getNumberOfRules()];
                for (int j = 0; j < f.getNumberOfRules(); j++) {
                  rules[j] = f.getRule(j);
                }
                CellRangeAddress[] cellRange = new CellRangeAddress[1];
                cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
                sheetToAdd
                    .getSheet()
                    .getSheetConditionalFormatting()
                    .addConditionalFormatting(cellRange, rules);
              }
            }
          }
        }

        if (ObjectUtil.notNull(contents)) {
          Object newContents =
              EvaluationUtil.evaluateExpression(
                  contents, cellToAdd.getReplacementData(), prefix, suffix);
          ExcelUtil.setCellContents(styleHelper, newCell, newContents);
        }
      }
    }

    return newRow;
  }