/** 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; }
/** 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; }