You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2020/03/08 11:17:34 UTC
svn commit: r1874973 - in /poi/trunk:
src/java/org/apache/poi/ss/util/SheetUtil.java
src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
test-data/spreadsheet/58896.xlsx
Author: centic
Date: Sun Mar 8 11:17:34 2020
New Revision: 1874973
URL: http://svn.apache.org/viewvc?rev=1874973&view=rev
Log:
Bug 58896 and 52834: Cache Sheet.getMergedRegions() as it seems to sometimes be the culprit for autosize taking a very long time.
Added:
poi/trunk/test-data/spreadsheet/58896.xlsx
Modified:
poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
Modified: poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java?rev=1874973&r1=1874972&r2=1874973&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java Sun Mar 8 11:17:34 2020
@@ -23,6 +23,7 @@ import java.awt.font.TextLayout;
import java.awt.geom.AffineTransform;
import java.awt.geom.Rectangle2D;
import java.text.AttributedString;
+import java.util.List;
import java.util.Locale;
import java.util.Map;
@@ -118,6 +119,26 @@ public class SheetUtil {
* @return the width in pixels or -1 if cell is empty
*/
public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
+ List<CellRangeAddress> mergedRegions = cell.getSheet().getMergedRegions();
+ return getCellWidth(cell, defaultCharWidth, formatter, useMergedCells, mergedRegions);
+ }
+
+ /**
+ * Compute width of a single cell
+ *
+ * This method receives the list of merged regions as querying it from the cell/sheet
+ * is time-consuming and thus caching the list across cells speeds up certain operations
+ * considerably.
+ *
+ * @param cell the cell whose width is to be calculated
+ * @param defaultCharWidth the width of a single character
+ * @param formatter formatter used to prepare the text to be measured
+ * @param useMergedCells whether to use merged cells
+ * @param mergedRegions The list of merged regions as received via cell.getSheet().getMergedRegions()
+ * @return the width in pixels or -1 if cell is empty
+ */
+ public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells,
+ List<CellRangeAddress> mergedRegions) {
Sheet sheet = cell.getSheet();
Workbook wb = sheet.getWorkbook();
Row row = cell.getRow();
@@ -126,7 +147,7 @@ public class SheetUtil {
// FIXME: this looks very similar to getCellWithMerges below. Consider consolidating.
// We should only be checking merged regions if useMergedCells is true. Why are we doing this for-loop?
int colspan = 1;
- for (CellRangeAddress region : sheet.getMergedRegions()) {
+ for (CellRangeAddress region : mergedRegions) {
if (region.isInRange(row.getRowNum(), column)) {
if (!useMergedCells) {
// If we're not using merged cells, skip this one and move on to the next.
@@ -232,7 +253,7 @@ public class SheetUtil {
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells) {
return getColumnWidth(sheet, column, useMergedCells, sheet.getFirstRowNum(), sheet.getLastRowNum());
}
-
+
/**
* Compute width of a column based on a subset of the rows and return the result
*
@@ -247,11 +268,12 @@ public class SheetUtil {
DataFormatter formatter = new DataFormatter();
int defaultCharWidth = getDefaultCharWidth(sheet.getWorkbook());
+ List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
double width = -1;
for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
Row row = sheet.getRow(rowIdx);
if( row != null ) {
- double cellWidth = getColumnWidthForRow(row, column, defaultCharWidth, formatter, useMergedCells);
+ double cellWidth = getColumnWidthForRow(row, column, defaultCharWidth, formatter, useMergedCells, mergedRegions);
width = Math.max(width, cellWidth);
}
}
@@ -286,7 +308,8 @@ public class SheetUtil {
* @return the width in pixels or -1 if cell is empty
*/
private static double getColumnWidthForRow(
- Row row, int column, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
+ Row row, int column, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells,
+ List<CellRangeAddress> mergedRegions) {
if( row == null ) {
return -1;
}
@@ -297,16 +320,16 @@ public class SheetUtil {
return -1;
}
- return getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
+ return getCellWidth(cell, defaultCharWidth, formatter, useMergedCells, mergedRegions);
}
/**
* Check if the Fonts are installed correctly so that Java can compute the size of
- * columns.
- *
- * If a Cell uses a Font which is not available on the operating system then Java may
+ * columns.
+ *
+ * If a Cell uses a Font which is not available on the operating system then Java may
* fail to return useful Font metrics and thus lead to an auto-computed size of 0.
- *
+ *
* This method allows to check if computing the sizes for a given Font will succeed or not.
*
* @param font The Font that is used in the Cell
@@ -358,7 +381,7 @@ public class SheetUtil {
/**
* Return the cell, taking account of merged regions. Allows you to find the
* cell who's contents are shown in a given position in the sheet.
- *
+ *
* <p>If the cell at the given co-ordinates is a merged cell, this will
* return the primary (top-left) most cell of the merged region.
* <p>If the cell at the given co-ordinates is not in a merged region,
@@ -375,7 +398,7 @@ public class SheetUtil {
public static Cell getCellWithMerges(Sheet sheet, int rowIx, int colIx) {
final Cell c = getCell(sheet, rowIx, colIx);
if (c != null) return c;
-
+
for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
if (mergedRegion.isInRange(rowIx, colIx)) {
// The cell wanted is in this merged range
@@ -386,7 +409,7 @@ public class SheetUtil {
}
}
}
-
+
// If we get here, then the cell isn't defined, and doesn't
// live within any merged regions
return null;
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java?rev=1874973&r1=1874972&r2=1874973&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java Sun Mar 8 11:17:34 2020
@@ -36,6 +36,8 @@ import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
+import java.time.Duration;
+import java.time.Instant;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
@@ -91,6 +93,8 @@ import org.apache.poi.ss.util.CellRefere
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.NullOutputStream;
+import org.apache.poi.util.POILogFactory;
+import org.apache.poi.util.POILogger;
import org.apache.poi.util.TempFile;
import org.apache.poi.util.XMLHelper;
import org.apache.poi.xssf.SXSSFITestDataProvider;
@@ -116,6 +120,8 @@ import org.xml.sax.SAXParseException;
import org.xml.sax.XMLReader;
public final class TestXSSFBugs extends BaseTestBugzillaIssues {
+ private static POILogger LOG = POILogFactory.getLogger(TestXSSFBugs.class);
+
public TestXSSFBugs() {
super(XSSFITestDataProvider.instance);
}
@@ -3465,4 +3471,27 @@ public final class TestXSSFBugs extends
fail("Should catch exception as the file is corrupted");
}
}
+
+ @Test
+ public void test58896WithFile() throws IOException {
+ try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58896.xlsx")) {
+ Sheet sheet = wb.getSheetAt(0);
+ Instant start = Instant.now();
+
+ LOG.log(POILogger.INFO, "Autosizing columns...");
+
+ for (int i = 0; i < 3; ++i) {
+ LOG.log(POILogger.INFO, "Autosize " + i + " - " + Duration.between(start, Instant.now()));
+ sheet.autoSizeColumn(i);
+ }
+
+ for (int i = 0; i < 69 - 35 + 1; ++i)
+ for (int j = 0; j < 8; ++j) {
+ int col = 3 + 2 + i * (8 + 2) + j;
+ LOG.log(POILogger.INFO, "Autosize " + col + " - " + Duration.between(start, Instant.now()));
+ sheet.autoSizeColumn(col);
+ }
+ LOG.log(POILogger.INFO, Duration.between(start, Instant.now()));
+ }
+ }
}
Added: poi/trunk/test-data/spreadsheet/58896.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/58896.xlsx?rev=1874973&view=auto
==============================================================================
Binary files poi/trunk/test-data/spreadsheet/58896.xlsx (added) and poi/trunk/test-data/spreadsheet/58896.xlsx Sun Mar 8 11:17:34 2020 differ
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org