Good Morning Dan, we are using the following clumpsy code to copy/shift 100 thousands of rows in our reporting -- with formulas and formatting. It works for us although it is certainly not optimal and has not been touched for years.
I attached it only in the hope that it can be helpful somehow. Cheers and best Andreas
/** * Copyright (C) 2024 Andreas Reichel <[email protected]> * * This program is free software; you can redistribute it and/or modify it under the terms of the * GNU Lesser General Public License as published by the Free Software Foundation; either version 3 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License along with this program; * if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA * 02110-1301, USA. */ /* * To change this license header, choose License Headers in Project Properties. To change this * template file, choose Tools | Templates and open the template in the editor. */ package com.manticore.report; import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; import org.apache.poi.hssf.usermodel.HSSFWorkbook; // import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.formula.*; import org.apache.poi.ss.formula.ptg.AreaPtgBase; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.RefPtgBase; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author are */ public class ExcelTools { public static void writeDataToSheet(final Workbook workbook, String sheetName, Object[][] data, FormulaParsingWorkbook parsingWorkbook, FormulaRenderingWorkbook renderingWorkbook, int rowOffset, int colOffset) { Sheet sheet = workbook.getSheet(sheetName); for (int r = 0; r < data.length; r++) { // if (r > 0) { shiftRows(workbook, sheet, parsingWorkbook, renderingWorkbook, rowOffset + r); // } for (int c = 0; c < data[r].length; c++) { Row row = sheet.getRow(rowOffset + r); if (row == null) { row = sheet.createRow(rowOffset + r); } Cell cell = row.getCell(colOffset + c); if (cell == null) { cell = row.createCell(colOffset + c); } Object value = data[r][c]; if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof java.sql.Date) { java.sql.Date sqlDate = (java.sql.Date) value; java.util.Date date = new java.util.Date(sqlDate.getTime()); cell.setCellValue(date); } else if (value instanceof java.sql.Timestamp) { java.sql.Timestamp timestamp = (java.sql.Timestamp) value; java.util.Date date = new java.util.Date(timestamp.getTime()); cell.setCellValue(date); } else if (value instanceof java.util.Date) { cell.setCellValue((java.util.Date) value); } else { cell.setCellValue(value != null ? value.toString() : ""); } } } } public static void shiftRows(Workbook workbook, Sheet worksheet, FormulaParsingWorkbook parsingWorkbook, FormulaRenderingWorkbook renderingWorkbook, int rowNum) { for (int r = worksheet.getLastRowNum(); r >= rowNum; r--) { Row sourceRow = worksheet.getRow(r); if (sourceRow != null) { Row newRow = worksheet.createRow(r + 1); for (int c = 0; c < sourceRow.getLastCellNum(); c++) { Cell oldCell = sourceRow.getCell(c); if (oldCell != null) { Cell newCell = newRow.createCell(c); newCell.setCellStyle(oldCell.getCellStyle()); switch (oldCell.getCellType()) { case BLANK: newCell.setBlank(); break; case BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case FORMULA: try { String oldFormula = oldCell.getCellFormula(); Ptg[] ptgs = FormulaParser.parse(oldFormula, parsingWorkbook, FormulaType.CELL, workbook.getSheetIndex(worksheet)); // iterating through all PTG's for (Ptg ptg : ptgs) { if (ptg instanceof RefPtgBase) { RefPtgBase refPtgBase = (RefPtgBase) ptg; // if row is relative if (refPtgBase.isRowRelative()) { refPtgBase.setRow( newCell.getRowIndex() - (oldCell.getRowIndex() - refPtgBase.getRow())); } // if col is relative if (refPtgBase.isColRelative()) { refPtgBase.setColumn(newCell.getColumnIndex() - (oldCell.getColumnIndex() - refPtgBase.getColumn())); } } if (ptg instanceof AreaPtgBase) { AreaPtgBase areaPtgBase = (AreaPtgBase) ptg; // if first row is relative if (areaPtgBase.isFirstRowRelative() && areaPtgBase.getFirstRow() > oldCell .getRowIndex()) { areaPtgBase.setFirstRow(newCell.getRowIndex() - (oldCell.getRowIndex() - areaPtgBase.getFirstRow())); } // if last row is relative if (areaPtgBase.isLastRowRelative()) { areaPtgBase.setLastRow(newCell.getRowIndex() - (oldCell.getRowIndex() - areaPtgBase.getLastRow())); } // if first column is relative if (areaPtgBase.isFirstColRelative()) { areaPtgBase.setFirstColumn(newCell.getColumnIndex() - (oldCell.getColumnIndex() - areaPtgBase.getFirstColumn())); } // if last column is relative if (areaPtgBase.isLastColRelative()) { areaPtgBase.setLastColumn(newCell.getColumnIndex() - (oldCell.getColumnIndex() - areaPtgBase.getLastColumn())); } } } String newFormula = FormulaRenderer.toFormulaString(renderingWorkbook, ptgs); newCell.setCellFormula(newFormula); } catch (Exception ignore) { // fail silently } break; case NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } else { Row row = worksheet.getRow(r + 1); if (row != null) { worksheet.removeRow(row); } } } } public static void shiftRows(SXSSFWorkbook workbook, SXSSFSheet worksheet, int rowNum) { for (int r = worksheet.getLastRowNum(); r >= rowNum; r--) { Row sourceRow = worksheet.getRow(r); if (sourceRow != null) { Row newRow = worksheet.createRow(r + 1); for (int c = 0; c < sourceRow.getLastCellNum(); c++) { Cell oldCell = sourceRow.getCell(c); if (oldCell != null) { Cell newCell = newRow.createCell(c); newCell.setCellStyle(oldCell.getCellStyle()); switch (oldCell.getCellType()) { case BLANK: newCell.setBlank(); break; case BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case FORMULA: break; case NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } else { Row row = worksheet.getRow(r + 1); if (row != null) { worksheet.removeRow(row); } } } } public static void copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row Row newRow = null; // worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); if (sourceRow == null) { sourceRow = worksheet.createRow(sourceRowNum); } // try { // worksheet.shiftRows(sourceRowNum, sourceRowNum+1, 1); // } catch (Exception ex) { // FixFormatReport.LOGGER.log(Level.SEVERE, null, ex); // } // If the row exist in destination, push down all rows by 1 else create a new row newRow = worksheet.getRow(sourceRowNum + 1); if (newRow == null) { newRow = worksheet.createRow(sourceRowNum + 1); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell // HSSFCellStyle newCellStyle = workbook.createCellStyle(); // newCellStyle.cloneStyleFrom();; newCell.setCellStyle(oldCell.getCellStyle()); // Set the cell data value switch (oldCell.getCellType()) { case BLANK: newCell.setBlank(); break; case BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case FORMULA: newCell.setCellFormula(getCopyFormula(workbook, worksheet, oldCell, newCell)); break; case NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } } public static String getCopyFormula(Workbook workbook, Sheet sheet, Cell oldCell, Cell newCell) { String oldFormula = oldCell.getCellFormula(); String newFormula = ""; if (oldFormula != null) { FormulaParsingWorkbook parsingWorkbook = null; FormulaRenderingWorkbook renderingWorkbook = null; if (workbook instanceof HSSFWorkbook) { parsingWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook); renderingWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook); } else if (workbook instanceof XSSFWorkbook) { parsingWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook); renderingWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook); } // get PTG's in the formula Ptg[] ptgs = FormulaParser.parse(oldFormula, parsingWorkbook, FormulaType.CELL, workbook.getSheetIndex(sheet)); // iterating through all PTG's for (Ptg ptg : ptgs) { if (ptg instanceof RefPtgBase) { RefPtgBase refPtgBase = (RefPtgBase) ptg; // if row is relative if (refPtgBase.isRowRelative()) { refPtgBase.setRow( (short) (newCell.getRowIndex() - (oldCell.getRowIndex() - refPtgBase.getRow()))); } // if col is relative if (refPtgBase.isColRelative()) { refPtgBase.setColumn((short) (newCell.getColumnIndex() - (oldCell.getColumnIndex() - refPtgBase.getColumn()))); } } if (ptg instanceof AreaPtgBase) { AreaPtgBase areaPtgBase = (AreaPtgBase) ptg; // if first row is relative if (areaPtgBase.isFirstRowRelative()) { areaPtgBase.setFirstRow((short) (newCell.getRowIndex() - (oldCell.getRowIndex() - areaPtgBase.getFirstRow()))); } // if last row is relative if (areaPtgBase.isLastRowRelative()) { areaPtgBase.setLastRow((short) (newCell.getRowIndex() - (oldCell.getRowIndex() - areaPtgBase.getLastRow()))); } // if first column is relative if (areaPtgBase.isFirstColRelative()) { areaPtgBase.setFirstColumn((short) (newCell.getColumnIndex() - (oldCell.getColumnIndex() - areaPtgBase.getFirstColumn()))); } // if last column is relative if (areaPtgBase.isLastColRelative()) { areaPtgBase.setLastColumn((short) (newCell.getColumnIndex() - (oldCell.getColumnIndex() - areaPtgBase.getLastColumn()))); } } } newFormula = FormulaRenderer.toFormulaString(renderingWorkbook, ptgs); } return newFormula; } }
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
