TestXSSFCell.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.xssf.usermodel;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
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.time.LocalDateTime;
import java.util.List;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.HSSFITestDataProvider;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.tests.usermodel.BaseTestXCell;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.SXSSFITestDataProvider;
import org.apache.poi.xssf.XSSFITestDataProvider;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.EnumSource;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
public final class TestXSSFCell extends BaseTestXCell {
public TestXSSFCell() {
super(XSSFITestDataProvider.instance);
}
/**
* Bug 47026: trouble changing cell type when workbook doesn't contain
* Shared String Table
*/
@Test
void test47026_1() throws IOException {
try (Workbook wb = _testDataProvider.openSampleWorkbook("47026.xlsm")) {
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultType());
cell.setCellValue("456");
assertEquals(CellType.STRING, cell.getCachedFormulaResultType());
}
}
@Test
void test47026_2() throws IOException {
try (Workbook wb = _testDataProvider.openSampleWorkbook("47026.xlsm")) {
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultType());
cell.setCellFormula(null);
IllegalStateException e = assertThrows(IllegalStateException.class, cell::getCachedFormulaResultType);
assertEquals("Only formula cells have cached results", e.getMessage());
cell.setCellValue("456");
assertEquals(CellType.STRING, cell.getCellType());
}
}
/**
* Test that we can read inline strings that are expressed directly in the cell definition
* instead of implementing the shared string table.
*
* Some programs, for example, Microsoft Excel Driver for .xlsx insert inline string
* instead of using the shared string table. See bug 47206
*/
@Test
void testInlineString() throws IOException {
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("xlsx-jdbc.xlsx");
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row = sheet.getRow(1);
XSSFCell cell_0 = row.getCell(0);
assertEquals(STCellType.INT_INLINE_STR, cell_0.getCTCell().getT().intValue());
assertTrue(cell_0.getCTCell().isSetIs());
assertEquals("A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA", cell_0.getStringCellValue());
XSSFCell cell_1 = row.getCell(1);
assertEquals(STCellType.INT_INLINE_STR, cell_1.getCTCell().getT().intValue());
assertTrue(cell_1.getCTCell().isSetIs());
assertEquals("foo", cell_1.getStringCellValue());
XSSFCell cell_2 = row.getCell(2);
assertEquals(STCellType.INT_INLINE_STR, cell_2.getCTCell().getT().intValue());
assertTrue(cell_2.getCTCell().isSetIs());
assertEquals("bar", row.getCell(2).getStringCellValue());
wb.close();
}
/**
* Bug 47278 - xsi:nil attribute for <t> tag caused Excel 2007 to fail to open workbook
*/
@Test
void test47278() throws IOException {
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.createWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
SharedStringsTable sst = wb.getSharedStringSource();
assertEquals(0, sst.getCount());
//case 1. cell.setCellValue(new XSSFRichTextString((String)null));
Cell cell_0 = row.createCell(0);
RichTextString str = new XSSFRichTextString((String)null);
assertNull(str.getString());
cell_0.setCellValue(str);
assertEquals(0, sst.getCount());
assertEquals(CellType.BLANK, cell_0.getCellType(), "Having: " + cell_0);
//case 2. cell.setCellValue((String)null);
Cell cell_1 = row.createCell(1);
cell_1.setCellValue((String)null);
assertEquals(0, sst.getCount());
assertEquals(CellType.BLANK, cell_1.getCellType(), "Having: " + cell_1);
//case 3. cell.setCellValue((RichTextString)null);
Cell cell_2 = row.createCell(2);
cell_2.setCellValue((RichTextString) null);
assertEquals(0, sst.getCount());
assertEquals(CellType.BLANK, cell_2.getCellType(), "Having: " + cell_2);
wb.close();
}
@Test
void testFormulaString() throws IOException {
try (XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.createWorkbook()) {
XSSFCell cell = wb.createSheet().createRow(0).createCell(0);
CTCell ctCell = cell.getCTCell(); //low-level bean holding cell's xml
cell.setCellFormula("A2");
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals("A2", cell.getCellFormula());
//the value is not set and cell's type='N' which means blank
assertEquals(STCellType.N, ctCell.getT());
//set cached formula value
cell.setCellValue("t='str'");
//we are still of 'formula' type
assertEquals(CellType.FORMULA, cell.getCellType());
assertEquals("A2", cell.getCellFormula());
//cached formula value is set and cell's type='STR'
assertEquals(STCellType.STR, ctCell.getT());
assertEquals("t='str'", cell.getStringCellValue());
//now remove the formula, the cached formula result remains
cell.setCellFormula(null);
assertEquals(CellType.STRING, cell.getCellType());
assertEquals(STCellType.STR, ctCell.getT());
//the line below failed prior to fix of Bug #47889
assertEquals("t='str'", cell.getStringCellValue());
//revert to a blank cell
cell.setCellValue((String) null);
assertEquals(CellType.BLANK, cell.getCellType());
assertEquals(STCellType.N, ctCell.getT());
assertEquals("", cell.getStringCellValue());
// check behavior with setCellFormulaValidation
final String invalidFormula = "A", validFormula = "A2";
// check that default is true
assertTrue(wb.getCellFormulaValidation());
// check that valid formula does not throw exception
cell.setCellFormula(validFormula);
// check that invalid formula does throw exception
assertThrows(FormulaParseException.class, () -> cell.setCellFormula(invalidFormula));
// set cell formula validation to false
wb.setCellFormulaValidation(false);
assertFalse(wb.getCellFormulaValidation());
// check that neither valid nor invalid formula throw an exception
cell.setCellFormula(validFormula);
cell.setCellFormula(invalidFormula);
}
}
/**
* Bug 47889: problems when calling XSSFCell.getStringCellValue() on a workbook created in Gnumeric
*/
@Test
void test47889() throws IOException {
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("47889.xlsx");
XSSFSheet sh = wb.getSheetAt(0);
XSSFCell cell;
//try a string cell
cell = sh.getRow(0).getCell(0);
assertEquals(CellType.STRING, cell.getCellType());
assertEquals("a", cell.getStringCellValue());
assertEquals("a", cell.toString());
//Gnumeric produces spreadsheets without styles
//make sure we return null for that instead of throwing OutOfBounds
assertNull(cell.getCellStyle());
//try a numeric cell
cell = sh.getRow(1).getCell(0);
assertEquals(CellType.NUMERIC, cell.getCellType());
assertEquals(1.0, cell.getNumericCellValue(), 0);
assertEquals("1.0", cell.toString());
//Gnumeric produces spreadsheets without styles
//make sure we return null for that instead of throwing OutOfBounds
assertNull(cell.getCellStyle());
wb.close();
}
@Test
void testMissingRAttribute() throws IOException {
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell a1 = row.createCell(0);
a1.setCellValue("A1");
XSSFCell a2 = row.createCell(1);
a2.setCellValue("B1");
XSSFCell a4 = row.createCell(4);
a4.setCellValue("E1");
XSSFCell a6 = row.createCell(5);
a6.setCellValue("F1");
assertCellsWithMissingR(row);
a2.getCTCell().unsetR();
a6.getCTCell().unsetR();
assertCellsWithMissingR(row);
XSSFWorkbook wb2 = (XSSFWorkbook)_testDataProvider.writeOutAndReadBack(wb1);
row = wb2.getSheetAt(0).getRow(0);
assertCellsWithMissingR(row);
wb2.close();
wb1.close();
}
private void assertCellsWithMissingR(XSSFRow row){
XSSFCell a1 = row.getCell(0);
assertNotNull(a1);
XSSFCell a2 = row.getCell(1);
assertNotNull(a2);
XSSFCell a5 = row.getCell(4);
assertNotNull(a5);
XSSFCell a6 = row.getCell(5);
assertNotNull(a6);
assertEquals(6, row.getLastCellNum());
assertEquals(4, row.getPhysicalNumberOfCells());
assertEquals("A1", a1.getStringCellValue());
assertEquals("B1", a2.getStringCellValue());
assertEquals("E1", a5.getStringCellValue());
assertEquals("F1", a6.getStringCellValue());
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
assertEquals("A1", a1.getReference());
assertEquals("B1", a2.getReference());
assertEquals("E1", a5.getReference());
assertEquals("F1", a6.getReference());
}
@Test
void testMissingRAttributeBug54288() throws IOException {
// workbook with cells missing the R attribute
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("54288.xlsx");
// same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("54288-ref.xlsx");
XSSFSheet sheet = wb.getSheetAt(0);
XSSFSheet sheetRef = wbRef.getSheetAt(0);
assertEquals(sheetRef.getPhysicalNumberOfRows(), sheet.getPhysicalNumberOfRows());
// Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
// For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
// and assert that POI reads them equally:
DataFormatter formatter = new DataFormatter();
for (Row r : sheetRef) {
XSSFRow rowRef = (XSSFRow)r;
XSSFRow row = sheet.getRow(rowRef.getRowNum());
assertEquals(rowRef.getPhysicalNumberOfCells(), row.getPhysicalNumberOfCells(),
"number of cells in row["+row.getRowNum()+"]");
for(Cell c : rowRef){
XSSFCell cellRef = (XSSFCell)c;
XSSFCell cell = row.getCell(cellRef.getColumnIndex());
assertEquals(cellRef.getColumnIndex(), cell.getColumnIndex());
assertEquals(cellRef.getReference(), cell.getReference());
if(!cell.getCTCell().isSetR()){
assertTrue(cellRef.getCTCell().isSetR(), "R must e set in cellRef");
String valRef = formatter.formatCellValue(cellRef);
String val = formatter.formatCellValue(cell);
assertEquals(valRef, val);
}
}
}
wbRef.close();
wb.close();
}
@Test
void test56170() throws IOException {
final Workbook wb1 = XSSFTestDataSamples.openSampleWorkbook("56170.xlsx");
final XSSFSheet sheet = (XSSFSheet) wb1.getSheetAt(0);
Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
Cell cell;
// add some contents to table so that the table will need expansion
Row row = sheet.getRow(0);
Workbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
cell = row.createCell(0);
Workbook wb4 = XSSFTestDataSamples.writeOutAndReadBack(wb3);
cell.setCellValue("demo1");
Workbook wb5 = XSSFTestDataSamples.writeOutAndReadBack(wb4);
cell = row.createCell(1);
Workbook wb6 = XSSFTestDataSamples.writeOutAndReadBack(wb5);
cell.setCellValue("demo2");
Workbook wb7 = XSSFTestDataSamples.writeOutAndReadBack(wb6);
cell = row.createCell(2);
Workbook wb8 = XSSFTestDataSamples.writeOutAndReadBack(wb7);
cell.setCellValue("demo3");
Workbook wb9 = XSSFTestDataSamples.writeOutAndReadBack(wb8);
row = sheet.getRow(1);
cell = row.createCell(0);
cell.setCellValue("demo1");
cell = row.createCell(1);
cell.setCellValue("demo2");
cell = row.createCell(2);
cell.setCellValue("demo3");
Workbook wb10 = XSSFTestDataSamples.writeOutAndReadBack(wb9);
// expand table
XSSFTable table = sheet.getTables().get(0);
final CellReference startRef = table.getStartCellReference();
final CellReference endRef = table.getEndCellReference();
table.getCTTable().setRef(new CellRangeAddress(startRef.getRow(), 1, startRef.getCol(), endRef.getCol()).formatAsString());
Workbook wb11 = XSSFTestDataSamples.writeOutAndReadBack(wb10);
assertNotNull(wb11);
wb11.close();
wb10.close();
wb9.close();
wb8.close();
wb7.close();
wb6.close();
wb5.close();
wb4.close();
wb3.close();
wb2.close();
wb1.close();
}
@Test
void test56170Reproduce() throws IOException {
try (Workbook wb = new XSSFWorkbook()) {
final Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
// by creating Cells out of order we trigger the handling in onDocumentWrite()
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(0);
validateRow(row);
validateRow(row);
// once again with removing one cell
row.removeCell(cell1);
validateRow(row);
// once again with removing the same cell, this throws an exception
assertThrows(IllegalArgumentException.class, () -> row.removeCell(cell1));
// now check again
validateRow(row);
// once again with removing one cell
row.removeCell(cell2);
// now check again
validateRow(row);
}
}
private void validateRow(Row row) {
// trigger bug with CArray handling
((XSSFRow)row).onDocumentWrite();
for(Cell cell : row) {
assertNotNull(cell.toString());
}
}
@ParameterizedTest
@EnumSource(value = MissingCellPolicy.class)
void testBug56644ReturnNull(MissingCellPolicy policy) throws IOException {
try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56644.xlsx")) {
wb.setMissingCellPolicy(policy);
Sheet sheet = wb.getSheet("samplelist");
Row row = sheet.getRow(20);
switch (policy) {
case CREATE_NULL_AS_BLANK: {
Cell cell = row.getCell(2);
assertNotNull(cell);
assertEquals(CellType.STRING, cell.getCellType());
break;
}
case RETURN_BLANK_AS_NULL: {
Cell cell = row.getCell(2);
assertNotNull(cell);
assertEquals(CellType.STRING, cell.getCellType());
cell.setBlank();
cell = row.getCell(2);
assertNull(cell);
break;
}
case RETURN_NULL_AND_BLANK: {
Cell cell = row.getCell(2);
assertNotNull(cell);
break;
}
}
}
}
@Test
void testEncodingBelowAscii() throws IOException {
StringBuilder sb = new StringBuilder();
// test all possible characters
for(int i = 0; i < Character.MAX_VALUE; i++) {
sb.append((char)i);
}
String strAll = sb.toString();
// process in chunks as we have a limit on size of column now
int pos = 0;
while(pos < strAll.length()) {
String str = strAll.substring(pos, Math.min(strAll.length(), pos+SpreadsheetVersion.EXCEL2007.getMaxTextLength()));
Workbook wb = HSSFITestDataProvider.instance.createWorkbook();
Cell cell = wb.createSheet().createRow(0).createCell(0);
Workbook xwb = XSSFITestDataProvider.instance.createWorkbook();
Cell xCell = xwb.createSheet().createRow(0).createCell(0);
Workbook swb = SXSSFITestDataProvider.instance.createWorkbook();
Cell sCell = swb.createSheet().createRow(0).createCell(0);
cell.setCellValue(str);
assertEquals(str, cell.getStringCellValue());
xCell.setCellValue(str);
assertEquals(str, xCell.getStringCellValue());
sCell.setCellValue(str);
assertEquals(str, sCell.getStringCellValue());
Workbook wbBack = HSSFITestDataProvider.instance.writeOutAndReadBack(wb);
Workbook xwbBack = XSSFITestDataProvider.instance.writeOutAndReadBack(xwb);
Workbook swbBack = SXSSFITestDataProvider.instance.writeOutAndReadBack(swb);
cell = wbBack.getSheetAt(0).createRow(0).createCell(0);
xCell = xwbBack.getSheetAt(0).createRow(0).createCell(0);
sCell = swbBack.getSheetAt(0).createRow(0).createCell(0);
assertEquals(cell.getStringCellValue(), xCell.getStringCellValue());
assertEquals(cell.getStringCellValue(), sCell.getStringCellValue());
pos += SpreadsheetVersion.EXCEL97.getMaxTextLength();
swbBack.close();
xwbBack.close();
wbBack.close();
swb.close();
xwb.close();
wb.close();
}
}
private XSSFCell srcCell, destCell; //used for testCopyCellFrom_CellCopyPolicy
@Test
public final void testCopyCellFrom_CellCopyPolicy_default() {
setUp_testCopyCellFrom_CellCopyPolicy();
// default copy policy
final CellCopyPolicy policy = new CellCopyPolicy();
destCell.copyCellFrom(srcCell, policy);
assertEquals(CellType.FORMULA, destCell.getCellType());
assertEquals("2+3", destCell.getCellFormula());
assertEquals(srcCell.getCellStyle(), destCell.getCellStyle());
}
@Test
public final void testCopyCellFrom_CellCopyPolicy_value() {
setUp_testCopyCellFrom_CellCopyPolicy();
// Paste values only
final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(false).build();
destCell.copyCellFrom(srcCell, policy);
assertEquals(CellType.NUMERIC, destCell.getCellType());
}
@Test
public final void testCopyCellFrom_CellCopyPolicy_formulaWithUnregisteredUDF() {
setUp_testCopyCellFrom_CellCopyPolicy();
srcCell.setCellFormula("MYFUNC2(123, $A5, Sheet1!$B7)");
// Copy formula verbatim (no shifting). This is okay because copyCellFrom is Internal.
// Users should use higher-level copying functions to row- or column-shift formulas.
final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(true).build();
destCell.copyCellFrom(srcCell, policy);
assertEquals("MYFUNC2(123, $A5, Sheet1!$B7)", destCell.getCellFormula());
}
@Test
public final void testCopyCellFrom_CellCopyPolicy_style() {
setUp_testCopyCellFrom_CellCopyPolicy();
srcCell.setCellValue((String) null);
// Paste styles only
final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellValue(false).build();
destCell.copyCellFrom(srcCell, policy);
assertEquals(srcCell.getCellStyle(), destCell.getCellStyle());
// Old cell value should not have been overwritten
assertNotEquals(CellType.BLANK, destCell.getCellType());
assertEquals(CellType.BOOLEAN, destCell.getCellType());
assertTrue(destCell.getBooleanCellValue());
}
@Test
public final void testCopyCellFrom_CellCopyPolicy_copyHyperlink() throws IOException {
setUp_testCopyCellFrom_CellCopyPolicy();
final Workbook wb = srcCell.getSheet().getWorkbook();
final CreationHelper createHelper = wb.getCreationHelper();
srcCell.setCellValue("URL LINK");
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://poi.apache.org/");
srcCell.setHyperlink(link);
// Set link cell style (optional)
setLinkCellStyle(wb, srcCell);
// Copy hyperlink
final CellCopyPolicy policy = new CellCopyPolicy.Builder().copyHyperlink(true).mergeHyperlink(false).build();
destCell.copyCellFrom(srcCell, policy);
assertNotNull(destCell.getHyperlink());
assertSame(srcCell.getSheet(), destCell.getSheet(),
"unit test assumes srcCell and destCell are on the same sheet");
final List<XSSFHyperlink> links = srcCell.getSheet().getHyperlinkList();
assertEquals(2, links.size(), "number of hyperlinks on sheet");
assertEquals(new CellAddress(srcCell).formatAsString(), links.get(0).getCellRef(), "source hyperlink");
assertEquals(new CellAddress(destCell).formatAsString(), links.get(1).getCellRef(), "destination hyperlink");
wb.close();
}
private void setLinkCellStyle(Workbook wb, XSSFCell srcCell) {
CellStyle hlinkStyle = wb.createCellStyle();
Font hlinkFont = wb.createFont();
hlinkFont.setUnderline(Font.U_SINGLE);
hlinkFont.setColor(IndexedColors.BLUE.getIndex());
hlinkStyle.setFont(hlinkFont);
srcCell.setCellStyle(hlinkStyle);
}
@Test
public final void testCopyCellFrom_CellCopyPolicy_mergeHyperlink() throws IOException {
setUp_testCopyCellFrom_CellCopyPolicy();
final Workbook wb = srcCell.getSheet().getWorkbook();
final CreationHelper createHelper = wb.getCreationHelper();
srcCell.setCellValue("URL LINK");
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://poi.apache.org/");
destCell.setHyperlink(link);
// Set link cell style (optional)
setLinkCellStyle(wb, destCell);
// Pre-condition assumptions. This test is broken if either of these fail.
assertSame(srcCell.getSheet(), destCell.getSheet(),
"unit test assumes srcCell and destCell are on the same sheet");
assertNull(srcCell.getHyperlink());
// Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared).
final CellCopyPolicy policy = new CellCopyPolicy.Builder().mergeHyperlink(true).copyHyperlink(false).build();
destCell.copyCellFrom(srcCell, policy);
assertNull(srcCell.getHyperlink());
assertNotNull(destCell.getHyperlink());
assertSame(link, destCell.getHyperlink());
List<XSSFHyperlink> links;
links = srcCell.getSheet().getHyperlinkList();
assertEquals(1, links.size(), "number of hyperlinks on sheet");
assertEquals(new CellAddress(destCell).formatAsString(), links.get(0).getCellRef(), "source hyperlink");
// Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell.
srcCell.copyCellFrom(destCell, policy);
assertNotNull(srcCell.getHyperlink());
assertNotNull(destCell.getHyperlink());
links = srcCell.getSheet().getHyperlinkList();
assertEquals(2, links.size(), "number of hyperlinks on sheet");
assertEquals(new CellAddress(destCell).formatAsString(), links.get(0).getCellRef(), "dest hyperlink");
assertEquals(new CellAddress(srcCell).formatAsString(), links.get(1).getCellRef(), "source hyperlink");
wb.close();
}
private void setUp_testCopyCellFrom_CellCopyPolicy() {
@SuppressWarnings("resource")
final XSSFWorkbook wb = new XSSFWorkbook();
final XSSFRow row = wb.createSheet("Sheet1").createRow(0);
srcCell = row.createCell(0);
destCell = row.createCell(1);
srcCell.setCellFormula("2+3");
final CellStyle style = wb.createCellStyle();
style.setBorderTop(BorderStyle.THICK);
style.setFillBackgroundColor((short) 5);
srcCell.setCellStyle(style);
destCell.setCellValue(true);
}
/**
* Bug 61869: updating a shared formula produces an unreadable file
*/
@Test
void test61869() throws Exception {
try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("61869.xlsx")) {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCell c2 = sheet.getRow(1).getCell(2);
assertEquals("SUM(A2,B2)", c2.getCellFormula());
assertEquals(STCellFormulaType.SHARED, c2.getCTCell().getF().getT());
assertEquals(0, c2.getCTCell().getF().getSi());
XSSFCell c3 = sheet.getRow(2).getCell(2);
assertEquals(STCellFormulaType.SHARED, c3.getCTCell().getF().getT());
assertEquals(0, c3.getCTCell().getF().getSi());
assertEquals("SUM(A3,B3)", c3.getCellFormula());
assertEquals("SUM(A2,B2)", sheet.getSharedFormula(0).getStringValue());
c2.setCellFormula("SUM(A2:B2)");
assertEquals(STCellFormulaType.SHARED, c2.getCTCell().getF().getT()); // c2 remains the master formula
assertEquals("SUM(A2:B2)", sheet.getSharedFormula(0).getStringValue());
assertEquals(STCellFormulaType.SHARED, c3.getCTCell().getF().getT());
assertEquals(0, c3.getCTCell().getF().getSi());
assertEquals("SUM(A3:B3)", c3.getCellFormula()); // formula in the follower cell is rebuilt
}
}
@Test
void testBug58106RemoveSharedFormula() throws Exception {
try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("58106.xlsx")) {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row = sheet.getRow(12);
XSSFCell cell = row.getCell(1);
CTCellFormula f = cell.getCTCell().getF();
assertEquals("B13:G13", f.getRef());
assertEquals("SUM(B1:B3)", f.getStringValue());
assertEquals(0, f.getSi());
assertEquals(STCellFormulaType.SHARED, f.getT());
for(char i = 'C'; i <= 'G'; i++){
XSSFCell sc =row.getCell(i-'A');
CTCellFormula sf = sc.getCTCell().getF();
assertFalse(sf.isSetRef());
assertEquals("", sf.getStringValue());
assertEquals(0, sf.getSi());
assertEquals(STCellFormulaType.SHARED, sf.getT());
}
assertEquals("B13:G13", sheet.getSharedFormula(0).getRef());
cell.setCellType(CellType.NUMERIC);
assertFalse(cell.getCTCell().isSetF());
XSSFCell nextFormulaMaster = row.getCell(2);
assertEquals("C13:G13", nextFormulaMaster.getCTCell().getF().getRef());
assertEquals("SUM(C1:C3)", nextFormulaMaster.getCTCell().getF().getStringValue());
assertEquals(0, nextFormulaMaster.getCTCell().getF().getSi());
for(char i = 'D'; i <= 'G'; i++){
XSSFCell sc =row.getCell(i-'A');
CTCellFormula sf = sc.getCTCell().getF();
assertFalse(sf.isSetRef());
assertEquals("", sf.getStringValue());
assertEquals(0, sf.getSi());
assertEquals(STCellFormulaType.SHARED, sf.getT());
}
assertEquals("C13:G13", sheet.getSharedFormula(0).getRef());
}
}
@Test
void getErrorCellValue_returns0_onABlankCell() {
Cell cell = new XSSFWorkbook().createSheet().createRow(0).createCell(0);
assertThrows(IllegalStateException.class, cell::getErrorCellValue);
}
@Test
void setStringToBlank() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("test123");
cell.setCellType(CellType.BLANK);
assertEquals("", cell.getStringCellValue());
}
}
@Test
void setInlineStringToNewStringValue() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
setInlineString(cell, "text123");
cell.setCellValue("newValue");
assertEquals("newValue", cell.getStringCellValue());
assertFalse(cell.getCTCell().isSetIs(), "cell has InlineString XML struct still?");
}
}
@Test
void setInlineStringToNewNumberValue() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
setInlineString(cell, "text123");
cell.setCellValue(123.456d);
assertEquals(123.456d, cell.getNumericCellValue());
assertFalse(cell.getCTCell().isSetIs(), "cell has InlineString XML struct still?");
}
}
@Test
void setInlineStringToNewLocalDateValue() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
setInlineString(cell, "text123");
LocalDateTime ldt = LocalDateTime.parse("2025-12-18T19:01:34");
cell.setCellValue(ldt);
assertEquals(ldt, cell.getLocalDateTimeCellValue());
assertFalse(cell.getCTCell().isSetIs(), "cell has InlineString XML struct still?");
}
}
@Test
void setInlineStringToBlank() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
setInlineString(cell, "text123");
cell.setCellType(CellType.BLANK);
assertEquals("", cell.getStringCellValue());
assertFalse(cell.getCTCell().isSetIs(), "cell has InlineString XML struct still?");
}
}
@Test
void setFormulaToBlank() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet();
XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellFormula("ISODD(1)");
cell.setCellValue("3.14");
assertTrue(cell.getCTCell().isSetF(), "cell has formula XML struct?");
assertEquals("ISODD(1)", cell.getCellFormula());
assertEquals("3.14", cell.getStringCellValue());
cell.setCellType(CellType.BLANK);
assertEquals("", cell.getStringCellValue());
assertFalse(cell.getCTCell().isSetF(), "cell has formula XML struct still?");
}
}
private static void setInlineString(XSSFCell cell, String text) {
cell.setCellType(CellType.STRING);
CTRst rst = CTRst.Factory.newInstance();
rst.setT(text);
cell.getCTCell().setT(STCellType.INLINE_STR);
cell.getCTCell().setIs(rst);
assertEquals(text, cell.getStringCellValue());
}
}