From af689e73bd8f61cc155e012294a30cbada58a2f3 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Tue, 26 Jul 2022 21:42:24 +0000 Subject: [PATCH] [bug-65562] derive sheet dimensions when outputting SXSSFSheets git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1903037 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/streaming/SXSSFRow.java | 1 + .../apache/poi/xssf/streaming/SXSSFSheet.java | 45 ++++++++++++++++ .../poi/xssf/streaming/SXSSFWorkbook.java | 30 +++++++++-- .../apache/poi/xssf/usermodel/XSSFSheet.java | 53 +++++++++++++------ .../poi/xssf/streaming/TestSXSSFWorkbook.java | 40 ++++++++++++++ 5 files changed, 148 insertions(+), 21 deletions(-) diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFRow.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFRow.java index ff86b1ef30..fb9a8846a1 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFRow.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFRow.java @@ -141,6 +141,7 @@ public class SXSSFRow implements Row, Comparable checkBounds(column); SXSSFCell cell = new SXSSFCell(this, type); _cells.put(column, cell); + _sheet.trackNewCell(cell); return cell; } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFSheet.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFSheet.java index b5ad3dfb66..4fa4138b3f 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFSheet.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFSheet.java @@ -27,6 +27,8 @@ import java.util.Set; import java.util.Spliterator; import java.util.TreeMap; +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellAddress; @@ -47,6 +49,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; * Streaming version of XSSFSheet implementing the "BigGridDemo" strategy. */ public class SXSSFSheet implements Sheet, OoxmlSheetExtensions { + private static final Logger LOG = LogManager.getLogger(SXSSFSheet.class); + /*package*/ final XSSFSheet _sh; protected final SXSSFWorkbook _workbook; private final TreeMap _rows = new TreeMap<>(); @@ -56,14 +60,38 @@ public class SXSSFSheet implements Sheet, OoxmlSheetExtensions { private int outlineLevelRow; private int lastFlushedRowNumber = -1; private boolean allFlushed; + private int leftMostColumn = SpreadsheetVersion.EXCEL2007.getLastColumnIndex(); + private int rightMostColumn; protected SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet, int randomAccessWindowSize) { _workbook = workbook; _sh = xSheet; + calculateLeftAndRightMostColumns(xSheet); setRandomAccessWindowSize(randomAccessWindowSize); _autoSizeColumnTracker = new AutoSizeColumnTracker(this); } + private void calculateLeftAndRightMostColumns(XSSFSheet xssfSheet) { + if (_workbook.shouldCalculateSheetDimensions()) { + int rowCount = 0; + int leftMostColumn = Integer.MAX_VALUE; + int rightMostColumn = 0; + for (Row row : xssfSheet) { + rowCount++; + if (row.getFirstCellNum() < leftMostColumn) { + final int first = row.getFirstCellNum(); + final int last = row.getLastCellNum() - 1; + leftMostColumn = Math.min(first, leftMostColumn); + rightMostColumn = Math.max(last, rightMostColumn); + } + } + if (rowCount > 0) { + this.leftMostColumn = leftMostColumn; + this.rightMostColumn = rightMostColumn; + } + } + } + public SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws IOException { _workbook = workbook; _sh = xSheet; @@ -2106,4 +2134,21 @@ public class SXSSFSheet implements Sheet, OoxmlSheetExtensions { public void shiftColumns(int startColumn, int endColumn, int n){ throw new UnsupportedOperationException("Not Implemented"); } + + void trackNewCell(SXSSFCell cell) { + leftMostColumn = Math.min(cell.getColumnIndex(), leftMostColumn); + rightMostColumn = Math.max(cell.getColumnIndex(), rightMostColumn); + } + + void deriveDimension() { + if (_workbook.shouldCalculateSheetDimensions()) { + try { + CellRangeAddress cellRangeAddress = new CellRangeAddress( + getFirstRowNum(), getLastRowNum(), leftMostColumn, rightMostColumn); + _sh.setDimensionOverride(cellRangeAddress); + } catch (Exception e) { + LOG.atDebug().log("Failed to set dimension details on sheet", e); + } + } + } } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java index bc93ebd5fd..54457e86ff 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java @@ -136,6 +136,8 @@ public class SXSSFWorkbook implements Workbook { */ protected Zip64Mode zip64Mode = Zip64Mode.Always; + private boolean shouldCalculateSheetDimensions = true; + /** * Construct a new workbook with default row window size */ @@ -351,6 +353,24 @@ public class SXSSFWorkbook implements Workbook { _compressTmpFiles = compress; } + /** + * @param shouldCalculateSheetDimensions defaults to true, set to false if + * the calculated dimensions are causing trouble + * @since POI 5.2.3 + */ + public void setShouldCalculateSheetDimensions(boolean shouldCalculateSheetDimensions) { + this.shouldCalculateSheetDimensions = shouldCalculateSheetDimensions; + } + + /** + * @return shouldCalculateSheetDimensions defaults to true, set to false if + * the calculated dimensions are causing trouble + * @since POI 5.2.3 + */ + public boolean shouldCalculateSheetDimensions() { + return shouldCalculateSheetDimensions; + } + @Internal protected SharedStringsTable getSharedStringSource() { return _sharedStringSource; @@ -971,8 +991,10 @@ public class SXSSFWorkbook implements Workbook { } //Substitute the template entries with the generated sheet data files - try (ZipSecureFile zf = new ZipSecureFile(tmplFile); - ZipFileZipEntrySource source = new ZipFileZipEntrySource(zf)) { + try ( + ZipSecureFile zf = new ZipSecureFile(tmplFile); + ZipFileZipEntrySource source = new ZipFileZipEntrySource(zf) + ) { injectData(source, stream); } } finally { @@ -1012,8 +1034,8 @@ public class SXSSFWorkbook implements Workbook { } protected void flushSheets() throws IOException { - for (SXSSFSheet sheet : _xFromSxHash.values()) - { + for (SXSSFSheet sheet : _xFromSxHash.values()) { + sheet.deriveDimension(); sheet.flushRows(); } } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index deed5b68d2..83aa926529 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -106,6 +106,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx private List arrayFormulas; private final XSSFDataValidationHelper dataValidationHelper; private XSSFVMLDrawing xssfvmlDrawing; + private CellRangeAddress dimensionOverride; /** * Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch. @@ -3747,29 +3748,34 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx }*/ } - int minCell = Integer.MAX_VALUE, maxCell = Integer.MIN_VALUE; - for(Map.Entry entry : _rows.entrySet()) { - XSSFRow row = entry.getValue(); + CellRangeAddress cellRangeAddress = dimensionOverride; + if (cellRangeAddress == null) { + int minCell = Integer.MAX_VALUE, maxCell = Integer.MIN_VALUE; + for(Map.Entry entry : _rows.entrySet()) { + XSSFRow row = entry.getValue(); - // first perform the normal write actions for the row - row.onDocumentWrite(); + // first perform the normal write actions for the row + row.onDocumentWrite(); - // then calculate min/max cell-numbers for the worksheet-dimension - if(row.getFirstCellNum() != -1) { - minCell = Math.min(minCell, row.getFirstCellNum()); + // then calculate min/max cell-numbers for the worksheet-dimension + if(row.getFirstCellNum() != -1) { + minCell = Math.min(minCell, row.getFirstCellNum()); + } + if(row.getLastCellNum() != -1) { + maxCell = Math.max(maxCell, row.getLastCellNum()-1); + } } - if(row.getLastCellNum() != -1) { - maxCell = Math.max(maxCell, row.getLastCellNum()-1); + + // finally, if we had at least one cell we can populate the optional dimension-field + if(minCell != Integer.MAX_VALUE) { + cellRangeAddress = new CellRangeAddress(getFirstRowNum(), getLastRowNum(), minCell, maxCell); } } - - // finally, if we had at least one cell we can populate the optional dimension-field - if(minCell != Integer.MAX_VALUE) { - String ref = new CellRangeAddress(getFirstRowNum(), getLastRowNum(), minCell, maxCell).formatAsString(); - if(worksheet.isSetDimension()) { - worksheet.getDimension().setRef(ref); + if (cellRangeAddress != null) { + if (worksheet.isSetDimension()) { + worksheet.getDimension().setRef(cellRangeAddress.formatAsString()); } else { - worksheet.addNewDimension().setRef(ref); + worksheet.addNewDimension().setRef(cellRangeAddress.formatAsString()); } } @@ -4051,6 +4057,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx * @since POI 5.2.3 */ public CellRangeAddress getDimension() { + if (dimensionOverride != null) { + return dimensionOverride; + } CTSheetDimension ctSheetDimension = worksheet.getDimension(); String ref = ctSheetDimension == null ? null : ctSheetDimension.getRef(); if (ref != null) { @@ -4845,4 +4854,14 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx public XSSFHeaderFooterProperties getHeaderFooterProperties() { return new XSSFHeaderFooterProperties(getSheetTypeHeaderFooter()); } + + /** + * Currently, this is for internal use. Overrides the default dimensions of the sheet. + * @param dimension {@link CellRangeAddress}, null removes the existing override + * @since POI 5.2.3 + */ + @Beta + public void setDimensionOverride(CellRangeAddress dimension) { + this.dimensionOverride = dimension; + } } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java index 37b1740623..16449a9f8c 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java @@ -48,6 +48,7 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.SXSSFITestDataProvider; import org.apache.poi.xssf.XSSFTestDataSamples; @@ -560,6 +561,45 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { } } + @Test + void addDimension() throws IOException { + try ( + SXSSFWorkbook wb = new SXSSFWorkbook(); + UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream() + ) { + SXSSFSheet sheet = wb.createSheet(); + sheet.createRow(2).createCell(3).setCellValue("top left"); + sheet.createRow(6).createCell(5).setCellValue("bottom right"); + assertEquals(2, sheet.getFirstRowNum()); + assertEquals(6, sheet.getLastRowNum()); + wb.write(bos); + try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bos.toInputStream())) { + XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); + assertEquals(CellRangeAddress.valueOf("D3:F7"), xssfSheet.getDimension()); + } + } + } + + @Test + void addDimensionDisabled() throws IOException { + try ( + SXSSFWorkbook wb = new SXSSFWorkbook(); + UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream() + ) { + wb.setShouldCalculateSheetDimensions(false); + SXSSFSheet sheet = wb.createSheet(); + sheet.createRow(2).createCell(3).setCellValue("top left"); + sheet.createRow(6).createCell(5).setCellValue("bottom right"); + assertEquals(2, sheet.getFirstRowNum()); + assertEquals(6, sheet.getLastRowNum()); + wb.write(bos); + try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bos.toInputStream())) { + XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); + assertEquals(CellRangeAddress.valueOf("A1:A1"), xssfSheet.getDimension()); + } + } + } + @Override @Disabled("not implemented") protected void changeSheetNameWithSharedFormulas() { -- 2.39.5