src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheetAutoSizeColumn.java - poi - Git at Google

 /* ====================================================================
    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.jupiter.api.Assertions.assertDoesNotThrow;
 import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertFalse;
 import static org.junit.jupiter.api.Assertions.assertThrows;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 import static org.junit.jupiter.api.Assumptions.assumeFalse;
 import static org.junit.jupiter.api.Assumptions.assumeTrue;

 import java.io.IOException;
 import java.util.Collections;
 import java.util.SortedSet;
 import java.util.TreeSet;
 import java.util.stream.Stream;

 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.jupiter.api.AfterEach;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.params.ParameterizedTest;
 import org.junit.jupiter.params.provider.Arguments;
 import org.junit.jupiter.params.provider.MethodSource;


 /**
  * 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 misbehaviour
  */
 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";

     // Approximate 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<>();
         _columns.add(0);
         _columns.add(1);
         _columns.add(3);
         columns = Collections.unmodifiableSortedSet(_columns);
     }


     private SXSSFSheet sheet;
     private SXSSFWorkbook workbook;

     public static Stream<Arguments> data() {
         return Stream.of(Arguments.of(false), Arguments.of(true));
     }

     @AfterEach
     void tearDownSheetAndWorkbook() throws IOException {
         if (sheet != null) {
             sheet.dispose();
         }
         if (workbook != null) {
             workbook.close();
         }
     }

     @ParameterizedTest
     @MethodSource("data")
     void test_EmptySheet_NoException(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();
         sheet.trackAllColumnsForAutoSizing();

         for (int i = 0; i < 10; i++) {
             int i2 = i;
             assertDoesNotThrow(() -> sheet.autoSizeColumn(i2, useMergedCells));
         }
     }

     @ParameterizedTest
     @MethodSource("data")
     void test_WindowSizeDefault_AllRowsFitIntoWindowSize(boolean useMergedCells) {
         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);
     }

     @ParameterizedTest
     @MethodSource("data")
     void test_WindowSizeEqualsOne_ConsiderFlushedRows(boolean useMergedCells) {
         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);
     }

     @ParameterizedTest
     @MethodSource("data")
     void test_WindowSizeEqualsOne_lastRowIsNotWidest(boolean useMergedCells) {
         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);
     }

     @ParameterizedTest
     @MethodSource("data")
     void test_WindowSizeEqualsOne_lastRowIsWidest(boolean useMergedCells) {
         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
     @ParameterizedTest
     @MethodSource("data")
     void test_WindowSizeEqualsOne_flushedRowHasMergedCell(boolean useMergedCells) {
         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);
         assertEquals(0, 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
     }

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_trackColumnForAutoSizing(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();
         sheet.trackColumnForAutoSizing(0);

         SortedSet<Integer> expected = new TreeSet<>();
         expected.add(0);
         assertEquals(expected, sheet.getTrackedColumnsForAutoSizing());

         sheet.autoSizeColumn(0, useMergedCells);
         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(1, useMergedCells),
             "Should not be able to auto-size an untracked column");
     }

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_trackColumnsForAutoSizing(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();

         sheet.trackColumnsForAutoSizing(columns);
         SortedSet<Integer> sorted = new TreeSet<>(columns);
         assertEquals(sorted, sheet.getTrackedColumnsForAutoSizing());

         sheet.autoSizeColumn(sorted.first(), useMergedCells);
         assumeFalse(columns.contains(5));

         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(5, useMergedCells),
             "Should not be able to auto-size an untracked column");
     }

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_untrackColumnForAutoSizing(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();

         sheet.trackColumnsForAutoSizing(columns);
         sheet.untrackColumnForAutoSizing(columns.first());

         assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(columns.last()));
         sheet.autoSizeColumn(columns.last(), useMergedCells);
         assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));

         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(columns.first(), useMergedCells),
             "Should not be able to auto-size an untracked column");
     }

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_untrackColumnsForAutoSizing(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();

         sheet.trackColumnForAutoSizing(15);
         sheet.trackColumnsForAutoSizing(columns);
         sheet.untrackColumnsForAutoSizing(columns);

         assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(15));
         sheet.autoSizeColumn(15, useMergedCells);
         assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));

         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(columns.first(), useMergedCells),
             "Should not be able to auto-size an untracked column");
     }

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

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_trackAllColumns(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();

         sheet.trackAllColumnsForAutoSizing();
         sheet.autoSizeColumn(0, useMergedCells);

         sheet.untrackAllColumnsForAutoSizing();
         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0, useMergedCells),
             "Should not be able to auto-size an implicitly untracked column");
     }

     @ParameterizedTest
     @MethodSource("data")
     void autoSizeColumn_trackAllColumns_explicitUntrackColumn(boolean useMergedCells) {
         workbook = new SXSSFWorkbook();
         sheet = workbook.createSheet();

         sheet.trackColumnsForAutoSizing(columns);
         sheet.trackAllColumnsForAutoSizing();

         boolean untracked = sheet.untrackColumnForAutoSizing(0);
         assertTrue(untracked);
         assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0, useMergedCells),
             "Should not be able to auto-size an explicitly untracked column");
     }


     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());
         assumeTrue(SheetUtil.canComputeColumnWidth(font),
             "Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + 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(actualColumnWidth > lowerBoundExclusive,
             "Expected a column width greater than " + lowerBoundExclusive + " but found " + actualColumnWidth);
         assertTrue(actualColumnWidth < upperBoundExclusive,
             "Expected column width less than " + upperBoundExclusive + " but found " + actualColumnWidth);
     }

 }
	/* ====================================================================
	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.jupiter.api.Assertions.assertDoesNotThrow;
	import static org.junit.jupiter.api.Assertions.assertEquals;
	import static org.junit.jupiter.api.Assertions.assertFalse;
	import static org.junit.jupiter.api.Assertions.assertThrows;
	import static org.junit.jupiter.api.Assertions.assertTrue;
	import static org.junit.jupiter.api.Assumptions.assumeFalse;
	import static org.junit.jupiter.api.Assumptions.assumeTrue;

	import java.io.IOException;
	import java.util.Collections;
	import java.util.SortedSet;
	import java.util.TreeSet;
	import java.util.stream.Stream;

	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.jupiter.api.AfterEach;
	import org.junit.jupiter.api.Test;
	import org.junit.jupiter.params.ParameterizedTest;
	import org.junit.jupiter.params.provider.Arguments;
	import org.junit.jupiter.params.provider.MethodSource;


	/**
	* 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 misbehaviour
	*/
	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";

	// Approximate 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<>();
	_columns.add(0);
	_columns.add(1);
	_columns.add(3);
	columns = Collections.unmodifiableSortedSet(_columns);
	}


	private SXSSFSheet sheet;
	private SXSSFWorkbook workbook;

	public static Stream<Arguments> data() {
	return Stream.of(Arguments.of(false), Arguments.of(true));
	}

	@AfterEach
	void tearDownSheetAndWorkbook() throws IOException {
	if (sheet != null) {
	sheet.dispose();
	}
	if (workbook != null) {
	workbook.close();
	}
	}

	@ParameterizedTest
	@MethodSource("data")
	void test_EmptySheet_NoException(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();
	sheet.trackAllColumnsForAutoSizing();

	for (int i = 0; i < 10; i++) {
	int i2 = i;
	assertDoesNotThrow(() -> sheet.autoSizeColumn(i2, useMergedCells));
	}
	}

	@ParameterizedTest
	@MethodSource("data")
	void test_WindowSizeDefault_AllRowsFitIntoWindowSize(boolean useMergedCells) {
	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);
	}

	@ParameterizedTest
	@MethodSource("data")
	void test_WindowSizeEqualsOne_ConsiderFlushedRows(boolean useMergedCells) {
	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);
	}

	@ParameterizedTest
	@MethodSource("data")
	void test_WindowSizeEqualsOne_lastRowIsNotWidest(boolean useMergedCells) {
	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);
	}

	@ParameterizedTest
	@MethodSource("data")
	void test_WindowSizeEqualsOne_lastRowIsWidest(boolean useMergedCells) {
	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
	@ParameterizedTest
	@MethodSource("data")
	void test_WindowSizeEqualsOne_flushedRowHasMergedCell(boolean useMergedCells) {
	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);
	assertEquals(0, 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
	}

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_trackColumnForAutoSizing(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();
	sheet.trackColumnForAutoSizing(0);

	SortedSet<Integer> expected = new TreeSet<>();
	expected.add(0);
	assertEquals(expected, sheet.getTrackedColumnsForAutoSizing());

	sheet.autoSizeColumn(0, useMergedCells);
	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(1, useMergedCells),
	"Should not be able to auto-size an untracked column");
	}

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_trackColumnsForAutoSizing(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();

	sheet.trackColumnsForAutoSizing(columns);
	SortedSet<Integer> sorted = new TreeSet<>(columns);
	assertEquals(sorted, sheet.getTrackedColumnsForAutoSizing());

	sheet.autoSizeColumn(sorted.first(), useMergedCells);
	assumeFalse(columns.contains(5));

	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(5, useMergedCells),
	"Should not be able to auto-size an untracked column");
	}

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_untrackColumnForAutoSizing(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();

	sheet.trackColumnsForAutoSizing(columns);
	sheet.untrackColumnForAutoSizing(columns.first());

	assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(columns.last()));
	sheet.autoSizeColumn(columns.last(), useMergedCells);
	assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));

	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(columns.first(), useMergedCells),
	"Should not be able to auto-size an untracked column");
	}

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_untrackColumnsForAutoSizing(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();

	sheet.trackColumnForAutoSizing(15);
	sheet.trackColumnsForAutoSizing(columns);
	sheet.untrackColumnsForAutoSizing(columns);

	assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(15));
	sheet.autoSizeColumn(15, useMergedCells);
	assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));

	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(columns.first(), useMergedCells),
	"Should not be able to auto-size an untracked column");
	}

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

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_trackAllColumns(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();

	sheet.trackAllColumnsForAutoSizing();
	sheet.autoSizeColumn(0, useMergedCells);

	sheet.untrackAllColumnsForAutoSizing();
	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0, useMergedCells),
	"Should not be able to auto-size an implicitly untracked column");
	}

	@ParameterizedTest
	@MethodSource("data")
	void autoSizeColumn_trackAllColumns_explicitUntrackColumn(boolean useMergedCells) {
	workbook = new SXSSFWorkbook();
	sheet = workbook.createSheet();

	sheet.trackColumnsForAutoSizing(columns);
	sheet.trackAllColumnsForAutoSizing();

	boolean untracked = sheet.untrackColumnForAutoSizing(0);
	assertTrue(untracked);
	assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0, useMergedCells),
	"Should not be able to auto-size an explicitly untracked column");
	}


	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());
	assumeTrue(SheetUtil.canComputeColumnWidth(font),
	"Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + 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(actualColumnWidth > lowerBoundExclusive,
	"Expected a column width greater than " + lowerBoundExclusive + " but found " + actualColumnWidth);
	assertTrue(actualColumnWidth < upperBoundExclusive,
	"Expected column width less than " + upperBoundExclusive + " but found " + actualColumnWidth);
	}

	}