diff options
Diffstat (limited to 'src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java')
| -rw-r--r-- | src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java | 3642 |
1 files changed, 0 insertions, 3642 deletions
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java deleted file mode 100644 index 562fa9f246..0000000000 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ /dev/null @@ -1,3642 +0,0 @@ -/* ==================================================================== - 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 java.time.Duration.between; -import static java.time.Instant.now; -import static org.apache.logging.log4j.util.Unbox.box; -import static org.apache.poi.extractor.ExtractorFactory.OOXML_PACKAGE; -import static org.apache.poi.openxml4j.opc.TestContentType.isOldXercesActive; -import static org.junit.jupiter.api.Assertions.*; -import static org.junit.jupiter.api.Assumptions.assumeFalse; - -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; -import java.io.File; -import java.io.FileInputStream; -import java.io.FileOutputStream; -import java.io.IOException; -import java.io.InputStream; -import java.io.OutputStream; -import java.nio.charset.StandardCharsets; -import java.time.Instant; -import java.util.Arrays; -import java.util.Calendar; -import java.util.HashMap; -import java.util.HashSet; -import java.util.List; -import java.util.Map; -import java.util.Set; -import java.util.TimeZone; -import java.util.TreeMap; - -import org.apache.commons.compress.archivers.zip.ZipArchiveEntry; -import org.apache.commons.compress.archivers.zip.ZipFile; -import org.apache.logging.log4j.LogManager; -import org.apache.logging.log4j.Logger; -import org.apache.poi.POIDataSamples; -import org.apache.poi.common.usermodel.HyperlinkType; -import org.apache.poi.hssf.HSSFITestDataProvider; -import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ooxml.POIXMLDocumentPart; -import org.apache.poi.ooxml.POIXMLDocumentPart.RelationPart; -import org.apache.poi.ooxml.POIXMLException; -import org.apache.poi.ooxml.POIXMLProperties; -import org.apache.poi.ooxml.util.DocumentHelper; -import org.apache.poi.openxml4j.exceptions.InvalidFormatException; -import org.apache.poi.openxml4j.exceptions.InvalidOperationException; -import org.apache.poi.openxml4j.exceptions.OpenXML4JException; -import org.apache.poi.openxml4j.opc.OPCPackage; -import org.apache.poi.openxml4j.opc.PackageAccess; -import org.apache.poi.openxml4j.opc.PackagePart; -import org.apache.poi.openxml4j.opc.PackageRelationship; -import org.apache.poi.openxml4j.opc.PackagingURIHelper; -import org.apache.poi.openxml4j.util.ZipSecureFile; -import org.apache.poi.poifs.filesystem.DirectoryNode; -import org.apache.poi.poifs.filesystem.DocumentEntry; -import org.apache.poi.poifs.filesystem.DocumentInputStream; -import org.apache.poi.poifs.filesystem.POIFSFileSystem; -import org.apache.poi.sl.usermodel.ObjectShape; -import org.apache.poi.sl.usermodel.Slide; -import org.apache.poi.sl.usermodel.SlideShow; -import org.apache.poi.sl.usermodel.SlideShowFactory; -import org.apache.poi.ss.ITestDataProvider; -import org.apache.poi.ss.SpreadsheetVersion; -import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; -import org.apache.poi.ss.formula.EvaluationConditionalFormatRule; -import org.apache.poi.ss.formula.FormulaParser; -import org.apache.poi.ss.formula.FormulaRenderer; -import org.apache.poi.ss.formula.FormulaShifter; -import org.apache.poi.ss.formula.FormulaType; -import org.apache.poi.ss.formula.WorkbookEvaluator; -import org.apache.poi.ss.formula.WorkbookEvaluatorProvider; -import org.apache.poi.ss.formula.eval.ErrorEval; -import org.apache.poi.ss.formula.eval.NumberEval; -import org.apache.poi.ss.formula.eval.ValueEval; -import org.apache.poi.ss.formula.functions.Function; -import org.apache.poi.ss.formula.ptg.Ptg; -import org.apache.poi.ss.usermodel.*; -import org.apache.poi.ss.util.AreaReference; -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.ss.util.CellUtil; -import org.apache.poi.util.LocaleUtil; -import org.apache.poi.util.NullOutputStream; -import org.apache.poi.util.TempFile; -import org.apache.poi.util.XMLHelper; -import org.apache.poi.xssf.SXSSFITestDataProvider; -import org.apache.poi.xssf.XLSBUnsupportedException; -import org.apache.poi.xssf.XSSFITestDataProvider; -import org.apache.poi.xssf.XSSFTestDataSamples; -import org.apache.poi.xssf.model.CalculationChain; -import org.apache.poi.xssf.streaming.SXSSFWorkbook; -import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill; -import org.apache.xmlbeans.XmlException; -import org.junit.jupiter.api.Disabled; -import org.junit.jupiter.api.Test; -import org.junit.jupiter.params.ParameterizedTest; -import org.junit.jupiter.params.provider.CsvSource; -import org.junit.jupiter.params.provider.EnumSource; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcCell; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontImpl; -import org.xml.sax.InputSource; -import org.xml.sax.SAXParseException; -import org.xml.sax.XMLReader; - -public final class TestXSSFBugs extends BaseTestBugzillaIssues { - private static final Logger LOG = LogManager.getLogger(TestXSSFBugs.class); - - public TestXSSFBugs() { - super(XSSFITestDataProvider.instance); - } - - /** - * Named ranges had the right reference, but - * the wrong sheet name - */ - @Test - void bug45430() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("45430.xlsx")) { - assertFalse(wb.isMacroEnabled()); - assertEquals(3, wb.getNumberOfNames()); - - assertEquals(0, wb.getName("SheetAA1").getCTName().getLocalSheetId()); - assertFalse(wb.getName("SheetAA1").getCTName().isSetLocalSheetId()); - assertEquals("SheetA!$A$1", wb.getName("SheetAA1").getRefersToFormula()); - assertEquals("SheetA", wb.getName("SheetAA1").getSheetName()); - - assertEquals(0, wb.getName("SheetBA1").getCTName().getLocalSheetId()); - assertFalse(wb.getName("SheetBA1").getCTName().isSetLocalSheetId()); - assertEquals("SheetB!$A$1", wb.getName("SheetBA1").getRefersToFormula()); - assertEquals("SheetB", wb.getName("SheetBA1").getSheetName()); - - assertEquals(0, wb.getName("SheetCA1").getCTName().getLocalSheetId()); - assertFalse(wb.getName("SheetCA1").getCTName().isSetLocalSheetId()); - assertEquals("SheetC!$A$1", wb.getName("SheetCA1").getRefersToFormula()); - assertEquals("SheetC", wb.getName("SheetCA1").getSheetName()); - - // Save and re-load, still there - try (XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - assertEquals(3, nwb.getNumberOfNames()); - assertEquals("SheetA!$A$1", nwb.getName("SheetAA1").getRefersToFormula()); - } - } - } - - /** - * We should carry vba macros over after save - */ - @Test - void bug45431() throws IOException, InvalidFormatException { - XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("45431.xlsm"); - OPCPackage pkg1 = wb1.getPackage(); - assertTrue(wb1.isMacroEnabled()); - - // Check the various macro related bits can be found - PackagePart vba = pkg1.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - // And the drawing bit - PackagePart drw = pkg1.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - - // Save and re-open, both still there - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - pkg1.close(); - wb1.close(); - - OPCPackage pkg2 = wb2.getPackage(); - assertTrue(wb2.isMacroEnabled()); - - vba = pkg2.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - drw = pkg2.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - // And again, just to be sure - XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); - pkg2.close(); - wb2.close(); - OPCPackage pkg3 = wb3.getPackage(); - assertTrue(wb3.isMacroEnabled()); - - vba = pkg3.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - drw = pkg3.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - pkg3.close(); - wb3.close(); - } - - @Test - void bug47504() throws IOException { - XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("47504.xlsx"); - assertEquals(1, wb1.getNumberOfSheets()); - XSSFSheet sh = wb1.getSheetAt(0); - XSSFDrawing drawing = sh.createDrawingPatriarch(); - List<RelationPart> rels = drawing.getRelationParts(); - assertEquals(1, rels.size()); - assertEquals("Sheet1!A1", rels.get(0).getRelationship().getTargetURI().getFragment()); - - // And again, just to be sure - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - assertEquals(1, wb2.getNumberOfSheets()); - sh = wb2.getSheetAt(0); - drawing = sh.createDrawingPatriarch(); - rels = drawing.getRelationParts(); - assertEquals(1, rels.size()); - assertEquals("Sheet1!A1", rels.get(0).getRelationship().getTargetURI().getFragment()); - wb2.close(); - } - - /** - * Excel will sometimes write a button with a textbox - * containing >br< (not closed!). - * Clearly Excel shouldn't do this, but test that we can - * read the file despite the naughtiness - */ - @Test - void bug49020() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("BrNotClosed.xlsx")) { - assertNotNull(wb); - } - } - - /** - * ensure that CTPhoneticPr is loaded by the ooxml test suite so that it is included in poi-ooxml-lite - */ - @Test - void bug49325() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("49325.xlsx")) { - CTWorksheet sh = wb.getSheetAt(0).getCTWorksheet(); - assertNotNull(sh.getPhoneticPr()); - } - } - - /** - * Names which are defined with a Sheet - * should return that sheet index properly - */ - @Test - void bug48923() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48923.xlsx")) { - assertEquals(4, wb.getNumberOfNames()); - - Name b1 = wb.getName("NameB1"); - Name b2 = wb.getName("NameB2"); - Name sheet2 = wb.getName("NameSheet2"); - Name test = wb.getName("Test"); - - assertNotNull(b1); - assertEquals("NameB1", b1.getNameName()); - assertEquals("Sheet1", b1.getSheetName()); - assertEquals(-1, b1.getSheetIndex()); - assertFalse(b1.isDeleted()); - assertFalse(b1.isHidden()); - - assertNotNull(b2); - assertEquals("NameB2", b2.getNameName()); - assertEquals("Sheet1", b2.getSheetName()); - assertEquals(-1, b2.getSheetIndex()); - assertFalse(b2.isDeleted()); - assertFalse(b2.isHidden()); - - assertNotNull(sheet2); - assertEquals("NameSheet2", sheet2.getNameName()); - assertEquals("Sheet2", sheet2.getSheetName()); - assertEquals(-1, sheet2.getSheetIndex()); - - assertNotNull(test); - assertEquals("Test", test.getNameName()); - assertEquals("Sheet1", test.getSheetName()); - assertEquals(-1, test.getSheetIndex()); - } - } - - /** - * Problem with evaluation formulas due to - * NameXPtgs. - * Blows up on: - * IF(B6= (ROUNDUP(B6,0) + ROUNDDOWN(B6,0))/2, MROUND(B6,2),ROUND(B6,0)) - * <p> - * TODO: delete this test case when MROUND and VAR are implemented - */ - @Test - void bug48539() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48539.xlsx")) { - assertEquals(3, wb.getNumberOfSheets()); - assertEquals(0, wb.getNumberOfNames()); - - // Try each cell individually - XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb); - for (int i = 0; i < wb.getNumberOfSheets(); i++) { - Sheet s = wb.getSheetAt(i); - for (Row r : s) { - for (Cell c : r) { - if (c.getCellType() == CellType.FORMULA) { - CellValue cv = eval.evaluate(c); - - if (cv.getCellType() == CellType.NUMERIC) { - // assert that the calculated value agrees with - // the cached formula result calculated by Excel - String formula = c.getCellFormula(); - double cachedFormulaResult = c.getNumericCellValue(); - double evaluatedFormulaResult = cv.getNumberValue(); - assertEquals(cachedFormulaResult, evaluatedFormulaResult, 1E-7, formula); - } - } - } - } - } - - // Now all of them - XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); - } - } - - /** - * Foreground colours should be found even if - * a theme is used - */ - @Test - void bug48779() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48779.xlsx")) { - XSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0); - XSSFCellStyle cs = cell.getCellStyle(); - - assertNotNull(cs); - assertEquals(1, cs.getIndex()); - - // Look at the low level xml elements - assertEquals(2, cs.getCoreXf().getFillId()); - assertEquals(0, cs.getCoreXf().getXfId()); - assertTrue(cs.getCoreXf().getApplyFill()); - - XSSFCellFill fg = wb.getStylesSource().getFillAt(2); - assertNotNull(fg.getFillForegroundColor()); - assertEquals(0, fg.getFillForegroundColor().getIndexed()); - assertEquals(0.0, fg.getFillForegroundColor().getTint(), 0); - assertEquals("FFFF0000", fg.getFillForegroundColor().getARGBHex()); - assertNotNull(fg.getFillBackgroundColor()); - assertEquals(64, fg.getFillBackgroundColor().getIndexed()); - - // Now look higher up - assertNotNull(cs.getFillForegroundXSSFColor()); - assertEquals(0, cs.getFillForegroundColor()); - assertEquals("FFFF0000", cs.getFillForegroundXSSFColor().getARGBHex()); - assertEquals("FFFF0000", cs.getFillForegroundColorColor().getARGBHex()); - - assertEquals(64, cs.getFillBackgroundColor()); - assertNull(cs.getFillBackgroundXSSFColor().getARGBHex()); - assertNull(cs.getFillBackgroundColorColor().getARGBHex()); - } - } - - /** - * Ensure General and @ format are working properly - * for integers - */ - @Test - void bug47490() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx")) { - Sheet s = wb.getSheetAt(1); - Row r; - DataFormatter df = new DataFormatter(); - - r = s.getRow(1); - assertEquals(1.0, r.getCell(2).getNumericCellValue(), 0); - assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString()); - assertEquals("1", df.formatCellValue(r.getCell(2))); - assertEquals("1", df.formatRawCellContents(1.0, -1, "@")); - assertEquals("1", df.formatRawCellContents(1.0, -1, "General")); - - r = s.getRow(2); - assertEquals(12.0, r.getCell(2).getNumericCellValue(), 0); - assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString()); - assertEquals("12", df.formatCellValue(r.getCell(2))); - assertEquals("12", df.formatRawCellContents(12.0, -1, "@")); - assertEquals("12", df.formatRawCellContents(12.0, -1, "General")); - - r = s.getRow(3); - assertEquals(123.0, r.getCell(2).getNumericCellValue(), 0); - assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString()); - assertEquals("123", df.formatCellValue(r.getCell(2))); - assertEquals("123", df.formatRawCellContents(123.0, -1, "@")); - assertEquals("123", df.formatRawCellContents(123.0, -1, "General")); - } - } - - /** - * A problem file from a non-standard source (a scientific instrument that saves its - * output as an .xlsx file) that have two issues: - * 1. The Content Type part name is lower-case: [content_types].xml - * 2. The file appears to use backslashes as path separators - * <p> - * The OPC spec tolerates both of these peculiarities, so does POI - */ - @Test - void bug49609() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("49609.xlsx")) { - assertEquals("FAM", wb.getSheetName(0)); - assertEquals("Cycle", wb.getSheetAt(0).getRow(0).getCell(1).getStringCellValue()); - } - - } - - @Test - void bug49783() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("49783.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - Cell cell; - - cell = sheet.getRow(0).getCell(0); - assertEquals("#REF!*#REF!", cell.getCellFormula()); - assertEquals(CellType.ERROR, evaluator.evaluateInCell(cell).getCellType()); - assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString()); - - Name nm1 = wb.getName("sale_1"); - assertNotNull(nm1, "name sale_1 should be present"); - assertEquals("Sheet1!#REF!", nm1.getRefersToFormula()); - Name nm2 = wb.getName("sale_2"); - assertNotNull(nm2, "name sale_2 should be present"); - assertEquals("Sheet1!#REF!", nm2.getRefersToFormula()); - - cell = sheet.getRow(1).getCell(0); - assertEquals("sale_1*sale_2", cell.getCellFormula()); - assertEquals(CellType.ERROR, evaluator.evaluateInCell(cell).getCellType()); - assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString()); - } - } - - /** - * Creating a rich string of "hello world" and applying - * a font to characters 1-5 means we have two strings, - * "hello" and " world". As such, we need to apply - * preserve spaces to the 2nd bit, lest we end up - * with something like "helloworld" ! - */ - @Test - void bug49941() throws IOException { - XSSFWorkbook wb1 = new XSSFWorkbook(); - XSSFSheet s = wb1.createSheet(); - XSSFRow r = s.createRow(0); - XSSFCell c = r.createCell(0); - - // First without fonts - c.setCellValue( - new XSSFRichTextString(" with spaces ") - ); - assertEquals(" with spaces ", c.getRichStringCellValue().toString()); - assertEquals(0, c.getRichStringCellValue().getCTRst().sizeOfRArray()); - assertTrue(c.getRichStringCellValue().getCTRst().isSetT()); - // Should have the preserve set - assertEquals( - 1, - c.getRichStringCellValue().getCTRst().xgetT().getDomNode().getAttributes().getLength() - ); - assertEquals( - "preserve", - c.getRichStringCellValue().getCTRst().xgetT().getDomNode().getAttributes().item(0).getNodeValue() - ); - - // Save and check - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - assertEquals(" with spaces ", c.getRichStringCellValue().toString()); - assertEquals(0, c.getRichStringCellValue().getCTRst().sizeOfRArray()); - assertTrue(c.getRichStringCellValue().getCTRst().isSetT()); - - // Change the string - c.setCellValue( - new XSSFRichTextString("hello world") - ); - assertEquals("hello world", c.getRichStringCellValue().toString()); - // Won't have preserve - assertEquals( - 0, - c.getRichStringCellValue().getCTRst().xgetT().getDomNode().getAttributes().getLength() - ); - - // Apply a font - XSSFFont f = wb2.createFont(); - f.setBold(true); - c.getRichStringCellValue().applyFont(0, 5, f); - assertEquals("hello world", c.getRichStringCellValue().toString()); - // Does need preserving on the 2nd part - assertEquals(2, c.getRichStringCellValue().getCTRst().sizeOfRArray()); - assertEquals( - 0, - c.getRichStringCellValue().getCTRst().getRArray(0).xgetT().getDomNode().getAttributes().getLength() - ); - assertEquals( - 1, - c.getRichStringCellValue().getCTRst().getRArray(1).xgetT().getDomNode().getAttributes().getLength() - ); - assertEquals( - "preserve", - c.getRichStringCellValue().getCTRst().getRArray(1).xgetT().getDomNode().getAttributes().item(0).getNodeValue() - ); - - // Save and check - XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); - wb2.close(); - - s = wb3.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - assertEquals("hello world", c.getRichStringCellValue().toString()); - wb3.close(); - } - - /** - * Repeatedly writing the same file which has styles - */ - @Test - void bug49940() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("styles.xlsx")) { - assertEquals(3, wb.getNumberOfSheets()); - assertEquals(10, wb.getStylesSource().getNumCellStyles()); - - ByteArrayOutputStream b1 = new ByteArrayOutputStream(); - ByteArrayOutputStream b2 = new ByteArrayOutputStream(); - ByteArrayOutputStream b3 = new ByteArrayOutputStream(); - wb.write(b1); - wb.write(b2); - wb.write(b3); - - for (byte[] data : new byte[][]{ - b1.toByteArray(), b2.toByteArray(), b3.toByteArray() - }) { - ByteArrayInputStream bais = new ByteArrayInputStream(data); - XSSFWorkbook wb2 = new XSSFWorkbook(bais); - assertEquals(3, wb2.getNumberOfSheets()); - assertEquals(10, wb2.getStylesSource().getNumCellStyles()); - wb2.close(); - } - } - } - - /** - * Various ways of removing a cell formula should all zap the calcChain - * entry. - */ - @Test - void bug49966() throws IOException { - try (XSSFWorkbook wb1 = XSSFTestDataSamples - .openSampleWorkbook("shared_formulas.xlsx")) { - XSSFSheet sheet = wb1.getSheetAt(0); - - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - // CalcChain has lots of entries - CalculationChain cc = wb1.getCalculationChain(); - assertEquals("A2", cc.getCTCalcChain().getCArray(0).getR()); - assertEquals("A3", cc.getCTCalcChain().getCArray(1).getR()); - assertEquals("A4", cc.getCTCalcChain().getCArray(2).getR()); - assertEquals("A5", cc.getCTCalcChain().getCArray(3).getR()); - assertEquals("A6", cc.getCTCalcChain().getCArray(4).getR()); - assertEquals("A7", cc.getCTCalcChain().getCArray(5).getR()); - assertEquals("A8", cc.getCTCalcChain().getCArray(6).getR()); - assertEquals(40, cc.getCTCalcChain().sizeOfCArray()); - - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - // Try various ways of changing the formulas - // If it stays a formula, chain entry should remain - // Otherwise should go - sheet.getRow(1).getCell(0).setCellFormula("A1"); // stay - sheet.getRow(2).getCell(0).setCellFormula(null); // go - sheet.getRow(3).getCell(0).setCellFormula("14"); // stay - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - sheet.getRow(4).getCell(0).setBlank(); // go - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - validateCells(sheet); - sheet.getRow(5).removeCell(sheet.getRow(5).getCell(0)); // go - validateCells(sheet); - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - sheet.getRow(6).getCell(0).setBlank(); // go - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - sheet.getRow(7).getCell(0).setCellValue((String) null); // go - - XSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - - // Save and check - try (XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1)) { - wb1.close(); - assertEquals(35, cc.getCTCalcChain().sizeOfCArray()); - - cc = wb2.getCalculationChain(); - assertEquals("A2", cc.getCTCalcChain().getCArray(0).getR()); - assertEquals("A4", cc.getCTCalcChain().getCArray(1).getR()); - assertEquals("A9", cc.getCTCalcChain().getCArray(2).getR()); - } - } - } - - @Test - void bug49966Row() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples - .openSampleWorkbook("shared_formulas.xlsx")) { - XSSFSheet sheet = wb.getSheetAt(0); - - validateCells(sheet); - sheet.getRow(5).removeCell(sheet.getRow(5).getCell(0)); // go - validateCells(sheet); - } - } - - private void validateCells(XSSFSheet sheet) { - for (Row row : sheet) { - // trigger handling - ((XSSFRow) row).onDocumentWrite(); - } - } - - @Test - void bug49156() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("49156.xlsx")) { - FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - - Sheet sheet = wb.getSheetAt(0); - for (Row row : sheet) { - for (Cell cell : row) { - if (cell.getCellType() == CellType.FORMULA) { - // caused NPE on some cells - assertDoesNotThrow(() -> formulaEvaluator.evaluateInCell(cell)); - } - } - } - } - } - - /** - * Newlines are valid characters in a formula - */ - @Test - void bug50440And51875() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("NewlineInFormulas.xlsx")) { - Sheet s = wb.getSheetAt(0); - Cell c = s.getRow(0).getCell(0); - - assertEquals("SUM(\n1,2\n)", c.getCellFormula()); - assertEquals(3.0, c.getNumericCellValue(), 0); - - FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); - formulaEvaluator.evaluateFormulaCell(c); - - assertEquals("SUM(\n1,2\n)", c.getCellFormula()); - assertEquals(3.0, c.getNumericCellValue(), 0); - - // For 51875 - Cell b3 = s.getRow(2).getCell(1); - formulaEvaluator.evaluateFormulaCell(b3); - assertEquals("B1+B2", b3.getCellFormula()); // The newline is lost for shared formulas - assertEquals(3.0, b3.getNumericCellValue(), 0); - } - } - - /** - * Moving a cell comment from one cell to another - */ - @Test - void bug50795() throws IOException { - XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("50795.xlsx"); - XSSFSheet sheet = wb1.getSheetAt(0); - XSSFRow row = sheet.getRow(0); - - XSSFCell cellWith = row.getCell(0); - XSSFCell cellWithoutComment = row.getCell(1); - - assertNotNull(cellWith.getCellComment()); - assertNull(cellWithoutComment.getCellComment()); - - String exp = "\u0410\u0432\u0442\u043e\u0440:\ncomment"; - XSSFComment comment = cellWith.getCellComment(); - assertEquals(exp, comment.getString().getString()); - - - // Check we can write it out and read it back as-is - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - - sheet = wb2.getSheetAt(0); - row = sheet.getRow(0); - cellWith = row.getCell(0); - cellWithoutComment = row.getCell(1); - - // Double check things are as expected - assertNotNull(cellWith.getCellComment()); - assertNull(cellWithoutComment.getCellComment()); - comment = cellWith.getCellComment(); - assertEquals(exp, comment.getString().getString()); - - - // Move the comment - cellWithoutComment.setCellComment(comment); - - - // Write out and re-check - XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); - wb2.close(); - - sheet = wb3.getSheetAt(0); - row = sheet.getRow(0); - - // Ensure it swapped over - cellWith = row.getCell(0); - cellWithoutComment = row.getCell(1); - assertNull(cellWith.getCellComment()); - assertNotNull(cellWithoutComment.getCellComment()); - - comment = cellWithoutComment.getCellComment(); - assertEquals(exp, comment.getString().getString()); - - wb3.close(); - } - - /** - * When the cell background colour is set with one of the first - * two columns of the theme colour palette, the colours are - * shades of white or black. - * For those cases, ensure we don't break on reading the colour - */ - @Test - void bug50299() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("50299.xlsx")) { - - // Check all the colours - for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { - Sheet s = wb.getSheetAt(sn); - for (Row r : s) { - for (Cell c : r) { - CellStyle cs = c.getCellStyle(); - if (cs != null) { - cs.getFillForegroundColor(); - } - } - } - } - - // Check one bit in detail - // Check that we get back foreground=0 for the theme colours, - // and background=64 for the auto colouring - Sheet s = wb.getSheetAt(0); - assertEquals(0, s.getRow(0).getCell(8).getCellStyle().getFillForegroundColor()); - assertEquals(64, s.getRow(0).getCell(8).getCellStyle().getFillBackgroundColor()); - assertEquals(0, s.getRow(1).getCell(8).getCellStyle().getFillForegroundColor()); - assertEquals(64, s.getRow(1).getCell(8).getCellStyle().getFillBackgroundColor()); - } - } - - /** - * Excel .xls style indexed colours in a .xlsx file - */ - @Test - void bug50786() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("50786-indexed_colours.xlsx")) { - XSSFSheet s = wb.getSheetAt(0); - XSSFRow r = s.getRow(2); - - // Check we have the right cell - XSSFCell c = r.getCell(1); - assertEquals("test\u00a0", c.getRichStringCellValue().getString()); - - // It should be light green - XSSFCellStyle cs = c.getCellStyle(); - assertEquals(42, cs.getFillForegroundColor()); - assertEquals(42, cs.getFillForegroundColorColor().getIndexed()); - assertNotNull(cs.getFillForegroundColorColor().getRGB()); - assertEquals("FFCCFFCC", cs.getFillForegroundColorColor().getARGBHex()); - } - } - - /** - * If the border colours are set with themes, then we - * should still be able to get colours - */ - @Test - void bug50846() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("50846-border_colours.xlsx")) { - - XSSFSheet sheet = wb.getSheetAt(0); - XSSFRow row = sheet.getRow(0); - - // Border from a theme, brown - XSSFCell cellT = row.getCell(0); - XSSFCellStyle styleT = cellT.getCellStyle(); - XSSFColor colorT = styleT.getBottomBorderXSSFColor(); - - assertEquals(5, colorT.getTheme()); - assertEquals("FFC0504D", colorT.getARGBHex()); - - // Border from a style direct, red - XSSFCell cellS = row.getCell(1); - XSSFCellStyle styleS = cellS.getCellStyle(); - XSSFColor colorS = styleS.getBottomBorderXSSFColor(); - - assertEquals(0, colorS.getTheme()); - assertEquals("FFFF0000", colorS.getARGBHex()); - } - } - - /** - * Fonts where their colours come from the theme rather - * then being set explicitly still should allow the - * fetching of the RGB. - */ - @Test - void bug50784() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("50784-font_theme_colours.xlsx")) { - XSSFSheet s = wb.getSheetAt(0); - XSSFRow r = s.getRow(0); - - // Column 1 has a font with regular colours - XSSFCell cr = r.getCell(1); - XSSFFont fr = wb.getFontAt(cr.getCellStyle().getFontIndex()); - XSSFColor colr = fr.getXSSFColor(); - // No theme, has colours - assertEquals(0, colr.getTheme()); - assertNotNull(colr.getRGB()); - - // Column 0 has a font with colours from a theme - XSSFCell ct = r.getCell(0); - XSSFFont ft = wb.getFontAt(ct.getCellStyle().getFontIndex()); - XSSFColor colt = ft.getXSSFColor(); - // Has a theme, which has the colours on it - assertEquals(9, colt.getTheme()); - XSSFColor themeC = wb.getTheme().getThemeColor(colt.getTheme()); - assertNotNull(themeC.getRGB()); - assertNotNull(colt.getRGB()); - assertEquals(themeC.getARGBHex(), colt.getARGBHex()); // The same colour - } - } - - /** - * New lines were being eaten when setting a font on - * a rich text string - */ - @Test - void bug48877() throws IOException { - String text = "Use \n with word wrap on to create a new line.\n" + - "This line finishes with two trailing spaces. "; - - XSSFWorkbook wb1 = new XSSFWorkbook(); - XSSFSheet sheet = wb1.createSheet(); - - Font font1 = wb1.createFont(); - font1.setColor((short) 20); - Font font2 = wb1.createFont(); - font2.setColor(Font.COLOR_RED); - Font font3 = wb1.getFontAt(0); - - XSSFRow row = sheet.createRow(2); - XSSFCell cell = row.createCell(2); - - XSSFRichTextString richTextString = - wb1.getCreationHelper().createRichTextString(text); - - // Check the text has the newline - assertEquals(text, richTextString.getString()); - - // Apply the font - richTextString.applyFont(font3); - richTextString.applyFont(0, 3, font1); - cell.setCellValue(richTextString); - - // To enable newlines you need set a cell styles with wrap=true - CellStyle cs = wb1.createCellStyle(); - cs.setWrapText(true); - cell.setCellStyle(cs); - - // Check the text has the - assertEquals(text, cell.getStringCellValue()); - - // Save the file and re-read it - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - sheet = wb2.getSheetAt(0); - row = sheet.getRow(2); - cell = row.getCell(2); - assertEquals(text, cell.getStringCellValue()); - - // Now add a 2nd, and check again - int fontAt = text.indexOf('\n', 6); - cell.getRichStringCellValue().applyFont(10, fontAt + 1, font2); - assertEquals(text, cell.getStringCellValue()); - - assertEquals(4, cell.getRichStringCellValue().numFormattingRuns()); - assertEquals("Use", cell.getRichStringCellValue().getCTRst().getRArray(0).getT()); - - String r3 = cell.getRichStringCellValue().getCTRst().getRArray(2).getT(); - assertEquals("line.\n", r3.substring(r3.length() - 6)); - - // Save and re-check - XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); - wb2.close(); - - sheet = wb3.getSheetAt(0); - row = sheet.getRow(2); - cell = row.getCell(2); - assertEquals(text, cell.getStringCellValue()); - wb3.close(); - - // FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx"); - // wb.write(out); - // out.close(); - } - - /** - * Adding sheets when one has a table, then re-ordering - */ - @Test - void bug50867() throws IOException { - XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("50867_with_table.xlsx"); - assertEquals(3, wb1.getNumberOfSheets()); - - XSSFSheet s1 = wb1.getSheetAt(0); - XSSFSheet s2 = wb1.getSheetAt(1); - XSSFSheet s3 = wb1.getSheetAt(2); - assertEquals(1, s1.getTables().size()); - assertEquals(0, s2.getTables().size()); - assertEquals(0, s3.getTables().size()); - - XSSFTable t = s1.getTables().get(0); - assertEquals("Tabella1", t.getName()); - assertEquals("Tabella1", t.getDisplayName()); - assertEquals("A1:C3", t.getCTTable().getRef()); - - // Add a sheet and re-order - XSSFSheet s4 = wb1.createSheet("NewSheet"); - wb1.setSheetOrder(s4.getSheetName(), 0); - - // Check on tables - assertEquals(1, s1.getTables().size()); - assertEquals(0, s2.getTables().size()); - assertEquals(0, s3.getTables().size()); - assertEquals(0, s4.getTables().size()); - - // Refetch to get the new order - s1 = wb1.getSheetAt(0); - s2 = wb1.getSheetAt(1); - s3 = wb1.getSheetAt(2); - s4 = wb1.getSheetAt(3); - assertEquals(0, s1.getTables().size()); - assertEquals(1, s2.getTables().size()); - assertEquals(0, s3.getTables().size()); - assertEquals(0, s4.getTables().size()); - - // Save and re-load - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - - s1 = wb2.getSheetAt(0); - s2 = wb2.getSheetAt(1); - s3 = wb2.getSheetAt(2); - s4 = wb2.getSheetAt(3); - assertEquals(0, s1.getTables().size()); - assertEquals(1, s2.getTables().size()); - assertEquals(0, s3.getTables().size()); - assertEquals(0, s4.getTables().size()); - - t = s2.getTables().get(0); - assertEquals("Tabella1", t.getName()); - assertEquals("Tabella1", t.getDisplayName()); - assertEquals("A1:C3", t.getCTTable().getRef()); - - - // Add some more tables, and check - t = s2.createTable(null); - t.setName("New 2"); - t.setDisplayName("New 2"); - t = s3.createTable(null); - t.setName("New 3"); - t.setDisplayName("New 3"); - - XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); - wb2.close(); - s1 = wb3.getSheetAt(0); - s2 = wb3.getSheetAt(1); - s3 = wb3.getSheetAt(2); - s4 = wb3.getSheetAt(3); - assertEquals(0, s1.getTables().size()); - assertEquals(2, s2.getTables().size()); - assertEquals(1, s3.getTables().size()); - assertEquals(0, s4.getTables().size()); - - t = s2.getTables().get(0); - assertEquals("Tabella1", t.getName()); - assertEquals("Tabella1", t.getDisplayName()); - assertEquals("A1:C3", t.getCTTable().getRef()); - - t = s2.getTables().get(1); - assertEquals("New 2", t.getName()); - assertEquals("New 2", t.getDisplayName()); - - t = s3.getTables().get(0); - assertEquals("New 3", t.getName()); - assertEquals("New 3", t.getDisplayName()); - - // Check the relationships - assertEquals(0, s1.getRelations().size()); - assertEquals(3, s2.getRelations().size()); - assertEquals(1, s3.getRelations().size()); - assertEquals(0, s4.getRelations().size()); - - assertEquals( - XSSFRelation.PRINTER_SETTINGS.getContentType(), - s2.getRelations().get(0).getPackagePart().getContentType() - ); - assertEquals( - XSSFRelation.TABLE.getContentType(), - s2.getRelations().get(1).getPackagePart().getContentType() - ); - assertEquals( - XSSFRelation.TABLE.getContentType(), - s2.getRelations().get(2).getPackagePart().getContentType() - ); - assertEquals( - XSSFRelation.TABLE.getContentType(), - s3.getRelations().get(0).getPackagePart().getContentType() - ); - assertEquals( - "/xl/tables/table3.xml", - s3.getRelations().get(0).getPackagePart().getPartName().toString() - ); - wb3.close(); - } - - /** - * Setting repeating rows and columns shouldn't break - * any print settings that were there before - */ - @Test - void bug49253() throws IOException { - XSSFWorkbook wb1 = new XSSFWorkbook(); - XSSFWorkbook wb2 = new XSSFWorkbook(); - CellRangeAddress cra = CellRangeAddress.valueOf("C2:D3"); - - // No print settings before repeating - XSSFSheet s1 = wb1.createSheet(); - assertFalse(s1.getCTWorksheet().isSetPageSetup()); - assertTrue(s1.getCTWorksheet().isSetPageMargins()); - s1.setRepeatingColumns(cra); - s1.setRepeatingRows(cra); - - assertTrue(s1.getCTWorksheet().isSetPageSetup()); - assertTrue(s1.getCTWorksheet().isSetPageMargins()); - - PrintSetup ps1 = s1.getPrintSetup(); - assertFalse(ps1.getValidSettings()); - assertFalse(ps1.getLandscape()); - - - // Had valid print settings before repeating - XSSFSheet s2 = wb2.createSheet(); - PrintSetup ps2 = s2.getPrintSetup(); - assertTrue(s2.getCTWorksheet().isSetPageSetup()); - assertTrue(s2.getCTWorksheet().isSetPageMargins()); - - ps2.setLandscape(false); - assertTrue(ps2.getValidSettings()); - assertFalse(ps2.getLandscape()); - s2.setRepeatingColumns(cra); - s2.setRepeatingRows(cra); - - ps2 = s2.getPrintSetup(); - assertTrue(s2.getCTWorksheet().isSetPageSetup()); - assertTrue(s2.getCTWorksheet().isSetPageMargins()); - assertTrue(ps2.getValidSettings()); - assertFalse(ps2.getLandscape()); - - wb1.close(); - wb2.close(); - } - - /** - * Default Column style - */ - @Test - void bug51037() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet s = wb.createSheet(); - - CellStyle defaultStyle = wb.getCellStyleAt((short) 0); - assertEquals(0, defaultStyle.getIndex()); - - CellStyle blueStyle = wb.createCellStyle(); - blueStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex()); - blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); - assertEquals(1, blueStyle.getIndex()); - - CellStyle pinkStyle = wb.createCellStyle(); - pinkStyle.setFillForegroundColor(IndexedColors.PINK.getIndex()); - pinkStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); - assertEquals(2, pinkStyle.getIndex()); - - // Starts empty - assertEquals(1, s.getCTWorksheet().sizeOfColsArray()); - CTCols cols = s.getCTWorksheet().getColsArray(0); - assertEquals(0, cols.sizeOfColArray()); - - // Add some rows and columns - XSSFRow r1 = s.createRow(0); - XSSFRow r2 = s.createRow(1); - r1.createCell(0); - r1.createCell(2); - r2.createCell(0); - r2.createCell(3); - - // Check no style is there - assertEquals(1, s.getCTWorksheet().sizeOfColsArray()); - assertEquals(0, cols.sizeOfColArray()); - - assertEquals(defaultStyle, s.getColumnStyle(0)); - assertEquals(defaultStyle, s.getColumnStyle(2)); - assertEquals(defaultStyle, s.getColumnStyle(3)); - - - // Apply the styles - s.setDefaultColumnStyle(0, pinkStyle); - s.setDefaultColumnStyle(3, blueStyle); - - // Check - assertEquals(pinkStyle, s.getColumnStyle(0)); - assertEquals(defaultStyle, s.getColumnStyle(2)); - assertEquals(blueStyle, s.getColumnStyle(3)); - - assertEquals(1, s.getCTWorksheet().sizeOfColsArray()); - assertEquals(2, cols.sizeOfColArray()); - - assertEquals(1, cols.getColArray(0).getMin()); - assertEquals(1, cols.getColArray(0).getMax()); - assertEquals(pinkStyle.getIndex(), cols.getColArray(0).getStyle()); - - assertEquals(4, cols.getColArray(1).getMin()); - assertEquals(4, cols.getColArray(1).getMax()); - assertEquals(blueStyle.getIndex(), cols.getColArray(1).getStyle()); - - - // Save, re-load and re-check - XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); - wb.close(); - s = wbBack.getSheetAt(0); - defaultStyle = wbBack.getCellStyleAt(defaultStyle.getIndex()); - blueStyle = wbBack.getCellStyleAt(blueStyle.getIndex()); - pinkStyle = wbBack.getCellStyleAt(pinkStyle.getIndex()); - - assertEquals(pinkStyle, s.getColumnStyle(0)); - assertEquals(defaultStyle, s.getColumnStyle(2)); - assertEquals(blueStyle, s.getColumnStyle(3)); - wbBack.close(); - } - - /** - * Repeatedly writing a file. - * Something with the SharedStringsTable currently breaks... - */ - @Test - void bug46662() throws IOException { - for (int i=0; i<2; i++) { - try (XSSFWorkbook wb1 = (i == 0) ? new XSSFWorkbook() : XSSFTestDataSamples.openSampleWorkbook("sample.xlsx")) { - for (int j=0; j<3; j++) { - try (XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1)) { - assertEquals(wb1.getNumberOfSheets(), wb2.getNumberOfSheets()); - } - } - } - } - - // TODO: Complex file - } - - /** - * Colours and styles when the list has gaps in it - */ - @Test - void bug51222() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("51222.xlsx")) { - XSSFSheet s = wb.getSheetAt(0); - - XSSFCell cA4_EEECE1 = s.getRow(3).getCell(0); - XSSFCell cA5_1F497D = s.getRow(4).getCell(0); - - // Check the text - assertEquals("A4", cA4_EEECE1.getRichStringCellValue().getString()); - assertEquals("A5", cA5_1F497D.getRichStringCellValue().getString()); - - // Check the styles assigned to them - assertEquals(4, cA4_EEECE1.getCTCell().getS()); - assertEquals(5, cA5_1F497D.getCTCell().getS()); - - // Check we look up the correct style - assertEquals(4, cA4_EEECE1.getCellStyle().getIndex()); - assertEquals(5, cA5_1F497D.getCellStyle().getIndex()); - - // Check the fills on them at the low level - assertEquals(5, cA4_EEECE1.getCellStyle().getCoreXf().getFillId()); - assertEquals(6, cA5_1F497D.getCellStyle().getCoreXf().getFillId()); - - // These should reference themes 2 and 3 - assertEquals(2, wb.getStylesSource().getFillAt(5).getCTFill().getPatternFill().getFgColor().getTheme()); - assertEquals(3, wb.getStylesSource().getFillAt(6).getCTFill().getPatternFill().getFgColor().getTheme()); - - // Ensure we get the right colours for these themes - // TODO fix - // assertEquals("FFEEECE1", wb.getTheme().getThemeColor(2).getARGBHex()); - // assertEquals("FF1F497D", wb.getTheme().getThemeColor(3).getARGBHex()); - - // Finally check the colours on the styles - // TODO fix - // assertEquals("FFEEECE1", cA4_EEECE1.getCellStyle().getFillForegroundXSSFColor().getARGBHex()); - // assertEquals("FF1F497D", cA5_1F497D.getCellStyle().getFillForegroundXSSFColor().getARGBHex()); - } - } - - @Test - void bug51470() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("51470.xlsx")) { - XSSFSheet sh0 = wb.getSheetAt(0); - XSSFSheet sh1 = wb.cloneSheet(0); - List<RelationPart> rels0 = sh0.getRelationParts(); - List<RelationPart> rels1 = sh1.getRelationParts(); - assertEquals(1, rels0.size()); - assertEquals(1, rels1.size()); - - PackageRelationship pr0 = rels0.get(0).getRelationship(); - PackageRelationship pr1 = rels1.get(0).getRelationship(); - - assertEquals(pr0.getTargetMode(), pr1.getTargetMode()); - assertEquals(pr0.getTargetURI(), pr1.getTargetURI()); - POIXMLDocumentPart doc0 = rels0.get(0).getDocumentPart(); - POIXMLDocumentPart doc1 = rels1.get(0).getDocumentPart(); - - assertEquals(doc0, doc1); - } - } - - /** - * Add comments to Sheet 1, when Sheet 2 already has - * comments (so /xl/comments1.xml is taken) - */ - @Test - void bug51850() throws IOException { - XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("51850.xlsx"); - XSSFSheet sh1 = wb1.getSheetAt(0); - XSSFSheet sh2 = wb1.getSheetAt(1); - - // Sheet 2 has comments - assertNotNull(sh2.getCommentsTable(false)); - assertEquals(1, sh2.getCommentsTable(false).getNumberOfComments()); - - // Sheet 1 doesn't (yet) - assertNull(sh1.getCommentsTable(false)); - - // Try to add comments to Sheet 1 - CreationHelper factory = wb1.getCreationHelper(); - Drawing<?> drawing = sh1.createDrawingPatriarch(); - - ClientAnchor anchor = factory.createClientAnchor(); - anchor.setCol1(0); - anchor.setCol2(4); - anchor.setRow1(0); - anchor.setRow2(1); - - Comment comment1 = drawing.createCellComment(anchor); - comment1.setString( - factory.createRichTextString("I like this cell. It's my favourite.")); - comment1.setAuthor("Bob T. Fish"); - - anchor = factory.createClientAnchor(); - anchor.setCol1(0); - anchor.setCol2(4); - anchor.setRow1(1); - anchor.setRow2(1); - Comment comment2 = drawing.createCellComment(anchor); - comment2.setString( - factory.createRichTextString("This is much less fun...")); - comment2.setAuthor("Bob T. Fish"); - - Cell c1 = sh1.getRow(0).createCell(4); - c1.setCellValue(2.3); - c1.setCellComment(comment1); - - Cell c2 = sh1.getRow(0).createCell(5); - c2.setCellValue(2.1); - c2.setCellComment(comment2); - - - // Save and re-load - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - sh1 = wb2.getSheetAt(0); - sh2 = wb2.getSheetAt(1); - - // Check the comments - assertNotNull(sh2.getCommentsTable(false)); - assertEquals(1, sh2.getCommentsTable(false).getNumberOfComments()); - - assertNotNull(sh1.getCommentsTable(false)); - assertEquals(2, sh1.getCommentsTable(false).getNumberOfComments()); - wb2.close(); - } - - /** - * Sheet names with a , in them - */ - @Test - void bug51963() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("51963.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - assertEquals("Abc,1", sheet.getSheetName()); - - Name name = wb.getName("Intekon.ProdCodes"); - assertEquals("'Abc,1'!$A$1:$A$2", name.getRefersToFormula()); - - AreaReference ref = wb.getCreationHelper().createAreaReference(name.getRefersToFormula()); - assertEquals(0, ref.getFirstCell().getRow()); - assertEquals(0, ref.getFirstCell().getCol()); - assertEquals(1, ref.getLastCell().getRow()); - assertEquals(0, ref.getLastCell().getCol()); - } - } - - /** - * Sum across multiple workbooks - * eg =SUM($Sheet1.C1:$Sheet4.C1) - */ - @Test - void bug48703() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48703.xlsx")) { - XSSFSheet sheet = wb.getSheetAt(0); - - // Contains two forms, one with a range and one a list - XSSFRow r1 = sheet.getRow(0); - XSSFRow r2 = sheet.getRow(1); - XSSFCell c1 = r1.getCell(1); - XSSFCell c2 = r2.getCell(1); - - assertEquals(20.0, c1.getNumericCellValue(), 0); - assertEquals("SUM(Sheet1!C1,Sheet2!C1,Sheet3!C1,Sheet4!C1)", c1.getCellFormula()); - - assertEquals(20.0, c2.getNumericCellValue(), 0); - assertEquals("SUM(Sheet1:Sheet4!C1)", c2.getCellFormula()); - - // Try evaluating both - XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb); - eval.evaluateFormulaCell(c1); - eval.evaluateFormulaCell(c2); - - assertEquals(20.0, c1.getNumericCellValue(), 0); - assertEquals(20.0, c2.getNumericCellValue(), 0); - } - } - - /** - * Bugzilla 51710: problems reading shared formuals from .xlsx - */ - @Test - void bug51710() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("51710.xlsx")) { - - final String[] columns = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"}; - final int rowMax = 500; // bug triggers on row index 59 - - Sheet sheet = wb.getSheetAt(0); - - - // go through all formula cells - for (int rInd = 2; rInd <= rowMax; rInd++) { - Row row = sheet.getRow(rInd); - - for (int cInd = 1; cInd <= 12; cInd++) { - Cell cell = row.getCell(cInd); - String formula = cell.getCellFormula(); - CellReference ref = new CellReference(cell); - - //simulate correct answer - String correct = "$A" + (rInd + 1) + "*" + columns[cInd] + "$2"; - - assertEquals(correct, formula, "Incorrect formula in " + ref.formatAsString()); - } - - } - } - } - - /** - * Bug 53101: - */ - @Test - void bug5301() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("53101.xlsx")) { - FormulaEvaluator evaluator = - wb.getCreationHelper().createFormulaEvaluator(); - // A1: SUM(B1: IZ1) - double a1Value = - evaluator.evaluate(wb.getSheetAt(0).getRow(0).getCell(0)).getNumberValue(); - - // Assert - assertEquals(259.0, a1Value, 0.0); - - // KY: SUM(B1: IZ1) - /*double ky1Value =*/ - assertEquals(259.0, evaluator.evaluate(wb.getSheetAt(0).getRow(0).getCell(310)).getNumberValue(), 0.0001); - - // Assert - assertEquals(259.0, a1Value, 0.0); - } - } - - @Test - void bug54436() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("54436.xlsx")) { - if (!WorkbookEvaluator.getSupportedFunctionNames().contains("GETPIVOTDATA")) { - Function func = (args, srcRowIndex, srcColumnIndex) -> ErrorEval.NA; - - WorkbookEvaluator.registerFunction("GETPIVOTDATA", func); - } - FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); - assertDoesNotThrow(fe::evaluateAll); - } - } - - /** - * Password Protected .xlsx files are now (as of 4.0.0) tested for the default password - * when opened via WorkbookFactory, so there's no EncryptedDocumentException thrown anymore - */ - @Test - void bug55692_poifs() throws IOException { - // Via a POIFSFileSystem - try (POIFSFileSystem fsP = new POIFSFileSystem( - POIDataSamples.getPOIFSInstance().openResourceAsStream("protect.xlsx")); - Workbook wb = WorkbookFactory.create(fsP)) { - assertNotNull(wb); - assertEquals(3, wb.getNumberOfSheets()); - } - } - - @Test - void bug55692_stream() throws IOException { - // Directly on a Stream, will go via POIFS and spot it's - // actually a .xlsx file encrypted with the default password, and open - try (Workbook wb = WorkbookFactory.create( - POIDataSamples.getPOIFSInstance().openResourceAsStream("protect.xlsx"))) { - assertNotNull(wb); - assertEquals(3, wb.getNumberOfSheets()); - } - } - - @Test - void bug55692_poifs2() throws IOException { - // Via a POIFSFileSystem, will spot it's actually a .xlsx file - // encrypted with the default password, and open - try (POIFSFileSystem fsNP = new POIFSFileSystem( - POIDataSamples.getPOIFSInstance().openResourceAsStream("protect.xlsx"))) { - Workbook wb = WorkbookFactory.create(fsNP); - assertNotNull(wb); - assertEquals(3, wb.getNumberOfSheets()); - wb.close(); - } - } - - @Test - void bug53282() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("53282b.xlsx")) { - Cell c = wb.getSheetAt(0).getRow(1).getCell(0); - assertEquals("#@_#", c.getStringCellValue()); - assertEquals("http://invalid.uri", c.getHyperlink().getAddress()); - } - } - - /** - * Was giving NullPointerException - * at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead - * due to a lack of Styles Table - */ - @Test - void bug56278() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56278.xlsx")) { - assertEquals(0, wb.getSheetIndex("Market Rates")); - - // Save and re-check - Workbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb); - assertEquals(0, nwb.getSheetIndex("Market Rates")); - nwb.close(); - } - } - - @Test - void bug56315() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56315.xlsx")) { - Cell c = wb.getSheetAt(0).getRow(1).getCell(0); - CellValue cv = wb.getCreationHelper().createFormulaEvaluator().evaluate(c); - double rounded = cv.getNumberValue(); - assertEquals(0.1, rounded, 0.0); - } - } - - @Test - void bug56468() throws IOException, InterruptedException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet(); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = row.createCell(0); - cell.setCellValue("Hi"); - sheet.setRepeatingRows(new CellRangeAddress(0, 0, 0, 0)); - - // small hack to try to make this test stable, previously it failed whenever the two written ZIP files had - // different file-creation dates stored. We try to do a loop until the current second changes in order to - // avoid problems with some date information that is written to the ZIP and thus causes differences - long start = System.currentTimeMillis() / 1000; - while (System.currentTimeMillis() / 1000 == start) { - Thread.sleep(10); - } - - ByteArrayOutputStream bos = new ByteArrayOutputStream(8096); - wb.write(bos); - byte[] firstSave = bos.toByteArray(); - bos.reset(); - wb.write(bos); - byte[] secondSave = bos.toByteArray(); - - assertArrayEquals(firstSave, secondSave, - "Had: \n" + Arrays.toString(firstSave) + " and \n" + Arrays.toString(secondSave)); - } - } - - /** - * ISO-8601 style cell formats with a T in them, eg - * cell format of "yyyy-MM-ddTHH:mm:ss" - */ - @Test - void bug54034() throws IOException { - TimeZone tz = LocaleUtil.getUserTimeZone(); - LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET")); - try { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("54034.xlsx")) { - Sheet sheet = wb.getSheet("Sheet1"); - Row row = sheet.getRow(1); - Cell cell = row.getCell(2); - assertTrue(DateUtil.isCellDateFormatted(cell)); - - DataFormatter fmt = new DataFormatter(); - assertEquals("yyyy\\-mm\\-dd\\Thh:mm", cell.getCellStyle().getDataFormatString()); - assertEquals("2012-08-08T22:59", fmt.formatCellValue(cell)); - } - } finally { - LocaleUtil.setUserTimeZone(tz); - } - } - - - @Test - void testBug53798XLSX() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("53798_shiftNegative_TMPL.xlsx")) { - File xlsOutput = TempFile.createTempFile("testBug53798", ".xlsx"); - bug53798Work(wb, xlsOutput); - } - } - - @Disabled("Shifting rows is not yet implemented in SXSSFSheet") - @Test - void testBug53798XLSXStream() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("53798_shiftNegative_TMPL.xlsx")) { - File xlsOutput = TempFile.createTempFile("testBug53798", ".xlsx"); - SXSSFWorkbook wb2 = new SXSSFWorkbook(wb); - bug53798Work(wb2, xlsOutput); - wb2.close(); - } - } - - @Test - void testBug53798XLS() throws IOException { - Workbook wb = HSSFTestDataSamples.openSampleWorkbook("53798_shiftNegative_TMPL.xls"); - File xlsOutput = TempFile.createTempFile("testBug53798", ".xls"); - bug53798Work(wb, xlsOutput); - wb.close(); - } - - /** - * SUMIF was throwing a NPE on some formulas - */ - @Test - void testBug56420SumIfNPE() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56420.xlsx")) { - - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - - Sheet sheet = wb.getSheetAt(0); - Row r = sheet.getRow(2); - Cell c = r.getCell(2); - assertEquals("SUMIF($A$1:$A$4,A3,$B$1:$B$4)", c.getCellFormula()); - Cell eval = evaluator.evaluateInCell(c); - assertEquals(0.0, eval.getNumericCellValue(), 0.0001); - } - } - - private void bug53798Work(Workbook wb, File xlsOutput) throws IOException { - Sheet testSheet = wb.getSheetAt(0); - - testSheet.shiftRows(2, 2, 1); - - saveAndReloadReport(wb, xlsOutput); - - // 1) corrupted xlsx (unreadable data in the first row of a shifted group) already comes about - // when shifted by less than -1 negative amount (try -2) - testSheet.shiftRows(3, 3, -1); - - saveAndReloadReport(wb, xlsOutput); - - testSheet.shiftRows(2, 2, 1); - - saveAndReloadReport(wb, xlsOutput); - - // 2) attempt to create a new row IN PLACE of a removed row by a negative shift causes corrupted - // xlsx file with unreadable data in the negative shifted row. - // NOTE it's ok to create any other row. - Row newRow = testSheet.createRow(3); - - saveAndReloadReport(wb, xlsOutput); - - Cell newCell = newRow.createCell(0); - - saveAndReloadReport(wb, xlsOutput); - - newCell.setCellValue("new Cell in row " + newRow.getRowNum()); - - saveAndReloadReport(wb, xlsOutput); - - // 3) once a negative shift has been made any attempt to shift another group of rows - // (note: outside of previously negative shifted rows) by a POSITIVE amount causes POI exception: - // org.apache.xmlbeans.impl.values.XmlValueDisconnectedException. - // NOTE: another negative shift on another group of rows is successful, provided no new rows in - // place of previously shifted rows were attempted to be created as explained above. - testSheet.shiftRows(6, 7, 1); // -- CHANGE the shift to positive once the behaviour of - // the above has been tested - - saveAndReloadReport(wb, xlsOutput); - } - - /** - * XSSFCell.typeMismatch on certain blank cells when formatting - * with DataFormatter - */ - @Test - void bug56702() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56702.xlsx")) { - - Sheet sheet = wb.getSheetAt(0); - - // Get wrong cell by row 8 & column 7 - Cell cell = sheet.getRow(8).getCell(7); - assertEquals(CellType.NUMERIC, cell.getCellType()); - - // Check the value - will be zero as it is <c><v/></c> - assertEquals(0.0, cell.getNumericCellValue(), 0.001); - - // Try to format - DataFormatter formatter = new DataFormatter(); - formatter.formatCellValue(cell); - - // Check the formatting - assertEquals("0", formatter.formatCellValue(cell)); - } - } - - /** - * Formulas which reference named ranges, either in other - * sheets, or workbook scoped but in other workbooks. - * Used to fail with with errors like - * org.apache.poi.ss.formula.FormulaParseException: Cell reference expected after sheet name at index 9 - * org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '[' in specified formula '[0]!NR_Global_B2'. Expected number, string, or defined name - */ - @Test - void bug56737() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx")) { - - // Check the named range definitions - Name nSheetScope = wb.getName("NR_To_A1"); - Name nWBScope = wb.getName("NR_Global_B2"); - - assertNotNull(nSheetScope); - assertNotNull(nWBScope); - - assertEquals("Defines!$A$1", nSheetScope.getRefersToFormula()); - assertEquals("Defines!$B$2", nWBScope.getRefersToFormula()); - - // Check the different kinds of formulas - Sheet s = wb.getSheetAt(0); - Cell cRefSName = s.getRow(1).getCell(3); - Cell cRefWName = s.getRow(2).getCell(3); - - assertEquals("Defines!NR_To_A1", cRefSName.getCellFormula()); - // Note the formula, as stored in the file, has the external name index not filename - // TODO Provide a way to get the one with the filename - assertEquals("[0]!NR_Global_B2", cRefWName.getCellFormula()); - - // Try to evaluate them - FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); - assertEquals("Test A1", eval.evaluate(cRefSName).getStringValue()); - assertEquals(142, (int) eval.evaluate(cRefWName).getNumberValue()); - - // Try to evaluate everything - eval.evaluateAll(); - } - } - - private void saveAndReloadReport(Workbook wb, File outFile) throws IOException { - // run some method on the font to verify if it is "disconnected" already - //for(short i = 0;i < 256;i++) - { - Font font = wb.getFontAt(0); - if (font instanceof XSSFFont) { - XSSFFont xfont = (XSSFFont) wb.getFontAt(0); - CTFontImpl ctFont = (CTFontImpl) xfont.getCTFont(); - assertEquals(0, ctFont.sizeOfBArray()); - } - } - - try (FileOutputStream fileOutStream = new FileOutputStream(outFile)) { - wb.write(fileOutStream); - } - - try (FileInputStream is = new FileInputStream(outFile)) { - Workbook newWB = null; - try { - if (wb instanceof XSSFWorkbook) { - newWB = new XSSFWorkbook(is); - } else if (wb instanceof HSSFWorkbook) { - newWB = new HSSFWorkbook(is); - } else if (wb instanceof SXSSFWorkbook) { - newWB = new SXSSFWorkbook(new XSSFWorkbook(is)); - } else { - throw new IllegalStateException("Unknown workbook: " + wb); - } - assertNotNull(newWB.getSheet("test")); - } finally { - if (newWB != null) { - newWB.close(); - } - } - } - } - - @Test - void testBug56688_1() throws IOException { - XSSFWorkbook excel = XSSFTestDataSamples.openSampleWorkbook("56688_1.xlsx"); - checkValue(excel, "-1.0"); /* Not 0.0 because POI sees date "0" minus one month as invalid date, which is -1! */ - excel.close(); - } - - @Test - void testBug56688_2() throws IOException { - XSSFWorkbook excel = XSSFTestDataSamples.openSampleWorkbook("56688_2.xlsx"); - checkValue(excel, "#VALUE!"); - excel.close(); - } - - @Test - void testBug56688_3() throws IOException { - XSSFWorkbook excel = XSSFTestDataSamples.openSampleWorkbook("56688_3.xlsx"); - checkValue(excel, "#VALUE!"); - excel.close(); - } - - @Test - void testBug56688_4() throws IOException { - XSSFWorkbook excel = XSSFTestDataSamples.openSampleWorkbook("56688_4.xlsx"); - - Calendar calendar = LocaleUtil.getLocaleCalendar(); - calendar.add(Calendar.MONTH, 2); - double excelDate = DateUtil.getExcelDate(calendar.getTime()); - NumberEval eval = new NumberEval(Math.floor(excelDate)); - checkValue(excel, eval.getStringValue() + ".0"); - - excel.close(); - } - - /** - * New hyperlink with no initial cell reference, still need - * to be able to change it - */ - @Test - void testBug56527() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet(); - XSSFCreationHelper creationHelper = wb.getCreationHelper(); - XSSFHyperlink hyperlink; - - // Try with a cell reference - hyperlink = creationHelper.createHyperlink(HyperlinkType.URL); - sheet.addHyperlink(hyperlink); - hyperlink.setAddress("http://myurl"); - hyperlink.setCellReference("B4"); - assertEquals(3, hyperlink.getFirstRow()); - assertEquals(1, hyperlink.getFirstColumn()); - assertEquals(3, hyperlink.getLastRow()); - assertEquals(1, hyperlink.getLastColumn()); - - // Try with explicit rows / columns - hyperlink = creationHelper.createHyperlink(HyperlinkType.URL); - sheet.addHyperlink(hyperlink); - hyperlink.setAddress("http://myurl"); - hyperlink.setFirstRow(5); - hyperlink.setFirstColumn(3); - - assertEquals(5, hyperlink.getFirstRow()); - assertEquals(3, hyperlink.getFirstColumn()); - assertEquals(5, hyperlink.getLastRow()); - assertEquals(3, hyperlink.getLastColumn()); - wb.close(); - } - - /** - * Shifting rows with a formula that references a - * function in another file - */ - @Test - void bug56502() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56502.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - - Cell cFunc = sheet.getRow(3).getCell(0); - assertEquals("[1]!LUCANET(\"Ist\")", cFunc.getCellFormula()); - Cell cRef = sheet.getRow(3).createCell(1); - cRef.setCellFormula("A3"); - - // Shift it down one row - sheet.shiftRows(1, sheet.getLastRowNum(), 1); - - // Check the new formulas: Function won't change, Reference will - cFunc = sheet.getRow(4).getCell(0); - assertEquals("[1]!LUCANET(\"Ist\")", cFunc.getCellFormula()); - cRef = sheet.getRow(4).getCell(1); - assertEquals("A4", cRef.getCellFormula()); - } - } - - @Test - void bug54764() throws IOException, OpenXML4JException, XmlException { - try (OPCPackage pkg = XSSFTestDataSamples.openSamplePackage("54764.xlsx")) { - // Check the core properties - will be found but empty, due - // to the expansion being too much to be considered valid - POIXMLProperties props = new POIXMLProperties(pkg); - assertNull(props.getCoreProperties().getTitle()); - assertNull(props.getCoreProperties().getSubject()); - assertNull(props.getCoreProperties().getDescription()); - - // Now check the spreadsheet itself - assertThrows(POIXMLException.class, () -> new XSSFWorkbook(pkg), "Should fail as too much expansion occurs"); - } - - // Try with one with the entities in the Content Types - assertThrows(Exception.class, () -> XSSFTestDataSamples.openSamplePackage("54764-2.xlsx"), - "Should fail as too much expansion occurs"); - - // Check we can still parse valid files after all that - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("sample.xlsx")) { - assertEquals(3, wb.getNumberOfSheets()); - } - } - - @Test - void test54764WithSAXHelper() throws Exception { - File testFile = XSSFTestDataSamples.getSampleFile("54764.xlsx"); - try (ZipFile zip = new ZipFile(testFile)) { - ZipArchiveEntry ze = zip.getEntry("xl/sharedStrings.xml"); - XMLReader reader = XMLHelper.newXMLReader(); - SAXParseException e = assertThrows(SAXParseException.class, - () -> reader.parse(new InputSource(zip.getInputStream(ze)))); - assertNotNull(e.getMessage()); - assertTrue(e.getMessage().contains("more than \"1\" entity")); - } - } - - @Test - void test54764WithDocumentHelper() throws Exception { - File testFile = XSSFTestDataSamples.getSampleFile("54764.xlsx"); - try (ZipFile zip = new ZipFile(testFile)) { - ZipArchiveEntry ze = zip.getEntry("xl/sharedStrings.xml"); - SAXParseException e = assertThrows(SAXParseException.class, - () -> DocumentHelper.readDocument(zip.getInputStream(ze))); - assertNotNull(e.getMessage()); - assertNotEquals(isOldXercesActive(), e.getMessage().contains("DOCTYPE is disallowed when the feature")); - } - } - - /** - * CTDefinedNamesImpl should be included in the smaller - * poi-ooxml-lite jar - */ - @Test - void bug57176() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57176.xlsx")) { - CTDefinedNames definedNames = wb.getCTWorkbook().getDefinedNames(); - List<CTDefinedName> definedNameList = definedNames.getDefinedNameList(); - for (CTDefinedName defName : definedNameList) { - assertNotNull(defName.getName()); - assertNotNull(defName.getStringValue()); - } - assertEquals("TestDefinedName", definedNameList.get(0).getName()); - } - } - - /** - * .xlsb files are not supported, but we should generate a helpful - * error message if given one - */ - @Test - void bug56800_xlsb() throws IOException { - // Can be opened at the OPC level - try (OPCPackage pkg = XSSFTestDataSamples.openSamplePackage("Simple.xlsb")) { - // XSSF Workbook gives helpful error - assertThrows(XLSBUnsupportedException.class, () -> new XSSFWorkbook(pkg), ".xlsb files not supported"); - - // Workbook Factory gives helpful error on package - assertThrows(XLSBUnsupportedException.class, () -> XSSFWorkbookFactory.createWorkbook(pkg), ".xlsb files not supported"); - } - - // Workbook Factory gives helpful error on file - File xlsbFile = HSSFTestDataSamples.getSampleFile("Simple.xlsb"); - assertThrows(XLSBUnsupportedException.class, () -> WorkbookFactory.create(xlsbFile), ".xlsb files not supported"); - } - - private void checkValue(XSSFWorkbook excel, String expect) { - XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(excel); - evaluator.evaluateAll(); - - XSSFCell cell = excel.getSheetAt(0).getRow(1).getCell(1); - CellValue value = evaluator.evaluate(cell); - - assertEquals(expect, value.formatAsString()); - } - - @Test - void testBug57196() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57196.xlsx")) { - Sheet sheet = wb.getSheet("Feuil1"); - Row mod = sheet.getRow(1); - mod.getCell(1).setCellValue(3); - mod = sheet.getRow(2); - mod.createCell(0).setCellValue(10); - HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); - assertEquals(256, mod.getCell(2).getNumericCellValue()); - } - } - - @Test - void test57196_Detail() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet("Sheet1"); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = row.createCell(0); - cell.setCellFormula("DEC2HEX(HEX2DEC(O8)-O2+D2)"); - XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb); - CellValue cv = fe.evaluate(cell); - - assertNotNull(cv); - wb.close(); - } - - @Test - void test57196_Detail2() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet("Sheet1"); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = row.createCell(0); - cell.setCellFormula("DEC2HEX(O2+D2)"); - XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb); - CellValue cv = fe.evaluate(cell); - - assertNotNull(cv); - wb.close(); - } - - @ParameterizedTest - @CsvSource({ - // simple formula worked - "DEC2HEX(O2+D2), org.apache.poi.ss.formula.eval.StringEval [0]", - // this already failed! Hex2Dec did not correctly handle RefEval - "HEX2DEC(O8), org.apache.poi.ss.formula.eval.NumberEval [0]", - // slightly more complex one failed - "HEX2DEC(O8)-O2+D2, org.apache.poi.ss.formula.eval.NumberEval [0]", - // more complicated failed - "DEC2HEX(HEX2DEC(O8)-O2+D2), org.apache.poi.ss.formula.eval.StringEval [0]", - // what other similar functions - "DEC2BIN(O8)-O2+D2, org.apache.poi.ss.formula.eval.ErrorEval [#VALUE!]", - // what other similar functions - "DEC2BIN(A1), org.apache.poi.ss.formula.eval.StringEval [0]" - }) - void test57196_WorkbookEvaluator(String formula, String expValue) throws IOException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet("Sheet1"); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = row.createCell(0); - cell.setCellValue("0"); - cell = row.createCell(1); - cell.setCellValue(0); - cell = row.createCell(2); - cell.setCellValue(0); - - cell.setCellFormula(formula); - - WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null); - workbookEvaluator.setDebugEvaluationOutputForNextEval(true); - ValueEval ve = workbookEvaluator.evaluate(new XSSFEvaluationCell(cell)); - - assertEquals(expValue, ve.toString()); - } - } - - /** - * A .xlsx file with no Shared Strings table should open fine - * in read-only mode - */ - @ParameterizedTest - @EnumSource(value = PackageAccess.class, names = {"READ_WRITE", "READ"}) - void bug57482(PackageAccess access) throws IOException, InvalidFormatException { - File file = HSSFTestDataSamples.getSampleFile("57482-OnlyNumeric.xlsx"); - try (OPCPackage pkg = OPCPackage.open(file, access); - XSSFWorkbook wb1 = new XSSFWorkbook(pkg)) { - // Try to open it and read the contents - - assertNotNull(wb1.getSharedStringSource()); - assertEquals(0, wb1.getSharedStringSource().getCount()); - - DataFormatter fmt = new DataFormatter(); - XSSFSheet s = wb1.getSheetAt(0); - assertEquals("1", fmt.formatCellValue(s.getRow(0).getCell(0))); - assertEquals("11", fmt.formatCellValue(s.getRow(0).getCell(1))); - assertEquals("5", fmt.formatCellValue(s.getRow(4).getCell(0))); - - // Add a text cell - s.getRow(0).createCell(3).setCellValue("Testing"); - assertEquals("Testing", fmt.formatCellValue(s.getRow(0).getCell(3))); - - // Try to write-out and read again, should only work - // in read-write mode, not read-only mode - try (XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1)) { - if (access == PackageAccess.READ) { - fail("Shouln't be able to write from read-only mode"); - } - - // Check again - s = wb2.getSheetAt(0); - assertEquals("1", fmt.formatCellValue(s.getRow(0).getCell(0))); - assertEquals("11", fmt.formatCellValue(s.getRow(0).getCell(1))); - assertEquals("5", fmt.formatCellValue(s.getRow(4).getCell(0))); - assertEquals("Testing", fmt.formatCellValue(s.getRow(0).getCell(3))); - - wb2.getPackage().revert(); - } catch (InvalidOperationException e) { - if (access == PackageAccess.READ_WRITE) { - // Shouldn't occur in write-mode - throw e; - } - } - - pkg.revert(); - } - } - - /** - * "Unknown error type: -60" fetching formula error value - */ - @Test - void bug57535() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57535.xlsx")) { - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - evaluator.clearAllCachedResultValues(); - - Sheet sheet = wb.getSheet("Sheet1"); - Cell cell = sheet.getRow(5).getCell(4); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("E4+E5", cell.getCellFormula()); - - CellValue value = evaluator.evaluate(cell); - assertEquals(CellType.ERROR, value.getCellType()); - assertEquals(-60, value.getErrorValue()); - assertEquals("~CIRCULAR~REF~", FormulaError.forInt(value.getErrorValue()).getString()); - assertEquals("CIRCULAR_REF", FormulaError.forInt(value.getErrorValue()).toString()); - } - } - - - @Test - void test57165() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57171_57163_57165.xlsx")) { - removeAllSheetsBut(3, wb); - // Throws exception here - assertDoesNotThrow(() -> wb.cloneSheet(0)); - wb.setSheetName(1, "New Sheet"); - - try (XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - assertNotNull(wbBack.getSheet("New Sheet")); - } - } - } - - @Test - void test57165_create() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57171_57163_57165.xlsx")) { - removeAllSheetsBut(3, wb); - // Throws exception here - assertDoesNotThrow(() -> wb.createSheet("newsheet")); - wb.setSheetName(1, "New Sheet"); - - try (XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - assertNotNull(wbBack.getSheet("New Sheet")); - } - } - } - - private static void removeAllSheetsBut(@SuppressWarnings("SameParameterValue") int sheetIndex, Workbook wb) { - int sheetNb = wb.getNumberOfSheets(); - // Move this sheet at the first position - wb.setSheetOrder(wb.getSheetName(sheetIndex), 0); - for (int sn = sheetNb - 1; sn > 0; sn--) { - wb.removeSheetAt(sn); - } - } - - /** - * Sums 2 plus the cell at the left, indirectly to avoid reference - * problems when deleting columns, conditionally to stop recursion - */ - private static final String FORMULA1 = - "IF( INDIRECT( ADDRESS( ROW(), COLUMN()-1 ) ) = 0, 0, " - + "INDIRECT( ADDRESS( ROW(), COLUMN()-1 ) ) ) + 2"; - - /** - * Sums 2 plus the upper cell, indirectly to avoid reference - * problems when deleting rows, conditionally to stop recursion - */ - private static final String FORMULA2 = - "IF( INDIRECT( ADDRESS( ROW()-1, COLUMN() ) ) = 0, 0, " - + "INDIRECT( ADDRESS( ROW()-1, COLUMN() ) ) ) + 2"; - - /** - * Expected: - * <p> - * [ 0][ 2][ 4] - */ - @Test - void testBug56820_Formula1() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - Sheet sh = wb.createSheet(); - - sh.createRow(0).createCell(0).setCellValue(0.0d); - Cell formulaCell1 = sh.getRow(0).createCell(1); - Cell formulaCell2 = sh.getRow(0).createCell(2); - formulaCell1.setCellFormula(FORMULA1); - formulaCell2.setCellFormula(FORMULA1); - - double A1 = evaluator.evaluate(formulaCell1).getNumberValue(); - double A2 = evaluator.evaluate(formulaCell2).getNumberValue(); - - assertEquals(2, A1, 0); - assertEquals(4, A2, 0); //<-- FAILS EXPECTATIONS - } - } - - /** - * Expected: - * <p> - * [ 0] <- number - * [ 2] <- formula - * [ 4] <- formula - */ - @Test - void testBug56820_Formula2() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - Sheet sh = wb.createSheet(); - - sh.createRow(0).createCell(0).setCellValue(0.0d); - Cell formulaCell1 = sh.createRow(1).createCell(0); - Cell formulaCell2 = sh.createRow(2).createCell(0); - formulaCell1.setCellFormula(FORMULA2); - formulaCell2.setCellFormula(FORMULA2); - - double A1 = evaluator.evaluate(formulaCell1).getNumberValue(); - double A2 = evaluator.evaluate(formulaCell2).getNumberValue(); //<-- FAILS EVALUATION - - assertEquals(2, A1, 0); - assertEquals(4, A2, 0); - } - } - - @Test - void test56467() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("picture.xlsx")) { - Sheet orig = wb.getSheetAt(0); - assertNotNull(orig); - - Sheet sheet = wb.cloneSheet(0); - Drawing<?> drawing = sheet.createDrawingPatriarch(); - for (XSSFShape shape : ((XSSFDrawing) drawing).getShapes()) { - if (shape instanceof XSSFPicture) { - XSSFPictureData pictureData = ((XSSFPicture) shape).getPictureData(); - assertNotNull(pictureData); - } - } - - } - } - - /** - * OOXML-Strict files - * Not currently working - namespace mis-match from XMLBeans - */ - @Test - @Disabled("XMLBeans namespace mis-match on ooxml-strict files") - void test57699() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("sample.strict.xlsx")) { - assertEquals(3, wb.getNumberOfSheets()); - // TODO Check sheet contents - // TODO Check formula evaluation - - try (XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - assertEquals(3, wbBack.getNumberOfSheets()); - // TODO Re-check sheet contents - // TODO Re-check formula evaluation - } - } - } - - @Test - void testBug56295_MergeXlslsWithStyles() throws IOException { - XSSFWorkbook xlsToAppendWorkbook = XSSFTestDataSamples.openSampleWorkbook("56295.xlsx"); - XSSFSheet sheet = xlsToAppendWorkbook.getSheetAt(0); - XSSFRow srcRow = sheet.getRow(0); - XSSFCell oldCell = srcRow.getCell(0); - XSSFCellStyle cellStyle = oldCell.getCellStyle(); - - checkStyle(cellStyle); - -// StylesTable table = xlsToAppendWorkbook.getStylesSource(); -// List<XSSFCellFill> fills = table.getFills(); -// System.out.println("Having " + fills.size() + " fills"); -// for(XSSFCellFill fill : fills) { -// System.out.println("Fill: " + fill.getFillBackgroundColor() + "/" + fill.getFillForegroundColor()); -// } - xlsToAppendWorkbook.close(); - - XSSFWorkbook targetWorkbook = new XSSFWorkbook(); - XSSFSheet newSheet = targetWorkbook.createSheet(sheet.getSheetName()); - XSSFRow destRow = newSheet.createRow(0); - XSSFCell newCell = destRow.createCell(0); - - //newCell.getCellStyle().cloneStyleFrom(cellStyle); - CellStyle newCellStyle = targetWorkbook.createCellStyle(); - newCellStyle.cloneStyleFrom(cellStyle); - newCell.setCellStyle(newCellStyle); - checkStyle(newCell.getCellStyle()); - newCell.setCellValue(oldCell.getStringCellValue()); - -// OutputStream os = new FileOutputStream("output.xlsm"); -// try { -// targetWorkbook.write(os); -// } finally { -// os.close(); -// } - - XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(targetWorkbook); - XSSFCellStyle styleBack = wbBack.getSheetAt(0).getRow(0).getCell(0).getCellStyle(); - checkStyle(styleBack); - - targetWorkbook.close(); - wbBack.close(); - } - - /** - * Paragraph with property BuFont but none of the properties - * BuNone, BuChar, and BuAutoNum, used to trigger a NPE - * Excel treats this as not-bulleted, so now do we - */ - @Test - void testBug57826() throws IOException { - XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("57826.xlsx"); - - assertTrue(workbook.getNumberOfSheets() >= 1, "no sheets in workbook"); - XSSFSheet sheet = workbook.getSheetAt(0); - - XSSFDrawing drawing = sheet.getDrawingPatriarch(); - assertNotNull(drawing); - - List<XSSFShape> shapes = drawing.getShapes(); - assertEquals(1, shapes.size()); - assertTrue(shapes.get(0) instanceof XSSFSimpleShape); - - XSSFSimpleShape shape = (XSSFSimpleShape) shapes.get(0); - - // Used to throw a NPE - String text = shape.getText(); - - // No bulleting info included - assertEquals("test ok", text); - - workbook.close(); - } - - private void checkStyle(XSSFCellStyle cellStyle) { - assertNotNull(cellStyle); - assertEquals(0, cellStyle.getFillForegroundColor()); - assertNotNull(cellStyle.getFillForegroundXSSFColor()); - XSSFColor fgColor = cellStyle.getFillForegroundColorColor(); - assertNotNull(fgColor); - assertEquals("FF00FFFF", fgColor.getARGBHex()); - - assertEquals(0, cellStyle.getFillBackgroundColor()); - assertNotNull(cellStyle.getFillBackgroundXSSFColor()); - XSSFColor bgColor = cellStyle.getFillBackgroundColorColor(); - assertNotNull(bgColor); - assertEquals("FF00FFFF", fgColor.getARGBHex()); - } - - @Test - void bug57642() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet s = wb.createSheet("TestSheet"); - XSSFCell c = s.createRow(0).createCell(0); - c.setCellFormula("ISERROR(TestSheet!A1)"); - c = s.createRow(1).createCell(1); - c.setCellFormula("ISERROR(B2)"); - - wb.setSheetName(0, "CSN"); - c = s.getRow(0).getCell(0); - assertEquals("ISERROR(CSN!A1)", c.getCellFormula()); - c = s.getRow(1).getCell(1); - assertEquals("ISERROR(B2)", c.getCellFormula()); - - wb.close(); - } - - /** - * .xlsx supports 64000 cell styles, the style indexes after - * 32,767 must not be -32,768, then -32,767, -32,766 - */ - @Test - void bug57880() throws IOException { - int numStyles = 33000; - XSSFWorkbook wb = new XSSFWorkbook(); - for (int i = 1; i < numStyles; i++) { - // Create a style and use it - XSSFCellStyle style = wb.createCellStyle(); - assertEquals(i, style.getUIndex()); - } - assertEquals(numStyles, wb.getNumCellStyles()); - - // avoid OOM in Gump run - File file = XSSFTestDataSamples.writeOutAndClose(wb, "bug57880"); - //noinspection UnusedAssignment - wb = null; - // Garbage collection may happen here - - // avoid zip bomb detection - double ratio = ZipSecureFile.getMinInflateRatio(); - ZipSecureFile.setMinInflateRatio(0.00005); - wb = XSSFTestDataSamples.readBackAndDelete(file); - ZipSecureFile.setMinInflateRatio(ratio); - - //Assume identical cell styles aren't consolidated - //If XSSFWorkbooks ever implicitly optimize/consolidate cell styles (such as when the workbook is written to disk) - //then this unit test should be updated - assertEquals(numStyles, wb.getNumCellStyles()); - for (int i = 1; i < numStyles; i++) { - XSSFCellStyle style = wb.getCellStyleAt(i); - assertNotNull(style); - assertEquals(i, style.getUIndex()); - } - wb.close(); - } - - - @Test - void test56574() throws IOException { - runTest56574(false); - runTest56574(true); - } - - private void runTest56574(boolean createRow) throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56574.xlsx")) { - - Sheet sheet = wb.getSheet("Func"); - assertNotNull(sheet); - - Map<String, Object[]> data; - data = new TreeMap<>(); - data.put("1", new Object[]{"ID", "NAME", "LASTNAME"}); - data.put("2", new Object[]{2, "Amit", "Shukla"}); - data.put("3", new Object[]{1, "Lokesh", "Gupta"}); - data.put("4", new Object[]{4, "John", "Adwards"}); - data.put("5", new Object[]{2, "Brian", "Schultz"}); - - int rownum = 1; - for (Map.Entry<String, Object[]> me : data.entrySet()) { - final Row row; - if (createRow) { - row = sheet.createRow(rownum++); - } else { - row = sheet.getRow(rownum++); - } - assertNotNull(row); - - int cellnum = 0; - for (Object obj : me.getValue()) { - Cell cell = row.getCell(cellnum); - if (cell == null) { - cell = row.createCell(cellnum); - } else { - if (cell.getCellType() == CellType.FORMULA) { - cell.setCellFormula(null); - cell.getCellStyle().setDataFormat((short) 0); - } - } - if (obj instanceof String) { - cell.setCellValue((String) obj); - } else if (obj instanceof Integer) { - cell.setCellValue((Integer) obj); - } - cellnum++; - } - } - - XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - - CalculationChain chain = wb.getCalculationChain(); - checkCellsAreGone(chain); - - XSSFWorkbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); - Sheet sheetBack = wbBack.getSheet("Func"); - assertNotNull(sheetBack); - - chain = wbBack.getCalculationChain(); - checkCellsAreGone(chain); - - wbBack.close(); - } - } - - private void checkCellsAreGone(CalculationChain chain) { - for (CTCalcCell calc : chain.getCTCalcChain().getCList()) { - // A2 to A6 should be gone - assertNotEquals("A2", calc.getR()); - assertNotEquals("A3", calc.getR()); - assertNotEquals("A4", calc.getR()); - assertNotEquals("A5", calc.getR()); - assertNotEquals("A6", calc.getR()); - } - } - - /** - * Excel 2007 generated Macro-Enabled .xlsm file - */ - @Test - void bug57181() throws IOException { - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57181.xlsm")) { - assertEquals(9, wb.getNumberOfSheets()); - } - } - - @Test - void bug52111() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("Intersection-52111-xssf.xlsx")) { - Sheet s = wb.getSheetAt(0); - assertFormula(wb, s.getRow(2).getCell(0), "(C2:D3 D3:E4)", "4.0"); - assertFormula(wb, s.getRow(6).getCell(0), "Tabelle2!E:E Tabelle2!11:11", "5.0"); - assertFormula(wb, s.getRow(8).getCell(0), "Tabelle2!E:F Tabelle2!11:12", null); - } - } - - @Test - void test48962() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("48962.xlsx")) { - Sheet sh = wb.getSheetAt(0); - Row row = sh.getRow(1); - Cell cell = row.getCell(0); - - CellStyle style = cell.getCellStyle(); - assertNotNull(style); - - // color index - assertEquals(64, style.getFillBackgroundColor()); - XSSFColor color = ((XSSFCellStyle) style).getFillBackgroundXSSFColor(); - assertNotNull(color); - - // indexed color - assertEquals(64, color.getIndexed()); - assertEquals(64, color.getIndex()); - - // not an RGB color - assertFalse(color.isRGB()); - assertNull(color.getRGB()); - } - } - - @Test - void test50755_workday_formula_example() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("50755_workday_formula_example.xlsx")) { - Sheet sheet = wb.getSheet("Sheet1"); - for (Row aRow : sheet) { - Cell cell = aRow.getCell(1); - if (cell.getCellType() == CellType.FORMULA) { - String formula = cell.getCellFormula(); - assertNotNull(formula); - assertTrue(formula.contains("WORKDAY")); - } else { - assertNotNull(cell.toString()); - } - } - } - } - - @Test - void test51626() throws IOException { - Workbook wb = XSSFTestDataSamples.openSampleWorkbook("51626.xlsx"); - assertNotNull(wb); - wb.close(); - - InputStream stream = HSSFTestDataSamples.openSampleFileStream("51626.xlsx"); - wb = WorkbookFactory.create(stream); - stream.close(); - wb.close(); - - wb = XSSFTestDataSamples.openSampleWorkbook("51626_contact.xlsx"); - assertNotNull(wb); - wb.close(); - - stream = HSSFTestDataSamples.openSampleFileStream("51626_contact.xlsx"); - wb = WorkbookFactory.create(stream); - stream.close(); - wb.close(); - } - - @Disabled("this test is only for manual verification, as we can't test if the cell is visible in Excel") - void test51451() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - Sheet sh = wb.createSheet(); - - Row row = sh.createRow(0); - Cell cell = row.createCell(0); - cell.setCellValue(239827342); - - CellStyle style = wb.createCellStyle(); - //style.setHidden(false); - DataFormat excelFormat = wb.createDataFormat(); - style.setDataFormat(excelFormat.getFormat("#,##0")); - sh.setDefaultColumnStyle(0, style); - } - } - - @Test - void test53105() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("53105.xlsx")) { - assertNotNull(wb); - - - // Act - // evaluate SUM('Skye Lookup Input'!A4:XFD4), cells in range each contain "1" - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - double numericValue = evaluator.evaluate(wb.getSheetAt(0).getRow(1).getCell(0)).getNumberValue(); - - // Assert - assertEquals(16384.0, numericValue, 0.0); - } - } - - - @Test - void test58315() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58315.xlsx")) { - Cell cell = wb.getSheetAt(0).getRow(0).getCell(0); - assertNotNull(cell); - StringBuilder tmpCellContent = new StringBuilder(cell.getStringCellValue()); - XSSFRichTextString richText = (XSSFRichTextString) cell.getRichStringCellValue(); - - for (int i = richText.length() - 1; i >= 0; i--) { - Font f = richText.getFontAtIndex(i); - if (f != null && f.getStrikeout()) { - tmpCellContent.deleteCharAt(i); - } - } - String result = tmpCellContent.toString(); - assertEquals("320 350", result); - } - } - - @Test - void test55406() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("55406_Conditional_formatting_sample.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - Cell cellA1 = sheet.getRow(0).getCell(0); - Cell cellA2 = sheet.getRow(1).getCell(0); - - assertEquals(0, cellA1.getCellStyle().getFillForegroundColor()); - assertEquals("FFFDFDFD", ((XSSFColor) cellA1.getCellStyle().getFillForegroundColorColor()).getARGBHex()); - assertEquals(0, cellA2.getCellStyle().getFillForegroundColor()); - assertEquals("FFFDFDFD", ((XSSFColor) cellA2.getCellStyle().getFillForegroundColorColor()).getARGBHex()); - - SheetConditionalFormatting cond = sheet.getSheetConditionalFormatting(); - assertEquals(2, cond.getNumConditionalFormattings()); - - assertEquals(1, cond.getConditionalFormattingAt(0).getNumberOfRules()); - assertEquals(64, cond.getConditionalFormattingAt(0).getRule(0).getPatternFormatting().getFillForegroundColor()); - assertEquals("ISEVEN(ROW())", cond.getConditionalFormattingAt(0).getRule(0).getFormula1()); - assertNull(((XSSFColor) cond.getConditionalFormattingAt(0).getRule(0).getPatternFormatting().getFillForegroundColorColor()).getARGBHex()); - - assertEquals(1, cond.getConditionalFormattingAt(1).getNumberOfRules()); - assertEquals(64, cond.getConditionalFormattingAt(1).getRule(0).getPatternFormatting().getFillForegroundColor()); - assertEquals("ISEVEN(ROW())", cond.getConditionalFormattingAt(1).getRule(0).getFormula1()); - assertNull(((XSSFColor) cond.getConditionalFormattingAt(1).getRule(0).getPatternFormatting().getFillForegroundColorColor()).getARGBHex()); - } - } - - @Test - void test51998() throws IOException { - Workbook wb = XSSFTestDataSamples.openSampleWorkbook("51998.xlsx"); - - Set<String> sheetNames = new HashSet<>(); - - for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { - sheetNames.add(wb.getSheetName(sheetNum)); - } - - for (String sheetName : sheetNames) { - int sheetIndex = wb.getSheetIndex(sheetName); - - wb.removeSheetAt(sheetIndex); - - Sheet newSheet = wb.createSheet(); - //Sheet newSheet = wb.createSheet(sheetName); - int newSheetIndex = wb.getSheetIndex(newSheet); - wb.setSheetName(newSheetIndex, sheetName); - wb.setSheetOrder(sheetName, sheetIndex); - } - - Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); - wb.close(); - - assertNotNull(wbBack); - wbBack.close(); - } - - @Test - void test58731() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58731.xlsx")) { - Sheet sheet = wb.createSheet("Java Books"); - - Object[][] bookData = { - {"Head First Java", "Kathy Serria", 79}, - {"Effective Java", "Joshua Bloch", 36}, - {"Clean Code", "Robert martin", 42}, - {"Thinking in Java", "Bruce Eckel", 35}, - }; - - int rowCount = 0; - for (Object[] aBook : bookData) { - Row row = sheet.createRow(rowCount++); - - int columnCount = 0; - for (Object field : aBook) { - Cell cell = row.createCell(columnCount++); - if (field instanceof String) { - cell.setCellValue((String) field); - } else if (field instanceof Integer) { - cell.setCellValue((Integer) field); - } - } - } - - try (Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - sheet = wb2.getSheet("Java Books"); - assertNotNull(sheet.getRow(0)); - assertNotNull(sheet.getRow(0).getCell(0)); - assertEquals(bookData[0][0], sheet.getRow(0).getCell(0).getStringCellValue()); - } - } - } - - /** - * Regression between 3.10.1 and 3.13 - - * org.apache.poi.openxml4j.exceptions.InvalidFormatException: - * The part /xl/sharedStrings.xml does not have any content type - * ! Rule: Package require content types when retrieving a part from a package. [M.1.14] - */ - @Test - void test58760() throws IOException { - Workbook wb1 = XSSFTestDataSamples.openSampleWorkbook("58760.xlsx"); - assertEquals(1, wb1.getNumberOfSheets()); - assertEquals("Sheet1", wb1.getSheetName(0)); - Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1); - assertEquals(1, wb2.getNumberOfSheets()); - assertEquals("Sheet1", wb2.getSheetName(0)); - wb2.close(); - wb1.close(); - } - - @Test - void test57236() throws IOException { - // Having very small numbers leads to different formatting, Excel uses the scientific notation, but POI leads to "0" - - /* - DecimalFormat format = new DecimalFormat("#.##########", new DecimalFormatSymbols(Locale.getDefault())); - double d = 3.0E-104; - assertEquals("3.0E-104", format.format(d)); - */ - - DataFormatter formatter = new DataFormatter(true); - - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57236.xlsx")) { - for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { - Sheet sheet = wb.getSheetAt(sheetNum); - for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum(); rowNum++) { - Row row = sheet.getRow(rowNum); - for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) { - Cell cell = row.getCell(cellNum); - String fmtCellValue = formatter.formatCellValue(cell); - assertNotNull(fmtCellValue); - assertNotEquals("0", fmtCellValue); - } - } - } - } - } - - /** - * helper function for {@link #test58043()} - * Side-effects: closes the provided workbook! - * - * @param workbook the workbook to save for manual checking - * @param outputFile the output file location to save the workbook to - */ - private void saveRotatedTextExample(Workbook workbook, File outputFile) throws IOException { - Sheet sheet = workbook.createSheet(); - Row row = sheet.createRow((short) 0); - - Cell cell = row.createCell(0); - - cell.setCellValue("Unsuccessful rotated text."); - - CellStyle style = workbook.createCellStyle(); - style.setRotation((short) -90); - - cell.setCellStyle(style); - - OutputStream fos = new FileOutputStream(outputFile); - workbook.write(fos); - fos.close(); - workbook.close(); - } - - @Disabled("Creates files for checking results manually, actual values are tested in Test*CellStyle") - void test58043() throws IOException { - saveRotatedTextExample(new HSSFWorkbook(), TempFile.createTempFile("rotated", ".xls")); - saveRotatedTextExample(new XSSFWorkbook(), TempFile.createTempFile("rotated", ".xlsx")); - } - - @Test - void test59132() throws IOException { - try (Workbook workbook = XSSFTestDataSamples.openSampleWorkbook("59132.xlsx")) { - Sheet worksheet = workbook.getSheet("sheet1"); - - // B3 - Row row = worksheet.getRow(2); - Cell cell = row.getCell(1); - - cell.setCellValue((String) null); - - FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); - - // B3 - row = worksheet.getRow(2); - cell = row.getCell(1); - - assertEquals(CellType.BLANK, cell.getCellType()); - assertEquals(CellType._NONE, evaluator.evaluateFormulaCell(cell)); - - // A3 - row = worksheet.getRow(2); - cell = row.getCell(0); - - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(ISBLANK(B3),\"\",B3)", cell.getCellFormula()); - assertEquals(CellType.STRING, evaluator.evaluateFormulaCell(cell)); - CellValue value = evaluator.evaluate(cell); - assertEquals("", value.getStringValue()); - - // A5 - row = worksheet.getRow(4); - cell = row.getCell(0); - - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("COUNTBLANK(A1:A4)", cell.getCellFormula()); - assertEquals(CellType.NUMERIC, evaluator.evaluateFormulaCell(cell)); - value = evaluator.evaluate(cell); - assertEquals(1.0, value.getNumberValue(), 0.1); - } - } - - @Disabled("bug 59442") - @Test - void testSetRGBBackgroundColor() throws IOException { - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFCell cell = workbook.createSheet().createRow(0).createCell(0); - - XSSFColor color = new XSSFColor(java.awt.Color.RED, workbook.getStylesSource().getIndexedColors()); - XSSFCellStyle style = workbook.createCellStyle(); - style.setFillForegroundColor(color); - style.setFillPattern(FillPatternType.SOLID_FOREGROUND); - cell.setCellStyle(style); - - // Everything is fine at this point, cell is red - XSSFColor actual = cell.getCellStyle().getFillBackgroundColorColor(); - assertNull(actual); - actual = cell.getCellStyle().getFillForegroundColorColor(); - assertNotNull(actual); - assertEquals(color.getARGBHex(), actual.getARGBHex()); - - Map<String, Object> properties = new HashMap<>(); - properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.THIN); - CellUtil.setCellStyleProperties(cell, properties); - - // Now the cell is all black - actual = cell.getCellStyle().getFillBackgroundColorColor(); - assertNotNull(actual); - assertNull(actual.getARGBHex()); - actual = cell.getCellStyle().getFillForegroundColorColor(); - assertNotNull(actual); - assertEquals(color.getARGBHex(), actual.getARGBHex()); - - XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(workbook); - workbook.close(); - XSSFCell ncell = nwb.getSheetAt(0).getRow(0).getCell(0); - XSSFColor ncolor = new XSSFColor(java.awt.Color.RED, workbook.getStylesSource().getIndexedColors()); - - // Now the cell is all black - XSSFColor nactual = ncell.getCellStyle().getFillBackgroundColorColor(); - assertNotNull(nactual); - assertEquals(ncolor.getARGBHex(), nactual.getARGBHex()); - - nwb.close(); - } - - @Disabled("currently fails on POI 3.15 beta 2") - @Test - void test55273() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("ExcelTables.xlsx")) { - Sheet sheet = wb.getSheet("ExcelTable"); - - Name name = wb.getName("TableAsRangeName"); - assertEquals("TableName[#All]", name.getRefersToFormula()); - // POI 3.15-beta 2 (2016-06-15): getSheetName throws IllegalArgumentException: Invalid CellReference: TableName[#All] - assertEquals("TableName", name.getSheetName()); - - XSSFSheet xsheet = (XSSFSheet) sheet; - List<XSSFTable> tables = xsheet.getTables(); - assertEquals(2, tables.size()); //FIXME: how many tables are there in this spreadsheet? - assertEquals("Table1", tables.get(0).getName()); //FIXME: what is the table name? - assertEquals("Table2", tables.get(1).getName()); //FIXME: what is the table name? - } - } - - @Test - void test57523() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57523.xlsx")) { - Sheet sheet = wb.getSheet("Attribute Master"); - Row row = sheet.getRow(15); - - int N = CellReference.convertColStringToIndex("N"); - Cell N16 = row.getCell(N); - assertEquals(500.0, N16.getNumericCellValue(), 0.00001); - - int P = CellReference.convertColStringToIndex("P"); - Cell P16 = row.getCell(P); - assertEquals(10.0, P16.getNumericCellValue(), 0.00001); - } - } - - /** - * Files produced by some scientific equipment neglect - * to include the row number on the row tags - */ - @Test - void noRowNumbers59746() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("59746_NoRowNums.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - assertTrue(sheet.getLastRowNum() > 20, "Last row num: " + sheet.getLastRowNum()); - assertEquals("Checked", sheet.getRow(0).getCell(0).getStringCellValue()); - assertEquals("Checked", sheet.getRow(9).getCell(2).getStringCellValue()); - assertFalse(sheet.getRow(70).getCell(8).getBooleanCellValue()); - assertEquals(71, sheet.getPhysicalNumberOfRows()); - assertEquals(70, sheet.getLastRowNum()); - assertEquals(70, sheet.getRow(sheet.getLastRowNum()).getRowNum()); - } - } - - @Test - void testWorkdayFunction() throws IOException { - try (XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("59106.xlsx")) { - XSSFSheet sheet = workbook.getSheet("Test"); - Row row = sheet.getRow(1); - Cell cell = row.getCell(0); - DataFormatter form = new DataFormatter(); - FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); - String result = form.formatCellValue(cell, evaluator); - - assertEquals("09 Mar 2016", result); - } - } - - // This bug is currently open. When this bug is fixed, it should not throw an AssertionError - @Test - void test55076_collapseColumnGroups() throws Exception { - try (Workbook wb = new XSSFWorkbook()) { - Sheet sheet = wb.createSheet(); - - // this column collapsing bug only occurs when the grouped columns are different widths - sheet.setColumnWidth(1, 400); - sheet.setColumnWidth(2, 600); - sheet.setColumnWidth(3, 800); - - assertEquals(400, sheet.getColumnWidth(1)); - assertEquals(600, sheet.getColumnWidth(2)); - assertEquals(800, sheet.getColumnWidth(3)); - - sheet.groupColumn(1, 3); - sheet.setColumnGroupCollapsed(1, true); - - assertEquals(0, sheet.getColumnOutlineLevel(0)); - assertEquals(1, sheet.getColumnOutlineLevel(1)); - assertEquals(1, sheet.getColumnOutlineLevel(2)); - assertEquals(1, sheet.getColumnOutlineLevel(3)); - assertEquals(0, sheet.getColumnOutlineLevel(4)); - - // none of the columns should be hidden - // column group collapsing is a different concept - for (int c = 0; c < 5; c++) { - // if this fails for c == 1 in the future, then the implementation will be correct - // and we need to adapt this test accordingly - assertEquals(c == 1, sheet.isColumnHidden(c), "Column " + c); - } - - assertEquals(400, sheet.getColumnWidth(1)); - // 600 is the correct value! ... 2048 is just for pacifying the test (see above comment) - assertEquals(2048, sheet.getColumnWidth(2)); - assertEquals(800, sheet.getColumnWidth(3)); - - } - } - - /** - * Other things, including charts, may end up taking drawing part - * numbers. (Uses a test file hand-crafted with an extra non-drawing - * part with a part number) - */ - @Test - void drawingNumbersAlreadyTaken_60255() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("60255_extra_drawingparts.xlsx")) { - assertEquals(4, wb.getNumberOfSheets()); - - // Sheet 3 starts with a drawing - Sheet sheet = wb.getSheetAt(0); - assertNull(sheet.getDrawingPatriarch()); - sheet = wb.getSheetAt(1); - assertNull(sheet.getDrawingPatriarch()); - sheet = wb.getSheetAt(2); - assertNotNull(sheet.getDrawingPatriarch()); - sheet = wb.getSheetAt(3); - assertNull(sheet.getDrawingPatriarch()); - - // Add another sheet, and give it a drawing - sheet = wb.createSheet(); - assertNull(sheet.getDrawingPatriarch()); - sheet.createDrawingPatriarch(); - assertNotNull(sheet.getDrawingPatriarch()); - - // Save and check - Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); - assertEquals(5, wbBack.getNumberOfSheets()); - - // Sheets 3 and 5 now - sheet = wbBack.getSheetAt(0); - assertNull(sheet.getDrawingPatriarch()); - sheet = wbBack.getSheetAt(1); - assertNull(sheet.getDrawingPatriarch()); - sheet = wbBack.getSheetAt(2); - assertNotNull(sheet.getDrawingPatriarch()); - sheet = wbBack.getSheetAt(3); - assertNull(sheet.getDrawingPatriarch()); - sheet = wbBack.getSheetAt(4); - assertNotNull(sheet.getDrawingPatriarch()); - } - } - - @Test - void test53611() throws IOException { - Workbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet("test"); - Row row = sheet.createRow(1); - Cell cell = row.createCell(1); - cell.setCellValue("blabla"); - - //0 1 2 3 4 5 6 7 - //A B C D E F G H - row = sheet.createRow(4); - cell = row.createCell(7); - cell.setCellValue("blabla"); - - // we currently only populate the dimension during writing out - // to avoid having to iterate all rows/cells in each add/remove of a row or cell - wb.write(new NullOutputStream()); - - assertEquals("B2:H5", ((XSSFSheet) sheet).getCTWorksheet().getDimension().getRef()); - - wb.close(); - } - - @Test - void test61798() throws IOException { - Workbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet("test"); - Row row = sheet.createRow(1); - Cell cell = row.createCell(1); - cell.setCellValue("blabla"); - - row = sheet.createRow(4); - // Allowable column range for EXCEL2007 is (0..16383) or ('A'..'XDF') - cell = row.createCell(16383); - cell.setCellValue("blabla"); - - // we currently only populate the dimension during writing out - // to avoid having to iterate all rows/cells in each add/remove of a row or cell - wb.write(new NullOutputStream()); - - assertEquals("B2:XFD5", ((XSSFSheet)sheet).getCTWorksheet().getDimension().getRef()); - - wb.close(); - } - - @Test - void bug61063() throws Exception { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61063.xlsx")) { - - FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); - Sheet s = wb.getSheetAt(0); - - Row r = s.getRow(3); - Cell c = r.getCell(0); - assertEquals(CellType.FORMULA, c.getCellType()); - eval.setDebugEvaluationOutputForNextEval(true); - CellValue cv = eval.evaluate(c); - assertNotNull(cv); - assertEquals(2.0, cv.getNumberValue(), 0.00001, "Had: " + cv); - } - } - - @Test - void bug61516() throws IOException { - final String initialFormula = "A1"; - final String expectedFormula = "#REF!"; // from ms excel - - XSSFWorkbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet("sheet1"); - sheet.createRow(0).createCell(0).setCellValue(1); // A1 = 1 - - { - Cell c3 = sheet.createRow(2).createCell(2); - c3.setCellFormula(initialFormula); // C3 = =A1 - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - CellValue cellValue = evaluator.evaluate(c3); - assertEquals(1, cellValue.getNumberValue(), 0.0001); - } - - { - FormulaShifter formulaShifter = FormulaShifter.createForRowCopy(0, "sheet1", 2/*firstRowToShift*/, 2/*lastRowToShift*/ - , -1/*step*/, SpreadsheetVersion.EXCEL2007); // parameters 2, 2, -1 should mean : move row range [2-2] one level up - XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); - Ptg[] ptgs = FormulaParser.parse(initialFormula, fpb, FormulaType.CELL, 0); // [A1] - formulaShifter.adjustFormula(ptgs, 0); // adjusted to [A] - String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); //A - //System.out.println(String.format("initial formula : A1; expected formula value after shifting up : #REF!; actual formula value : %s", shiftedFmla)); - assertEquals(expectedFormula, shiftedFmla, - "On copy we expect the formula to be adjusted, in this case it would point to row -1, which is an invalid REF"); - } - - { - FormulaShifter formulaShifter = FormulaShifter.createForRowShift(0, "sheet1", 2/*firstRowToShift*/, 2/*lastRowToShift*/ - , -1/*step*/, SpreadsheetVersion.EXCEL2007); // parameters 2, 2, -1 should mean : move row range [2-2] one level up - XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); - Ptg[] ptgs = FormulaParser.parse(initialFormula, fpb, FormulaType.CELL, 0); // [A1] - formulaShifter.adjustFormula(ptgs, 0); // adjusted to [A] - String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); //A - //System.out.println(String.format("initial formula : A1; expected formula value after shifting up : #REF!; actual formula value : %s", shiftedFmla)); - assertEquals(initialFormula, shiftedFmla, - "On move we expect the formula to stay the same, thus expecting the initial formula A1 here"); - } - - sheet.shiftRows(2, 2, -1); - { - Cell c2 = sheet.getRow(1).getCell(2); - assertNotNull(c2, "cell C2 needs to exist now"); - assertEquals(CellType.FORMULA, c2.getCellType()); - assertEquals(initialFormula, c2.getCellFormula()); - FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); - CellValue cellValue = evaluator.evaluate(c2); - assertEquals(1, cellValue.getNumberValue(), 0.0001); - } - - wb.close(); - } - - @Test - void test61652() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61652.xlsx")) { - Sheet sheet = wb.getSheet("IRPPCalc"); - Row row = sheet.getRow(11); - Cell cell = row.getCell(18); - WorkbookEvaluatorProvider fe = (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator(); - ConditionalFormattingEvaluator condfmt = new ConditionalFormattingEvaluator(wb, fe); - - assertEquals("[]", condfmt.getConditionalFormattingForCell(cell).toString(), - "Conditional formatting is not triggered for this cell"); - - // but we can read the conditional formatting itself - List<EvaluationConditionalFormatRule> rules = condfmt.getFormatRulesForSheet(sheet); - assertEquals(1, rules.size()); - assertEquals("AND($A1>=EDATE($D$6,3),$B1>0)", rules.get(0).getFormula1()); - } - } - - @Test - void test61543() throws IOException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet(); - XSSFTable table1 = sheet.createTable(null); - XSSFTable table2 = sheet.createTable(null); - XSSFTable table3 = sheet.createTable(null); - - assertDoesNotThrow(() -> sheet.removeTable(table1)); - - assertDoesNotThrow(() -> sheet.createTable(null)); - - assertDoesNotThrow(() -> sheet.removeTable(table2)); - assertDoesNotThrow(() -> sheet.removeTable(table3)); - - assertDoesNotThrow(() -> sheet.createTable(null)); - } - } - - /** - * Auto column sizing failed when there were loads of fonts with - * errors like ArrayIndexOutOfBoundsException: -32765 - */ - @Test - void test62108() throws IOException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet(); - XSSFRow row = sheet.createRow(0); - - // Create lots of fonts - XSSFDataFormat formats = wb.createDataFormat(); - XSSFFont[] fonts = new XSSFFont[50000]; - for (int i = 0; i < fonts.length; i++) { - XSSFFont font = wb.createFont(); - font.setFontHeight(i); - fonts[i] = font; - } - - // Create a moderate number of columns, which use - // fonts from the start and end of the font list - final int numCols = 125; - for (int i = 0; i < numCols; i++) { - XSSFCellStyle cs = wb.createCellStyle(); - cs.setDataFormat(formats.getFormat("'Test " + i + "' #,###")); - - XSSFFont font = fonts[i]; - if (i % 2 == 1) { - font = fonts[fonts.length - i]; - } - cs.setFont(font); - - XSSFCell c = row.createCell(i); - c.setCellValue(i); - c.setCellStyle(cs); - } - - // Do the auto-size - for (int i = 0; i < numCols; i++) { - int i2 = i; - assertDoesNotThrow(() -> sheet.autoSizeColumn(i2)); - } - } - } - - @Test - void test61905xlsx() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - checkActiveSheet(wb, XSSFITestDataProvider.instance); - } - } - - @Test - void test61905xls() throws IOException { - try (Workbook wb = new HSSFWorkbook()) { - checkActiveSheet(wb, HSSFITestDataProvider.instance); - } - } - - private void checkActiveSheet(Workbook wb, ITestDataProvider instance) throws IOException { - Sheet sheet = wb.createSheet("new sheet"); - sheet.setActiveCell(new CellAddress("E11")); - assertEquals("E11", sheet.getActiveCell().formatAsString()); - - Workbook wbBack = instance.writeOutAndReadBack(wb); - sheet = wbBack.getSheetAt(0); - assertEquals("E11", sheet.getActiveCell().formatAsString()); - wbBack.close(); - } - - @Test - void testBug54084Unicode() throws IOException { - // sample XLSX with the same text-contents as the text-file above - try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("54084 - Greek - beyond BMP.xlsx")) { - - verifyBug54084Unicode(wb); - - //XSSFTestDataSamples.writeOut(wb, "bug 54084 for manual review"); - - // now write the file and read it back in - XSSFWorkbook wbWritten = XSSFTestDataSamples.writeOutAndReadBack(wb); - verifyBug54084Unicode(wbWritten); - - // finally also write it out via the streaming interface and verify that we still can read it back in - SXSSFWorkbook swb = new SXSSFWorkbook(wb); - Workbook wbStreamingWritten = SXSSFITestDataProvider.instance.writeOutAndReadBack(swb); - verifyBug54084Unicode(wbStreamingWritten); - - wbWritten.close(); - swb.close(); - wbStreamingWritten.close(); - } - } - - private void verifyBug54084Unicode(Workbook wb) { - // expected data is stored in UTF-8 in a text-file - byte[] data = HSSFTestDataSamples.getTestDataFileContent("54084 - Greek - beyond BMP.txt"); - String testData = new String(data, StandardCharsets.UTF_8).trim(); - - Sheet sheet = wb.getSheetAt(0); - Row row = sheet.getRow(0); - Cell cell = row.getCell(0); - - String value = cell.getStringCellValue(); - //System.out.println(value); - - assertEquals(testData, value, "The data in the text-file should exactly match the data that we read from the workbook"); - } - - - @Test - void bug63371() throws IOException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet(); - - CellRangeAddress region = new CellRangeAddress(1, 1, 1, 2); - assertEquals(0, sheet.addMergedRegion(region)); - //System.out.println(String.format("%s: index=%d", "testAddMergedRegion", index)); - - final List<CellRangeAddress> ranges = sheet.getMergedRegions(); - final int numMergedRegions = sheet.getNumMergedRegions(); - final CTWorksheet ctSheet = sheet.getCTWorksheet(); - final CTMergeCells ctMergeCells = ctSheet.getMergeCells(); - final List<CTMergeCell> ctMergeCellList = ctMergeCells.getMergeCellList(); - final long ctMergeCellCount = ctMergeCells.getCount(); - final int ctMergeCellListSize = ctMergeCellList.size(); - - /*System.out.println(String.format("\ntestMergeRegions(%s)", "After adding first region")); - System.out.println(String.format("ranges.size=%d", ranges.size())); - System.out.println(String.format("numMergedRegions=%d", numMergedRegions)); - System.out.println(String.format("ctMergeCellCount=%d", ctMergeCellCount)); - System.out.println(String.format("ctMergeCellListSize=%d", ctMergeCellListSize));*/ - - assertEquals(1, ranges.size()); - assertEquals(1, numMergedRegions); - assertEquals(1, ctMergeCellCount); - assertEquals(1, ctMergeCellListSize); - - } - } - - @Test - void bug60397() throws IOException { - try (XSSFWorkbook wb = new XSSFWorkbook()) { - XSSFSheet sheet = wb.createSheet(); - - CellRangeAddress region = new CellRangeAddress(1, 1, 1, 2); - assertEquals(0, sheet.addMergedRegion(region)); - //System.out.println(String.format("%s: index=%d", "testAddMergedRegion", index)); - - List<CellRangeAddress> ranges = sheet.getMergedRegions(); - int numMergedRegions = sheet.getNumMergedRegions(); - CTWorksheet ctSheet = sheet.getCTWorksheet(); - CTMergeCells ctMergeCells = ctSheet.getMergeCells(); - List<CTMergeCell> ctMergeCellList = ctMergeCells.getMergeCellList(); - long ctMergeCellCount = ctMergeCells.getCount(); - int ctMergeCellListSize = ctMergeCellList.size(); - - /*System.out.println(String.format("\ntestMergeRegions(%s)", "After adding first region")); - System.out.println(String.format("ranges.size=%d", ranges.size())); - System.out.println(String.format("numMergedRegions=%d", numMergedRegions)); - System.out.println(String.format("ctMergeCellCount=%d", ctMergeCellCount)); - System.out.println(String.format("ctMergeCellListSize=%d", ctMergeCellListSize)); */ - - assertEquals(1, ranges.size()); - assertEquals(1, numMergedRegions); - assertEquals(1, ctMergeCellCount); - assertEquals(1, ctMergeCellListSize); - - CellRangeAddress region2 = new CellRangeAddress(1, 2, 4, 6); - assertEquals(1, sheet.addMergedRegion(region2)); - //System.out.println(String.format("%s: index=%d", "testAddMergedRegion", index)); - - ranges = sheet.getMergedRegions(); - numMergedRegions = sheet.getNumMergedRegions(); - ctSheet = sheet.getCTWorksheet(); - ctMergeCells = ctSheet.getMergeCells(); - ctMergeCellList = ctMergeCells.getMergeCellList(); - ctMergeCellCount = ctMergeCells.getCount(); - ctMergeCellListSize = ctMergeCellList.size(); - - /*System.out.println(String.format("\ntestMergeRegions(%s)", "After adding second region")); - System.out.println(String.format("ranges.size=%d", ranges.size())); - System.out.println(String.format("numMergedRegions=%d", numMergedRegions)); - System.out.println(String.format("ctMergeCellCount=%d", ctMergeCellCount)); - System.out.println(String.format("ctMergeCellListSize=%d", ctMergeCellListSize));*/ - - assertEquals(2, ranges.size()); - assertEquals(2, numMergedRegions); - assertEquals(2, ctMergeCellCount); - assertEquals(2, ctMergeCellListSize); - } - } - - @Test - void testBug63509() throws IOException { - try (XSSFWorkbook workbook = new XSSFWorkbook()) { - - XSSFSheet sheet = workbook.createSheet("sheet1"); - - Row row = sheet.createRow(0); - - Cell cell = row.createCell(0); - cell.setCellValue("1000"); - - // This causes the error - sheet.addIgnoredErrors(new CellReference(cell), IgnoredErrorType.NUMBER_STORED_AS_TEXT); - - // Workaround - // sheet.addIgnoredErrors(new CellReference(cell.getRowIndex(), cell.getColumnIndex(), false, false), - // IgnoredErrorType.NUMBER_STORED_AS_TEXT); - - String sqref = sheet.getCTWorksheet().getIgnoredErrors().getIgnoredErrorArray(0).getSqref().get(0).toString(); - assertEquals("A1", sqref); - } - } - - @Test - void test64045() { - File file = XSSFTestDataSamples.getSampleFile("xlsx-corrupted.xlsx"); - assertThrows(POIXMLException.class, () -> new XSSFWorkbook(file), "Should catch exception as the file is corrupted"); - } - - @Test - void test58896WithFile() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58896.xlsx")) { - Sheet sheet = wb.getSheetAt(0); - Instant start = now(); - - LOG.atInfo().log("Autosizing columns..."); - - for (int i = 0; i < 3; ++i) { - LOG.atInfo().log("Autosize {} - {}", box(i), between(start, now())); - sheet.autoSizeColumn(i); - } - - for (int i = 0; i < 69 - 35 + 1; ++i) - for (int j = 0; j < 8; ++j) { - int col = 3 + 2 + i * (8 + 2) + j; - LOG.atInfo().log("Autosize {} - {}", box(col), between(start, now())); - sheet.autoSizeColumn(col); - } - LOG.atInfo().log(between(start, now())); - - assertTrue(between(start, now()).getSeconds() < 25); - } - } - - @Test - void testBug63845() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - Sheet sheet = wb.createSheet(); - Row row = sheet.createRow(0); - - Cell cell = row.createCell(0, CellType.FORMULA); - cell.setCellFormula("SUM(B1:E1)"); - - assertNull(((XSSFCell) cell).getCTCell().getV(), - "Element 'v' should not be set for formulas unless the value was calculated"); - - try (Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - Cell cellBack = wbBack.getSheetAt(0).getRow(0).getCell(0); - assertNull(((XSSFCell) cellBack).getCTCell().getV(), - "Element 'v' should not be set for formulas unless the value was calculated"); - assertNotNull(((XSSFCell) cellBack).getCTCell().getF(), - "Formula should be set internally now"); - - wbBack.getCreationHelper().createFormulaEvaluator().evaluateInCell(cellBack); - - assertEquals("0.0", ((XSSFCell) cellBack).getCTCell().getV(), - "Element 'v' should be set now as the formula was calculated manually"); - - cellBack.setCellValue("123"); - assertEquals("123", cellBack.getStringCellValue(), - "String value should be set now"); - assertNull(((XSSFCell) cellBack).getCTCell().getF(), "No formula should be set any more"); - } - } - } - - @Test - void testBug63845_2() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - Sheet sheet = wb.createSheet("test"); - Row row = sheet.createRow(0); - row.createCell(0).setCellValue(2); - row.createCell(1).setCellValue(5); - row.createCell(2).setCellFormula("A1+B1"); - - try (Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb)) { - Cell cellBack = wbBack.getSheetAt(0).getRow(0).getCell(2); - - assertNull(((XSSFCell) cellBack).getCTCell().getV(), - "Element 'v' should not be set for formulas unless the value was calculated"); - - wbBack.getCreationHelper().createFormulaEvaluator().evaluateInCell(cellBack); - - assertEquals("7.0", ((XSSFCell) cellBack).getCTCell().getV(), - "Element 'v' should be set now as the formula was calculated manually"); - } - } - } - - @Test - void testBug64508() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("64508.xlsx")) { - int activeSheet = wb.getActiveSheetIndex(); - Sheet sheet1 = wb.getSheetAt(activeSheet); - Row row = sheet1.getRow(1); - CellReference aCellReference = new CellReference("E2"); - Cell aCell = row.getCell(aCellReference.getCol()); - assertEquals(CellType.STRING, aCell.getCellType()); - assertEquals("", aCell.getStringCellValue()); - } - } - - @Test - void testBug64667() throws IOException { - //test that an NPE isn't thrown on opening - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("64667.xlsx")) { - int activeSheet = wb.getActiveSheetIndex(); - assertEquals(0, activeSheet); - assertNotNull(wb.getSheetAt(activeSheet)); - } - } - - - - @Test - void testXLSXinPPT() throws Exception { - assumeFalse(Boolean.getBoolean("scratchpad.ignore")); - - try (SlideShow<?,?> ppt = SlideShowFactory.create( - POIDataSamples.getSlideShowInstance().openResourceAsStream("testPPT_oleWorkbook.ppt"))) { - - Slide<?, ?> slide = ppt.getSlides().get(1); - ObjectShape<?,?> oleShape = (ObjectShape<?,?>)slide.getShapes().get(2); - - org.apache.poi.sl.usermodel.ObjectData data = oleShape.getObjectData(); - assertNull(data.getFileName()); - - // Will be OOXML wrapped in OLE2, not directly SpreadSheet - POIFSFileSystem fs = new POIFSFileSystem(data.getInputStream()); - assertTrue(fs.getRoot().hasEntry(OOXML_PACKAGE)); - assertFalse(fs.getRoot().hasEntry("Workbook")); - - - // Can fetch Package to get OOXML - DirectoryNode root = fs.getRoot(); - DocumentEntry docEntry = (DocumentEntry) root.getEntry(OOXML_PACKAGE); - try (DocumentInputStream dis = new DocumentInputStream(docEntry); - OPCPackage pkg = OPCPackage.open(dis); - XSSFWorkbook wb = new XSSFWorkbook(pkg)) { - assertEquals(1, wb.getNumberOfSheets()); - } - - // Via the XSSF Factory - XSSFWorkbookFactory xssfFactory = new XSSFWorkbookFactory(); - try (XSSFWorkbook wb = xssfFactory.create(fs.getRoot(), null)) { - assertEquals(1, wb.getNumberOfSheets()); - } - - - // Or can open via the normal Factory, as stream or OLE2 - try (Workbook wb = WorkbookFactory.create(fs)) { - assertEquals(1, wb.getNumberOfSheets()); - } - try (Workbook wb = WorkbookFactory.create(data.getInputStream())) { - assertEquals(1, wb.getNumberOfSheets()); - } - } - } - - @Test - void test64986() { - XSSFWorkbook w = new XSSFWorkbook(); - XSSFSheet s = w.createSheet(); - XSSFRow r = s.createRow(0); - XSSFCell c = r.createCell(0); - c.setCellFormula("MATCH(\"VAL\",B1:B11,)"); - - FormulaEvaluator evaluator = w.getCreationHelper().createFormulaEvaluator(); - CellValue value = evaluator.evaluate(c); - assertEquals(CellType.ERROR, value.getCellType()); - assertEquals(ErrorEval.NA.getErrorCode(), value.getErrorValue()); - - // put a value in place so the match should find something - Cell val = r.createCell(1); - val.setCellValue("VAL"); - - // clear and check that now we find a match - evaluator.clearAllCachedResultValues(); - value = evaluator.evaluate(c); - assertEquals(CellType.NUMERIC, value.getCellType()); - assertEquals(1, value.getNumberValue(), 0.01); - } - - @Test - void test64750() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("64750.xlsx")) { - Sheet sheet = wb.getSheet("Sheet1"); - assertEquals(1, sheet.getDataValidations().size()); - } - } - - @Test - void test64450() throws IOException { - try (Workbook wb = XSSFTestDataSamples.openSampleWorkbook("64450.xlsx")) { - assertNotNull(wb); - } - } - - @Test - void test64494() throws IOException { - try (Workbook wb = new XSSFWorkbook()) { - CellStyle styleRight = wb.createCellStyle(); - CellStyle styleLeft = wb.createCellStyle(); - styleRight.setAlignment(HorizontalAlignment.RIGHT); - //styleRight.setBorderBottom(BorderStyle.DASH_DOT); - styleLeft.setAlignment(HorizontalAlignment.LEFT); - //styleLeft.setBorderRight(BorderStyle.MEDIUM); - - assertEquals(HorizontalAlignment.RIGHT, styleRight.getAlignment()); - assertEquals(HorizontalAlignment.LEFT, styleLeft.getAlignment()); - - Sheet sheet = wb.createSheet("test"); - Row row = sheet.createRow(0); - - Cell cellRight = row.createCell(0); - cellRight.setCellValue("R"); - cellRight.setCellStyle(styleRight); - - Cell cellLeft = row.createCell(1); - cellLeft.setCellValue("L"); - cellLeft.setCellStyle(styleLeft); - - /*try (OutputStream out = new FileOutputStream("/tmp/64494.xlsx")) { - wb.write(out); - }*/ - - assertEquals(HorizontalAlignment.RIGHT, cellRight.getCellStyle().getAlignment()); - assertEquals(HorizontalAlignment.LEFT, cellLeft.getCellStyle().getAlignment()); - } - } -} |