/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.xssf.streaming;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import static org.junit.Assume.assumeFalse;
import static org.junit.Assume.assumeTrue;
import java.io.IOException;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.SortedSet;
import java.util.TreeSet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
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.util.CellRangeAddress;
import org.apache.poi.ss.util.SheetUtil;
import org.junit.After;
import org.junit.Assume;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
import org.junit.runners.Parameterized.Parameter;
import org.junit.runners.Parameterized.Parameters;
/**
* Tests the auto-sizing behaviour of {@link SXSSFSheet} when not all
* rows fit into the memory window size etc.
*
* @see Bug #57450 which reported the original mis-behaviour
*/
@RunWith(Parameterized.class)
public class TestSXSSFSheetAutoSizeColumn {
private static final String SHORT_CELL_VALUE = "Ben";
private static final String LONG_CELL_VALUE = "B Be Ben Beni Benif Benify Benif Beni Ben Be B";
// Approximative threshold to decide whether test is PASS or FAIL:
// shortCellValue ends up with approx column width 1_000 (on my machine),
// longCellValue ends up with approx. column width 10_000 (on my machine)
// so shortCellValue can be expected to be < 5000 for all fonts
// and longCellValue can be expected to be > 5000 for all fonts
private static final int COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG = 4000;
private static final int MAX_COLUMN_WIDTH = 255*256;
private static final SortedSet<Integer> columns;
static {
SortedSet<Integer>_columns = new TreeSet<Integer>();
_columns.add(0);
_columns.add(1);
_columns.add(3);
columns = Collections.unmodifiableSortedSet(_columns);
}
private SXSSFSheet sheet;
private SXSSFWorkbook workbook;
@Parameter(0)
public boolean useMergedCells;
@Parameters(name="{index}: useMergedCells={0}")
public static Collection<Object[]> data() {
return Arrays.asList(new Object[][] {
{false},
{true},
});
}
@After
public void tearDownSheetAndWorkbook() throws IOException {
if (sheet != null) {
sheet.dispose();
}
if (workbook != null) {
workbook.close();
}
}
@Test
public void test_EmptySheet_NoException() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
for (int i = 0; i < 10; i++) {
sheet.autoSizeColumn(i, useMergedCells);
}
}
@Test
public void test_WindowSizeDefault_AllRowsFitIntoWindowSize() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
sheet.autoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
@Test
public void test_WindowSizeEqualsOne_ConsiderFlushedRows() {
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
sheet.autoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
@Test
public void test_WindowSizeEqualsOne_lastRowIsNotWidest() {
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
sheet.autoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
@Test
public void test_WindowSizeEqualsOne_lastRowIsWidest() {
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
final Cell cellRow0 = createRowWithCellValues(sheet, 0, SHORT_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
createRowWithCellValues(sheet, 1, LONG_CELL_VALUE);
sheet.autoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
// fails only for useMergedCell=true
@Test
public void test_WindowSizeEqualsOne_flushedRowHasMergedCell() {
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
Cell a1 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, a1);
sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE, SHORT_CELL_VALUE);
/**
* A B
* 1 LONGMERGED
* 2 SHORT SHORT
*/
sheet.autoSizeColumn(0, useMergedCells);
sheet.autoSizeColumn(1, useMergedCells);
if (useMergedCells) {
// Excel and LibreOffice behavior: ignore merged cells for auto-sizing.
// POI behavior: evenly distribute the column width among the merged columns.
// each column must be auto-sized in order for the column widths
// to add up to the best fit width.
final int colspan = 2;
final int expectedWidth = (10000 + 1000)/colspan; //average of 1_000 and 10_000
final int minExpectedWidth = expectedWidth / 2;
final int maxExpectedWidth = expectedWidth * 3 / 2;
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), minExpectedWidth, maxExpectedWidth); //short
} else {
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); //long
}
assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(1), 0, COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG); //short
}
@Test
public void autoSizeColumn_trackColumnForAutoSizing() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnForAutoSizing(0);
SortedSet<Integer> expected = new TreeSet<Integer>();
expected.add(0);
assertEquals(expected, sheet.getTrackedColumnsForAutoSizing());
sheet.autoSizeColumn(0, useMergedCells);
try {
sheet.autoSizeColumn(1, useMergedCells);
fail("Should not be able to auto-size an untracked column");
}
catch (final IllegalStateException e) {
// expected
}
}
@Test
public void autoSizeColumn_trackColumnsForAutoSizing() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnsForAutoSizing(columns);
SortedSet<Integer> sorted = new TreeSet<Integer>(columns);
assertEquals(sorted, sheet.getTrackedColumnsForAutoSizing());
sheet.autoSizeColumn(sorted.first(), useMergedCells);
try {
assumeFalse(columns.contains(5));
sheet.autoSizeColumn(5, useMergedCells);
fail("Should not be able to auto-size an untracked column");
}
catch (final IllegalStateException e) {
// expected
}
}
@Test
public void autoSizeColumn_untrackColumnForAutoSizing() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnsForAutoSizing(columns);
sheet.untrackColumnForAutoSizing(columns.first());
assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(columns.last()));
sheet.autoSizeColumn(columns.last(), useMergedCells);
try {
assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));
sheet.autoSizeColumn(columns.first(), useMergedCells);
fail("Should not be able to auto-size an untracked column");
}
catch (final IllegalStateException e) {
// expected
}
}
@Test
public void autoSizeColumn_untrackColumnsForAutoSizing() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnForAutoSizing(15);
sheet.trackColumnsForAutoSizing(columns);
sheet.untrackColumnsForAutoSizing(columns);
assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(15));
sheet.autoSizeColumn(15, useMergedCells);
try {
assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));
sheet.autoSizeColumn(columns.first(), useMergedCells);
fail("Should not be able to auto-size an untracked column");
}
catch (final IllegalStateException e) {
// expected
}
}
@Test
public void autoSizeColumn_isColumnTrackedForAutoSizing() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnsForAutoSizing(columns);
for (int column : columns) {
assertTrue(sheet.isColumnTrackedForAutoSizing(column));
assumeFalse(columns.contains(column+10));
assertFalse(sheet.isColumnTrackedForAutoSizing(column+10));
}
}
@Test
public void autoSizeColumn_trackAllColumns() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackAllColumnsForAutoSizing();
sheet.autoSizeColumn(0, useMergedCells);
sheet.untrackAllColumnsForAutoSizing();
try {
sheet.autoSizeColumn(0, useMergedCells);
fail("Should not be able to auto-size an implicitly untracked column");
} catch (final IllegalStateException e) {
// expected
}
}
@Test
public void autoSizeColumn_trackAllColumns_explicitUntrackColumn() {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
sheet.trackColumnsForAutoSizing(columns);
sheet.trackAllColumnsForAutoSizing();
sheet.untrackColumnForAutoSizing(0);
try {
sheet.autoSizeColumn(0, useMergedCells);
fail("Should not be able to auto-size an explicitly untracked column");
} catch (final IllegalStateException e) {
// expected
}
}
private static void assumeRequiredFontsAreInstalled(final Workbook workbook, final Cell cell) {
// autoSize will fail if required fonts are not installed, skip this test then
Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());
Assume.assumeTrue("Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + font,
SheetUtil.canComputeColumnWidth(font));
}
private static Cell createRowWithCellValues(final Sheet sheet, final int rowNumber, final String... cellValues) {
Row row = sheet.createRow(rowNumber);
int cellIndex = 0;
Cell firstCell = null;
for (final String cellValue : cellValues) {
Cell cell = row.createCell(cellIndex++);
if (firstCell == null) {
firstCell = cell;
}
cell.setCellValue(cellValue);
}
return firstCell;
}
private static void assertColumnWidthStrictlyWithinRange(final int actualColumnWidth, final int lowerBoundExclusive, final int upperBoundExclusive) {
assertTrue("Expected a column width greater than " + lowerBoundExclusive + " but found " + actualColumnWidth,
actualColumnWidth > lowerBoundExclusive);
assertTrue("Expected column width less than " + upperBoundExclusive + " but found " + actualColumnWidth, actualColumnWidth < upperBoundExclusive);
}
}