log4j/src/testcases/org/apache/poi/ss/util/BaseTestCellUtil.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.util;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertSame;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;

/**
 * Tests Spreadsheet CellUtil
 *
 * @see org.apache.poi.ss.util.CellUtil
 */
public abstract class BaseTestCellUtil {
    protected final ITestDataProvider _testDataProvider;

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

    @Test
    void setCellStyleProperty() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet s = wb.createSheet();
            Row r = s.createRow(0);
            Cell c = r.createCell(0);

            // Add a border should create a new style
            int styCnt1 = wb.getNumCellStyles();
            CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.THIN);
            int styCnt2 = wb.getNumCellStyles();
            assertEquals(styCnt1 + 1, styCnt2);

            // Add same border to another cell, should not create another style
            c = r.createCell(1);
            CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.THIN);
            int styCnt3 = wb.getNumCellStyles();
            assertEquals(styCnt2, styCnt3);
        }
    }

    @Test
    void setCellStylePropertyWithInvalidValue() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet s = wb.createSheet();
            Row r = s.createRow(0);
            Cell c = r.createCell(0);

            // An invalid BorderStyle constant
            assertThrows(RuntimeException.class, () -> CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, 42));
        }
    }

    @Test()
    void setCellStylePropertyBorderWithShortAndEnum() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet s = wb.createSheet();
            Row r = s.createRow(0);
            Cell c = r.createCell(0);

            // A valid BorderStyle constant, as a Short
            CellUtil.setCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.DASH_DOT.getCode());
            assertEquals(BorderStyle.DASH_DOT, c.getCellStyle().getBorderBottom());

            // A valid BorderStyle constant, as an Enum
            CellUtil.setCellStyleProperty(c, CellUtil.BORDER_TOP, BorderStyle.MEDIUM_DASH_DOT);
            assertEquals(BorderStyle.MEDIUM_DASH_DOT, c.getCellStyle().getBorderTop());
        }
    }

    @Test
    void setCellStyleProperties() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet s = wb.createSheet();
            Row r = s.createRow(0);
            Cell c = r.createCell(0);

            // Add multiple border properties to cell should create a single new style
            int styCnt1 = wb.getNumCellStyles();
            Map<String, Object> props = new HashMap<>();
            props.put(CellUtil.BORDER_TOP, BorderStyle.THIN);
            props.put(CellUtil.BORDER_BOTTOM, BorderStyle.THIN);
            props.put(CellUtil.BORDER_LEFT, BorderStyle.THIN);
            props.put(CellUtil.BORDER_RIGHT, BorderStyle.THIN);
            props.put(CellUtil.ALIGNMENT, HorizontalAlignment.CENTER.getCode()); // try it both with a Short (deprecated)
            props.put(CellUtil.VERTICAL_ALIGNMENT, VerticalAlignment.CENTER); // and with an enum
            CellUtil.setCellStyleProperties(c, props);
            int styCnt2 = wb.getNumCellStyles();
            assertEquals(styCnt1 + 1, styCnt2, "Only one additional style should have been created");

            // Add same border another to same cell, should not create another style
            c = r.createCell(1);
            CellUtil.setCellStyleProperties(c, props);
            int styCnt3 = wb.getNumCellStyles();
            assertEquals(styCnt2, styCnt3, "No additional styles should have been created");
        }
    }

    @Test
    void getRow() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet sh = wb.createSheet();
            Row row1 = sh.createRow(0);

            // Get row that already exists
            Row r1 = CellUtil.getRow(0, sh);
            assertNotNull(r1);
            assertSame(row1, r1, "An existing row should not be recreated");

            // Get row that does not exist yet
            assertNotNull(CellUtil.getRow(1, sh));
        }
    }

    @Test
    void getCell() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet sh = wb.createSheet();
            Row row = sh.createRow(0);
            Cell A1 = row.createCell(0);

            // Get cell that already exists
            Cell a1 = CellUtil.getCell(row, 0);
            assertNotNull(a1);
            assertSame(A1, a1, "An existing cell should not be recreated");

            // Get cell that does not exist yet
            assertNotNull(CellUtil.getCell(row, 1));
        }
    }

    @Test
    void createCell() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet sh = wb.createSheet();
            Row row = sh.createRow(0);

            CellStyle style = wb.createCellStyle();
            style.setWrapText(true);

            // calling createCell on a non-existing cell should create a cell and set the cell value and style.
            Cell F1 = CellUtil.createCell(row, 5, "Cell Value", style);

            assertSame(row.getCell(5), F1);
            assertEquals("Cell Value", F1.getStringCellValue());
            assertEquals(style, F1.getCellStyle());
            // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call.
            // HSSFCellStyle wraps an underlying style record, and the underlying
            // style record is the same between multiple getCellStyle() calls.

            // calling createCell on an existing cell should return the existing cell and modify the cell value and style.
            Cell f1 = CellUtil.createCell(row, 5, "Overwritten cell value", null);
            assertSame(row.getCell(5), f1);
            assertSame(F1, f1);
            assertEquals("Overwritten cell value", f1.getStringCellValue());
            assertEquals("Overwritten cell value", F1.getStringCellValue());
            assertEquals(style, f1.getCellStyle(), "cell style should be unchanged with createCell(..., null)");
            assertEquals(style, F1.getCellStyle(), "cell style should be unchanged with createCell(..., null)");

            // test createCell(row, column, value) (no CellStyle)
            f1 = CellUtil.createCell(row, 5, "Overwritten cell with default style");
            assertSame(F1, f1);
        }
    }

    /**
     * @deprecated by {@link #setAlignmentEnum()}
     */
    @Deprecated
    @SuppressWarnings("deprecated")
    @Test
    void setAlignment() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sh = wb.createSheet();
        Row row = sh.createRow(0);
        Cell A1 = row.createCell(0);
        Cell B1 = row.createCell(1);

        // Assumptions
        assertEquals(A1.getCellStyle(), B1.getCellStyle());
        // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call.
        // HSSFCellStyle wraps an underlying style record, and the underlying
        // style record is the same between multiple getCellStyle() calls.
        assertEquals(HorizontalAlignment.GENERAL, A1.getCellStyle().getAlignment());
        assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignment());

        // get/set alignment modifies the cell's style
        CellUtil.setAlignment(A1, HorizontalAlignment.RIGHT);
        assertEquals(HorizontalAlignment.RIGHT, A1.getCellStyle().getAlignment());

        // get/set alignment doesn't affect the style of cells with
        // the same style prior to modifying the style
        assertNotEquals(A1.getCellStyle(), B1.getCellStyle());
        assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignment());

        wb.close();
    }

    @Test
    void setAlignmentEnum() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sh = wb.createSheet();
        Row row = sh.createRow(0);
        Cell A1 = row.createCell(0);
        Cell B1 = row.createCell(1);

        // Assumptions
        assertEquals(A1.getCellStyle(), B1.getCellStyle());
        // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call.
        // HSSFCellStyle wraps an underlying style record, and the underlying
        // style record is the same between multiple getCellStyle() calls.
        assertEquals(HorizontalAlignment.GENERAL, A1.getCellStyle().getAlignment());
        assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignment());

        // get/set alignment modifies the cell's style
        CellUtil.setAlignment(A1, HorizontalAlignment.RIGHT);
        assertEquals(HorizontalAlignment.RIGHT, A1.getCellStyle().getAlignment());

        // get/set alignment doesn't affect the style of cells with
        // the same style prior to modifying the style
        assertNotEquals(A1.getCellStyle(), B1.getCellStyle());
        assertEquals(HorizontalAlignment.GENERAL, B1.getCellStyle().getAlignment());

        wb.close();
    }

    @Test
    void setVerticalAlignmentEnum() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet sh = wb.createSheet();
            Row row = sh.createRow(0);
            Cell A1 = row.createCell(0);
            Cell B1 = row.createCell(1);

            // Assumptions
            assertEquals(A1.getCellStyle(), B1.getCellStyle());
            // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call.
            // HSSFCellStyle wraps an underlying style record, and the underlying
            // style record is the same between multiple getCellStyle() calls.
            assertEquals(VerticalAlignment.BOTTOM, A1.getCellStyle().getVerticalAlignment());
            assertEquals(VerticalAlignment.BOTTOM, B1.getCellStyle().getVerticalAlignment());

            // get/set alignment modifies the cell's style
            CellUtil.setVerticalAlignment(A1, VerticalAlignment.TOP);
            assertEquals(VerticalAlignment.TOP, A1.getCellStyle().getVerticalAlignment());

            // get/set alignment doesn't affect the style of cells with
            // the same style prior to modifying the style
            assertNotEquals(A1.getCellStyle(), B1.getCellStyle());
            assertEquals(VerticalAlignment.BOTTOM, B1.getCellStyle().getVerticalAlignment());
        }
    }

    @Test
    void setFont() throws IOException {
        try (Workbook wb = _testDataProvider.createWorkbook()) {
            Sheet sh = wb.createSheet();
            Row row = sh.createRow(0);
            Cell A1 = row.createCell(0);
            Cell B1 = row.createCell(1);
            final int defaultFontIndex = 0;
            Font font = wb.createFont();
            font.setItalic(true);
            final int customFontIndex = font.getIndex();

            // Assumptions
            assertNotEquals(defaultFontIndex, customFontIndex);
            assertEquals(A1.getCellStyle(), B1.getCellStyle());
            // should be assertSame, but a new HSSFCellStyle is returned for each getCellStyle() call.
            // HSSFCellStyle wraps an underlying style record, and the underlying
            // style record is the same between multiple getCellStyle() calls.
            assertEquals(defaultFontIndex, A1.getCellStyle().getFontIndex());
            assertEquals(defaultFontIndex, B1.getCellStyle().getFontIndex());

            // get/set alignment modifies the cell's style
            CellUtil.setFont(A1, font);
            assertEquals(customFontIndex, A1.getCellStyle().getFontIndex());

            // get/set alignment doesn't affect the style of cells with
            // the same style prior to modifying the style
            assertNotEquals(A1.getCellStyle(), B1.getCellStyle());
            assertEquals(defaultFontIndex, B1.getCellStyle().getFontIndex());
        }
    }

    @Test
    void setFontFromDifferentWorkbook() throws IOException {
        try (Workbook wb1 = _testDataProvider.createWorkbook();
            Workbook wb2 = _testDataProvider.createWorkbook()) {
            Font font1 = wb1.createFont();
            Font font2 = wb2.createFont();
            // do something to make font1 and font2 different
            // so they are not same or equal.
            font1.setItalic(true);
            Cell A1 = wb1.createSheet().createRow(0).createCell(0);

            // okay
            CellUtil.setFont(A1, font1);

            // font belongs to different workbook
            IllegalArgumentException e = assertThrows(IllegalArgumentException.class, () -> CellUtil.setFont(A1, font2));
            assertTrue(e.getMessage().startsWith("Font does not belong to this workbook"));
        }
    }

    /**
     * bug 55555
     * @since POI 3.15 beta 3
     */
    @Test
    void setFillForegroundColorBeforeFillBackgroundColorEnum() throws IOException {
        try (Workbook wb1 = _testDataProvider.createWorkbook()) {
            Cell A1 = wb1.createSheet().createRow(0).createCell(0);
            Map<String, Object> properties = new HashMap<>();
            properties.put(CellUtil.FILL_PATTERN, FillPatternType.BRICKS);
            properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.BLUE.index);
            properties.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.RED.index);

            CellUtil.setCellStyleProperties(A1, properties);
            CellStyle style = A1.getCellStyle();
            assertEquals(FillPatternType.BRICKS, style.getFillPattern(), "fill pattern");
            assertEquals(IndexedColors.BLUE, IndexedColors.fromInt(style.getFillForegroundColor()), "fill foreground color");
            assertEquals(IndexedColors.RED, IndexedColors.fromInt(style.getFillBackgroundColor()), "fill background color");
        }
    }

    /**
     * bug 63268
     * @since POI 4.1.0
     */
    @Test
    void setFontShouldNotCreateDuplicateStyle() throws IOException {
        Workbook wb1 = _testDataProvider.createWorkbook();
        Cell c = wb1.createSheet().createRow(1).createCell(1);
        Font f = wb1.createFont();

        CellUtil.setFont(c, f);
        int num1 = wb1.getNumCellStyles();

        CellUtil.setFont(c, f);
        int num2 = wb1.getNumCellStyles();
        assertEquals(num1, num2);
        wb1.close();
    }
}