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); } } } } }
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; }