main/src/test/java/org/apache/poi/ss/usermodel/BaseTestSheetAutosizeColumn.java

/* ====================================================================
   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.ss.usermodel;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.LocaleUtil;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

/**
 * Common superclass for testing automatic sizing of sheet columns
 *
 * @author Yegor Kozlov
 */
public abstract class BaseTestSheetAutosizeColumn {

    private final ITestDataProvider _testDataProvider;

    private static Locale userLocale;

    @BeforeAll
    public static void initLocale() {
        userLocale = LocaleUtil.getUserLocale();
        LocaleUtil.setUserLocale(Locale.ROOT);
    }

    @AfterAll
    public static void resetLocale() {
        LocaleUtil.setUserLocale(userLocale);
    }

    protected BaseTestSheetAutosizeColumn(ITestDataProvider testDataProvider) {
        _testDataProvider = testDataProvider;
    }

    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
        // do nothing for Sheet base class. This will be overridden for SXSSFSheets.
    }

    @Test
    void numericCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        DataFormat df = workbook.getCreationHelper().createDataFormat();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
        row.createCell(1).setCellValue(10);
        row.createCell(2).setCellValue("10");
        row.createCell(3).setCellFormula("(A1+B1)*1.0"); // a formula that returns '10'

        Cell cell4 = row.createCell(4);       // numeric cell with a custom style
        CellStyle style4 = workbook.createCellStyle();
        style4.setDataFormat(df.getFormat("0.0000"));
        cell4.setCellStyle(style4);
        cell4.setCellValue(10); // formatted as '10.0000'

        row.createCell(5).setCellValue("10.0000");

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(0) < sheet.getColumnWidth(1));  // width of '0' is less then width of '10'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
        assertEquals(sheet.getColumnWidth(3), sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated  for '0'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(0) < sheet.getColumnWidth(1));  // width of '0' is less then width of '10'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'

        workbook.close();
    }

    @Test
    void booleanCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
        row.createCell(1).setCellValue(true);
        row.createCell(2).setCellValue("TRUE");
        row.createCell(3).setCellFormula("1 > 0"); // a formula that returns true

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0));  // 'true' is wider than '0'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2));  // 10 and '10' should be sized equally
        assertEquals(sheet.getColumnWidth(3), sheet.getColumnWidth(0));  // formula result is unknown, the width is calculated  for '0'

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0));  // 'true' is wider than '0'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2));  // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(3));  // columns 1, 2 and 3 should have the same width

        workbook.close();
    }

    @Test
    void dateCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        DataFormat df = workbook.getCreationHelper().createDataFormat();

        CellStyle style1 = workbook.createCellStyle();
        style1.setDataFormat(df.getFormat("m"));

        CellStyle style3 = workbook.createCellStyle();
        style3.setDataFormat(df.getFormat("mmm"));

        CellStyle style5 = workbook.createCellStyle(); //rotated text
        style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));

        Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(DateUtil.getJavaDate(0));   //default date

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(calendar);
        cell1.setCellStyle(style1);
        row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'

        Cell cell3 = row.createCell(3);
        cell3.setCellValue(calendar);
        cell3.setCellStyle(style3);
        row.createCell(4).setCellValue("Jan");

        Cell cell5 = row.createCell(5);
        cell5.setCellValue(calendar);
        cell5.setCellStyle(style5);
        row.createCell(6).setCellValue("Jan/01/2010");

        Cell cell7 = row.createCell(7);
        cell7.setCellFormula("DATE(2010,1,1)");
        cell7.setCellStyle(style3); // should be sized as 'Jan'

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
        assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1));  // 'mmm' is wider than 'm'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
        assertTrue(sheet.getColumnWidth(5) > sheet.getColumnWidth(3));  // 'mmm/dd/yyyy' is wider than 'mmm'
        assertEquals(sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'

        // YK: width of not-evaluated formulas that return data is not determined
        // POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
        assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1));  // 'mmm' is wider than 'm'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
        assertTrue(sheet.getColumnWidth(5) > sheet.getColumnWidth(3));  // 'mmm/dd/yyyy' is wider than 'mmm'
        assertEquals(sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'

        workbook.close();
    }

    @Test
    void stringCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        Row row = sheet.createRow(0);

        Font defaultFont = workbook.getFontAt(0);

        CellStyle style1 = workbook.createCellStyle();
        Font font1 = workbook.createFont();
        font1.setFontHeight((short)(2*defaultFont.getFontHeight()));
        style1.setFont(font1);

        row.createCell(0).setCellValue("x");
        row.createCell(1).setCellValue("xxxx");
        row.createCell(2).setCellValue("xxxxxxxxxxxx");
        row.createCell(3).setCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines
        row.createCell(4).setCellValue("Software Foundation");

        Cell cell5 = row.createCell(5);
        cell5.setCellValue("Software Foundation");
        cell5.setCellStyle(style1); // same as in column 4 but the font is twice larger than the default font

        for (int i = 0; i < 10; i++) sheet.autoSizeColumn(i);

        assertTrue(2*sheet.getColumnWidth(0) < sheet.getColumnWidth(1)); // width is roughly proportional to the number of characters
        assertTrue(2*sheet.getColumnWidth(1) < sheet.getColumnWidth(2));
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3));
        assertTrue(sheet.getColumnWidth(5) > sheet.getColumnWidth(4)); //larger font results in a wider column width

        workbook.close();
    }

    @Test
    void rotatedText() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        Row row = sheet.createRow(0);

        CellStyle style1 = workbook.createCellStyle();
        style1.setRotation((short)90);

        Cell cell0 = row.createCell(0);
        cell0.setCellValue("Apache Software Foundation");
        cell0.setCellStyle(style1);

        Cell cell1 = row.createCell(1);
        cell1.setCellValue("Apache Software Foundation");

        for (int i = 0; i < 2; i++) sheet.autoSizeColumn(i);

        int w0 = sheet.getColumnWidth(0);
        int w1 = sheet.getColumnWidth(1);

        assertTrue(w0*5 < w1); // rotated text occupies at least five times less horizontal space than normal text

        workbook.close();
    }

    @Test
    void mergedCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        assertEquals(0, sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1")));

        Cell cell0 = row.createCell(0);
        cell0.setCellValue("Apache Software Foundation");

        int defaulWidth = sheet.getColumnWidth(0);
        sheet.autoSizeColumn(0);
        // column is unchanged if merged regions are ignored (Excel like behavior)
        assertEquals(defaulWidth, sheet.getColumnWidth(0));

        sheet.autoSizeColumn(0, true);
        assertTrue(sheet.getColumnWidth(0) > defaulWidth);

        workbook.close();
    }

    /**
     * Auto-Sizing a column needs to work when we have rows
     *  passed the 32767 boundary. See bug #48079
     */
    @Test
    void largeRowNumbers() throws Exception {
       try (Workbook workbook = _testDataProvider.createWorkbook()) {
           boolean isHssf = workbook instanceof HSSFWorkbook;
           Sheet sheet = workbook.createSheet();
           trackColumnsForAutoSizingIfSXSSF(sheet);

           Row r0 = sheet.createRow(0);
           r0.createCell(0).setCellValue("I am ROW 0");
           Row r200 = sheet.createRow(200);
           r200.createCell(0).setCellValue("I am ROW 200");

           // This should work fine
           sheet.autoSizeColumn(0);
           int colWidth1 = sheet.getColumnWidth(0);

           // Get close to 32767
           Row r32765 = sheet.createRow(32765);
           r32765.createCell(0).setCellValue("Nearly there...");
           sheet.autoSizeColumn(0);
           int colWidth2 = sheet.getColumnWidth(0);
           assertTrue(colWidth1 <= colWidth2);
           colWidth1 = colWidth2;

           // To it
           Row r32767 = sheet.createRow(32767);
           r32767.createCell(0).setCellValue("At the boundary");
           sheet.autoSizeColumn(0);
           colWidth2 = sheet.getColumnWidth(0);
           assertTrue(colWidth1 <= colWidth2);
           colWidth1 = colWidth2;

           // And passed it
           Row r32768 = sheet.createRow(32768);
           r32768.createCell(0).setCellValue("Passed");
           Row r32769 = sheet.createRow(32769);
           r32769.createCell(0).setCellValue("More Passed");
           sheet.autoSizeColumn(0);
           colWidth2 = sheet.getColumnWidth(0);
           assertTrue(colWidth1 <= colWidth2);
           colWidth1 = colWidth2;

           // Long way passed
           Row r60708 = sheet.createRow(60708);
           r60708.createCell(0).setCellValue("Near the end");
           sheet.autoSizeColumn(0);
           colWidth2 = sheet.getColumnWidth(0);
           assertTrue(colWidth1 <= colWidth2);
       }
    }

    // TODO should we have this stuff in the FormulaEvaluator?
    private void evaluateWorkbook(Workbook workbook){
        FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
        for(int i=0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA){
                        eval.evaluateFormulaCell(c);
                    }
                }
            }
        }
    }


    @Test
    void testExcelExporter() throws IOException {
        try (final Workbook wb = _testDataProvider.createWorkbook()) {
            boolean isHssf = wb instanceof HSSFWorkbook;
            final Sheet sheet = wb.createSheet("test");
            trackColumnsForAutoSizingIfSXSSF(sheet);
            final Row row = sheet.createRow(0);
            final Cell cell = row.createCell(0);

            CellStyle csDateTime = wb.createCellStyle();
            csDateTime.setAlignment(HorizontalAlignment.LEFT);

            cell.setCellValue(new Date(Long.parseLong("1439800763994")));
            cell.setCellStyle(csDateTime);

            sheet.autoSizeColumn(0);
            assertTrue(sheet.getColumnWidth(0) > 2500);
        }
    }
}