diff options
Diffstat (limited to 'poi-ooxml')
12 files changed, 760 insertions, 775 deletions
diff --git a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/DigestOutputStream.java b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/DigestOutputStream.java index 35e0f4924a..077d487bcc 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/DigestOutputStream.java +++ b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/DigestOutputStream.java @@ -66,13 +66,14 @@ import org.ietf.jgss.Oid; } public byte[] sign() throws IOException, GeneralSecurityException { - UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream(); - bos.write(getHashMagic()); - bos.write(md.digest()); + try (UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream()) { + bos.write(getHashMagic()); + bos.write(md.digest()); - final Cipher cipher = CryptoFunctions.getCipher(key, CipherAlgorithm.rsa - , ChainingMode.ecb, null, Cipher.ENCRYPT_MODE, "PKCS1Padding"); - return cipher.doFinal(bos.toByteArray()); + final Cipher cipher = CryptoFunctions.getCipher(key, CipherAlgorithm.rsa + , ChainingMode.ecb, null, Cipher.ENCRYPT_MODE, "PKCS1Padding"); + return cipher.doFinal(bos.toByteArray()); + } } static boolean isMSCapi(final PrivateKey key) { @@ -91,10 +92,9 @@ import org.ietf.jgss.Oid; // in an earlier release the hashMagic (aka DigestAlgorithmIdentifier) contained only // an object identifier, but to conform with the header generated by the // javax-signature API, the empty <associated parameters> are also included - try { + try (UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream()) { final byte[] oidBytes = new Oid(algo.rsaOid).getDER(); - final UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream(); bos.write(0x30); bos.write(algo.hashSize+oidBytes.length+6); bos.write(0x30); diff --git a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/OOXMLURIDereferencer.java b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/OOXMLURIDereferencer.java index ff977fa45a..d01724a923 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/OOXMLURIDereferencer.java +++ b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/OOXMLURIDereferencer.java @@ -90,12 +90,13 @@ public class OOXMLURIDereferencer implements URIDereferencer { // although xmlsec has an option to ignore line breaks, currently this // only affects .rels files, so we only modify these // http://stackoverflow.com/questions/4728300 - UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream(); - for (int ch; (ch = dataStream.read()) != -1; ) { - if (ch == 10 || ch == 13) continue; - bos.write(ch); + try (UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream()) { + for (int ch; (ch = dataStream.read()) != -1; ) { + if (ch == 10 || ch == 13) continue; + bos.write(ch); + } + dataStream = bos.toInputStream(); } - dataStream = bos.toInputStream(); } } catch (IOException e) { throw new URIReferenceException("I/O error: " + e.getMessage(), e); diff --git a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/facets/XAdESXLSignatureFacet.java b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/facets/XAdESXLSignatureFacet.java index 2a1a32c5a5..d500c6a8c7 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/facets/XAdESXLSignatureFacet.java +++ b/poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/facets/XAdESXLSignatureFacet.java @@ -289,8 +289,7 @@ public class XAdESXLSignatureFacet implements SignatureFacet { } public static byte[] getC14nValue(List<Node> nodeList, String c14nAlgoId) { - UnsynchronizedByteArrayOutputStream c14nValue = new UnsynchronizedByteArrayOutputStream(); - try { + try (UnsynchronizedByteArrayOutputStream c14nValue = new UnsynchronizedByteArrayOutputStream()) { for (Node node : nodeList) { /* * Re-initialize the c14n else the namespaces will get cached @@ -299,12 +298,12 @@ public class XAdESXLSignatureFacet implements SignatureFacet { Canonicalizer c14n = Canonicalizer.getInstance(c14nAlgoId); c14n.canonicalizeSubtree(node, c14nValue); } + return c14nValue.toByteArray(); } catch (RuntimeException e) { throw e; } catch (Exception e) { throw new RuntimeException("c14n error: " + e.getMessage(), e); } - return c14nValue.toByteArray(); } private BigInteger getCrlNumber(X509CRL crl) { diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java index f868f14a2b..b155a79654 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java @@ -27,34 +27,39 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCfvoType; * Colour Scale change thresholds */ public class XSSFConditionalFormattingThreshold implements org.apache.poi.ss.usermodel.ConditionalFormattingThreshold { - private CTCfvo cfvo; - + private final CTCfvo cfvo; + protected XSSFConditionalFormattingThreshold(CTCfvo cfvo) { this.cfvo = cfvo; } - + protected CTCfvo getCTCfvo() { return cfvo; } + @Override public RangeType getRangeType() { return RangeType.byName(cfvo.getType().toString()); } + @Override public void setRangeType(RangeType type) { STCfvoType.Enum xtype = STCfvoType.Enum.forString(type.name); cfvo.setType(xtype); } + @Override public String getFormula() { if (cfvo.getType() == STCfvoType.FORMULA) { return cfvo.getVal(); } return null; } + @Override public void setFormula(String formula) { cfvo.setVal(formula); } + @Override public Double getValue() { if (cfvo.getType() == STCfvoType.FORMULA || cfvo.getType() == STCfvoType.MIN || @@ -67,6 +72,7 @@ public class XSSFConditionalFormattingThreshold implements org.apache.poi.ss.use return null; } } + @Override public void setValue(Double value) { if (value == null) { cfvo.unsetVal(); diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java index 29062ee06f..50bf90fd8b 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java @@ -25,7 +25,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTIconSet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.STIconSetType; /** - * High level representation for Icon / Multi-State Formatting + * High level representation for Icon / Multi-State Formatting * component of Conditional Formatting settings */ public class XSSFIconMultiStateFormatting implements IconMultiStateFormatting { @@ -35,42 +35,50 @@ public class XSSFIconMultiStateFormatting implements IconMultiStateFormatting { _iconset = iconset; } + @Override public IconSet getIconSet() { String set = _iconset.getIconSet().toString(); return IconSet.byName(set); } + @Override public void setIconSet(IconSet set) { STIconSetType.Enum xIconSet = STIconSetType.Enum.forString(set.name); _iconset.setIconSet(xIconSet); } + @Override public boolean isIconOnly() { if (_iconset.isSetShowValue()) return !_iconset.getShowValue(); return false; } + @Override public void setIconOnly(boolean only) { _iconset.setShowValue(!only); } + @Override public boolean isReversed() { if (_iconset.isSetReverse()) return _iconset.getReverse(); return false; } + @Override public void setReversed(boolean reversed) { _iconset.setReverse(reversed); } + @Override public XSSFConditionalFormattingThreshold[] getThresholds() { CTCfvo[] cfvos = _iconset.getCfvoArray(); - XSSFConditionalFormattingThreshold[] t = + XSSFConditionalFormattingThreshold[] t = new XSSFConditionalFormattingThreshold[cfvos.length]; for (int i=0; i<cfvos.length; i++) { t[i] = new XSSFConditionalFormattingThreshold(cfvos[i]); } return t; } + @Override public void setThresholds(ConditionalFormattingThreshold[] thresholds) { CTCfvo[] cfvos = new CTCfvo[thresholds.length]; for (int i=0; i<thresholds.length; i++) { @@ -78,6 +86,7 @@ public class XSSFIconMultiStateFormatting implements IconMultiStateFormatting { } _iconset.setCfvoArray(cfvos); } + @Override public XSSFConditionalFormattingThreshold createThreshold() { return new XSSFConditionalFormattingThreshold(_iconset.addNewCfvo()); } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbookFactory.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbookFactory.java index 3ac689f372..8d49b1eaf9 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbookFactory.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbookFactory.java @@ -139,6 +139,7 @@ public class XSSFWorkbookFactory implements WorkbookProvider { * @throws IOException if an error occurs while reading the data * @throws EncryptedDocumentException If the wrong password is given for a protected file */ + @Override @SuppressWarnings("resource") public XSSFWorkbook create(File file, String password, boolean readOnly) throws IOException { FileMagic fm = FileMagic.valueOf(file); diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java index 5672564fd0..0afb927551 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/extractor/TestXSSFExportToXML.java @@ -45,7 +45,6 @@ import org.apache.poi.xssf.XSSFTestDataSamples; import org.apache.poi.xssf.model.MapInfo; import org.apache.poi.xssf.usermodel.XSSFMap; import org.apache.poi.xssf.usermodel.XSSFWorkbook; -import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Test; import org.xml.sax.SAXException; import org.xml.sax.SAXParseException; @@ -216,15 +215,13 @@ public final class TestXSSFExportToXML { } @Test - @Disabled(value="Fails, but I don't know if it is ok or not...") void testExportToXMLSingleAttributeNamespace() throws Exception { try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMapping-singleattributenamespace.xlsx")) { - for (XSSFMap map : wb.getCustomXMLMappings()) { XSSFExportToXml exporter = new XSSFExportToXml(map); - UnsynchronizedByteArrayOutputStream os = new UnsynchronizedByteArrayOutputStream(); - exporter.exportToXML(os, true); + SAXParseException ex = assertThrows(SAXParseException.class, () -> exporter.exportToXML(os, true)); + assertEquals("schema_reference: Failed to read schema document 'Schema11', because 'file' access is not allowed due to restriction set by the accessExternalSchema property.", ex.getMessage().trim()); } } } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestDeferredSXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestDeferredSXSSFWorkbook.java index 92cbd37ef2..c07b59ca13 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestDeferredSXSSFWorkbook.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestDeferredSXSSFWorkbook.java @@ -41,6 +41,8 @@ import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Test; +import org.junit.jupiter.params.ParameterizedTest; +import org.junit.jupiter.params.provider.ValueSource; public final class TestDeferredSXSSFWorkbook extends BaseTestXWorkbook { @@ -90,112 +92,109 @@ public final class TestDeferredSXSSFWorkbook extends BaseTestXWorkbook { @Test void existingWorkbook() throws IOException { - XSSFWorkbook xssfWb1 = new XSSFWorkbook(); - xssfWb1.createSheet("S1"); - DeferredSXSSFWorkbook wb1 = new DeferredSXSSFWorkbook(xssfWb1); - XSSFWorkbook xssfWb2 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb1); - assertTrue(wb1.dispose()); - - DeferredSXSSFWorkbook wb2 = new DeferredSXSSFWorkbook(xssfWb2); - assertEquals(1, wb2.getNumberOfSheets()); - Sheet sheet = wb2.getStreamingSheetAt(0); - assertNotNull(sheet); - assertEquals("S1", sheet.getSheetName()); - assertTrue(wb2.dispose()); - xssfWb2.close(); - xssfWb1.close(); - - wb2.close(); - wb1.close(); + try (XSSFWorkbook xssfWb1 = new XSSFWorkbook()) { + xssfWb1.createSheet("S1"); + try (DeferredSXSSFWorkbook wb1 = new DeferredSXSSFWorkbook(xssfWb1); + XSSFWorkbook xssfWb2 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb1)) { + assertTrue(wb1.dispose()); + + try (DeferredSXSSFWorkbook wb2 = new DeferredSXSSFWorkbook(xssfWb2)) { + assertEquals(1, wb2.getNumberOfSheets()); + Sheet sheet = wb2.getStreamingSheetAt(0); + assertNotNull(sheet); + assertEquals("S1", sheet.getSheetName()); + assertTrue(wb2.dispose()); + } + } + } } @Test void addToExistingWorkbook() throws IOException { - XSSFWorkbook xssfWb1 = new XSSFWorkbook(); - xssfWb1.createSheet("S1"); - Sheet sheet = xssfWb1.createSheet("S2"); - Row row = sheet.createRow(1); - Cell cell = row.createCell(1); - cell.setCellValue("value 2_1_1"); - DeferredSXSSFWorkbook wb1 = new DeferredSXSSFWorkbook(xssfWb1); - XSSFWorkbook xssfWb2 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb1); - assertTrue(wb1.dispose()); - xssfWb1.close(); - - DeferredSXSSFWorkbook wb2 = new DeferredSXSSFWorkbook(xssfWb2); - // Add a row to the existing empty sheet - DeferredSXSSFSheet ssheet1 = wb2.getStreamingSheetAt(0); - ssheet1.setRowGenerator((ssxSheet) -> { - Row row1_1 = ssxSheet.createRow(1); - Cell cell1_1_1 = row1_1.createCell(1); - cell1_1_1.setCellValue("value 1_1_1"); - }); - - // Add a row to the existing non-empty sheet - DeferredSXSSFSheet ssheet2 = wb2.getStreamingSheetAt(1); - ssheet2.setRowGenerator((ssxSheet) -> { - Row row2_2 = ssxSheet.createRow(2); - Cell cell2_2_1 = row2_2.createCell(1); - cell2_2_1.setCellValue("value 2_2_1"); - }); - // Add a sheet with one row - DeferredSXSSFSheet ssheet3 = wb2.createSheet("S3"); - ssheet3.setRowGenerator((ssxSheet) -> { - Row row3_1 = ssxSheet.createRow(1); - Cell cell3_1_1 = row3_1.createCell(1); - cell3_1_1.setCellValue("value 3_1_1"); - }); - - XSSFWorkbook xssfWb3 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb2); - wb2.close(); - - assertEquals(3, xssfWb3.getNumberOfSheets()); - // Verify sheet 1 - XSSFSheet sheet1 = xssfWb3.getSheetAt(0); - assertEquals("S1", sheet1.getSheetName()); - assertEquals(1, sheet1.getPhysicalNumberOfRows()); - XSSFRow row1_1 = sheet1.getRow(1); - assertNotNull(row1_1); - XSSFCell cell1_1_1 = row1_1.getCell(1); - assertNotNull(cell1_1_1); - assertEquals("value 1_1_1", cell1_1_1.getStringCellValue()); - // Verify sheet 2 - XSSFSheet sheet2 = xssfWb3.getSheetAt(1); - assertEquals("S2", sheet2.getSheetName()); - assertEquals(2, sheet2.getPhysicalNumberOfRows()); - Row row2_1 = sheet2.getRow(1); - assertNotNull(row2_1); - Cell cell2_1_1 = row2_1.getCell(1); - assertNotNull(cell2_1_1); - assertEquals("value 2_1_1", cell2_1_1.getStringCellValue()); - XSSFRow row2_2 = sheet2.getRow(2); - assertNotNull(row2_2); - XSSFCell cell2_2_1 = row2_2.getCell(1); - assertNotNull(cell2_2_1); - assertEquals("value 2_2_1", cell2_2_1.getStringCellValue()); - // Verify sheet 3 - XSSFSheet sheet3 = xssfWb3.getSheetAt(2); - assertEquals("S3", sheet3.getSheetName()); - assertEquals(1, sheet3.getPhysicalNumberOfRows()); - XSSFRow row3_1 = sheet3.getRow(1); - assertNotNull(row3_1); - XSSFCell cell3_1_1 = row3_1.getCell(1); - assertNotNull(cell3_1_1); - assertEquals("value 3_1_1", cell3_1_1.getStringCellValue()); - - xssfWb2.close(); - xssfWb3.close(); - wb1.close(); + try (XSSFWorkbook xssfWb1 = new XSSFWorkbook()) { + xssfWb1.createSheet("S1"); + Sheet sheet = xssfWb1.createSheet("S2"); + Row row = sheet.createRow(1); + Cell cell = row.createCell(1); + cell.setCellValue("value 2_1_1"); + + try (DeferredSXSSFWorkbook wb1 = new DeferredSXSSFWorkbook(xssfWb1); + XSSFWorkbook xssfWb2 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb1)) { + assertTrue(wb1.dispose()); + + try (DeferredSXSSFWorkbook wb2 = new DeferredSXSSFWorkbook(xssfWb2)) { + // Add a row to the existing empty sheet + DeferredSXSSFSheet ssheet1 = wb2.getStreamingSheetAt(0); + ssheet1.setRowGenerator((ssxSheet) -> { + Row row1_1 = ssxSheet.createRow(1); + Cell cell1_1_1 = row1_1.createCell(1); + cell1_1_1.setCellValue("value 1_1_1"); + }); + + // Add a row to the existing non-empty sheet + DeferredSXSSFSheet ssheet2 = wb2.getStreamingSheetAt(1); + ssheet2.setRowGenerator((ssxSheet) -> { + Row row2_2 = ssxSheet.createRow(2); + Cell cell2_2_1 = row2_2.createCell(1); + cell2_2_1.setCellValue("value 2_2_1"); + }); + // Add a sheet with one row + DeferredSXSSFSheet ssheet3 = wb2.createSheet("S3"); + ssheet3.setRowGenerator((ssxSheet) -> { + Row row3_1 = ssxSheet.createRow(1); + Cell cell3_1_1 = row3_1.createCell(1); + cell3_1_1.setCellValue("value 3_1_1"); + }); + + try (XSSFWorkbook xssfWb3 = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb2)) { + + assertEquals(3, xssfWb3.getNumberOfSheets()); + // Verify sheet 1 + XSSFSheet sheet1 = xssfWb3.getSheetAt(0); + assertEquals("S1", sheet1.getSheetName()); + assertEquals(1, sheet1.getPhysicalNumberOfRows()); + XSSFRow row1_1 = sheet1.getRow(1); + assertNotNull(row1_1); + XSSFCell cell1_1_1 = row1_1.getCell(1); + assertNotNull(cell1_1_1); + assertEquals("value 1_1_1", cell1_1_1.getStringCellValue()); + // Verify sheet 2 + XSSFSheet sheet2 = xssfWb3.getSheetAt(1); + assertEquals("S2", sheet2.getSheetName()); + assertEquals(2, sheet2.getPhysicalNumberOfRows()); + Row row2_1 = sheet2.getRow(1); + assertNotNull(row2_1); + Cell cell2_1_1 = row2_1.getCell(1); + assertNotNull(cell2_1_1); + assertEquals("value 2_1_1", cell2_1_1.getStringCellValue()); + XSSFRow row2_2 = sheet2.getRow(2); + assertNotNull(row2_2); + XSSFCell cell2_2_1 = row2_2.getCell(1); + assertNotNull(cell2_2_1); + assertEquals("value 2_2_1", cell2_2_1.getStringCellValue()); + // Verify sheet 3 + XSSFSheet sheet3 = xssfWb3.getSheetAt(2); + assertEquals("S3", sheet3.getSheetName()); + assertEquals(1, sheet3.getPhysicalNumberOfRows()); + XSSFRow row3_1 = sheet3.getRow(1); + assertNotNull(row3_1); + XSSFCell cell3_1_1 = row3_1.getCell(1); + assertNotNull(cell3_1_1); + assertEquals("value 3_1_1", cell3_1_1.getStringCellValue()); + } + } + } + } } @Test void sheetdataWriter() throws IOException { - DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook(); - SXSSFSheet sh = wb.createSheet(); - assertSame(sh.getClass(), DeferredSXSSFSheet.class); - SheetDataWriter wr = sh.getSheetDataWriter(); - assertNull(wr); - wb.close(); + try (DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook()) { + SXSSFSheet sh = wb.createSheet(); + assertSame(sh.getClass(), DeferredSXSSFSheet.class); + SheetDataWriter wr = sh.getSheetDataWriter(); + assertNull(wr); + } } @Test @@ -225,71 +224,65 @@ public final class TestDeferredSXSSFWorkbook extends BaseTestXWorkbook { @Test void gzipSheetdataWriter() throws IOException { - DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook(); - - final int rowNum = 1000; - final int sheetNum = 5; - populateData(wb, 1000, 5); - - XSSFWorkbook xwb = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb); - for (int i = 0; i < sheetNum; i++) { - Sheet sh = xwb.getSheetAt(i); - assertEquals("sheet" + i, sh.getSheetName()); - for (int j = 0; j < rowNum; j++) { - Row row = sh.getRow(j); - assertNotNull(row, "row[" + j + "]"); - Cell cell1 = row.getCell(0); - assertEquals(new CellReference(cell1).formatAsString(), cell1.getStringCellValue()); - - Cell cell2 = row.getCell(1); - assertEquals(i, (int) cell2.getNumericCellValue()); - - Cell cell3 = row.getCell(2); - assertEquals(j, (int) cell3.getNumericCellValue()); + try (DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook()) { + + final int rowNum = 1000; + final int sheetNum = 5; + populateData(wb); + + try (XSSFWorkbook xwb = DeferredSXSSFITestDataProvider.instance.writeOutAndReadBack(wb)) { + for (int i = 0; i < sheetNum; i++) { + Sheet sh = xwb.getSheetAt(i); + assertEquals("sheet" + i, sh.getSheetName()); + for (int j = 0; j < rowNum; j++) { + Row row = sh.getRow(j); + assertNotNull(row, "row[" + j + "]"); + Cell cell1 = row.getCell(0); + assertEquals(new CellReference(cell1).formatAsString(), cell1.getStringCellValue()); + + Cell cell2 = row.getCell(1); + assertEquals(i, (int) cell2.getNumericCellValue()); + + Cell cell3 = row.getCell(2); + assertEquals(j, (int) cell3.getNumericCellValue()); + } + } + + assertTrue(wb.dispose()); } } - - assertTrue(wb.dispose()); - xwb.close(); - wb.close(); } - @Test - void workbookDispose() throws IOException { - DeferredSXSSFWorkbook wb1 = new DeferredSXSSFWorkbook(); - // the underlying writer is SheetDataWriter - assertWorkbookDispose(wb1); - wb1.close(); - - DeferredSXSSFWorkbook wb2 = new DeferredSXSSFWorkbook(); - wb2.setCompressTempFiles(true); - // the underlying writer is GZIPSheetDataWriter - assertWorkbookDispose(wb2); - wb2.close(); - } + @ParameterizedTest + @ValueSource(booleans = {false, true}) + void workbookDispose(boolean compressTempFiles) throws IOException { + try (DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook()) { + // compressTempFiles == false: the underlying writer is SheetDataWriter + // compressTempFiles == true: the underlying writer is GZIPSheetDataWriter + wb.setCompressTempFiles(compressTempFiles); - private static void assertWorkbookDispose(DeferredSXSSFWorkbook wb) { - populateData(wb, 1000, 5); + populateData(wb); - for (Sheet sheet : wb) { - DeferredSXSSFSheet sxSheet = (DeferredSXSSFSheet) sheet; - assertNull(sxSheet.getSheetDataWriter()); - } + for (Sheet sheet : wb) { + DeferredSXSSFSheet sxSheet = (DeferredSXSSFSheet) sheet; + assertNull(sxSheet.getSheetDataWriter()); + } - assertTrue(wb.dispose()); + assertTrue(wb.dispose()); - for (Sheet sheet : wb) { - DeferredSXSSFSheet sxSheet = (DeferredSXSSFSheet) sheet; - assertNull(sxSheet.getSheetDataWriter()); + for (Sheet sheet : wb) { + DeferredSXSSFSheet sxSheet = (DeferredSXSSFSheet) sheet; + assertNull(sxSheet.getSheetDataWriter()); + } } } - private static void populateData(DeferredSXSSFWorkbook wb, final int rowNum, final int sheetNum) { - for (int i = 0; i < sheetNum; i++) { + private static void populateData(DeferredSXSSFWorkbook wb) { + for (int i = 0; i < 5; i++) { DeferredSXSSFSheet sheet = wb.createSheet("sheet" + i); int index = i; sheet.setRowGenerator((sh) -> { - for (int j = 0; j < rowNum; j++) { + for (int j = 0; j < 1000; j++) { Row row = sh.createRow(j); Cell cell1 = row.createCell(0); cell1.setCellValue(new CellReference(cell1).formatAsString()); @@ -304,7 +297,8 @@ public final class TestDeferredSXSSFWorkbook extends BaseTestXWorkbook { } } - void changeSheetNameWithSharedFormulas() { - /* not implemented */ + @Override + @Disabled("not implemented") + protected void changeSheetNameWithSharedFormulas() { } } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java index 982c5e7373..f4eb9e0990 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/streaming/TestSXSSFWorkbook.java @@ -55,6 +55,8 @@ import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Test; +import org.junit.jupiter.params.ParameterizedTest; +import org.junit.jupiter.params.provider.ValueSource; public final class TestSXSSFWorkbook extends BaseTestXWorkbook { @@ -98,225 +100,201 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { @Test void existingWorkbook() throws IOException { - XSSFWorkbook xssfWb1 = new XSSFWorkbook(); - xssfWb1.createSheet("S1"); - SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1); - XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb1); - assertTrue(wb1.dispose()); - - SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2); - assertEquals(1, wb2.getNumberOfSheets()); - Sheet sheet = wb2.getSheetAt(0); - assertNotNull(sheet); - assertEquals("S1", sheet.getSheetName()); - assertTrue(wb2.dispose()); - xssfWb2.close(); - xssfWb1.close(); - - wb2.close(); - wb1.close(); + try (XSSFWorkbook xssfWb1 = new XSSFWorkbook()) { + xssfWb1.createSheet("S1"); + try (SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1); + XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb1)) { + assertTrue(wb1.dispose()); + + try (SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2)) { + assertEquals(1, wb2.getNumberOfSheets()); + Sheet sheet = wb2.getSheetAt(0); + assertNotNull(sheet); + assertEquals("S1", sheet.getSheetName()); + assertTrue(wb2.dispose()); + } + } + } } @Test void useSharedStringsTable() throws Exception { - SXSSFWorkbook wb = new SXSSFWorkbook(null, 10, false, true); - - SharedStringsTable sss = wb.getSharedStringSource(); - - assertNotNull(sss); - - Row row = wb.createSheet("S1").createRow(0); - - row.createCell(0).setCellValue("A"); - row.createCell(1).setCellValue("B"); - row.createCell(2).setCellValue("A"); - - XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb); - sss = wb.getSharedStringSource(); - assertEquals(2, sss.getUniqueCount()); - assertTrue(wb.dispose()); - - Sheet sheet1 = xssfWorkbook.getSheetAt(0); - assertEquals("S1", sheet1.getSheetName()); - assertEquals(1, sheet1.getPhysicalNumberOfRows()); - row = sheet1.getRow(0); - assertNotNull(row); - Cell cell = row.getCell(0); - assertNotNull(cell); - assertEquals("A", cell.getStringCellValue()); - cell = row.getCell(1); - assertNotNull(cell); - assertEquals("B", cell.getStringCellValue()); - cell = row.getCell(2); - assertNotNull(cell); - assertEquals("A", cell.getStringCellValue()); - - xssfWorkbook.close(); - wb.close(); + try (SXSSFWorkbook wb = new SXSSFWorkbook(null, 10, false, true)) { + + SharedStringsTable sss = wb.getSharedStringSource(); + + assertNotNull(sss); + + Row row = wb.createSheet("S1").createRow(0); + + row.createCell(0).setCellValue("A"); + row.createCell(1).setCellValue("B"); + row.createCell(2).setCellValue("A"); + + try (XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb)) { + sss = wb.getSharedStringSource(); + assertEquals(2, sss.getUniqueCount()); + assertTrue(wb.dispose()); + + Sheet sheet1 = xssfWorkbook.getSheetAt(0); + assertEquals("S1", sheet1.getSheetName()); + assertEquals(1, sheet1.getPhysicalNumberOfRows()); + row = sheet1.getRow(0); + assertNotNull(row); + Cell cell = row.getCell(0); + assertNotNull(cell); + assertEquals("A", cell.getStringCellValue()); + cell = row.getCell(1); + assertNotNull(cell); + assertEquals("B", cell.getStringCellValue()); + cell = row.getCell(2); + assertNotNull(cell); + assertEquals("A", cell.getStringCellValue()); + } + } } @Test void addToExistingWorkbook() throws IOException { - XSSFWorkbook xssfWb1 = new XSSFWorkbook(); - xssfWb1.createSheet("S1"); - Sheet sheet = xssfWb1.createSheet("S2"); - Row row = sheet.createRow(1); - Cell cell = row.createCell(1); - cell.setCellValue("value 2_1_1"); - SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1); - XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb1); - assertTrue(wb1.dispose()); - xssfWb1.close(); - - SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2); - // Add a row to the existing empty sheet - Sheet sheet1 = wb2.getSheetAt(0); - Row row1_1 = sheet1.createRow(1); - Cell cell1_1_1 = row1_1.createCell(1); - cell1_1_1.setCellValue("value 1_1_1"); - - // Add a row to the existing non-empty sheet - Sheet sheet2 = wb2.getSheetAt(1); - Row row2_2 = sheet2.createRow(2); - Cell cell2_2_1 = row2_2.createCell(1); - cell2_2_1.setCellValue("value 2_2_1"); - - // Add a sheet with one row - Sheet sheet3 = wb2.createSheet("S3"); - Row row3_1 = sheet3.createRow(1); - Cell cell3_1_1 = row3_1.createCell(1); - cell3_1_1.setCellValue("value 3_1_1"); - - XSSFWorkbook xssfWb3 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb2); - wb2.close(); - - assertEquals(3, xssfWb3.getNumberOfSheets()); - // Verify sheet 1 - sheet1 = xssfWb3.getSheetAt(0); - assertEquals("S1", sheet1.getSheetName()); - assertEquals(1, sheet1.getPhysicalNumberOfRows()); - row1_1 = sheet1.getRow(1); - assertNotNull(row1_1); - cell1_1_1 = row1_1.getCell(1); - assertNotNull(cell1_1_1); - assertEquals("value 1_1_1", cell1_1_1.getStringCellValue()); - // Verify sheet 2 - sheet2 = xssfWb3.getSheetAt(1); - assertEquals("S2", sheet2.getSheetName()); - assertEquals(2, sheet2.getPhysicalNumberOfRows()); - Row row2_1 = sheet2.getRow(1); - assertNotNull(row2_1); - Cell cell2_1_1 = row2_1.getCell(1); - assertNotNull(cell2_1_1); - assertEquals("value 2_1_1", cell2_1_1.getStringCellValue()); - row2_2 = sheet2.getRow(2); - assertNotNull(row2_2); - cell2_2_1 = row2_2.getCell(1); - assertNotNull(cell2_2_1); - assertEquals("value 2_2_1", cell2_2_1.getStringCellValue()); - // Verify sheet 3 - sheet3 = xssfWb3.getSheetAt(2); - assertEquals("S3", sheet3.getSheetName()); - assertEquals(1, sheet3.getPhysicalNumberOfRows()); - row3_1 = sheet3.getRow(1); - assertNotNull(row3_1); - cell3_1_1 = row3_1.getCell(1); - assertNotNull(cell3_1_1); - assertEquals("value 3_1_1", cell3_1_1.getStringCellValue()); - - xssfWb2.close(); - xssfWb3.close(); - wb1.close(); + try (XSSFWorkbook xssfWb1 = new XSSFWorkbook()) { + xssfWb1.createSheet("S1"); + Sheet sheet = xssfWb1.createSheet("S2"); + Row row = sheet.createRow(1); + Cell cell = row.createCell(1); + cell.setCellValue("value 2_1_1"); + try (SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1); + XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb1)) { + assertTrue(wb1.dispose()); + + try (SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2)) { + // Add a row to the existing empty sheet + Sheet sheet1 = wb2.getSheetAt(0); + Row row1_1 = sheet1.createRow(1); + Cell cell1_1_1 = row1_1.createCell(1); + cell1_1_1.setCellValue("value 1_1_1"); + + // Add a row to the existing non-empty sheet + Sheet sheet2 = wb2.getSheetAt(1); + Row row2_2 = sheet2.createRow(2); + Cell cell2_2_1 = row2_2.createCell(1); + cell2_2_1.setCellValue("value 2_2_1"); + + // Add a sheet with one row + Sheet sheet3 = wb2.createSheet("S3"); + Row row3_1 = sheet3.createRow(1); + Cell cell3_1_1 = row3_1.createCell(1); + cell3_1_1.setCellValue("value 3_1_1"); + + try (XSSFWorkbook xssfWb3 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb2)) { + assertEquals(3, xssfWb3.getNumberOfSheets()); + // Verify sheet 1 + sheet1 = xssfWb3.getSheetAt(0); + assertEquals("S1", sheet1.getSheetName()); + assertEquals(1, sheet1.getPhysicalNumberOfRows()); + row1_1 = sheet1.getRow(1); + assertNotNull(row1_1); + cell1_1_1 = row1_1.getCell(1); + assertNotNull(cell1_1_1); + assertEquals("value 1_1_1", cell1_1_1.getStringCellValue()); + // Verify sheet 2 + sheet2 = xssfWb3.getSheetAt(1); + assertEquals("S2", sheet2.getSheetName()); + assertEquals(2, sheet2.getPhysicalNumberOfRows()); + Row row2_1 = sheet2.getRow(1); + assertNotNull(row2_1); + Cell cell2_1_1 = row2_1.getCell(1); + assertNotNull(cell2_1_1); + assertEquals("value 2_1_1", cell2_1_1.getStringCellValue()); + row2_2 = sheet2.getRow(2); + assertNotNull(row2_2); + cell2_2_1 = row2_2.getCell(1); + assertNotNull(cell2_2_1); + assertEquals("value 2_2_1", cell2_2_1.getStringCellValue()); + // Verify sheet 3 + sheet3 = xssfWb3.getSheetAt(2); + assertEquals("S3", sheet3.getSheetName()); + assertEquals(1, sheet3.getPhysicalNumberOfRows()); + row3_1 = sheet3.getRow(1); + assertNotNull(row3_1); + cell3_1_1 = row3_1.getCell(1); + assertNotNull(cell3_1_1); + assertEquals("value 3_1_1", cell3_1_1.getStringCellValue()); + } + } + } + } } @Test void sheetdataWriter() throws IOException{ - SXSSFWorkbook wb = new SXSSFWorkbook(); - SXSSFSheet sh = wb.createSheet(); - SheetDataWriter wr = sh.getSheetDataWriter(); - assertSame(wr.getClass(), SheetDataWriter.class); - File tmp = wr.getTempFile(); - assertStartsWith(tmp.getName(), "poi-sxssf-sheet"); - assertEndsWith(tmp.getName(), ".xml"); - assertTrue(wb.dispose()); - wb.close(); - - wb = new SXSSFWorkbook(); - wb.setCompressTempFiles(true); - sh = wb.createSheet(); - wr = sh.getSheetDataWriter(); - assertSame(wr.getClass(), GZIPSheetDataWriter.class); - tmp = wr.getTempFile(); - assertStartsWith(tmp.getName(), "poi-sxssf-sheet-xml"); - assertEndsWith(tmp.getName(), ".gz"); - assertTrue(wb.dispose()); - wb.close(); + try (SXSSFWorkbook wb = new SXSSFWorkbook()) { + SXSSFSheet sh = wb.createSheet(); + SheetDataWriter wr = sh.getSheetDataWriter(); + assertSame(wr.getClass(), SheetDataWriter.class); + File tmp = wr.getTempFile(); + assertStartsWith(tmp.getName(), "poi-sxssf-sheet"); + assertEndsWith(tmp.getName(), ".xml"); + assertTrue(wb.dispose()); + } - //Test escaping of Unicode control characters - wb = new SXSSFWorkbook(); - wb.createSheet("S1").createRow(0).createCell(0).setCellValue("value\u0019"); - XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb); - Cell cell = xssfWorkbook.getSheet("S1").getRow(0).getCell(0); - assertEquals("value?", cell.getStringCellValue()); - - assertTrue(wb.dispose()); - wb.close(); - xssfWorkbook.close(); - } + try (SXSSFWorkbook wb = new SXSSFWorkbook()) { + wb.setCompressTempFiles(true); + SXSSFSheet sh = wb.createSheet(); + SheetDataWriter wr = sh.getSheetDataWriter(); + assertSame(wr.getClass(), GZIPSheetDataWriter.class); + File tmp = wr.getTempFile(); + assertStartsWith(tmp.getName(), "poi-sxssf-sheet-xml"); + assertEndsWith(tmp.getName(), ".gz"); + assertTrue(wb.dispose()); + } - @Test - void gzipSheetdataWriter() throws IOException { - SXSSFWorkbook wb = new SXSSFWorkbook(); - wb.setCompressTempFiles(true); - - final int rowNum = 1000; - final int sheetNum = 5; - populateData(wb, 1000, 5); - - XSSFWorkbook xwb = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb); - for(int i = 0; i < sheetNum; i++){ - Sheet sh = xwb.getSheetAt(i); - assertEquals("sheet" + i, sh.getSheetName()); - for(int j = 0; j < rowNum; j++){ - Row row = sh.getRow(j); - assertNotNull(row, "row[" + j + "]"); - Cell cell1 = row.getCell(0); - assertEquals(new CellReference(cell1).formatAsString(), cell1.getStringCellValue()); - - Cell cell2 = row.getCell(1); - assertEquals(i, (int)cell2.getNumericCellValue()); - - Cell cell3 = row.getCell(2); - assertEquals(j, (int)cell3.getNumericCellValue()); + //Test escaping of Unicode control characters + try (SXSSFWorkbook wb = new SXSSFWorkbook()) { + wb.createSheet("S1").createRow(0).createCell(0).setCellValue("value\u0019"); + try (XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb)) { + Cell cell = xssfWorkbook.getSheet("S1").getRow(0).getCell(0); + assertEquals("value?", cell.getStringCellValue()); + assertTrue(wb.dispose()); } } - - assertTrue(wb.dispose()); - xwb.close(); - wb.close(); } - private static void assertWorkbookDispose(SXSSFWorkbook wb) - { - populateData(wb, 1000, 5); - - for (Sheet sheet : wb) { - SXSSFSheet sxSheet = (SXSSFSheet) sheet; - assertTrue(sxSheet.getSheetDataWriter().getTempFile().exists()); - } - - assertTrue(wb.dispose()); + @Test + void gzipSheetdataWriter() throws IOException { + try (SXSSFWorkbook wb = new SXSSFWorkbook()) { + wb.setCompressTempFiles(true); + + final int rowNum = 1000; + final int sheetNum = 5; + populateData(wb); + + try (XSSFWorkbook xwb = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb)) { + for (int i = 0; i < sheetNum; i++) { + Sheet sh = xwb.getSheetAt(i); + assertEquals("sheet" + i, sh.getSheetName()); + for (int j = 0; j < rowNum; j++) { + Row row = sh.getRow(j); + assertNotNull(row, "row[" + j + "]"); + Cell cell1 = row.getCell(0); + assertEquals(new CellReference(cell1).formatAsString(), cell1.getStringCellValue()); + + Cell cell2 = row.getCell(1); + assertEquals(i, (int) cell2.getNumericCellValue()); + + Cell cell3 = row.getCell(2); + assertEquals(j, (int) cell3.getNumericCellValue()); + } + } - for (Sheet sheet : wb) { - SXSSFSheet sxSheet = (SXSSFSheet) sheet; - assertFalse(sxSheet.getSheetDataWriter().getTempFile().exists()); + assertTrue(wb.dispose()); + } } } - private static void populateData(Workbook wb, final int rowNum, final int sheetNum) { - for(int i = 0; i < sheetNum; i++){ + private static void populateData(Workbook wb) { + for(int i = 0; i < 5; i++){ Sheet sh = wb.createSheet("sheet" + i); - for(int j = 0; j < rowNum; j++){ + for(int j = 0; j < 1000; j++){ Row row = sh.createRow(j); Cell cell1 = row.createCell(0); cell1.setCellValue(new CellReference(cell1).formatAsString()); @@ -330,28 +308,40 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { } } - @Test - void workbookDispose() throws IOException { - SXSSFWorkbook wb1 = new SXSSFWorkbook(); - // the underlying writer is SheetDataWriter - assertWorkbookDispose(wb1); - wb1.close(); - - SXSSFWorkbook wb2 = new SXSSFWorkbook(); - wb2.setCompressTempFiles(true); - // the underlying writer is GZIPSheetDataWriter - assertWorkbookDispose(wb2); - wb2.close(); + @ParameterizedTest + @ValueSource(booleans = {false, true}) + void workbookDispose(boolean compressTempFiles) throws IOException { + try (SXSSFWorkbook wb = new SXSSFWorkbook()) { + // compressTempFiles == false: the underlying writer is SheetDataWriter + // compressTempFiles == true: the underlying writer is GZIPSheetDataWriter + wb.setCompressTempFiles(compressTempFiles); + + populateData(wb); + + for (Sheet sheet : wb) { + SXSSFSheet sxSheet = (SXSSFSheet) sheet; + assertTrue(sxSheet.getSheetDataWriter().getTempFile().exists()); + } + + assertTrue(wb.dispose()); + + for (Sheet sheet : wb) { + SXSSFSheet sxSheet = (SXSSFSheet) sheet; + assertFalse(sxSheet.getSheetDataWriter().getTempFile().exists()); + } + } } @Test void bug53515() throws Exception { try (Workbook wb1 = new SXSSFWorkbook(10)) { populateWorkbook(wb1); - saveTwice(wb1); + assertDoesNotThrow(() -> wb1.write(NULL_OUTPUT_STREAM)); + assertDoesNotThrow(() -> wb1.write(NULL_OUTPUT_STREAM)); try (Workbook wb2 = new XSSFWorkbook()) { populateWorkbook(wb2); - saveTwice(wb2); + assertDoesNotThrow(() -> wb2.write(NULL_OUTPUT_STREAM)); + assertDoesNotThrow(() -> wb2.write(NULL_OUTPUT_STREAM)); } } } @@ -405,18 +395,6 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { } } - private static void saveTwice(Workbook wb) throws Exception { - for (int i = 0; i < 2; i++) { - try { - wb.write(NULL_OUTPUT_STREAM); - } catch (Exception e) { - throw new Exception("ERROR: failed on " + (i + 1) - + "th time calling " + wb.getClass().getName() - + ".write() with exception " + e.getMessage(), e); - } - } - } - @Test void closeDoesNotModifyWorkbook() throws IOException { final String filename = "SampleSS.xlsx"; @@ -460,10 +438,10 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { char[] useless = new char[32767]; Arrays.fill(useless, ' '); - for (int row = 0; row < 1; row++) { + for (int row = 0; row < 10; row++) { Row r = s.createRow(row); for (int col = 0; col < 10; col++) { - char[] prefix = Integer.toHexString(row * 1000 + col).toCharArray(); + char[] prefix = Integer.toHexString(row * 10 + col).toCharArray(); Arrays.fill(useless, 0, 10, ' '); System.arraycopy(prefix, 0, useless, 0, prefix.length); String ul = new String(useless); @@ -528,8 +506,8 @@ public final class TestSXSSFWorkbook extends BaseTestXWorkbook { } } + @Override @Disabled("not implemented") - @Test - void changeSheetNameWithSharedFormulas() { + protected void changeSheetNameWithSharedFormulas() { } } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index febe557ad0..25a16e0f69 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -100,7 +100,6 @@ 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.commons.io.output.NullOutputStream; import org.apache.poi.util.TempFile; import org.apache.poi.util.XMLHelper; import org.apache.poi.xssf.SXSSFITestDataProvider; @@ -117,7 +116,14 @@ import org.junit.jupiter.params.ParameterizedTest; import org.junit.jupiter.params.provider.CsvSource; import org.junit.jupiter.params.provider.EnumSource; import org.junit.jupiter.params.provider.ValueSource; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; +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.STCellFormulaType; import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontImpl; import org.xml.sax.InputSource; import org.xml.sax.SAXParseException; @@ -2714,6 +2720,7 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { CellStyle style = wb.createCellStyle(); style.setRotation((short) -90); cell.setCellStyle(style); + assertEquals(180, style.getRotation()); XSSFTestDataSamples.writeOut(wb, fileName); } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFont.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFont.java index f5e81dbdb9..a3878dcc31 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFont.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFont.java @@ -259,11 +259,11 @@ public final class TestXSSFFont extends BaseTestFont{ xssfFont.setUnderline(Font.U_DOUBLE); assertEquals(ctFont.sizeOfUArray(),1); - assertEquals(STUnderlineValues.DOUBLE,ctFont.getUArray(0).getVal()); + assertSame(STUnderlineValues.DOUBLE,ctFont.getUArray(0).getVal()); xssfFont.setUnderline(FontUnderline.DOUBLE_ACCOUNTING); assertEquals(ctFont.sizeOfUArray(),1); - assertEquals(STUnderlineValues.DOUBLE_ACCOUNTING,ctFont.getUArray(0).getVal()); + assertSame(STUnderlineValues.DOUBLE_ACCOUNTING,ctFont.getUArray(0).getVal()); } @Test @@ -342,7 +342,7 @@ public final class TestXSSFFont extends BaseTestFont{ assertEquals(FontScheme.MAJOR,font.getScheme()); font.setScheme(FontScheme.NONE); - assertEquals(STFontScheme.NONE,ctFont.getSchemeArray(0).getVal()); + assertSame(STFontScheme.NONE,ctFont.getSchemeArray(0).getVal()); } @Test @@ -356,17 +356,15 @@ public final class TestXSSFFont extends BaseTestFont{ assertEquals(Font.SS_NONE,font.getTypeOffset()); font.setTypeOffset(XSSFFont.SS_SUPER); - assertEquals(STVerticalAlignRun.SUPERSCRIPT,ctFont.getVertAlignArray(0).getVal()); + assertSame(STVerticalAlignRun.SUPERSCRIPT,ctFont.getVertAlignArray(0).getVal()); } // store test from TestSheetUtil here as it uses XSSF @Test void testCanComputeWidthXSSF() throws IOException { try (Workbook wb = new XSSFWorkbook()) { - // cannot check on result because on some machines we get back false here! - SheetUtil.canComputeColumnWidth(wb.getFontAt(0)); - + assertDoesNotThrow(() -> SheetUtil.canComputeColumnWidth(wb.getFontAt(0))); } } @@ -377,7 +375,7 @@ public final class TestXSSFFont extends BaseTestFont{ font.setFontName("some non existing font name"); // Even with invalid fonts we still get back useful data most of the time... - SheetUtil.canComputeColumnWidth(font); + assertDoesNotThrow(() -> SheetUtil.canComputeColumnWidth(font)); } /** diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFSheet.java index 1ca51d3dcc..54229938e4 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -252,14 +252,14 @@ public final class TestXSSFSheet extends BaseTestXSheet { sheet.createFreezePane(2, 4); assertEquals(2.0, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getXSplit(), 0.0); - assertEquals(STPane.BOTTOM_RIGHT, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getActivePane()); + assertSame(STPane.BOTTOM_RIGHT, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getActivePane()); sheet.createFreezePane(3, 6, 10, 10); assertEquals(3.0, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getXSplit(), 0.0); // assertEquals(10, sheet.getTopRow()); // assertEquals(10, sheet.getLeftCol()); sheet.createSplitPane(4, 8, 12, 12, 1); assertEquals(8.0, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getYSplit(), 0.0); - assertEquals(STPane.BOTTOM_RIGHT, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getActivePane()); + assertSame(STPane.BOTTOM_RIGHT, ctWorksheet.getSheetViews().getSheetViewArray(0).getPane().getActivePane()); workbook.close(); } @@ -1152,7 +1152,7 @@ public final class TestXSSFSheet extends BaseTestXSheet { CTCalcPr calcPr = wb1.getCTWorkbook().addNewCalcPr(); calcPr.setCalcMode(STCalcMode.MANUAL); sheet.setForceFormulaRecalculation(true); - assertEquals(STCalcMode.AUTO, calcPr.getCalcMode()); + assertSame(STCalcMode.AUTO, calcPr.getCalcMode()); // Check sheet.setForceFormulaRecalculation(false); @@ -1422,350 +1422,337 @@ public final class TestXSSFSheet extends BaseTestXSheet { wb.close(); } - private void testCopyOneRow(String copyRowsTestWorkbook) throws IOException { - final double FLOAT_PRECISION = 1e-9; - final XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook); - final XSSFSheet sheet = wb.getSheetAt(0); - final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); - sheet.copyRows(1, 1, 6, defaultCopyPolicy); - - final Row srcRow = sheet.getRow(1); - final Row destRow = sheet.getRow(6); - int col = 0; - Cell cell; - - cell = CellUtil.getCell(destRow, col++); - assertEquals("Source row ->", cell.getStringCellValue()); - - // Style - cell = CellUtil.getCell(destRow, col++); - assertEquals("Red", cell.getStringCellValue(), "[Style] B7 cell value"); - assertEquals(CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle(), "[Style] B7 cell style"); - - // Blank - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C7 cell type"); - - // Error - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D7 cell type"); - final FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); - //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here - assertEquals(FormulaError.NA, error, "[Error] D7 cell value"); - - // Date - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E7 cell type"); - final Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime(); - assertEquals(date, cell.getDateCellValue(), "[Date] E7 cell value"); - - // Boolean - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F7 cell type"); - assertTrue(cell.getBooleanCellValue(), "[Boolean] F7 cell value"); - - // String - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.STRING, cell.getCellType(), "[String] G7 cell type"); - assertEquals("Hello", cell.getStringCellValue(), "[String] G7 cell value"); - - // Int - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H7 cell type"); - assertEquals(15, (int) cell.getNumericCellValue(), "[Int] H7 cell value"); - - // Float - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I7 cell type"); - assertEquals(12.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I7 cell value"); - - // Cell Formula - cell = CellUtil.getCell(destRow, col++); - assertEquals("Sheet1!J7", new CellReference(cell).formatAsString()); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J7 cell type"); - assertEquals("5+2", cell.getCellFormula(), "[Cell Formula] J7 cell formula"); - //System.out.println("Cell formula evaluation currently unsupported"); - - // Cell Formula with Reference - // Formula row references should be adjusted by destRowNum-srcRowNum - cell = CellUtil.getCell(destRow, col++); - assertEquals("Sheet1!K7", new CellReference(cell).formatAsString()); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K7 cell type"); - assertEquals("J7+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K7 cell formula"); - - // Cell Formula with Reference spanning multiple rows - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L7 cell type"); - assertEquals("G7&\" \"&G8", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L7 cell formula"); - - // Cell Formula with Reference spanning multiple rows - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M7 cell type"); - assertEquals("SUM(H7:I8)", cell.getCellFormula(), "[Cell Formula with Area Reference] M7 cell formula"); - - // Array Formula - cell = CellUtil.getCell(destRow, col++); - //System.out.println("Array formulas currently unsupported"); - // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() - /* - assertEquals(CellType.FORMULA, cell.getCellType(), "[Array Formula] N7 cell type"); - assertEquals("{SUM(H7:J7*{1,2,3})}", cell.getCellFormula(), "[Array Formula] N7 cell formula"); - */ - - // Data Format - cell = CellUtil.getCell(destRow, col++); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Data Format] O7 cell type"); - assertEquals(100.20, cell.getNumericCellValue(), FLOAT_PRECISION, "[Data Format] O7 cell value"); - //FIXME: currently fails - final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; - assertEquals(moneyFormat, cell.getCellStyle().getDataFormatString(), "[Data Format] O7 data format"); - - // Merged - cell = CellUtil.getCell(destRow, col); - assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P7:Q7 cell value"); - assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7")), "[Merged] P7:Q7 merged region"); - - // Merged across multiple rows - // Microsoft Excel 2013 does not copy a merged region unless all rows of - // the source merged region are selected - // POI's behavior should match this behavior - col += 2; - cell = CellUtil.getCell(destRow, col); - // Note: this behavior deviates from Microsoft Excel, - // which will not overwrite a cell in destination row if merged region extends beyond the copied row. - // The Excel way would require: - //assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue()); - //assertFalse("[Merged across multiple rows] R7:S8 merged region", - // sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); - // As currently implemented, cell value is copied but merged region is not copied - assertEquals("Merged cells across multiple rows", cell.getStringCellValue(), "[Merged across multiple rows] R7:S8 cell value"); - // shouldn't do 1-row merge - assertFalse(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7")), "[Merged across multiple rows] R7:S7 merged region (one row)"); - //shouldn't do 2-row merge - assertFalse(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")), "[Merged across multiple rows] R7:S8 merged region"); - - // Make sure other rows are blank (off-by-one errors) - assertNull(sheet.getRow(5)); - assertNull(sheet.getRow(7)); - - wb.close(); - } - - private void testCopyMultipleRows(String copyRowsTestWorkbook) throws IOException { - final double FLOAT_PRECISION = 1e-9; - final XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(copyRowsTestWorkbook); - final XSSFSheet sheet = wb.getSheetAt(0); - final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); - sheet.copyRows(0, 3, 8, defaultCopyPolicy); - - sheet.getRow(0); - final Row srcRow1 = sheet.getRow(1); - final Row srcRow2 = sheet.getRow(2); - final Row srcRow3 = sheet.getRow(3); - final Row destHeaderRow = sheet.getRow(8); - final Row destRow1 = sheet.getRow(9); - final Row destRow2 = sheet.getRow(10); - final Row destRow3 = sheet.getRow(11); - int col = 0; - Cell cell; - - // Header row should be copied - assertNotNull(destHeaderRow); - - // Data rows - cell = CellUtil.getCell(destRow1, col); - assertEquals("Source row ->", cell.getStringCellValue()); - - // Style - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals("Red", cell.getStringCellValue(), "[Style] B10 cell value"); - assertEquals(CellUtil.getCell(srcRow1, 1).getCellStyle(), cell.getCellStyle(), "[Style] B10 cell style"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals("Blue", cell.getStringCellValue(), "[Style] B11 cell value"); - assertEquals(CellUtil.getCell(srcRow2, 1).getCellStyle(), cell.getCellStyle(), "[Style] B11 cell style"); - - // Blank - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C10 cell type"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C11 cell type"); - - // Error - col++; - cell = CellUtil.getCell(destRow1, col); - FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); - //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here - assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D10 cell type"); - assertEquals(FormulaError.NA, error, "[Error] D10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - error = FormulaError.forInt(cell.getErrorCellValue()); - //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here - assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D11 cell type"); - assertEquals(FormulaError.NAME, error, "[Error] D11 cell value"); - - // Date - col++; - cell = CellUtil.getCell(destRow1, col); - Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime(); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E10 cell type"); - assertEquals(date, cell.getDateCellValue(), "[Date] E10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 2).getTime(); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E11 cell type"); - assertEquals(date, cell.getDateCellValue(), "[Date] E11 cell value"); - - // Boolean - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F10 cell type"); - assertTrue(cell.getBooleanCellValue(), "[Boolean] F10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F11 cell type"); - assertFalse(cell.getBooleanCellValue(), "[Boolean] F11 cell value"); - - // String - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.STRING, cell.getCellType(), "[String] G10 cell type"); - assertEquals("Hello", cell.getStringCellValue(), "[String] G10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.STRING, cell.getCellType(), "[String] G11 cell type"); - assertEquals("World", cell.getStringCellValue(), "[String] G11 cell value"); - - // Int - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H10 cell type"); - assertEquals(15, (int) cell.getNumericCellValue(), "[Int] H10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H11 cell type"); - assertEquals(42, (int) cell.getNumericCellValue(), "[Int] H11 cell value"); - - // Float - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I10 cell type"); - assertEquals(12.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I10 cell value"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I11 cell type"); - assertEquals(5.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I11 cell value"); - - // Cell Formula - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J10 cell type"); - assertEquals("5+2", cell.getCellFormula(), "[Cell Formula] J10 cell formula"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J11 cell type"); - assertEquals("6+18", cell.getCellFormula(), "[Cell Formula] J11 cell formula"); - - // Cell Formula with Reference - col++; - // Formula row references should be adjusted by destRowNum-srcRowNum - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K10 cell type"); - assertEquals("J10+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K10 cell formula"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K11 cell type"); - assertEquals("J11+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K11 cell formula"); - - // Cell Formula with Reference spanning multiple rows - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L10 cell type"); - assertEquals("G10&\" \"&G11", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L10 cell formula"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L11 cell type"); - assertEquals("G11&\" \"&G12", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L11 cell formula"); - - // Cell Formula with Area Reference - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M10 cell type"); - assertEquals("SUM(H10:I11)", cell.getCellFormula(), "[Cell Formula with Area Reference] M10 cell formula"); - - cell = CellUtil.getCell(destRow2, col); - // Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order - assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M11 cell type"); - assertEquals("SUM($H$3:I10)", cell.getCellFormula(), "[Cell Formula with Area Reference] M11 cell formula"); - - // Array Formula - col++; - cell = CellUtil.getCell(destRow1, col); - // System.out.println("Array formulas currently unsupported"); - /* - // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() - assertEquals(CellType.FORMULA, cell.getCellType(), "[Array Formula] N10 cell type"); - assertEquals("{SUM(H10:J10*{1,2,3})}", cell.getCellFormula(), "[Array Formula] N10 cell formula"); - - cell = CellUtil.getCell(destRow2, col); - // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() - assertEquals(CellType.FORMULA, cell.getCellType(). "[Array Formula] N11 cell type"); - assertEquals("{SUM(H11:J11*{1,2,3})}", cell.getCellFormula(). "[Array Formula] N11 cell formula"); - */ - - // Data Format - col++; - cell = CellUtil.getCell(destRow2, col); - assertEquals(CellType.NUMERIC, cell.getCellType(), "[Data Format] O10 cell type"); - assertEquals(100.20, cell.getNumericCellValue(), FLOAT_PRECISION, "[Data Format] O10 cell value"); - final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; - assertEquals(moneyFormat, cell.getCellStyle().getDataFormatString(), "[Data Format] O10 cell data format"); - - // Merged - col++; - cell = CellUtil.getCell(destRow1, col); - assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P10:Q10 cell value"); - assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P10:Q10")), "[Merged] P10:Q10 merged region"); - - cell = CellUtil.getCell(destRow2, col); - assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P11:Q11 cell value"); - assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P11:Q11")), "[Merged] P11:Q11 merged region"); - - // Should Q10/Q11 be checked? - - // Merged across multiple rows - // Microsoft Excel 2013 does not copy a merged region unless all rows of - // the source merged region are selected - // POI's behavior should match this behavior - col += 2; - cell = CellUtil.getCell(destRow1, col); - assertEquals("Merged cells across multiple rows", cell.getStringCellValue(), "[Merged across multiple rows] R10:S11 cell value"); - assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R10:S11")), "[Merged across multiple rows] R10:S11 merged region"); - - // Row 3 (zero-based) was empty, so Row 11 (zero-based) should be empty too. - if (srcRow3 == null) { - assertNull(destRow3, "Row 3 was empty, so Row 11 should be empty"); - } - - // Make sure other rows are blank (off-by-one errors) - assertNull(sheet.getRow(7), "Off-by-one lower edge case"); //one row above destHeaderRow - assertNull(sheet.getRow(12), "Off-by-one upper edge case"); //one row below destRow3 - - wb.close(); - } - @Test void testCopyOneRow() throws IOException { - testCopyOneRow("XSSFSheet.copyRows.xlsx"); + final double FLOAT_PRECISION = 1e-9; + try (final XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("XSSFSheet.copyRows.xlsx")) { + final XSSFSheet sheet = wb.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(1, 1, 6, defaultCopyPolicy); + + final Row srcRow = sheet.getRow(1); + final Row destRow = sheet.getRow(6); + int col = 0; + Cell cell; + + cell = CellUtil.getCell(destRow, col++); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + cell = CellUtil.getCell(destRow, col++); + assertEquals("Red", cell.getStringCellValue(), "[Style] B7 cell value"); + assertEquals(CellUtil.getCell(srcRow, 1).getCellStyle(), cell.getCellStyle(), "[Style] B7 cell style"); + + // Blank + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C7 cell type"); + + // Error + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D7 cell type"); + final FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + assertEquals(FormulaError.NA, error, "[Error] D7 cell value"); + + // Date + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E7 cell type"); + final Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals(date, cell.getDateCellValue(), "[Date] E7 cell value"); + + // Boolean + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F7 cell type"); + assertTrue(cell.getBooleanCellValue(), "[Boolean] F7 cell value"); + + // String + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.STRING, cell.getCellType(), "[String] G7 cell type"); + assertEquals("Hello", cell.getStringCellValue(), "[String] G7 cell value"); + + // Int + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H7 cell type"); + assertEquals(15, (int) cell.getNumericCellValue(), "[Int] H7 cell value"); + + // Float + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I7 cell type"); + assertEquals(12.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I7 cell value"); + + // Cell Formula + cell = CellUtil.getCell(destRow, col++); + assertEquals("Sheet1!J7", new CellReference(cell).formatAsString()); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J7 cell type"); + assertEquals("5+2", cell.getCellFormula(), "[Cell Formula] J7 cell formula"); + //System.out.println("Cell formula evaluation currently unsupported"); + + // Cell Formula with Reference + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow, col++); + assertEquals("Sheet1!K7", new CellReference(cell).formatAsString()); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K7 cell type"); + assertEquals("J7+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K7 cell formula"); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L7 cell type"); + assertEquals("G7&\" \"&G8", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L7 cell formula"); + + // Cell Formula with Reference spanning multiple rows + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M7 cell type"); + assertEquals("SUM(H7:I8)", cell.getCellFormula(), "[Cell Formula with Area Reference] M7 cell formula"); + + // Array Formula + col++; + // cell = CellUtil.getCell(destRow, col++); + // Array formulas currently unsupported" + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + // assertEquals(CellType.FORMULA, cell.getCellType(), "[Array Formula] N7 cell type"); + // assertEquals("{SUM(H7:J7*{1,2,3})}", cell.getCellFormula(), "[Array Formula] N7 cell formula"); + + // Data Format + cell = CellUtil.getCell(destRow, col++); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Data Format] O7 cell type"); + assertEquals(100.20, cell.getNumericCellValue(), FLOAT_PRECISION, "[Data Format] O7 cell value"); + //FIXME: currently fails + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals(moneyFormat, cell.getCellStyle().getDataFormatString(), "[Data Format] O7 data format"); + + // Merged + cell = CellUtil.getCell(destRow, col); + assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P7:Q7 cell value"); + assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P7:Q7")), "[Merged] P7:Q7 merged region"); + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow, col); + // Note: this behavior deviates from Microsoft Excel, + // which will not overwrite a cell in destination row if merged region extends beyond the copied row. + // The Excel way would require: + //assertEquals("[Merged across multiple rows] R7:S8 merged region", "Should NOT be overwritten", cell.getStringCellValue()); + //assertFalse("[Merged across multiple rows] R7:S8 merged region", + // sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8"))); + // As currently implemented, cell value is copied but merged region is not copied + assertEquals("Merged cells across multiple rows", cell.getStringCellValue(), "[Merged across multiple rows] R7:S8 cell value"); + // shouldn't do 1-row merge + assertFalse(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S7")), "[Merged across multiple rows] R7:S7 merged region (one row)"); + //shouldn't do 2-row merge + assertFalse(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R7:S8")), "[Merged across multiple rows] R7:S8 merged region"); + + // Make sure other rows are blank (off-by-one errors) + assertNull(sheet.getRow(5)); + assertNull(sheet.getRow(7)); + } } @Test void testCopyMultipleRows() throws IOException { - testCopyMultipleRows("XSSFSheet.copyRows.xlsx"); + final double FLOAT_PRECISION = 1e-9; + try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("XSSFSheet.copyRows.xlsx")) { + final XSSFSheet sheet = wb.getSheetAt(0); + final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy(); + sheet.copyRows(0, 3, 8, defaultCopyPolicy); + + sheet.getRow(0); + final Row srcRow1 = sheet.getRow(1); + final Row srcRow2 = sheet.getRow(2); + final Row srcRow3 = sheet.getRow(3); + final Row destHeaderRow = sheet.getRow(8); + final Row destRow1 = sheet.getRow(9); + final Row destRow2 = sheet.getRow(10); + final Row destRow3 = sheet.getRow(11); + int col = 0; + Cell cell; + + // Header row should be copied + assertNotNull(destHeaderRow); + + // Data rows + cell = CellUtil.getCell(destRow1, col); + assertEquals("Source row ->", cell.getStringCellValue()); + + // Style + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("Red", cell.getStringCellValue(), "[Style] B10 cell value"); + assertEquals(CellUtil.getCell(srcRow1, 1).getCellStyle(), cell.getCellStyle(), "[Style] B10 cell style"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("Blue", cell.getStringCellValue(), "[Style] B11 cell value"); + assertEquals(CellUtil.getCell(srcRow2, 1).getCellStyle(), cell.getCellStyle(), "[Style] B11 cell style"); + + // Blank + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C10 cell type"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.BLANK, cell.getCellType(), "[Blank] C11 cell type"); + + // Error + col++; + cell = CellUtil.getCell(destRow1, col); + FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); + //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D10 cell type"); + assertEquals(FormulaError.NA, error, "[Error] D10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + error = FormulaError.forInt(cell.getErrorCellValue()); + //FIXME: XSSFCell and HSSFCell expose different interfaces. getErrorCellString would be helpful here + assertEquals(CellType.ERROR, cell.getCellType(), "[Error] D11 cell type"); + assertEquals(FormulaError.NAME, error, "[Error] D11 cell value"); + + // Date + col++; + cell = CellUtil.getCell(destRow1, col); + Date date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 1).getTime(); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E10 cell type"); + assertEquals(date, cell.getDateCellValue(), "[Date] E10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + date = LocaleUtil.getLocaleCalendar(2000, Calendar.JANUARY, 2).getTime(); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Date] E11 cell type"); + assertEquals(date, cell.getDateCellValue(), "[Date] E11 cell value"); + + // Boolean + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F10 cell type"); + assertTrue(cell.getBooleanCellValue(), "[Boolean] F10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.BOOLEAN, cell.getCellType(), "[Boolean] F11 cell type"); + assertFalse(cell.getBooleanCellValue(), "[Boolean] F11 cell value"); + + // String + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.STRING, cell.getCellType(), "[String] G10 cell type"); + assertEquals("Hello", cell.getStringCellValue(), "[String] G10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.STRING, cell.getCellType(), "[String] G11 cell type"); + assertEquals("World", cell.getStringCellValue(), "[String] G11 cell value"); + + // Int + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H10 cell type"); + assertEquals(15, (int) cell.getNumericCellValue(), "[Int] H10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Int] H11 cell type"); + assertEquals(42, (int) cell.getNumericCellValue(), "[Int] H11 cell value"); + + // Float + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I10 cell type"); + assertEquals(12.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I10 cell value"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Float] I11 cell type"); + assertEquals(5.5, cell.getNumericCellValue(), FLOAT_PRECISION, "[Float] I11 cell value"); + + // Cell Formula + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J10 cell type"); + assertEquals("5+2", cell.getCellFormula(), "[Cell Formula] J10 cell formula"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula] J11 cell type"); + assertEquals("6+18", cell.getCellFormula(), "[Cell Formula] J11 cell formula"); + + // Cell Formula with Reference + col++; + // Formula row references should be adjusted by destRowNum-srcRowNum + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K10 cell type"); + assertEquals("J10+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K10 cell formula"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference] K11 cell type"); + assertEquals("J11+H$2", cell.getCellFormula(), "[Cell Formula with Reference] K11 cell formula"); + + // Cell Formula with Reference spanning multiple rows + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L10 cell type"); + assertEquals("G10&\" \"&G11", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L10 cell formula"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Reference spanning multiple rows] L11 cell type"); + assertEquals("G11&\" \"&G12", cell.getCellFormula(), "[Cell Formula with Reference spanning multiple rows] L11 cell formula"); + + // Cell Formula with Area Reference + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M10 cell type"); + assertEquals("SUM(H10:I11)", cell.getCellFormula(), "[Cell Formula with Area Reference] M10 cell formula"); + + cell = CellUtil.getCell(destRow2, col); + // Also acceptable: SUM($H10:I$3), but this AreaReference isn't in ascending order + assertEquals(CellType.FORMULA, cell.getCellType(), "[Cell Formula with Area Reference] M11 cell type"); + assertEquals("SUM($H$3:I10)", cell.getCellFormula(), "[Cell Formula with Area Reference] M11 cell formula"); + + // Array Formula + col++; + // cell = CellUtil.getCell(destRow1, col); + // Array formulas currently unsupported + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + // assertEquals(CellType.FORMULA, cell.getCellType(), "[Array Formula] N10 cell type"); + // assertEquals("{SUM(H10:J10*{1,2,3})}", cell.getCellFormula(), "[Array Formula] N10 cell formula"); + + // cell = CellUtil.getCell(destRow2, col); + // FIXME: Array Formula set with Sheet.setArrayFormula() instead of cell.setFormula() + // assertEquals(CellType.FORMULA, cell.getCellType(). "[Array Formula] N11 cell type"); + // assertEquals("{SUM(H11:J11*{1,2,3})}", cell.getCellFormula(). "[Array Formula] N11 cell formula"); + + // Data Format + col++; + cell = CellUtil.getCell(destRow2, col); + assertEquals(CellType.NUMERIC, cell.getCellType(), "[Data Format] O10 cell type"); + assertEquals(100.20, cell.getNumericCellValue(), FLOAT_PRECISION, "[Data Format] O10 cell value"); + final String moneyFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"; + assertEquals(moneyFormat, cell.getCellStyle().getDataFormatString(), "[Data Format] O10 cell data format"); + + // Merged + col++; + cell = CellUtil.getCell(destRow1, col); + assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P10:Q10 cell value"); + assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P10:Q10")), "[Merged] P10:Q10 merged region"); + + cell = CellUtil.getCell(destRow2, col); + assertEquals("Merged cells", cell.getStringCellValue(), "[Merged] P11:Q11 cell value"); + assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("P11:Q11")), "[Merged] P11:Q11 merged region"); + + // Should Q10/Q11 be checked? + + // Merged across multiple rows + // Microsoft Excel 2013 does not copy a merged region unless all rows of + // the source merged region are selected + // POI's behavior should match this behavior + col += 2; + cell = CellUtil.getCell(destRow1, col); + assertEquals("Merged cells across multiple rows", cell.getStringCellValue(), "[Merged across multiple rows] R10:S11 cell value"); + assertTrue(sheet.getMergedRegions().contains(CellRangeAddress.valueOf("R10:S11")), "[Merged across multiple rows] R10:S11 merged region"); + + // Row 3 (zero-based) was empty, so Row 11 (zero-based) should be empty too. + if (srcRow3 == null) { + assertNull(destRow3, "Row 3 was empty, so Row 11 should be empty"); + } + + // Make sure other rows are blank (off-by-one errors) + assertNull(sheet.getRow(7), "Off-by-one lower edge case"); //one row above destHeaderRow + assertNull(sheet.getRow(12), "Off-by-one upper edge case"); //one row below destRow3 + } } @Test @@ -1998,26 +1985,34 @@ public final class TestXSSFSheet extends BaseTestXSheet { @Test public void bug65120() throws IOException { - XSSFWorkbook wb = new XSSFWorkbook(); - CreationHelper creationHelper = wb.getCreationHelper(); - - Sheet sheet1 = wb.createSheet(); - Cell cell1 = sheet1.createRow(0).createCell(0); - Comment comment1 = sheet1.createDrawingPatriarch().createCellComment(creationHelper.createClientAnchor()); - cell1.setCellComment(comment1); - - Sheet sheet2 = wb.createSheet(); - Cell cell2 = sheet2.createRow(0).createCell(0); - Comment comment2 = sheet2.createDrawingPatriarch().createCellComment(creationHelper.createClientAnchor()); - cell2.setCellComment(comment2); - - wb.removeSheetAt(0); - - Sheet sheet3 = wb.createSheet(); - Cell cell3 = sheet3.createRow(0).createCell(0); - Comment comment3 = sheet3.createDrawingPatriarch().createCellComment(creationHelper.createClientAnchor()); - cell3.setCellComment(comment3); - - wb.close(); + try (XSSFWorkbook wb = new XSSFWorkbook()) { + XSSFCreationHelper creationHelper = wb.getCreationHelper(); + + XSSFSheet sheet1 = wb.createSheet(); + Cell cell1 = sheet1.createRow(0).createCell(0); + XSSFDrawing dwg1 = sheet1.createDrawingPatriarch(); + XSSFComment comment1 = dwg1.createCellComment(creationHelper.createClientAnchor()); + cell1.setCellComment(comment1); + XSSFVMLDrawing vml0 = sheet1.getVMLDrawing(false); + assertEquals("/xl/drawings/vmlDrawing0.vml", vml0.getPackagePart().getPartName().getName()); + + XSSFSheet sheet2 = wb.createSheet(); + Cell cell2 = sheet2.createRow(0).createCell(0); + XSSFDrawing dwg2 = sheet2.createDrawingPatriarch(); + Comment comment2 = dwg2.createCellComment(creationHelper.createClientAnchor()); + cell2.setCellComment(comment2); + XSSFVMLDrawing vml1 = sheet2.getVMLDrawing(false); + assertEquals("/xl/drawings/vmlDrawing1.vml", vml1.getPackagePart().getPartName().getName()); + + wb.removeSheetAt(0); + + XSSFSheet sheet3 = wb.createSheet(); + Cell cell3 = sheet3.createRow(0).createCell(0); + XSSFDrawing dwg3 = sheet3.createDrawingPatriarch(); + Comment comment3 = dwg3.createCellComment(creationHelper.createClientAnchor()); + cell3.setCellComment(comment3); + XSSFVMLDrawing vml2 = sheet3.getVMLDrawing(false); + assertEquals("/xl/drawings/vmlDrawing2.vml", vml2.getPackagePart().getPartName().getName()); + } } } |