BaseTestCell.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.apache.poi.ss.usermodel.FormulaError.forInt;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;
import static org.mockito.Mockito.doCallRealMethod;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.verify;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneOffset;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Locale;
import java.util.TimeZone;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.eval.NotImplementedException;
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 implementations of
* {@link org.apache.poi.ss.usermodel.Cell}
*/
@SuppressWarnings("deprecation")
public abstract class BaseTestCell {
protected static TimeZone userTimeZone;
protected final ITestDataProvider _testDataProvider;
/**
* @param testDataProvider an object that provides test data in HSSF / XSSF specific way
*/
protected BaseTestCell(ITestDataProvider testDataProvider) {
_testDataProvider = testDataProvider;
}
@BeforeAll
public static void setTimeZone() {
userTimeZone = LocaleUtil.getUserTimeZone();
LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
LocaleUtil.setUserLocale(Locale.US);
}
@AfterAll
public static void resetTimeZone() {
LocaleUtil.setUserTimeZone(userTimeZone);
LocaleUtil.setUserLocale(Locale.ROOT);
}
@Test
void testSetValues() throws Exception {
try (Workbook book = _testDataProvider.createWorkbook()) {
Sheet sheet = book.createSheet("test");
Row row = sheet.createRow(0);
CreationHelper factory = book.getCreationHelper();
Cell cell = row.createCell(0);
cell.setCellValue(1.2);
assertEquals(1.2, cell.getNumericCellValue(), 0.0001);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
cell.setCellValue(false);
assertFalse(cell.getBooleanCellValue());
assertEquals(CellType.BOOLEAN, cell.getCellType());
cell.setCellValue(true);
assertTrue(cell.getBooleanCellValue());
assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.STRING, CellType.BOOLEAN,
CellType.FORMULA, CellType.ERROR);
cell.setCellValue(factory.createRichTextString("Foo"));
assertEquals("Foo", cell.getRichStringCellValue().getString());
assertEquals("Foo", cell.getStringCellValue());
assertEquals(CellType.STRING, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.BOOLEAN,
CellType.FORMULA, CellType.ERROR);
cell.setCellValue("345");
assertEquals("345", cell.getRichStringCellValue().getString());
assertEquals("345", cell.getStringCellValue());
assertEquals(CellType.STRING, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.BOOLEAN,
CellType.FORMULA, CellType.ERROR);
Calendar c = LocaleUtil.getLocaleCalendar();
c.setTimeInMillis(123456789);
cell.setCellValue(c.getTime());
assertEquals(c.getTime().getTime(), cell.getDateCellValue().getTime());
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
cell.setCellValue(c);
assertEquals(c.getTime().getTime(), cell.getDateCellValue().getTime());
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
cell.setCellErrorValue(FormulaError.NA.getCode());
assertEquals(FormulaError.NA.getCode(), cell.getErrorCellValue());
assertEquals(CellType.ERROR, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.BOOLEAN,
CellType.FORMULA, CellType.STRING);
LocalDateTime ldt = DateUtil.toLocalDateTime(c);
cell.setCellValue(ldt);
assertEquals(ldt, cell.getLocalDateTimeCellValue());
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
LocalDate ld = ldt.toLocalDate();
cell.setCellValue(ld);
assertEquals(ld, cell.getLocalDateTimeCellValue().toLocalDate());
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
cell.setCellValue((String)null);
assertEquals("", cell.getStringCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
cell.setCellValue((LocalDate)null);
assertNull(cell.getLocalDateTimeCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
cell.setCellValue((LocalDateTime)null);
assertNull(cell.getLocalDateTimeCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
cell.setCellErrorValue(FormulaError.NA.getCode());
assertEquals(FormulaError.NA.getCode(), cell.getErrorCellValue());
assertEquals(CellType.ERROR, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.BOOLEAN,
CellType.FORMULA, CellType.STRING);
}
}
private static void assertProhibitedValueAccess(Cell cell, CellType ... types) {
for(CellType type : types){
try {
switch (type) {
case NUMERIC:
cell.getNumericCellValue();
break;
case STRING:
cell.getStringCellValue();
break;
case BOOLEAN:
cell.getBooleanCellValue();
break;
case FORMULA:
cell.getCellFormula();
break;
case ERROR:
cell.getErrorCellValue();
break;
default:
fail("Should get exception when reading cell type (" + type + ").");
}
} catch (IllegalStateException e){
// expected during successful test
assertTrue(e.getMessage().startsWith("Cannot get a"));
}
}
}
/**
* test that Boolean (BoolErrRecord) are supported properly.
*/
@Test
void testBool() throws IOException {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet s = wb1.createSheet("testSheet1");
Row r;
Cell c;
// B1
r = s.createRow(0);
c = r.createCell(1);
assertEquals(0, c.getRowIndex());
assertEquals(1, c.getColumnIndex());
c.setCellValue(true);
assertTrue(c.getBooleanCellValue(), "B1 value");
// C1
c = r.createCell(2);
assertEquals(0, c.getRowIndex());
assertEquals(2, c.getColumnIndex());
c.setCellValue(false);
assertFalse(c.getBooleanCellValue(), "C1 value");
// Make sure values are saved and re-read correctly.
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
s = wb2.getSheet("testSheet1");
r = s.getRow(0);
assertEquals(2, r.getPhysicalNumberOfCells(), "Row 1 should have 2 cells");
c = r.getCell(1);
assertEquals(0, c.getRowIndex());
assertEquals(1, c.getColumnIndex());
assertEquals(CellType.BOOLEAN, c.getCellType());
assertTrue(c.getBooleanCellValue(), "B1 value");
c = r.getCell(2);
assertEquals(0, c.getRowIndex());
assertEquals(2, c.getColumnIndex());
assertEquals(CellType.BOOLEAN, c.getCellType());
assertFalse(c.getBooleanCellValue(), "C1 value");
}
}
}
/**
* test that Error types (BoolErrRecord) are supported properly.
* @see #testBool
*/
@Test
void testErr() throws IOException {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet s = wb1.createSheet("testSheet1");
Row r;
Cell c;
// B1
r = s.createRow(0);
c = r.createCell(1);
assertEquals(0, c.getRowIndex());
assertEquals(1, c.getColumnIndex());
c.setCellErrorValue(FormulaError.NULL.getCode());
assertEquals(FormulaError.NULL.getCode(), c.getErrorCellValue(), "B1 value == #NULL!");
// C1
c = r.createCell(2);
assertEquals(0, c.getRowIndex());
assertEquals(2, c.getColumnIndex());
c.setCellErrorValue(FormulaError.DIV0.getCode());
assertEquals(FormulaError.DIV0.getCode(), c.getErrorCellValue(), "C1 value == #DIV/0!");
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
s = wb2.getSheet("testSheet1");
r = s.getRow(0);
assertEquals(2, r.getPhysicalNumberOfCells(), "Row 1 should have 2 cells");
c = r.getCell(1);
assertEquals(0, c.getRowIndex());
assertEquals(1, c.getColumnIndex());
assertEquals(CellType.ERROR, c.getCellType());
assertEquals(FormulaError.NULL.getCode(), c.getErrorCellValue(), "B1 value == #NULL!");
c = r.getCell(2);
assertEquals(0, c.getRowIndex());
assertEquals(2, c.getColumnIndex());
assertEquals(CellType.ERROR, c.getCellType());
assertEquals(FormulaError.DIV0.getCode(), c.getErrorCellValue(), "C1 value == #DIV/0!");
}
}
}
/**
* test that Cell Styles being applied to formulas remain intact
*/
@Test
void testFormulaStyle() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet s = wb1.createSheet("testSheet1");
Row r;
Cell c;
CellStyle cs = wb1.createCellStyle();
Font f = wb1.createFont();
f.setFontHeightInPoints((short) 20);
f.setColor(IndexedColors.RED.getIndex());
f.setBold(true);
f.setFontName("Arial Unicode MS");
cs.setFillBackgroundColor((short) 3);
cs.setFont(f);
cs.setBorderTop(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderBottom(BorderStyle.THIN);
r = s.createRow(0);
c = r.createCell(0);
c.setCellStyle(cs);
c.setCellFormula("2*3");
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
s = wb2.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertEquals(CellType.FORMULA, c.getCellType(), "Formula Cell at 0,0");
cs = c.getCellStyle();
assertNotNull(cs, "Formula Cell Style");
assertEquals(f.getIndex(), cs.getFontIndex(), "Font Index Matches");
assertEquals(BorderStyle.THIN, cs.getBorderTop(), "Top Border");
assertEquals(BorderStyle.THIN, cs.getBorderLeft(), "Left Border");
assertEquals(BorderStyle.THIN, cs.getBorderRight(), "Right Border");
assertEquals(BorderStyle.THIN, cs.getBorderBottom(), "Bottom Border");
}
}
}
/**tests the toString() method of HSSFCell*/
@Test
void testToString() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Row r = wb1.createSheet("Sheet1").createRow(0);
CreationHelper factory = wb1.getCreationHelper();
r.createCell(0).setCellValue(false);
r.createCell(1).setCellValue(true);
r.createCell(2).setCellValue(1.5);
r.createCell(3).setCellValue(factory.createRichTextString("Astring"));
r.createCell(4).setCellErrorValue(FormulaError.DIV0.getCode());
r.createCell(5).setCellFormula("A1+B1");
r.createCell(6); // blank
// create date-formatted cell
Calendar c = LocaleUtil.getLocaleCalendar();
c.set(2010, Calendar.FEBRUARY, 2, 0, 0, 0);
r.createCell(7).setCellValue(c);
CellStyle dateStyle = wb1.createCellStyle();
short formatId = wb1.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"); // any date format will do
dateStyle.setDataFormat(formatId);
r.getCell(7).setCellStyle(dateStyle);
// null rich text
r.createCell(8).setCellValue(factory.createRichTextString(null)); // blank
assertEquals("FALSE", r.getCell(0).toString(), "Boolean");
assertEquals("TRUE", r.getCell(1).toString(), "Boolean");
assertEquals("1.5", r.getCell(2).toString(), "Numeric");
assertEquals("Astring", r.getCell(3).toString(), "String");
assertEquals("#DIV/0!", r.getCell(4).toString(), "Error");
assertEquals("A1+B1", r.getCell(5).toString(), "Formula");
assertEquals("", r.getCell(6).toString(), "Blank");
// toString on a date-formatted cell displays dates as dd-MMM-yyyy, which has locale problems with the month
String dateCell1 = r.getCell(7).toString();
assertEquals("2/2/10 0:00", dateCell1);
assertEquals("", r.getCell(8).toString(), "Blank");
//Write out the file, read it in, and then check cell values
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
r = wb2.getSheetAt(0).getRow(0);
assertEquals("FALSE", r.getCell(0).toString(), "Boolean");
assertEquals("TRUE", r.getCell(1).toString(), "Boolean");
assertEquals("1.5", r.getCell(2).toString(), "Numeric");
assertEquals("Astring", r.getCell(3).toString(), "String");
assertEquals("#DIV/0!", r.getCell(4).toString(), "Error");
assertEquals("A1+B1", r.getCell(5).toString(), "Formula");
assertEquals("", r.getCell(6).toString(), "Blank");
String dateCell2 = r.getCell(7).toString();
assertEquals(dateCell1, dateCell2, "Date");
assertEquals("", r.getCell(8).toString(), "Blank");
}
}
}
/**
* Test that setting cached formula result keeps the cell type
*/
@Test
void testSetFormulaValue() throws Exception {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet s = wb.createSheet();
Row r = s.createRow(0);
Cell c1 = r.createCell(0);
c1.setCellFormula("NA()");
assertEquals(0.0, c1.getNumericCellValue(), 0.0);
assertEquals(CellType.NUMERIC, c1.getCachedFormulaResultType());
c1.setCellValue(10);
assertEquals(10.0, c1.getNumericCellValue(), 0.0);
assertEquals(CellType.FORMULA, c1.getCellType());
assertEquals(CellType.NUMERIC, c1.getCachedFormulaResultType());
Cell c2 = r.createCell(1);
c2.setCellFormula("NA()");
assertEquals(0.0, c2.getNumericCellValue(), 0.0);
assertEquals(CellType.NUMERIC, c2.getCachedFormulaResultType());
c2.setCellValue("I changed!");
assertEquals("I changed!", c2.getStringCellValue());
assertEquals(CellType.FORMULA, c2.getCellType());
assertEquals(CellType.STRING, c2.getCachedFormulaResultType());
//calglin Cell.setCellFormula(null) for a non-formula cell
Cell c3 = r.createCell(2);
c3.setCellFormula(null);
assertEquals(CellType.BLANK, c3.getCellType());
}
}
private Cell createACell(Workbook wb) {
return wb.createSheet("Sheet1").createRow(0).createCell(0);
}
/**
* bug 58452: Copy cell formulas containing unregistered function names
* Make sure that formulas with unknown/unregistered UDFs can be written to and read back from a file.
*/
@Test
void testFormulaWithUnknownUDF() throws IOException {
try (final Workbook wb1 = _testDataProvider.createWorkbook()) {
final FormulaEvaluator evaluator1 = wb1.getCreationHelper().createFormulaEvaluator();
final Cell cell1 = wb1.createSheet().createRow(0).createCell(0);
final String formula = "myFunc(\"arg\")";
cell1.setCellFormula(formula);
confirmFormulaWithUnknownUDF(formula, cell1, evaluator1);
try (final Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
final FormulaEvaluator evaluator2 = wb2.getCreationHelper().createFormulaEvaluator();
final Cell cell2 = wb2.getSheetAt(0).getRow(0).getCell(0);
confirmFormulaWithUnknownUDF(formula, cell2, evaluator2);
}
}
}
private static void confirmFormulaWithUnknownUDF(String expectedFormula, Cell cell, FormulaEvaluator evaluator) {
assertEquals(expectedFormula, cell.getCellFormula());
assertThrows(NotImplementedException.class, () -> evaluator.evaluate(cell));
}
@Test
void testChangeTypeStringToBool() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = createACell(wb);
cell.setCellValue("TRUE");
assertEquals(CellType.STRING, cell.getCellType());
// test conversion of cell from text to boolean
cell.setCellType(CellType.BOOLEAN);
assertEquals(CellType.BOOLEAN, cell.getCellType());
assertTrue(cell.getBooleanCellValue());
cell.setCellType(CellType.STRING);
assertEquals("TRUE", cell.getRichStringCellValue().getString());
// 'false' text to bool and back
cell.setCellValue("FALSE");
cell.setCellType(CellType.BOOLEAN);
assertEquals(CellType.BOOLEAN, cell.getCellType());
assertFalse(cell.getBooleanCellValue());
cell.setCellType(CellType.STRING);
assertEquals("FALSE", cell.getRichStringCellValue().getString());
}
}
@Test
void testChangeTypeBoolToString() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = createACell(wb);
cell.setCellValue(true);
// test conversion of cell from boolean to text
cell.setCellType(CellType.STRING);
assertEquals("TRUE", cell.getRichStringCellValue().getString());
}
}
@Test
void testChangeTypeErrorToNumber() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = createACell(wb);
cell.setCellErrorValue(FormulaError.NAME.getCode());
// ClassCastException -> Identified bug 46479b
cell.setCellValue(2.5);
assertEquals(2.5, cell.getNumericCellValue(), 0.0);
}
}
@Test
void testChangeTypeErrorToBoolean() throws IOException {
Workbook wb = _testDataProvider.createWorkbook();
Cell cell = createACell(wb);
cell.setCellErrorValue(FormulaError.NAME.getCode());
cell.setCellValue(true);
// Identify bug 46479c
assertTrue(cell.getBooleanCellValue());
wb.close();
}
/**
* Test for a bug observed around svn r886733 when using
* {@link FormulaEvaluator#evaluateInCell(Cell)} with a
* string result type.
*/
@Test
void testConvertStringFormulaCell() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cellA1 = createACell(wb);
cellA1.setCellFormula("\"abc\"");
// default cached formula result is numeric zero
assertEquals(0.0, cellA1.getNumericCellValue(), 0.0);
FormulaEvaluator fe = cellA1.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
fe.evaluateFormulaCell(cellA1);
assertEquals("abc", cellA1.getStringCellValue());
fe.evaluateInCell(cellA1);
assertFalse(cellA1.getStringCellValue().isEmpty(), "Identified bug with writing back formula result of type string");
assertEquals("abc", cellA1.getStringCellValue());
}
}
/**
* similar to {@link #testConvertStringFormulaCell()} but checks at a
* lower level that {#link {@link Cell#setCellType(CellType)} works properly
*/
@Test
void testSetTypeStringOnFormulaCell() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cellA1 = createACell(wb);
FormulaEvaluator fe = cellA1.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
cellA1.setCellFormula("\"DEF\"");
fe.clearAllCachedResultValues();
fe.evaluateFormulaCell(cellA1);
assertEquals("DEF", cellA1.getStringCellValue());
cellA1.setCellType(CellType.STRING);
assertEquals("DEF", cellA1.getStringCellValue());
cellA1.setCellFormula("25.061");
fe.clearAllCachedResultValues();
fe.evaluateFormulaCell(cellA1);
confirmCannotReadString(cellA1);
assertEquals(25.061, cellA1.getNumericCellValue(), 0.0);
cellA1.setCellType(CellType.STRING);
assertEquals("25.061", cellA1.getStringCellValue());
cellA1.setCellFormula("TRUE");
fe.clearAllCachedResultValues();
fe.evaluateFormulaCell(cellA1);
confirmCannotReadString(cellA1);
assertTrue(cellA1.getBooleanCellValue());
cellA1.setCellType(CellType.STRING);
assertEquals("TRUE", cellA1.getStringCellValue());
cellA1.setCellFormula("#NAME?");
fe.clearAllCachedResultValues();
fe.evaluateFormulaCell(cellA1);
confirmCannotReadString(cellA1);
assertEquals(FormulaError.NAME, forInt(cellA1.getErrorCellValue()));
cellA1.setCellType(CellType.STRING);
assertEquals("#NAME?", cellA1.getStringCellValue());
}
}
private static void confirmCannotReadString(Cell cell) {
assertProhibitedValueAccess(cell, CellType.STRING);
}
/**
* Test for bug in convertCellValueToBoolean to make sure that formula results get converted
*/
@Test
void testChangeTypeFormulaToBoolean() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = createACell(wb);
cell.setCellFormula("1=1");
cell.setCellValue(true);
cell.setCellType(CellType.BOOLEAN);
assertTrue(cell.getBooleanCellValue(), "Identified bug 46479d");
assertTrue(cell.getBooleanCellValue());
}
}
/**
* Bug 40296: HSSFCell.setCellFormula throws
* ClassCastException if cell is created using HSSFRow.createCell(short column, int type)
*/
@Test
void test40296() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet workSheet = wb1.createSheet("Sheet1");
Cell cell;
Row row = workSheet.createRow(0);
cell = row.createCell(0, CellType.NUMERIC);
cell.setCellValue(1.0);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(1.0, cell.getNumericCellValue(), 0.0);
cell = row.createCell(1, CellType.NUMERIC);
cell.setCellValue(2.0);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(2.0, cell.getNumericCellValue(), 0.0);
cell = row.createCell(2, CellType.FORMULA);
cell.setCellFormula("SUM(A1:B1)");
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals("SUM(A1:B1)", cell.getCellFormula());
//serialize and check again
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
row = wb2.getSheetAt(0).getRow(0);
cell = row.getCell(0);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(1.0, cell.getNumericCellValue(), 0.0);
cell = row.getCell(1);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(2.0, cell.getNumericCellValue(), 0.0);
cell = row.getCell(2);
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals("SUM(A1:B1)", cell.getCellFormula());
}
}
}
@Test
void testSetStringInFormulaCell_bug44606() throws Exception {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
cell.setCellFormula("B1&C1");
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(wb.getCreationHelper().createRichTextString("hello"));
assertEquals(CellType.FORMULA, cell.getCellType());
}
}
/**
* Make sure that cell.setBlank() preserves the cell style
*/
@Test
void testSetBlank_bug47028() throws Exception {
try (Workbook wb = _testDataProvider.createWorkbook()) {
CellStyle style = wb.createCellStyle();
Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
cell.setCellStyle(style);
int i1 = cell.getCellStyle().getIndex();
cell.setBlank();
int i2 = cell.getCellStyle().getIndex();
assertEquals(i1, i2);
}
}
/**
* Excel's implementation of floating number arithmetic does not fully adhere to IEEE 754:
*
* From http://support.microsoft.com/kb/78113:
*
* <ul>
* <li> Positive/Negative Infinities:
* Infinities occur when you divide by 0. Excel does not support infinities, rather,
* it gives a #DIV/0! error in these cases.
* </li>
* <li>
* Not-a-Number (NaN):
* NaN is used to represent invalid operations (such as infinity/infinity,
* infinity-infinity, or the square root of -1). NaNs allow a program to
* continue past an invalid operation. Excel instead immediately generates
* an error such as #NUM! or #DIV/0!.
* </li>
* </ul>
*/
@Test
void testNanAndInfinity() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet workSheet = wb1.createSheet("Sheet1");
Row row = workSheet.createRow(0);
Cell cell0 = row.createCell(0);
cell0.setCellValue(Double.NaN);
assertEquals(CellType.ERROR, cell0.getCellType(), "Double.NaN should change cell type to CellType#ERROR");
assertEquals(FormulaError.NUM, forInt(cell0.getErrorCellValue()), "Double.NaN should change cell value to #NUM!");
Cell cell1 = row.createCell(1);
cell1.setCellValue(Double.POSITIVE_INFINITY);
assertEquals(CellType.ERROR, cell1.getCellType(), "Double.POSITIVE_INFINITY should change cell type to CellType#ERROR");
assertEquals(FormulaError.DIV0, forInt(cell1.getErrorCellValue()), "Double.POSITIVE_INFINITY should change cell value to #DIV/0!");
Cell cell2 = row.createCell(2);
cell2.setCellValue(Double.NEGATIVE_INFINITY);
assertEquals(CellType.ERROR, cell2.getCellType(), "Double.NEGATIVE_INFINITY should change cell type to CellType#ERROR");
assertEquals(FormulaError.DIV0, forInt(cell2.getErrorCellValue()), "Double.NEGATIVE_INFINITY should change cell value to #DIV/0!");
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
row = wb2.getSheetAt(0).getRow(0);
cell0 = row.getCell(0);
assertEquals(CellType.ERROR, cell0.getCellType());
assertEquals(FormulaError.NUM, forInt(cell0.getErrorCellValue()));
cell1 = row.getCell(1);
assertEquals(CellType.ERROR, cell1.getCellType());
assertEquals(FormulaError.DIV0, forInt(cell1.getErrorCellValue()));
cell2 = row.getCell(2);
assertEquals(CellType.ERROR, cell2.getCellType());
assertEquals(FormulaError.DIV0, forInt(cell2.getErrorCellValue()));
}
}
}
@Test
void testDefaultStyleProperties() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Cell cell = wb1.createSheet("Sheet1").createRow(0).createCell(0);
CellStyle style = cell.getCellStyle();
assertTrue(style.getLocked());
assertFalse(style.getHidden());
assertEquals(0, style.getIndention());
assertEquals(0, style.getFontIndex());
assertEquals(HorizontalAlignment.GENERAL, style.getAlignment());
assertEquals(0, style.getDataFormat());
assertFalse(style.getWrapText());
CellStyle style2 = wb1.createCellStyle();
assertTrue(style2.getLocked());
assertFalse(style2.getHidden());
style2.setLocked(false);
style2.setHidden(true);
assertFalse(style2.getLocked());
assertTrue(style2.getHidden());
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
cell = wb2.getSheetAt(0).getRow(0).getCell(0);
style = cell.getCellStyle();
assertFalse(style2.getLocked());
assertTrue(style2.getHidden());
assertTrue(style.getLocked());
assertFalse(style.getHidden());
style2.setLocked(true);
style2.setHidden(false);
assertTrue(style2.getLocked());
assertFalse(style2.getHidden());
}
}
}
@Test
void testBug55658SetNumericValue() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet sh = wb1.createSheet();
Row row = sh.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(23);
cell.setCellValue("some");
cell = row.createCell(1);
cell.setCellValue(23);
cell.setCellValue("24");
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
assertEquals("some", wb2.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
assertEquals("24", wb2.getSheetAt(0).getRow(0).getCell(1).getStringCellValue());
}
}
}
@Test
void testRemoveHyperlink() throws Exception {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet sh = wb1.createSheet("test");
Row row = sh.createRow(0);
CreationHelper helper = wb1.getCreationHelper();
Cell cell1 = row.createCell(1);
Hyperlink link1 = helper.createHyperlink(HyperlinkType.URL);
cell1.setHyperlink(link1);
assertNotNull(cell1.getHyperlink());
cell1.removeHyperlink();
assertNull(cell1.getHyperlink());
Cell cell2 = row.createCell(0);
Hyperlink link2 = helper.createHyperlink(HyperlinkType.URL);
cell2.setHyperlink(link2);
assertNotNull(cell2.getHyperlink());
cell2.setHyperlink(null);
assertNull(cell2.getHyperlink());
Cell cell3 = row.createCell(2);
Hyperlink link3 = helper.createHyperlink(HyperlinkType.URL);
link3.setAddress("https://poi.apache.org/");
cell3.setHyperlink(link3);
assertNotNull(cell3.getHyperlink());
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
assertNotNull(wb2);
cell1 = wb2.getSheet("test").getRow(0).getCell(1);
assertNull(cell1.getHyperlink());
cell2 = wb2.getSheet("test").getRow(0).getCell(0);
assertNull(cell2.getHyperlink());
cell3 = wb2.getSheet("test").getRow(0).getCell(2);
assertNotNull(cell3.getHyperlink());
}
}
}
/**
* Cell with the formula that returns error must return error code(There was
* an problem that cell could not return error value form formula cell).
*/
@Test
void testGetErrorCellValueFromFormulaCell() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellFormula("SQRT(-1)");
wb.getCreationHelper().createFormulaEvaluator().evaluateFormulaCell(cell);
assertEquals(36, cell.getErrorCellValue());
}
}
@Test
void testSetRemoveStyle() throws Exception {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
// different default style indexes for HSSF and XSSF/SXSSF
CellStyle defaultStyle = wb.getCellStyleAt(wb instanceof HSSFWorkbook ? 15 : 0);
// Starts out with the default style
assertEquals(defaultStyle, cell.getCellStyle());
// Create some styles, no change
CellStyle style1 = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
style1.setDataFormat((short) 2);
style2.setDataFormat((short) 3);
assertEquals(defaultStyle, cell.getCellStyle());
// Apply one, changes
cell.setCellStyle(style1);
assertEquals(style1, cell.getCellStyle());
// Apply the other, changes
cell.setCellStyle(style2);
assertEquals(style2, cell.getCellStyle());
// Remove, goes back to default
cell.setCellStyle(null);
assertEquals(defaultStyle, cell.getCellStyle());
// Add back, returns
cell.setCellStyle(style2);
assertEquals(style2, cell.getCellStyle());
}
}
@Test
void test57008() throws IOException {
try (Workbook wb1 = _testDataProvider.createWorkbook()) {
Sheet sheet = wb1.createSheet();
Row row0 = sheet.createRow(0);
Cell cell0 = row0.createCell(0);
cell0.setCellValue("row 0, cell 0 _x0046_ without changes");
Cell cell1 = row0.createCell(1);
cell1.setCellValue("row 0, cell 1 _x005fx0046_ with changes");
Cell cell2 = row0.createCell(2);
cell2.setCellValue("hgh_x0041_**_x0100_*_x0101_*_x0190_*_x0200_*_x0300_*_x0427_*");
checkUnicodeValues(wb1);
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
checkUnicodeValues(wb2);
}
}
}
/**
* Setting a cell value of a null RichTextString should set
* the cell to Blank, test case for 58558
*/
@SuppressWarnings("ConstantConditions")
@Test
void testSetCellValueNullRichTextString() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(0);
RichTextString nullStr = null;
cell.setCellValue(nullStr);
assertEquals("", cell.getStringCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
cell = sheet.createRow(0).createCell(1);
cell.setCellValue(1.2d);
assertEquals(CellType.NUMERIC, cell.getCellType());
cell.setCellValue(nullStr);
assertEquals("", cell.getStringCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
cell = sheet.createRow(0).createCell(1);
cell.setCellValue(wb.getCreationHelper().createRichTextString("Test"));
assertEquals(CellType.STRING, cell.getCellType());
cell.setCellValue(nullStr);
assertEquals("", cell.getStringCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
}
}
private void checkUnicodeValues(Workbook wb) {
assertEquals((wb instanceof HSSFWorkbook ? "row 0, cell 0 _x0046_ without changes" : "row 0, cell 0 F without changes"),
wb.getSheetAt(0).getRow(0).getCell(0).toString());
assertEquals((wb instanceof HSSFWorkbook ? "row 0, cell 1 _x005fx0046_ with changes" : "row 0, cell 1 _x005fx0046_ with changes"),
wb.getSheetAt(0).getRow(0).getCell(1).toString());
assertEquals((wb instanceof HSSFWorkbook ? "hgh_x0041_**_x0100_*_x0101_*_x0190_*_x0200_*_x0300_*_x0427_*" : "hghA**\u0100*\u0101*\u0190*\u0200*\u0300*\u0427*"),
wb.getSheetAt(0).getRow(0).getCell(2).toString());
}
/**
* The maximum length of cell contents (text) is 32,767 characters.
*/
@Test
void testMaxTextLength() throws IOException{
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(0);
int maxlen = wb instanceof HSSFWorkbook ?
SpreadsheetVersion.EXCEL97.getMaxTextLength()
: SpreadsheetVersion.EXCEL2007.getMaxTextLength();
assertEquals(32767, maxlen);
StringBuilder b = new StringBuilder();
// 32767 is okay
for (int i = 0; i < maxlen; i++) {
b.append("X");
}
cell.setCellValue(b.toString());
b.append("X");
// 32768 produces an invalid XLS file
IllegalArgumentException e = assertThrows(IllegalArgumentException.class, () -> cell.setCellValue(b.toString()));
assertEquals("The maximum length of cell contents (text) is 32767 characters", e.getMessage());
}
}
/**
* Tests that the setAsActiveCell and getActiveCell function pairs work together
*/
@Test
void setAsActiveCell() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell A1 = row.createCell(0);
Cell B1 = row.createCell(1);
A1.setAsActiveCell();
assertEquals(A1.getAddress(), sheet.getActiveCell());
B1.setAsActiveCell();
assertEquals(B1.getAddress(), sheet.getActiveCell());
}
}
@Test
void getCellComment() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
CreationHelper factory = wb.getCreationHelper();
Row row = sheet.createRow(0);
Cell cell = row.createCell(1);
// cell does not have a comment
assertNull(cell.getCellComment());
// add a cell comment
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 3);
Drawing<?> drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("Hello, World!");
comment.setString(str);
comment.setAuthor("Apache POI");
cell.setCellComment(comment);
// ideally assertSame, but XSSFCell creates a new XSSFCellComment wrapping the same bean for every call to getCellComment.
assertEquals(comment, cell.getCellComment());
}
}
@Test
void testSetErrorValue() throws Exception {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellFormula("A2");
cell.setCellErrorValue(FormulaError.NAME.getCode());
assertEquals(CellType.FORMULA, cell.getCellType(), "Should still be a formula even after we set an error value");
assertEquals(CellType.ERROR, cell.getCachedFormulaResultType(), "Should still be a formula even after we set an error value");
assertEquals("A2", cell.getCellFormula());
assertThrows(IllegalStateException.class, cell::getNumericCellValue);
assertThrows(IllegalStateException.class, cell::getStringCellValue);
assertThrows(IllegalStateException.class, cell::getRichStringCellValue);
assertThrows(IllegalStateException.class, cell::getDateCellValue);
assertEquals(FormulaError.NAME.getCode(), cell.getErrorCellValue());
assertNull(cell.getHyperlink());
}
}
@Test
void test62216() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell instance = wb.createSheet().createRow(0).createCell(0);
String formula = "2";
instance.setCellFormula(formula);
instance.setCellErrorValue(FormulaError.NAME.getCode());
assertEquals(formula, instance.getCellFormula());
}
}
@Test
void testSetNullValues() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = wb.createSheet("test").createRow(0).createCell(0);
cell.setCellValue((Calendar) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
cell.setCellValue((Date) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
cell.setCellValue((String) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
cell.setCellValue((RichTextString) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
cell.setCellValue((String) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
}
}
@Test
void testFormulaSetValueDoesNotChangeType() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellFormula("SQRT(-1)");
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(new Date());
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(GregorianCalendar.getInstance(TimeZone.getTimeZone(ZoneOffset.UTC), Locale.ROOT));
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(1.0);
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue("test");
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(wb.getCreationHelper().createRichTextString("test"));
assertEquals(CellType.FORMULA, cell.getCellType());
cell.setCellValue(false);
assertEquals(CellType.FORMULA, cell.getCellType());
}
}
@Test
void testGetNumericCellValueOnABlankCellReturnsZero() throws IOException {
try (Workbook workbook = _testDataProvider.createWorkbook()) {
Cell cell = workbook.createSheet().createRow(0).createCell(0);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals(0, cell.getNumericCellValue(), 0);
}
}
@Test
void getDateCellValue_returnsNull_onABlankCell() throws IOException {
try (Workbook workbook = _testDataProvider.createWorkbook()) {
Cell cell = workbook.createSheet().createRow(0).createCell(0);
assertEquals(CellType.BLANK, cell.getCellType());
Date result = cell.getDateCellValue();
assertNull(result);
}
}
@Test
void getBooleanCellValue_returnsFalse_onABlankCell() throws IOException {
try (Workbook workbook = _testDataProvider.createWorkbook()) {
Cell cell = workbook.createSheet().createRow(0).createCell(0);
assertEquals(CellType.BLANK, cell.getCellType());
boolean result = cell.getBooleanCellValue();
assertFalse(result);
}
}
@Test
void setStringCellValue_ifThrows_shallNotChangeCell() throws IOException {
try (Workbook workbook = _testDataProvider.createWorkbook()) {
Cell cell = workbook.createSheet().createRow(0).createCell(0);
final double value = 2.78;
cell.setCellValue(value);
assertEquals(CellType.NUMERIC, cell.getCellType());
int badLength = cell.getSheet().getWorkbook().getSpreadsheetVersion().getMaxTextLength() + 1;
String badStringValue = new String(new byte[badLength], StandardCharsets.UTF_8);
try {
cell.setCellValue(badStringValue);
} catch (IllegalArgumentException e) {
// no-op, expected to throw but we need to assert something more
}
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(value, cell.getNumericCellValue(), 0);
}
}
@Test
void setStringCellValueWithRichTextString_ifThrows_shallNotChangeCell() throws IOException {
try (Workbook workbook = _testDataProvider.createWorkbook()) {
Cell cell = workbook.createSheet().createRow(0).createCell(0);
final double value = 2.78;
cell.setCellValue(value);
assertEquals(CellType.NUMERIC, cell.getCellType());
int badLength = cell.getSheet().getWorkbook().getSpreadsheetVersion().getMaxTextLength() + 1;
RichTextString badStringValue = cell.getSheet().getWorkbook().getCreationHelper().
createRichTextString(new String(new byte[badLength], StandardCharsets.UTF_8));
try {
cell.setCellValue(badStringValue);
} catch (IllegalArgumentException e) {
// no-op, expected to throw but we need to assert something more
}
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(value, cell.getNumericCellValue(), 0);
}
}
@Test
void setCellType_null_throwsIAE() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
assertThrows(IllegalArgumentException.class, () -> cell.setCellType(null));
}
}
@Test
void setCellType_NONE_throwsIAE() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
assertThrows(IllegalArgumentException.class, () -> cell.setCellType(CellType._NONE));
}
}
@Test
protected void setBlank_removesArrayFormula_ifCellIsPartOfAnArrayFormulaGroupContainingOnlyThisCell() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("1", CellRangeAddress.valueOf("A1"));
cell.setCellValue("foo");
assertTrue(cell.isPartOfArrayFormulaGroup());
assertEquals("1", cell.getCellFormula());
cell.setBlank();
assertEquals(CellType.BLANK, cell.getCellType());
assertFalse(cell.isPartOfArrayFormulaGroup());
}
}
@Test
protected void setBlank_throwsISE_ifCellIsPartOfAnArrayFormulaGroupContainingOtherCells() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("1", CellRangeAddress.valueOf("A1:B1"));
cell.setCellValue("foo");
assertThrows(IllegalStateException.class, cell::setBlank);
}
}
@Test
protected void setCellFormula_throwsISE_ifCellIsPartOfAnArrayFormulaGroupContainingOtherCells() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("1", CellRangeAddress.valueOf("A1:B1"));
assertTrue(cell.isPartOfArrayFormulaGroup());
assertEquals(CellType.FORMULA, cell.getCellType());
assertThrows(IllegalStateException.class, () -> cell.setCellFormula("1"));
}
}
@Test
void removeFormula_preservesValue() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellFormula("#DIV/0!");
cell.setCellValue(true);
cell.removeFormula();
assertEquals(CellType.BOOLEAN, cell.getCellType());
assertTrue(cell.getBooleanCellValue());
cell.setCellFormula("#DIV/0!");
cell.setCellValue(2);
cell.removeFormula();
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(2, cell.getNumericCellValue(), 0);
cell.setCellFormula("#DIV/0!");
cell.setCellValue("foo");
cell.removeFormula();
assertEquals(CellType.STRING, cell.getCellType());
assertEquals("foo", cell.getStringCellValue());
cell.setCellFormula("#DIV/0!");
cell.setCellErrorValue(FormulaError.NUM.getCode());
cell.removeFormula();
assertEquals(CellType.ERROR, cell.getCellType());
assertEquals(FormulaError.NUM.getCode(), cell.getErrorCellValue());
}
}
@Test
protected void removeFormula_turnsCellToBlank_whenFormulaWasASingleCellArrayFormula() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("#DIV/0!", CellRangeAddress.valueOf("A1"));
cell.setCellValue(true);
cell.removeFormula();
assertEquals(CellType.BLANK, cell.getCellType());
cell.getSheet().setArrayFormula("#DIV/0!", CellRangeAddress.valueOf("A1"));
cell.setCellValue(2);
cell.removeFormula();
assertEquals(CellType.BLANK, cell.getCellType());
cell.getSheet().setArrayFormula("#DIV/0!", CellRangeAddress.valueOf("A1"));
cell.setCellValue(true);
cell.removeFormula();
assertEquals(CellType.BLANK, cell.getCellType());
cell.getSheet().setArrayFormula("#DIV/0!", CellRangeAddress.valueOf("A1"));
cell.setCellErrorValue(FormulaError.NUM.getCode());
cell.removeFormula();
assertEquals(CellType.BLANK, cell.getCellType());
}
}
@Test
void setCellFormula_onABlankCell_setsValueToZero() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellFormula("\"foo\"");
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultType());
assertEquals(0, cell.getNumericCellValue(), 0);
}
}
@Test
void setCellFormula_onANonBlankCell_preservesTheValue() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellValue(true);
cell.setCellFormula("\"foo\"");
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.BOOLEAN, cell.getCachedFormulaResultType());
assertTrue(cell.getBooleanCellValue(), "Expected a boolean cell-value, but had 'false'");
}
}
@Test
void setCellFormula_onAFormulaCell_changeFormula_preservesTheValue() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellFormula("\"foo\"");
cell.setCellValue(true);
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.BOOLEAN, cell.getCachedFormulaResultType());
assertTrue(cell.getBooleanCellValue());
cell.setCellFormula("\"bar\"");
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.BOOLEAN, cell.getCachedFormulaResultType());
assertTrue(cell.getBooleanCellValue(), "Expected a boolean cell-value, but had 'false'");
}
}
@Test
protected void setCellFormula_onASingleCellArrayFormulaCell_preservesTheValue() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("\"foo\"", CellRangeAddress.valueOf("A1"));
cell.setCellValue(true);
assertTrue(cell.isPartOfArrayFormulaGroup());
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.BOOLEAN, cell.getCachedFormulaResultType());
assertTrue(cell.getBooleanCellValue());
cell.getSheet().setArrayFormula("\"bar\"", CellRangeAddress.valueOf("A1"));
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.BOOLEAN, cell.getCachedFormulaResultType(),
"Expected a boolean cell-value, but had " + cell.getCachedFormulaResultType());
assertTrue(cell.getBooleanCellValue(),
"Expected a boolean cell-value, but had 'false'");
}
}
@Test
void setCellType_FORMULA_onANonFormulaCell_throwsIllegalArgumentException() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
assertThrows(IllegalArgumentException.class, () -> cell.setCellType(CellType.FORMULA));
}
}
@Test
void setCellType_FORMULA_onAFormulaCell_doesNothing() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellFormula("3");
cell.setCellValue("foo");
cell.setCellType(CellType.FORMULA);
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.STRING, cell.getCachedFormulaResultType());
assertEquals("foo", cell.getStringCellValue());
}
}
@Test
void setCellFormulaWithDateArithmetic() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cellA1 = getInstance(wb);
cellA1.setCellFormula("B1+1");
LocalDate date = LocalDate.parse("2021-07-31");
Cell cellB1 = cellA1.getRow().createCell(1);
cellB1.setCellValue(date);
assertEquals(CellType.FORMULA, cellA1.getCellType());
assertEquals(CellType.NUMERIC, cellA1.getCachedFormulaResultType());
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
CellValue valueA1 = fe.evaluate(cellA1);
assertEquals(CellType.NUMERIC, valueA1.getCellType());
assertEquals(DateUtil.getExcelDate(date.plusDays(1)), valueA1.getNumberValue());
}
}
@Test
void testZeroDate() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cellA1 = getInstance(wb);
cellA1.setCellValue(1.0);
assertEquals(LocalDate.parse("1900-01-01"),
cellA1.getLocalDateTimeCellValue().toLocalDate());
cellA1.setCellValue(0.0);
// this value is not strictly correct but our time only support relies on this
// time only means cells that have values with just times but no date parts
assertEquals(LocalDate.parse("1899-12-31"),
cellA1.getLocalDateTimeCellValue().toLocalDate());
cellA1.setCellValue(0.5);
assertEquals(LocalTime.parse("12:00"),
cellA1.getLocalDateTimeCellValue().toLocalTime());
}
}
@Test
protected void setCellType_FORMULA_onAnArrayFormulaCell_doesNothing() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.getSheet().setArrayFormula("3", CellRangeAddress.valueOf("A1:A2"));
cell.setCellValue("foo");
cell.setCellType(CellType.FORMULA);
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals(CellType.STRING, cell.getCachedFormulaResultType());
assertEquals("foo", cell.getStringCellValue());
}
}
@Test
public final void setBlank_delegatesTo_setCellType_BLANK() {
Cell cell = mock(CellBase.class);
doCallRealMethod().when(cell).setBlank();
cell.setBlank();
verify(cell).setBlank();
}
@Test
protected void setCellNullString() throws IOException {
try (Workbook wb = _testDataProvider.createWorkbook()) {
Cell cell = getInstance(wb);
cell.setCellValue((String)null);
// setting string "null" leads to a BLANK cell
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals("", cell.getStringCellValue());
assertEquals("", cell.toString());
cell.setCellType(CellType.STRING);
// forcing to string type leads to STRING cell, but still empty strings
assertEquals(CellType.STRING, cell.getCellType());
assertEquals("", cell.getStringCellValue());
assertEquals("", cell.toString());
try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb)) {
// read first sheet, first row, first cell
Cell cellBack = wb2.iterator().next().iterator().next().iterator().next();
assertEquals(CellType.STRING, cell.getCellType());
assertEquals("", cell.getStringCellValue());
assertEquals("", cell.toString());
}
}
}
private Cell getInstance(Workbook wb) {
return wb.createSheet().createRow(0).createCell(0);
}
}