Esempio n. 1
0
 public static void copySheet(Sheet sheet, Sheet newSheet) {
   int maxCol = 0;
   for (int row = 0; row <= sheet.getLastRowNum(); row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = newSheet.getRow(row);
     if (newRow == null) newRow = newSheet.createRow(row);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
     for (int col = 0; col < oldRow.getLastCellNum(); col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col);
       if (newCell == null) newCell = newRow.createCell(col);
       copyCell(oldCell, newCell, true);
     }
   }
   for (int col = 0; col <= maxCol; col++) {
     if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
   }
   for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
     CellRangeAddress cra = sheet.getMergedRegion(i);
     newSheet.addMergedRegion(cra);
   }
 }
  private void addSumBottom() {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);

      Row row = sheet.createRow(sheet.getLastRowNum() + 1);
      row.setHeight((short) (ROW_HEIGHT + 100));

      for (int j = 0; j < 1000000; j++) {
        if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
            && StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
          break;
        }
        Cell cell = row.createCell(j);
        cell.setCellStyle(Style.get(book).SUM);
        if (j == 0) {
          cell.setCellValue("合计");
        } else {
          cell.setCellValue(0);
        }

        if (j >= 7) {
          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          cell.setCellFormula(
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(j + 1),
                  5,
                  CellUtils.convertToABC(j + 1),
                  sheet.getLastRowNum()));
        }
      }
      sheet.addMergedRegion(
          new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
    }

    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);
      for (int j = 4; j <= sheet.getLastRowNum(); j++) {
        Row row = sheet.getRow(j);
        for (int k = 0; k <= row.getLastCellNum(); k++) {
          Cell cell = row.getCell(k);
          if (cell == null) {
            continue;
          }

          if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
            cell.setCellStyle(Style.get(book).SUM);
          }
        }
      }
    }
  }
Esempio n. 3
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);
     }
   }
 }
 private void setLayout(Sheet worksheet) {
   Row rowHeader = worksheet.createRow(0);
   rowHeader.setHeight((short) 500);
   worksheet.setColumnWidth(OFFICE_NAME_COL, 4000);
   worksheet.setColumnWidth(CLIENT_NAME_COL, 5000);
   worksheet.setColumnWidth(SAVINGS_ACCOUNT_NO_COL, 3000);
   worksheet.setColumnWidth(PRODUCT_COL, 4000);
   worksheet.setColumnWidth(OPENING_BALANCE_COL, 4000);
   worksheet.setColumnWidth(TRANSACTION_TYPE_COL, 3300);
   worksheet.setColumnWidth(AMOUNT_COL, 4000);
   worksheet.setColumnWidth(TRANSACTION_DATE_COL, 3000);
   worksheet.setColumnWidth(PAYMENT_TYPE_COL, 3000);
   worksheet.setColumnWidth(ACCOUNT_NO_COL, 3000);
   worksheet.setColumnWidth(CHECK_NO_COL, 3000);
   worksheet.setColumnWidth(RECEIPT_NO_COL, 3000);
   worksheet.setColumnWidth(ROUTING_CODE_COL, 3000);
   worksheet.setColumnWidth(BANK_NO_COL, 3000);
   worksheet.setColumnWidth(LOOKUP_CLIENT_NAME_COL, 5000);
   worksheet.setColumnWidth(LOOKUP_ACCOUNT_NO_COL, 3000);
   worksheet.setColumnWidth(LOOKUP_PRODUCT_COL, 3000);
   worksheet.setColumnWidth(LOOKUP_OPENING_BALANCE_COL, 3700);
   worksheet.setColumnWidth(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500);
   writeString(OFFICE_NAME_COL, rowHeader, "Office Name*");
   writeString(CLIENT_NAME_COL, rowHeader, "Client Name*");
   writeString(SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*");
   writeString(PRODUCT_COL, rowHeader, "Product Name");
   writeString(OPENING_BALANCE_COL, rowHeader, "Opening Balance");
   writeString(TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*");
   writeString(AMOUNT_COL, rowHeader, "Amount*");
   writeString(TRANSACTION_DATE_COL, rowHeader, "Date*");
   writeString(PAYMENT_TYPE_COL, rowHeader, "Type*");
   writeString(ACCOUNT_NO_COL, rowHeader, "Account No");
   writeString(CHECK_NO_COL, rowHeader, "Check No");
   writeString(RECEIPT_NO_COL, rowHeader, "Receipt No");
   writeString(ROUTING_CODE_COL, rowHeader, "Routing Code");
   writeString(BANK_NO_COL, rowHeader, "Bank No");
   writeString(LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client");
   writeString(LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account");
   writeString(LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product");
   writeString(LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance");
   writeString(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date");
 }
  /** 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;
  }