Exemple #1
0
  /**
   * Watch list serve for formula changes. Basically all the rows appeared in the formula in the
   * current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it
   * will be ignored.
   *
   * @param wbWrapper XSSFEvaluationWorkbook used for formula parse.
   * @param sheet current sheet.
   * @return List row number for monitoring.
   */
  private List<Integer> buildFormWatchList(
      final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) {

    List<Integer> watchList = new ArrayList<Integer>();

    ConfigRange cRange = this.getConfigRange();
    List<ConfigCommand> commandList = cRange.getCommandList();
    if (commandList.size() <= 0) {
      // if no command then no dynamic changes. then no need formula shifts.
      return watchList;
    }
    int lastStaticRow = commandList.get(0).getTopRow() - 1;
    if (lastStaticRow < 0) {
      lastStaticRow = this.getTopRow();
    }

    Workbook wb = sheet.getWorkbook();

    for (int i = this.getTopRow(); i <= this.getLastRow(); i++) {
      Row row = sheet.getRow(i);
      for (Cell cell : row) {
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

          String formula = cell.getCellFormula();

          Ptg[] ptgs =
              FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet));

          for (int k = 0; k < ptgs.length; k++) {
            Object ptg = ptgs[k];
            // For area formula, only first row is watched.
            // Reason is the lastRow must shift same rows with
            // firstRow.
            // Otherwise it's difficult to calculate.
            // In case some situation cannot fit, then should make
            // change to the formula.
            int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg);
            if (areaInt >= 0) {
              addToWatchList(sheet, areaInt, lastStaticRow, watchList);
            }
          }

          // when insert row, the formula may changed. so here is the workaround.
          // change formula to user formula to preserve the row changes.
          cell.setCellType(Cell.CELL_TYPE_STRING);
          cell.setCellValue(
              ConfigurationHelper.USER_FORMULA_PREFIX
                  + formula
                  + ConfigurationHelper.USER_FORMULA_SUFFIX);
        }
      }
    }

    return watchList;
  }
  /**
   * テンプレートワークブックのシートを、帳票出力単位に変換する。
   *
   * @param workbook テンプレートワークブック
   * @param reportBook 帳票ワークブック情報
   * @return 削除が必要なテンプレートシートインデックス
   */
  private Set<Integer> expandTemplate(Workbook workbook, ReportBook reportBook) {

    Set<Integer> delTemplateIndexs = new TreeSet<Integer>(Collections.reverseOrder());
    Set<Integer> useTemplateIndexs = new HashSet<Integer>();

    // 出力シート単位にコピーする
    for (ReportSheet reportSheet : reportBook.getReportSheets()) {

      if (reportSheet != null) {
        if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) {
          // テンプレート名=出力シート名
          int lastSheetIndex = workbook.getNumberOfSheets() - 1;
          workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex);
          useTemplateIndexs.add(lastSheetIndex);
        } else {
          int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName());

          Sheet sheet = workbook.cloneSheet(tempIdx);
          workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName());
          delTemplateIndexs.add(tempIdx);
        }
      }
    }

    // 出力対象外シートを削除インデックスに追加
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      Sheet sheet = workbook.getSheetAt(i);
      if (!isOutputSheet(sheet, reportBook)) {
        delTemplateIndexs.add(workbook.getSheetIndex(sheet));
      }
    }

    delTemplateIndexs.removeAll(useTemplateIndexs);

    return delTemplateIndexs;
  }
Exemple #3
0
 /**
  * @param wb
  * @param reviewerSheetName
  */
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
     sheet.setColumnWidth(i, COL_WIDTH * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
   }
 }
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < HEADER_TITLES.length; i++) {
     sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
     cell.setCellValue(HEADER_TITLES[i]);
   }
 }
  /** Clone the sheet at the passed index and replace values as needed */
  public Sheet addSheet(
      Workbook wb,
      SheetToAdd sheetToAdd,
      Set<String> usedSheetNames,
      ReportData reportData,
      ReportDesign design,
      Map<String, String> repeatSections) {

    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    Sheet sheet = sheetToAdd.getSheet();
    sheet.setForceFormulaRecalculation(true);

    int sheetIndex = wb.getSheetIndex(sheet);

    // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the
    // workbook
    String sheetName =
        EvaluationUtil.evaluateExpression(
                sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix)
            .toString();
    sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames);
    wb.setSheetName(sheetIndex, sheetName);
    usedSheetNames.add(sheetName);

    log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex);

    // Iterate across all of the rows in the sheet, and configure all those that need to be
    // added/cloned
    List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>();

    int totalRows = sheet.getPhysicalNumberOfRows();
    int rowsFound = 0;
    for (int rowNum = 0;
        rowsFound < totalRows && rowNum < 50000;
        rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases
      Row currentRow = sheet.getRow(rowNum);
      log.debug("Handling row: " + ExcelUtil.formatRow(currentRow));
      if (currentRow != null) {
        rowsFound++;
      }
      // If we find that the row that we are on is a repeating row, then add the appropriate number
      // of rows to clone
      String repeatingRowProperty =
          getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections);
      if (repeatingRowProperty != null) {
        String[] dataSetSpanSplit = repeatingRowProperty.split(",");
        String dataSetName = dataSetSpanSplit[0];
        DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData());

        int numRowsToRepeat = 1;
        if (dataSetSpanSplit.length == 2) {
          numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
        }
        log.debug(
            "Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat);
        int repeatNum = 0;
        for (DataSetRow dataSetRow : dataSet) {
          repeatNum++;
          for (int i = 0; i < numRowsToRepeat; i++) {
            Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i));
            if (repeatNum == 1 && row != null && row != currentRow) {
              rowsFound++;
            }
            Map<String, Object> newReplacements =
                getReplacementData(
                    sheetToAdd.getReplacementData(),
                    reportData,
                    design,
                    dataSetName,
                    dataSetRow,
                    repeatNum);
            rowsToAdd.add(new RowToAdd(row, newReplacements));
            log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow);
          }
        }
        if (numRowsToRepeat > 1) {
          rowNum += numRowsToRepeat - 1;
        }
      } else {
        rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData()));
        log.debug("Adding row: " + ExcelUtil.formatRow(currentRow));
      }
    }

    // Now, go through all of the collected rows, and add them back in
    for (int i = 0; i < rowsToAdd.size(); i++) {
      RowToAdd rowToAdd = rowsToAdd.get(i);
      if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) {
        Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections);
        log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow));
      }
    }

    return sheet;
  }