From a4c370f913436f79ecd5c963a6b3bb410b724ecf Mon Sep 17 00:00:00 2001 From: Andreas Beeker Date: Mon, 6 Jan 2020 21:48:00 +0000 Subject: [PATCH] sonar fixes - use assert in junit tests use try-with-resources remove obsolete and renamed OPOIFS references add logic to unit tests which just opened a file, by rewriting it and some formula evaluation logic git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1872397 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/usermodel/TestBugs.java | 3167 ++++++++--------- .../ss/usermodel/BaseTestBugzillaIssues.java | 2311 ++++++------ 2 files changed, 2610 insertions(+), 2868 deletions(-) diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index 060f665bc7..6252f552a5 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -18,31 +18,33 @@ package org.apache.poi.hssf.usermodel; import static org.apache.poi.POITestCase.assertContains; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertFalse; -import static org.junit.Assert.assertNotNull; -import static org.junit.Assert.assertNotSame; -import static org.junit.Assert.assertNull; -import static org.junit.Assert.assertSame; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; +import static org.apache.poi.hssf.HSSFTestDataSamples.openSampleWorkbook; +import static org.apache.poi.hssf.HSSFTestDataSamples.writeOutAndReadBack; +import static org.junit.Assert.*; -import javax.imageio.ImageIO; import java.awt.image.BufferedImage; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; -import java.io.FileInputStream; import java.io.IOException; -import java.io.InputStream; import java.net.URL; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; +import java.util.HashSet; import java.util.Iterator; +import java.util.LinkedHashMap; import java.util.List; import java.util.Locale; +import java.util.Map; +import java.util.Objects; +import java.util.Set; import java.util.TimeZone; +import java.util.function.Consumer; +import java.util.stream.Collectors; +import java.util.stream.IntStream; + +import javax.imageio.ImageIO; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hpsf.PropertySet; @@ -87,9 +89,13 @@ import org.apache.poi.ss.usermodel.SheetVisibility; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.LocaleUtil; +import org.junit.AfterClass; import org.junit.Assume; +import org.junit.BeforeClass; import org.junit.Ignore; +import org.junit.Rule; import org.junit.Test; +import org.junit.rules.ExpectedException; /** * Testcases for bugs entered in bugzilla @@ -99,42 +105,57 @@ import org.junit.Test; * define the test in the base class {@link BaseTestBugzillaIssues} */ public final class TestBugs extends BaseTestBugzillaIssues { + + @Rule + public ExpectedException thrown = ExpectedException.none(); + public TestBugs() { super(HSSFITestDataProvider.instance); } - private static HSSFWorkbook openSample(String sampleFileName) { - return HSSFITestDataProvider.instance.openSampleWorkbook(sampleFileName); + private static final Map SIMPLE_REFS = new LinkedHashMap<>(); + + // References used for the simpleTest convenience method + @BeforeClass + public static void initSimpleRefs() { + String[] refs = { + "Calculations", + "/Documents and Settings/crawformk.EUU/Local Settings/Temporary Internet Files/OLK64/Daily Status Report Generation Files/DST - Daily Data Transfer Sheet - 2002.xls", + "Sheet1", + "/Documents and Settings/donnag/Local Settings/Temporary Internet Files/OLK7/0231324V1-1.xls", + "Sheet1", + "refs/airport.xls", + "Sheet1", + "9http://www.principlesofeconometrics.com/excel/airline.xls", + "Sheet1", + "C:Documents and Settings/Yegor/My Documents/csco.xls", + }; + + for (int i=0; i assertNotNull(wb.getSheetAt(0).getRow(45))); } /** * Double byte strings */ @Test - public void bug22742() { - openSample("22742.xls"); + public void bug22742() throws IOException { + simpleTest("22742.xls"); } /** * Double byte strings */ @Test - public void bug12561_1() { - openSample("12561-1.xls"); + public void bug12561_1() throws IOException { + simpleTest("12561-1.xls"); } /** * Double byte strings */ @Test - public void bug12561_2() { - openSample("12561-2.xls"); + public void bug12561_2() throws IOException { + simpleTest("12561-2.xls"); } /** @@ -241,8 +252,8 @@ public final class TestBugs extends BaseTestBugzillaIssues { * File supplied by jubeson */ @Test - public void bug12843_1() { - openSample("12843-1.xls"); + public void bug12843_1() throws IOException { + simpleTest("12843-1.xls"); } /** @@ -250,45 +261,45 @@ public final class TestBugs extends BaseTestBugzillaIssues { * File supplied by Paul Chung */ @Test - public void bug12843_2() { - openSample("12843-2.xls"); + public void bug12843_2() throws IOException { + simpleTest("12843-2.xls"); } /** * Reference to Name */ @Test - public void bug13224() { - openSample("13224.xls"); + public void bug13224() throws IOException { + simpleTest("13224.xls"); } /** * Illegal argument exception - cannot store duplicate value in Map */ @Test - public void bug19599() { - openSample("19599-1.xls"); - openSample("19599-2.xls"); + public void bug19599() throws IOException { + simpleTest("19599-1.xls"); + simpleTest("19599-2.xls"); } @Test public void bug24215() throws Exception { - HSSFWorkbook wb = openSample("24215.xls"); + try (HSSFWorkbook wb = openSampleWorkbook("24215.xls")) { - for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { - HSSFSheet sheet = wb.getSheetAt(sheetIndex); - int rows = sheet.getLastRowNum(); + for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { + HSSFSheet sheet = wb.getSheetAt(sheetIndex); + int rows = sheet.getLastRowNum(); - for (int rowIndex = 0; rowIndex < rows; rowIndex++) { - HSSFRow row = sheet.getRow(rowIndex); - int cells = row.getLastCellNum(); + for (int rowIndex = 0; rowIndex < rows; rowIndex++) { + HSSFRow row = sheet.getRow(rowIndex); + int cells = row.getLastCellNum(); - for (int cellIndex = 0; cellIndex < cells; cellIndex++) { - row.getCell(cellIndex); + for (int cellIndex = 0; cellIndex < cells; cellIndex++) { + row.getCell(cellIndex); + } } } } - wb.close(); } /** @@ -299,16 +310,16 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void bugUnicodeStringFormulaRead() throws Exception { - HSSFWorkbook w = openSample("25695.xls"); + try (HSSFWorkbook w = openSampleWorkbook("25695.xls")) { - HSSFCell a1 = w.getSheetAt(0).getRow(0).getCell(0); - HSSFCell a2 = w.getSheetAt(0).getRow(0).getCell(1); - HSSFCell b1 = w.getSheetAt(0).getRow(1).getCell(0); - HSSFCell b2 = w.getSheetAt(0).getRow(1).getCell(1); - HSSFCell c1 = w.getSheetAt(0).getRow(2).getCell(0); - HSSFCell c2 = w.getSheetAt(0).getRow(2).getCell(1); - HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell(0); - HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell(1); + HSSFCell a1 = w.getSheetAt(0).getRow(0).getCell(0); + HSSFCell a2 = w.getSheetAt(0).getRow(0).getCell(1); + HSSFCell b1 = w.getSheetAt(0).getRow(1).getCell(0); + HSSFCell b2 = w.getSheetAt(0).getRow(1).getCell(1); + HSSFCell c1 = w.getSheetAt(0).getRow(2).getCell(0); + HSSFCell c2 = w.getSheetAt(0).getRow(2).getCell(1); + HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell(0); + HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell(1); /* // THAI code page @@ -325,34 +336,33 @@ public final class TestBugs extends BaseTestBugzillaIssues { System.out.println("d2="+unicodeString(d2)); */ - confirmSameCellText(a1, a2); - confirmSameCellText(b1, b2); - confirmSameCellText(c1, c2); - confirmSameCellText(d1, d2); - - HSSFWorkbook rw = writeOutAndReadBack(w); - w.close(); - - HSSFCell ra1 = rw.getSheetAt(0).getRow(0).getCell(0); - HSSFCell ra2 = rw.getSheetAt(0).getRow(0).getCell(1); - HSSFCell rb1 = rw.getSheetAt(0).getRow(1).getCell(0); - HSSFCell rb2 = rw.getSheetAt(0).getRow(1).getCell(1); - HSSFCell rc1 = rw.getSheetAt(0).getRow(2).getCell(0); - HSSFCell rc2 = rw.getSheetAt(0).getRow(2).getCell(1); - HSSFCell rd1 = rw.getSheetAt(0).getRow(3).getCell(0); - HSSFCell rd2 = rw.getSheetAt(0).getRow(3).getCell(1); - - confirmSameCellText(a1, ra1); - confirmSameCellText(b1, rb1); - confirmSameCellText(c1, rc1); - confirmSameCellText(d1, rd1); - - confirmSameCellText(a1, ra2); - confirmSameCellText(b1, rb2); - confirmSameCellText(c1, rc2); - confirmSameCellText(d1, rd2); - - rw.close(); + confirmSameCellText(a1, a2); + confirmSameCellText(b1, b2); + confirmSameCellText(c1, c2); + confirmSameCellText(d1, d2); + + try (HSSFWorkbook rw = writeOutAndReadBack(w)) { + + HSSFCell ra1 = rw.getSheetAt(0).getRow(0).getCell(0); + HSSFCell ra2 = rw.getSheetAt(0).getRow(0).getCell(1); + HSSFCell rb1 = rw.getSheetAt(0).getRow(1).getCell(0); + HSSFCell rb2 = rw.getSheetAt(0).getRow(1).getCell(1); + HSSFCell rc1 = rw.getSheetAt(0).getRow(2).getCell(0); + HSSFCell rc2 = rw.getSheetAt(0).getRow(2).getCell(1); + HSSFCell rd1 = rw.getSheetAt(0).getRow(3).getCell(0); + HSSFCell rd2 = rw.getSheetAt(0).getRow(3).getCell(1); + + confirmSameCellText(a1, ra1); + confirmSameCellText(b1, rb1); + confirmSameCellText(c1, rc1); + confirmSameCellText(d1, rd1); + + confirmSameCellText(a1, ra2); + confirmSameCellText(b1, rb2); + confirmSameCellText(c1, rc2); + confirmSameCellText(d1, rd2); + } + } } private static void confirmSameCellText(HSSFCell a, HSSFCell b) { @@ -363,16 +373,16 @@ public final class TestBugs extends BaseTestBugzillaIssues { * Error in opening wb */ @Test - public void bug32822() { - openSample("32822.xls"); + public void bug32822() throws IOException { + simpleTest("32822.xls"); } /** * fail to read wb with chart */ @Test - public void bug15573() { - openSample("15573.xls"); + public void bug15573() throws IOException { + simpleTest("15573.xls"); } /** @@ -380,98 +390,85 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug27852() throws Exception { - HSSFWorkbook wb = openSample("27852.xls"); + try (HSSFWorkbook wb = openSampleWorkbook("27852.xls")) { - for (int i = 0; i < wb.getNumberOfNames(); i++) { - HSSFName name = wb.getNameAt(i); - name.getNameName(); - if (name.isFunctionName()) { - continue; + for (int i = 0; i < wb.getNumberOfNames(); i++) { + HSSFName name = wb.getNameAt(i); + name.getNameName(); + if (name.isFunctionName()) { + continue; + } + name.getRefersToFormula(); } - name.getRefersToFormula(); } - wb.close(); } @Test - public void bug33082() { - openSample("33082.xls"); + public void bug33082() throws IOException { + simpleTest("33082.xls"); } @Test - public void bug34775() { - try { - openSample("34775.xls"); - } catch (NullPointerException e) { - fail("identified bug 34775"); - } + public void bug34775() throws IOException { + // NullPointerException -> identified bug 34775 + simpleTest("34775.xls"); } /** * Error when reading then writing ArrayValues in NameRecord's */ @Test - public void bug37630() throws Exception { - HSSFWorkbook wb = openSample("37630.xls"); - writeOutAndReadBack(wb); - wb.close(); + public void bug37630() throws IOException { + simpleTest("37630.xls"); } /** * Bug 25183: org.apache.poi.hssf.usermodel.HSSFSheet.setPropertiesFromSheet */ @Test - public void bug25183() throws Exception { - HSSFWorkbook wb = openSample("25183.xls"); - writeOutAndReadBack(wb); - wb.close(); + public void bug25183() throws IOException { + simpleTest("25183.xls"); } /** * Bug 26100: 128-character message in IF statement cell causes HSSFWorkbook open failure */ @Test - public void bug26100() throws Exception { - HSSFWorkbook wb = openSample("26100.xls"); - writeOutAndReadBack(wb); - wb.close(); + public void bug26100() throws IOException { + simpleTest("26100.xls"); } /** * Bug 27933: Unable to use a template (xls) file containing a wmf graphic */ @Test - public void bug27933() throws Exception { - HSSFWorkbook wb = openSample("27933.xls"); - writeOutAndReadBack(wb); - wb.close(); + public void bug27933() throws IOException { + simpleTest("27933.xls"); } /** - * Bug 29206: NPE on HSSFSheet.getRow for blank rows + * Bug 29206: NPE on HSSFSheet.getRow for blank rows */ @Test public void bug29206() throws Exception { //the first check with blank workbook - HSSFWorkbook wb = openSample("Simple.xls"); - HSSFSheet sheet = wb.createSheet(); - for (int i = 1; i < 400; i++) { - HSSFRow row = sheet.getRow(i); - if (row != null) { - row.getCell(0); + try (HSSFWorkbook wb = openSampleWorkbook("Simple.xls")) { + HSSFSheet sheet = wb.createSheet(); + for (int i = 1; i < 400; i++) { + HSSFRow row = sheet.getRow(i); + if (row != null) { + row.getCell(0); + } } } - wb.close(); } /** * Bug 29675: POI 2.5 final corrupts output when starting workbook has a graphic */ @Test - public void bug29675() throws Exception { - HSSFWorkbook wb = openSample("29675.xls"); - writeOutAndReadBack(wb); - wb.close(); + public void bug29675() throws IOException { + simpleTest("29675.xls"); } /** @@ -479,22 +476,22 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug29942() throws Exception { - HSSFWorkbook wb = openSample("29942.xls"); - - HSSFSheet sheet = wb.getSheetAt(0); - int count = 0; - for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { - HSSFRow row = sheet.getRow(i); - if (row != null) { - HSSFCell cell = row.getCell(0); - assertEquals(CellType.STRING, cell.getCellType()); - count++; + try (HSSFWorkbook wb = openSampleWorkbook("29942.xls")) { + + HSSFSheet sheet = wb.getSheetAt(0); + int count = 0; + for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { + HSSFRow row = sheet.getRow(i); + if (row != null) { + HSSFCell cell = row.getCell(0); + assertEquals(CellType.STRING, cell.getCellType()); + count++; + } } - } - assertEquals(85, count); //should read 85 rows + assertEquals(85, count); //should read 85 rows - writeOutAndReadBack(wb).close(); - wb.close(); + writeOutAndReadBack(wb).close(); + } } /** @@ -503,9 +500,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug29982() throws Exception { - HSSFWorkbook wb = openSample("29982.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("29982.xls"); } /** @@ -513,33 +508,33 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug30540() throws Exception { - HSSFWorkbook wb = openSample("30540.xls"); - - HSSFSheet s = wb.getSheetAt(0); - s.setRowBreak(1); - writeOutAndReadBack(wb).close(); - - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("30540.xls")) { + + HSSFSheet s1 = wb.getSheetAt(0); + s1.setRowBreak(1); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb)) { + HSSFSheet s2 = wb2.getSheetAt(0); + int[] act = s2.getRowBreaks(); + int[] exp = { 1 }; + assertArrayEquals(exp, act); + } + } } /** * Bug 31749: {Need help urgently}[This is critical] workbook.write() corrupts the file......? */ @Test - public void bug31749() throws Exception { - HSSFWorkbook wb = openSample("31749.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + public void bug31749() throws IOException { + simpleTest("31749.xls"); } /** * Bug 31979: {urgent help needed .....}poi library does not support form objects properly. */ @Test - public void bug31979() throws Exception { - HSSFWorkbook wb = openSample("31979.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + public void bug31979() throws IOException { + simpleTest("31979.xls"); } /** @@ -548,15 +543,11 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug35564() throws Exception { - HSSFWorkbook wb = openSample("35564.xls"); - - HSSFSheet sheet = wb.getSheetAt(0); - assertFalse(sheet.isGridsPrinted()); - assertFalse(sheet.getProtect()); - - writeOutAndReadBack(wb).close(); - - wb.close(); + simpleTest("35564.xls", wb -> { + HSSFSheet sheet = wb.getSheetAt(0); + assertFalse(sheet.isGridsPrinted()); + assertFalse(sheet.getProtect()); + }); } /** @@ -564,13 +555,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug35565() throws Exception { - HSSFWorkbook wb = openSample("35565.xls"); - - HSSFSheet sheet = wb.getSheetAt(0); - assertNotNull(sheet); - writeOutAndReadBack(wb).close(); - - wb.close(); + simpleTest("35565.xls", wb -> assertNotNull(wb.getSheetAt(0))); } /** @@ -578,9 +563,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug37376() throws Exception { - HSSFWorkbook wb = openSample("37376.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("37376.xls"); } /** @@ -588,20 +571,20 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug40285() throws Exception { - HSSFWorkbook wb = openSample("40285.xls"); - - HSSFSheet sheet = wb.getSheetAt(0); - int rownum = 0; - for (Iterator it = sheet.rowIterator(); it.hasNext(); rownum++) { - Row row = it.next(); - assertEquals(rownum, row.getRowNum()); - int cellNum = 0; - for (Iterator it2 = row.cellIterator(); it2.hasNext(); cellNum++) { - Cell cell = it2.next(); - assertEquals(cellNum, cell.getColumnIndex()); + try (HSSFWorkbook wb = openSampleWorkbook("40285.xls")) { + + HSSFSheet sheet = wb.getSheetAt(0); + int rownum = 0; + for (Iterator it = sheet.rowIterator(); it.hasNext(); rownum++) { + Row row = it.next(); + assertEquals(rownum, row.getRowNum()); + int cellNum = 0; + for (Iterator it2 = row.cellIterator(); it2.hasNext(); cellNum++) { + Cell cell = it2.next(); + assertEquals(cellNum, cell.getColumnIndex()); + } } } - wb.close(); } /** @@ -618,25 +601,23 @@ public final class TestBugs extends BaseTestBugzillaIssues { public void bug38266() throws Exception { String[] files = {"Simple.xls", "SimpleMultiCell.xls", "duprich1.xls"}; for (String file : files) { - HSSFWorkbook wb = openSample(file); + try (HSSFWorkbook wb = openSampleWorkbook(file)) { - HSSFSheet sheet = wb.getSheetAt(0); - int[] breaks = sheet.getRowBreaks(); - assertEquals(0, breaks.length); + HSSFSheet sheet = wb.getSheetAt(0); + int[] breaks = sheet.getRowBreaks(); + assertEquals(0, breaks.length); - //add 3 row breaks - for (int j = 1; j <= 3; j++) { - sheet.setRowBreak(j * 20); + //add 3 row breaks + for (int j = 1; j <= 3; j++) { + sheet.setRowBreak(j * 20); + } } - wb.close(); } } @Test public void bug40738() throws Exception { - HSSFWorkbook wb = openSample("SimpleWithAutofilter.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("SimpleWithAutofilter.xls"); } /** @@ -644,10 +625,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44200() throws Exception { - HSSFWorkbook wb = openSample("44200.xls"); - wb.cloneSheet(0); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("44200.xls", wb -> wb.cloneSheet(0)); } /** @@ -655,9 +633,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44201() throws Exception { - HSSFWorkbook wb = openSample("44201.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("44201.xls"); } /** @@ -665,12 +641,8 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug37684() throws Exception { - HSSFWorkbook wb1 = openSample("37684-1.xls"); - writeOutAndReadBack(wb1).close(); - wb1.close(); - HSSFWorkbook wb2 = openSample("37684-2.xls"); - writeOutAndReadBack(wb2).close(); - wb2.close(); + simpleTest("37684-1.xls"); + simpleTest("37684-2.xls"); } /** @@ -678,9 +650,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug41139() throws Exception { - HSSFWorkbook wb = openSample("41139.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("41139.xls"); } /** @@ -689,12 +659,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug41546() throws Exception { - HSSFWorkbook wb1 = openSample("41546.xls"); - assertEquals(1, wb1.getNumberOfSheets()); - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - assertEquals(1, wb2.getNumberOfSheets()); - wb2.close(); - wb1.close(); + simpleTest("41546.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -703,9 +668,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug42564() throws Exception { - HSSFWorkbook wb = openSample("ex42564-21435.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("ex42564-21435.xls"); } /** @@ -715,9 +678,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug42564Alt() throws Exception { - HSSFWorkbook wb = openSample("ex42564-21503.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("ex42564-21503.xls"); } /** @@ -726,31 +687,30 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug42618() throws Exception { - HSSFWorkbook wb1 = openSample("SimpleWithChoose.xls"); - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - // Check we detect the string properly too - HSSFSheet s = wb2.getSheetAt(0); + try (HSSFWorkbook wb1 = openSampleWorkbook("SimpleWithChoose.xls"); + HSSFWorkbook wb2 = writeOutAndReadBack(wb1)){ + // Check we detect the string properly too + HSSFSheet s = wb2.getSheetAt(0); - // Textual value - HSSFRow r1 = s.getRow(0); - HSSFCell c1 = r1.getCell(1); - assertEquals("=CHOOSE(2,A2,A3,A4)", c1.getRichStringCellValue().toString()); + // Textual value + HSSFRow r1 = s.getRow(0); + HSSFCell c1 = r1.getCell(1); + assertEquals("=CHOOSE(2,A2,A3,A4)", c1.getRichStringCellValue().toString()); - // Formula Value - HSSFRow r2 = s.getRow(1); - HSSFCell c2 = r2.getCell(1); - assertEquals(25, (int) c2.getNumericCellValue()); + // Formula Value + HSSFRow r2 = s.getRow(1); + HSSFCell c2 = r2.getCell(1); + assertEquals(25, (int) c2.getNumericCellValue()); - try { - assertEquals("CHOOSE(2,A2,A3,A4)", c2.getCellFormula()); - } catch (IllegalStateException e) { - if (e.getMessage().startsWith("Too few arguments") - && e.getMessage().indexOf("ConcatPtg") > 0) { - fail("identified bug 44306"); + try { + assertEquals("CHOOSE(2,A2,A3,A4)", c2.getCellFormula()); + } catch (IllegalStateException e) { + if (e.getMessage().startsWith("Too few arguments") + && e.getMessage().indexOf("ConcatPtg") > 0) { + fail("identified bug 44306"); + } } } - wb2.close(); } /** @@ -758,11 +718,8 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug43251() throws Exception { - // Used to blow up with an IllegalArgumentException - // when creating a FileSharingRecord - HSSFWorkbook wb = openSample("43251.xls"); - assertEquals(1, wb.getNumberOfSheets()); - wb.close(); + // Used to blow up with an IllegalArgumentException when creating a FileSharingRecord + simpleTest("43251.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -771,11 +728,8 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44471() throws Exception { - // Used to blow up with an ArrayIndexOutOfBounds - // when creating a StyleRecord - HSSFWorkbook wb = openSample("OddStyleRecord.xls"); - assertEquals(1, wb.getNumberOfSheets()); - wb.close(); + // Used to blow up with an ArrayIndexOutOfBounds when creating a StyleRecord + simpleTest("OddStyleRecord.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -784,19 +738,16 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44536() throws Exception { - // Used to blow up with an IllegalArgumentException - // when creating a FileSharingRecord - HSSFWorkbook wb1 = openSample("ReadOnlyRecommended.xls"); + // Used to blow up with an IllegalArgumentException when creating a FileSharingRecord + simpleTest("ReadOnlyRecommended.xls", wb -> { + // Check read only advised + assertEquals(3, wb.getNumberOfSheets()); + assertTrue(wb.isWriteProtected()); + }); - // Check read only advised - assertEquals(3, wb1.getNumberOfSheets()); - assertTrue(wb1.isWriteProtected()); - wb1.close(); // But also check that another wb isn't - HSSFWorkbook wb2 = openSample("SimpleWithChoose.xls"); - assertFalse(wb2.isWriteProtected()); - wb2.close(); + simpleTest("SimpleWithChoose.xls", wb -> assertFalse(wb.isWriteProtected())); } /** @@ -805,13 +756,9 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44593() throws Exception { - // Used to blow up with an IllegalArgumentException - // when creating a DVRecord - // Now won't, but no idea if this means we have - // rubbish in the DVRecord or not... - HSSFWorkbook wb = openSample("44593.xls"); - assertEquals(2, wb.getNumberOfSheets()); - wb.close(); + // Used to blow up with an IllegalArgumentException when creating a DVRecord + // Now won't, but no idea if this means we have rubbish in the DVRecord or not... + simpleTest("44593.xls", wb -> assertEquals(2, wb.getNumberOfSheets())); } /** @@ -821,9 +768,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void bug44643() throws Exception { // Used to blow up with an IllegalArgumentException - HSSFWorkbook wb = openSample("44643.xls"); - assertEquals(1, wb.getNumberOfSheets()); - wb.close(); + simpleTest("44643.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -832,23 +777,23 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44693() throws Exception { - HSSFWorkbook wb = openSample("44693.xls"); - HSSFSheet s = wb.getSheetAt(0); - - // Rows are 1 to 713 - assertEquals(0, s.getFirstRowNum()); - assertEquals(712, s.getLastRowNum()); - assertEquals(713, s.getPhysicalNumberOfRows()); + try (HSSFWorkbook wb = openSampleWorkbook("44693.xls")) { + HSSFSheet s = wb.getSheetAt(0); - // Now check the iterator - int rowsSeen = 0; - for (Iterator i = s.rowIterator(); i.hasNext(); ) { - Row r = i.next(); - assertNotNull(r); - rowsSeen++; + // Rows are 1 to 713 + assertEquals(0, s.getFirstRowNum()); + assertEquals(712, s.getLastRowNum()); + assertEquals(713, s.getPhysicalNumberOfRows()); + + // Now check the iterator + int rowsSeen = 0; + for (Iterator i = s.rowIterator(); i.hasNext(); ) { + Row r = i.next(); + assertNotNull(r); + rowsSeen++; + } + assertEquals(713, rowsSeen); } - assertEquals(713, rowsSeen); - wb.close(); } /** @@ -856,11 +801,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug28774() throws Exception { - HSSFWorkbook wb = openSample("28774.xls"); - assertTrue("no errors reading sample xls", true); - writeOutAndReadBack(wb).close(); - assertTrue("no errors writing sample xls", true); - wb.close(); + simpleTest("28774.xls"); } /** @@ -869,11 +810,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44891() throws Exception { - HSSFWorkbook wb = openSample("44891.xls"); - assertTrue("no errors reading sample xls", true); - writeOutAndReadBack(wb).close(); - assertTrue("no errors writing sample xls", true); - wb.close(); + simpleTest("44891.xls"); } /** @@ -883,29 +820,17 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44235() throws Exception { - HSSFWorkbook wb = openSample("44235.xls"); - assertTrue("no errors reading sample xls", true); - writeOutAndReadBack(wb).close(); - assertTrue("no errors writing sample xls", true); - wb.close(); + simpleTest("44235.xls"); } @Test public void bug36947() throws Exception { - HSSFWorkbook wb = openSample("36947.xls"); - assertTrue("no errors reading sample xls", true); - writeOutAndReadBack(wb).close(); - assertTrue("no errors writing sample xls", true); - wb.close(); + simpleTest("36947.xls"); } @Test public void bug39634() throws Exception { - HSSFWorkbook wb = openSample("39634.xls"); - assertTrue("no errors reading sample xls", true); - writeOutAndReadBack(wb).close(); - assertTrue("no errors writing sample xls", true); - wb.close(); + simpleTest("39634.xls"); } /** @@ -914,34 +839,31 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test(expected = IllegalArgumentException.class) public void bug44840() throws Exception { - HSSFWorkbook wb = openSample("WithCheckBoxes.xls"); + try (HSSFWorkbook wb = openSampleWorkbook("WithCheckBoxes.xls")) { - // Take a look at the embedded objects - List objects = wb.getAllEmbeddedObjects(); - assertEquals(1, objects.size()); + // Take a look at the embedded objects + List objects = wb.getAllEmbeddedObjects(); + assertEquals(1, objects.size()); - HSSFObjectData obj = objects.get(0); - assertNotNull(obj); + HSSFObjectData obj = objects.get(0); + assertNotNull(obj); - // Peek inside the underlying record - EmbeddedObjectRefSubRecord rec = obj.findObjectRecord(); - assertNotNull(rec); + // Peek inside the underlying record + EmbeddedObjectRefSubRecord rec = obj.findObjectRecord(); + assertNotNull(rec); -// assertEquals(32, rec.field_1_stream_id_offset); - assertEquals(0, rec.getStreamId().intValue()); // WRONG! - assertEquals("Forms.CheckBox.1", rec.getOLEClassName()); - assertEquals(12, rec.getObjectData().length); + // assertEquals(32, rec.field_1_stream_id_offset); + assertEquals(0, rec.getStreamId().intValue()); // WRONG! + assertEquals("Forms.CheckBox.1", rec.getOLEClassName()); + assertEquals(12, rec.getObjectData().length); - // Doesn't have a directory - assertFalse(obj.hasDirectoryEntry()); - assertNotNull(obj.getObjectData()); - assertEquals(12, obj.getObjectData().length); - assertEquals("Forms.CheckBox.1", obj.getOLE2ClassName()); + // Doesn't have a directory + assertFalse(obj.hasDirectoryEntry()); + assertNotNull(obj.getObjectData()); + assertEquals(12, obj.getObjectData().length); + assertEquals("Forms.CheckBox.1", obj.getOLE2ClassName()); - try { obj.getDirectory(); - } finally { - wb.close(); } } @@ -952,33 +874,33 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug30978() throws Exception { - HSSFWorkbook wb1 = openSample("30978-alt.xls"); - assertEquals(1, wb1.getNumberOfNames()); - assertEquals(3, wb1.getNumberOfSheets()); + try (HSSFWorkbook wb1 = openSampleWorkbook("30978-alt.xls")) { + assertEquals(1, wb1.getNumberOfNames()); + assertEquals(3, wb1.getNumberOfSheets()); - // Check all names fit within range, and use - // DeletedArea3DPtg - InternalWorkbook w = wb1.getWorkbook(); - assertNames(wb1, w); + // Check all names fit within range, and use + // DeletedArea3DPtg + InternalWorkbook w = wb1.getWorkbook(); + assertNames(wb1, w); - // Delete the 2nd sheet - wb1.removeSheetAt(1); + // Delete the 2nd sheet + wb1.removeSheetAt(1); - // Re-check - assertEquals(1, wb1.getNumberOfNames()); - assertEquals(2, wb1.getNumberOfSheets()); - assertNames(wb1, w); + // Re-check + assertEquals(1, wb1.getNumberOfNames()); + assertEquals(2, wb1.getNumberOfSheets()); + assertNames(wb1, w); - // Save and re-load - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - w = wb2.getWorkbook(); + // Save and re-load + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + w = wb2.getWorkbook(); - assertEquals(1, wb2.getNumberOfNames()); - assertEquals(2, wb2.getNumberOfSheets()); + assertEquals(1, wb2.getNumberOfNames()); + assertEquals(2, wb2.getNumberOfSheets()); - assertNames(wb2, w); - wb2.close(); + assertNames(wb2, w); + } + } } private void assertNames(HSSFWorkbook wb1, InternalWorkbook w) { @@ -997,85 +919,55 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45338() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - assertEquals(4, wb.getNumberOfFontsAsInt()); - - HSSFSheet s = wb.createSheet(); - s.createRow(0); - s.createRow(1); - s.getRow(0).createCell(0); - s.getRow(1).createCell(0); - - assertEquals(4, wb.getNumberOfFontsAsInt()); - - HSSFFont f1 = wb.getFontAt(0); - assertFalse(f1.getBold()); - - // Check that asking for the same font - // multiple times gives you the same thing. - // Otherwise, our tests wouldn't work! - assertSame(wb.getFontAt(0), wb.getFontAt(0)); - assertEquals( - wb.getFontAt(0), - wb.getFontAt(0) - ); - assertEquals( - wb.getFontAt(2), - wb.getFontAt(2) - ); - assertNotSame(wb.getFontAt(0), wb.getFontAt(2)); - - // Look for a new font we have - // yet to add - assertNull( - wb.findFont( - false, (short) 123, (short) 22, - "Thingy", false, true, (short) 2, (byte) 2 - ) - ); - - HSSFFont nf = wb.createFont(); - assertEquals(5, wb.getNumberOfFontsAsInt()); - - assertEquals(5, nf.getIndex()); - assertEquals(nf, wb.getFontAt(5)); - - nf.setBold(false); - nf.setColor((short) 123); - nf.setFontHeight((short) 22); - nf.setFontName("Thingy"); - nf.setItalic(false); - nf.setStrikeout(true); - nf.setTypeOffset((short) 2); - nf.setUnderline((byte) 2); - - assertEquals(5, wb.getNumberOfFontsAsInt()); - assertEquals(nf, wb.getFontAt(5)); - - // Find it now - assertNotNull( - wb.findFont( - false, (short) 123, (short) 22, - "Thingy", false, true, (short) 2, (byte) 2 - ) - ); - HSSFFont font = wb.findFont( - false, (short) 123, (short) 22, - "Thingy", false, true, (short) 2, (byte) 2 - ); - assertNotNull(font); - assertEquals( - 5, - font.getIndex() - ); - assertEquals(nf, - wb.findFont( - false, (short) 123, (short) 22, - "Thingy", false, true, (short) 2, (byte) 2 - ) - ); - - wb.close(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + assertEquals(4, wb.getNumberOfFontsAsInt()); + + HSSFSheet s = wb.createSheet(); + s.createRow(0); + s.createRow(1); + s.getRow(0).createCell(0); + s.getRow(1).createCell(0); + + assertEquals(4, wb.getNumberOfFontsAsInt()); + + HSSFFont f1 = wb.getFontAt(0); + assertFalse(f1.getBold()); + + // Check that asking for the same font multiple times gives you the same thing. + // Otherwise, our tests wouldn't work! + assertSame(wb.getFontAt(0), wb.getFontAt(0)); + assertEquals(wb.getFontAt(0), wb.getFontAt(0)); + assertEquals(wb.getFontAt(2), wb.getFontAt(2)); + assertNotSame(wb.getFontAt(0), wb.getFontAt(2)); + + // Look for a new font we have yet to add + assertNull(wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); + + HSSFFont nf = wb.createFont(); + assertEquals(5, wb.getNumberOfFontsAsInt()); + + assertEquals(5, nf.getIndex()); + assertEquals(nf, wb.getFontAt(5)); + + nf.setBold(false); + nf.setColor((short) 123); + nf.setFontHeight((short) 22); + nf.setFontName("Thingy"); + nf.setItalic(false); + nf.setStrikeout(true); + nf.setTypeOffset((short) 2); + nf.setUnderline((byte) 2); + + assertEquals(5, wb.getNumberOfFontsAsInt()); + assertEquals(nf, wb.getFontAt(5)); + + // Find it now + assertNotNull(wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); + HSSFFont font = wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2); + assertNotNull(font); + assertEquals(5, font.getIndex()); + assertEquals(nf, wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); + } } /** @@ -1084,87 +976,87 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bugZipCodeFormulas() throws Exception { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - s.createRow(0); - HSSFCell c1 = s.getRow(0).createCell(0); - HSSFCell c2 = s.getRow(0).createCell(1); - HSSFCell c3 = s.getRow(0).createCell(2); - - // As number and string - c1.setCellFormula("70164"); - c2.setCellFormula("\"70164\""); - c3.setCellFormula("\"90210\""); - - // Check the formulas - assertEquals("70164", c1.getCellFormula()); - assertEquals("\"70164\"", c2.getCellFormula()); - - // And check the values - blank - confirmCachedValue(0.0, c1); - confirmCachedValue(0.0, c2); - confirmCachedValue(0.0, c3); - - // Try changing the cached value on one of the string - // formula cells, so we can see it updates properly - c3.setCellValue(new HSSFRichTextString("test")); - confirmCachedValue("test", c3); - try { - c3.getNumericCellValue(); - fail("exception should have been thrown"); - } catch (IllegalStateException e) { - assertEquals("Cannot get a NUMERIC value from a STRING formula cell", e.getMessage()); - } - - - // Now evaluate, they should all be changed - HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1); - eval.evaluateFormulaCell(c1); - eval.evaluateFormulaCell(c2); - eval.evaluateFormulaCell(c3); - - // Check that the cells now contain - // the correct values - confirmCachedValue(70164.0, c1); - confirmCachedValue("70164", c2); - confirmCachedValue("90210", c3); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + s.createRow(0); + HSSFCell c1 = s.getRow(0).createCell(0); + HSSFCell c2 = s.getRow(0).createCell(1); + HSSFCell c3 = s.getRow(0).createCell(2); + + // As number and string + c1.setCellFormula("70164"); + c2.setCellFormula("\"70164\""); + c3.setCellFormula("\"90210\""); + + // Check the formulas + assertEquals("70164", c1.getCellFormula()); + assertEquals("\"70164\"", c2.getCellFormula()); + + // And check the values - blank + confirmCachedValue(0.0, c1); + confirmCachedValue(0.0, c2); + confirmCachedValue(0.0, c3); + + // Try changing the cached value on one of the string + // formula cells, so we can see it updates properly + c3.setCellValue(new HSSFRichTextString("test")); + confirmCachedValue("test", c3); + try { + c3.getNumericCellValue(); + fail("exception should have been thrown"); + } catch (IllegalStateException e) { + assertEquals("Cannot get a NUMERIC value from a STRING formula cell", e.getMessage()); + } - // Write and read - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - HSSFSheet ns = wb2.getSheetAt(0); - HSSFCell nc1 = ns.getRow(0).getCell(0); - HSSFCell nc2 = ns.getRow(0).getCell(1); - HSSFCell nc3 = ns.getRow(0).getCell(2); - - // Re-check - confirmCachedValue(70164.0, nc1); - confirmCachedValue("70164", nc2); - confirmCachedValue("90210", nc3); - - int i = 0; - for (Iterator it = ns.getSheet().getCellValueIterator(); it.hasNext(); i++) { - CellValueRecordInterface cvr = it.next(); - if (cvr instanceof FormulaRecordAggregate) { - FormulaRecordAggregate fr = (FormulaRecordAggregate) cvr; - - if (i == 0) { - assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001); - assertNull(fr.getStringRecord()); - } else if (i == 1) { - assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); - assertNotNull(fr.getStringRecord()); - assertEquals("70164", fr.getStringRecord().getString()); - } else { - assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); - assertNotNull(fr.getStringRecord()); - assertEquals("90210", fr.getStringRecord().getString()); + // Now evaluate, they should all be changed + HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1); + eval.evaluateFormulaCell(c1); + eval.evaluateFormulaCell(c2); + eval.evaluateFormulaCell(c3); + + // Check that the cells now contain + // the correct values + confirmCachedValue(70164.0, c1); + confirmCachedValue("70164", c2); + confirmCachedValue("90210", c3); + + + // Write and read + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + HSSFSheet ns = wb2.getSheetAt(0); + HSSFCell nc1 = ns.getRow(0).getCell(0); + HSSFCell nc2 = ns.getRow(0).getCell(1); + HSSFCell nc3 = ns.getRow(0).getCell(2); + + // Re-check + confirmCachedValue(70164.0, nc1); + confirmCachedValue("70164", nc2); + confirmCachedValue("90210", nc3); + + int i = 0; + for (Iterator it = ns.getSheet().getCellValueIterator(); it.hasNext(); i++) { + CellValueRecordInterface cvr = it.next(); + if (cvr instanceof FormulaRecordAggregate) { + FormulaRecordAggregate fr = (FormulaRecordAggregate) cvr; + + if (i == 0) { + assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001); + assertNull(fr.getStringRecord()); + } else if (i == 1) { + assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); + assertNotNull(fr.getStringRecord()); + assertEquals("70164", fr.getStringRecord().getString()); + } else { + assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); + assertNotNull(fr.getStringRecord()); + assertEquals("90210", fr.getStringRecord().getString()); + } + } } + assertEquals(3, i); } } - assertEquals(3, i); - wb2.close(); } private static void confirmCachedValue(double expectedValue, HSSFCell cell) { @@ -1193,32 +1085,32 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Ignore("For now, blows up with an exception from ExtPtg") @Test public void test43623() throws Exception { - HSSFWorkbook wb1 = openSample("43623.xls"); - assertEquals(1, wb1.getNumberOfSheets()); - - HSSFSheet s1 = wb1.getSheetAt(0); - - HSSFCell c1 = s1.getRow(0).getCell(2); - HSSFCell c2 = s1.getRow(1).getCell(2); - HSSFCell c3 = s1.getRow(2).getCell(2); - - // These formula contents are a guess... - assertEquals("{=sin(B1:B9){9,1)[0][0]", c1.getCellFormula()); - assertEquals("{=sin(B1:B9){9,1)[1][0]", c2.getCellFormula()); - assertEquals("{=sin(B1:B9){9,1)[2][0]", c3.getCellFormula()); - - // Save and re-open, ensure it still works - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - HSSFSheet ns1 = wb2.getSheetAt(0); - HSSFCell nc1 = ns1.getRow(0).getCell(2); - HSSFCell nc2 = ns1.getRow(1).getCell(2); - HSSFCell nc3 = ns1.getRow(2).getCell(2); - - assertEquals("{=sin(B1:B9){9,1)[0][0]", nc1.getCellFormula()); - assertEquals("{=sin(B1:B9){9,1)[1][0]", nc2.getCellFormula()); - assertEquals("{=sin(B1:B9){9,1)[2][0]", nc3.getCellFormula()); - wb2.close(); + try (HSSFWorkbook wb1 = openSampleWorkbook("43623.xls")) { + assertEquals(1, wb1.getNumberOfSheets()); + + HSSFSheet s1 = wb1.getSheetAt(0); + + HSSFCell c1 = s1.getRow(0).getCell(2); + HSSFCell c2 = s1.getRow(1).getCell(2); + HSSFCell c3 = s1.getRow(2).getCell(2); + + // These formula contents are a guess... + assertEquals("{=sin(B1:B9){9,1)[0][0]", c1.getCellFormula()); + assertEquals("{=sin(B1:B9){9,1)[1][0]", c2.getCellFormula()); + assertEquals("{=sin(B1:B9){9,1)[2][0]", c3.getCellFormula()); + + // Save and re-open, ensure it still works + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + HSSFSheet ns1 = wb2.getSheetAt(0); + HSSFCell nc1 = ns1.getRow(0).getCell(2); + HSSFCell nc2 = ns1.getRow(1).getCell(2); + HSSFCell nc3 = ns1.getRow(2).getCell(2); + + assertEquals("{=sin(B1:B9){9,1)[0][0]", nc1.getCellFormula()); + assertEquals("{=sin(B1:B9){9,1)[1][0]", nc2.getCellFormula()); + assertEquals("{=sin(B1:B9){9,1)[2][0]", nc3.getCellFormula()); + } + } } /** @@ -1227,50 +1119,50 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug30635() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet(); - - // No rows, first/last return -1 - assertEquals(-1, s.getFirstRowNum()); - assertEquals(-1, s.getLastRowNum()); - assertEquals(0, s.getPhysicalNumberOfRows()); - - // One row, most things are 0, physical is 1 - s.createRow(0); - assertEquals(0, s.getFirstRowNum()); - assertEquals(0, s.getLastRowNum()); - assertEquals(1, s.getPhysicalNumberOfRows()); - - // And another, things change - s.createRow(4); - assertEquals(0, s.getFirstRowNum()); - assertEquals(4, s.getLastRowNum()); - assertEquals(2, s.getPhysicalNumberOfRows()); - - - // Now start on cells - HSSFRow r = s.getRow(0); - assertEquals(-1, r.getFirstCellNum()); - assertEquals(-1, r.getLastCellNum()); - assertEquals(0, r.getPhysicalNumberOfCells()); - - // Add a cell, things move off -1 - r.createCell(0); - assertEquals(0, r.getFirstCellNum()); - assertEquals(1, r.getLastCellNum()); // last cell # + 1 - assertEquals(1, r.getPhysicalNumberOfCells()); - - r.createCell(1); - assertEquals(0, r.getFirstCellNum()); - assertEquals(2, r.getLastCellNum()); // last cell # + 1 - assertEquals(2, r.getPhysicalNumberOfCells()); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet(); + + // No rows, first/last return -1 + assertEquals(-1, s.getFirstRowNum()); + assertEquals(-1, s.getLastRowNum()); + assertEquals(0, s.getPhysicalNumberOfRows()); + + // One row, most things are 0, physical is 1 + s.createRow(0); + assertEquals(0, s.getFirstRowNum()); + assertEquals(0, s.getLastRowNum()); + assertEquals(1, s.getPhysicalNumberOfRows()); + + // And another, things change + s.createRow(4); + assertEquals(0, s.getFirstRowNum()); + assertEquals(4, s.getLastRowNum()); + assertEquals(2, s.getPhysicalNumberOfRows()); + + + // Now start on cells + HSSFRow r = s.getRow(0); + assertEquals(-1, r.getFirstCellNum()); + assertEquals(-1, r.getLastCellNum()); + assertEquals(0, r.getPhysicalNumberOfCells()); + + // Add a cell, things move off -1 + r.createCell(0); + assertEquals(0, r.getFirstCellNum()); + assertEquals(1, r.getLastCellNum()); // last cell # + 1 + assertEquals(1, r.getPhysicalNumberOfCells()); + + r.createCell(1); + assertEquals(0, r.getFirstCellNum()); + assertEquals(2, r.getLastCellNum()); // last cell # + 1 + assertEquals(2, r.getPhysicalNumberOfCells()); + + r.createCell(4); + assertEquals(0, r.getFirstCellNum()); + assertEquals(5, r.getLastCellNum()); // last cell # + 1 + assertEquals(3, r.getPhysicalNumberOfCells()); - r.createCell(4); - assertEquals(0, r.getFirstCellNum()); - assertEquals(5, r.getLastCellNum()); // last cell # + 1 - assertEquals(3, r.getPhysicalNumberOfCells()); - - wb.close(); + } } /** @@ -1278,32 +1170,32 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug44958() throws Exception { - HSSFWorkbook wb = openSample("44958.xls"); - HSSFSheet s; - HSSFRow r; - HSSFCell c; + try (HSSFWorkbook wb = openSampleWorkbook("44958.xls")) { + HSSFSheet s; + HSSFRow r; + HSSFCell c; - // Check the contents of the formulas + // Check the contents of the formulas - // E4 to G9 of sheet 4 make up the table - s = wb.getSheet("OneVariable Table Completed"); - r = s.getRow(3); - c = r.getCell(4); - assertEquals(CellType.FORMULA, c.getCellType()); + // E4 to G9 of sheet 4 make up the table + s = wb.getSheet("OneVariable Table Completed"); + r = s.getRow(3); + c = r.getCell(4); + assertEquals(CellType.FORMULA, c.getCellType()); - // TODO - check the formula once tables and - // arrays are properly supported + // TODO - check the formula once tables and + // arrays are properly supported - // E4 to H9 of sheet 5 make up the table - s = wb.getSheet("TwoVariable Table Example"); - r = s.getRow(3); - c = r.getCell(4); - assertEquals(CellType.FORMULA, c.getCellType()); + // E4 to H9 of sheet 5 make up the table + s = wb.getSheet("TwoVariable Table Example"); + r = s.getRow(3); + c = r.getCell(4); + assertEquals(CellType.FORMULA, c.getCellType()); - // TODO - check the formula once tables and - // arrays are properly supported - wb.close(); + // TODO - check the formula once tables and + // arrays are properly supported + } } /** @@ -1311,10 +1203,13 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45322() throws Exception { - HSSFWorkbook wb = openSample("44958.xls"); - HSSFSheet sh = wb.getSheetAt(0); - for (short i = 0; i < 30; i++) sh.autoSizeColumn(i); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("44958.xls")) { + HSSFSheet sh = wb.getSheetAt(0); + for (short i = 0; i < 30; i++) { + sh.autoSizeColumn(i); + assertNotEquals(0, sh.getColumnWidth(i)); + } + } } /** @@ -1323,21 +1218,21 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45414() throws Exception { - HSSFWorkbook wb1 = openSample("WithThreeCharts.xls"); - wb1.getSheetAt(0).setForceFormulaRecalculation(true); - wb1.getSheetAt(1).setForceFormulaRecalculation(false); - wb1.getSheetAt(2).setForceFormulaRecalculation(true); - - // Write out and back in again - // This used to break - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - - // Check now set as it should be - assertTrue(wb2.getSheetAt(0).getForceFormulaRecalculation()); - assertFalse(wb2.getSheetAt(1).getForceFormulaRecalculation()); - assertTrue(wb2.getSheetAt(2).getForceFormulaRecalculation()); - wb2.close(); + try (HSSFWorkbook wb1 = openSampleWorkbook("WithThreeCharts.xls")) { + wb1.getSheetAt(0).setForceFormulaRecalculation(true); + wb1.getSheetAt(1).setForceFormulaRecalculation(false); + wb1.getSheetAt(2).setForceFormulaRecalculation(true); + + // Write out and back in again + // This used to break + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + + // Check now set as it should be + assertTrue(wb2.getSheetAt(0).getForceFormulaRecalculation()); + assertFalse(wb2.getSheetAt(1).getForceFormulaRecalculation()); + assertTrue(wb2.getSheetAt(2).getForceFormulaRecalculation()); + } + } } /** @@ -1345,29 +1240,28 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45761() throws Exception { - HSSFWorkbook wb1 = openSample("45761.xls"); - assertEquals(3, wb1.getNumberOfSheets()); - - assertFalse(wb1.isSheetHidden(0)); - assertFalse(wb1.isSheetVeryHidden(0)); - assertTrue(wb1.isSheetHidden(1)); - assertFalse(wb1.isSheetVeryHidden(1)); - assertFalse(wb1.isSheetHidden(2)); - assertTrue(wb1.isSheetVeryHidden(2)); - - // Change sheet 0 to be very hidden, and re-load - wb1.setSheetVisibility(0, SheetVisibility.VERY_HIDDEN); - - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - - assertFalse(wb2.isSheetHidden(0)); - assertTrue(wb2.isSheetVeryHidden(0)); - assertTrue(wb2.isSheetHidden(1)); - assertFalse(wb2.isSheetVeryHidden(1)); - assertFalse(wb2.isSheetHidden(2)); - assertTrue(wb2.isSheetVeryHidden(2)); - wb2.close(); + try (HSSFWorkbook wb1 = openSampleWorkbook("45761.xls")) { + assertEquals(3, wb1.getNumberOfSheets()); + + assertFalse(wb1.isSheetHidden(0)); + assertFalse(wb1.isSheetVeryHidden(0)); + assertTrue(wb1.isSheetHidden(1)); + assertFalse(wb1.isSheetVeryHidden(1)); + assertFalse(wb1.isSheetHidden(2)); + assertTrue(wb1.isSheetVeryHidden(2)); + + // Change sheet 0 to be very hidden, and re-load + wb1.setSheetVisibility(0, SheetVisibility.VERY_HIDDEN); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + assertFalse(wb2.isSheetHidden(0)); + assertTrue(wb2.isSheetVeryHidden(0)); + assertTrue(wb2.isSheetHidden(1)); + assertFalse(wb2.isSheetVeryHidden(1)); + assertFalse(wb2.isSheetHidden(2)); + assertTrue(wb2.isSheetVeryHidden(2)); + } + } } /** @@ -1377,49 +1271,49 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45777() throws Exception { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet(); - char[] cc248 = new char[248]; - Arrays.fill(cc248, 'x'); - String s248 = new String(cc248); + char[] cc248 = new char[248]; + Arrays.fill(cc248, 'x'); + String s248 = new String(cc248); - String s249 = s248 + "1"; - String s250 = s248 + "12"; - String s251 = s248 + "123"; - assertEquals(248, s248.length()); - assertEquals(249, s249.length()); - assertEquals(250, s250.length()); - assertEquals(251, s251.length()); + String s249 = s248 + "1"; + String s250 = s248 + "12"; + String s251 = s248 + "123"; + assertEquals(248, s248.length()); + assertEquals(249, s249.length()); + assertEquals(250, s250.length()); + assertEquals(251, s251.length()); - // Try on headers - s.getHeader().setCenter(s248); - assertEquals(254, s.getHeader().getRawText().length()); - writeOutAndReadBack(wb).close(); + // Try on headers + s.getHeader().setCenter(s248); + assertEquals(254, s.getHeader().getRawText().length()); + writeOutAndReadBack(wb).close(); - s.getHeader().setCenter(s251); - assertEquals(257, s.getHeader().getRawText().length()); - writeOutAndReadBack(wb).close(); + s.getHeader().setCenter(s251); + assertEquals(257, s.getHeader().getRawText().length()); + writeOutAndReadBack(wb).close(); - // header can be more than 256 bytes - s.getHeader().setCenter(s250); // 256 bytes required - s.getHeader().setCenter(s251); // 257 bytes required + // header can be more than 256 bytes + s.getHeader().setCenter(s250); // 256 bytes required + s.getHeader().setCenter(s251); // 257 bytes required - // Now try on footers - s.getFooter().setCenter(s248); - assertEquals(254, s.getFooter().getRawText().length()); - writeOutAndReadBack(wb).close(); + // Now try on footers + s.getFooter().setCenter(s248); + assertEquals(254, s.getFooter().getRawText().length()); + writeOutAndReadBack(wb).close(); - s.getFooter().setCenter(s251); - assertEquals(257, s.getFooter().getRawText().length()); - writeOutAndReadBack(wb).close(); + s.getFooter().setCenter(s251); + assertEquals(257, s.getFooter().getRawText().length()); + writeOutAndReadBack(wb).close(); - // footer can be more than 256 bytes - s.getFooter().setCenter(s250); // 256 bytes required - s.getFooter().setCenter(s251); // 257 bytes required + // footer can be more than 256 bytes + s.getFooter().setCenter(s250); // 256 bytes required + s.getFooter().setCenter(s251); // 257 bytes required - wb.close(); + } } /** @@ -1427,11 +1321,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45784() throws Exception { - // This used to break - HSSFWorkbook wb = openSample("45784.xls"); - assertEquals(1, wb.getNumberOfSheets()); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("45784.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -1439,37 +1329,37 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45492() throws Exception { - HSSFWorkbook wb = openSample("45492.xls"); - HSSFSheet s = wb.getSheetAt(0); - HSSFRow r = s.getRow(0); - HSSFPalette p = wb.getCustomPalette(); - - HSSFCell auto = r.getCell(0); - HSSFCell grey = r.getCell(1); - HSSFCell red = r.getCell(2); - HSSFCell blue = r.getCell(3); - HSSFCell green = r.getCell(4); - - assertEquals(64, auto.getCellStyle().getFillForegroundColor()); - assertEquals(64, auto.getCellStyle().getFillBackgroundColor()); - assertEquals("0:0:0", p.getColor(64).getHexString()); - - assertEquals(22, grey.getCellStyle().getFillForegroundColor()); - assertEquals(64, grey.getCellStyle().getFillBackgroundColor()); - assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString()); - - assertEquals(10, red.getCellStyle().getFillForegroundColor()); - assertEquals(64, red.getCellStyle().getFillBackgroundColor()); - assertEquals("FFFF:0:0", p.getColor(10).getHexString()); - - assertEquals(12, blue.getCellStyle().getFillForegroundColor()); - assertEquals(64, blue.getCellStyle().getFillBackgroundColor()); - assertEquals("0:0:FFFF", p.getColor(12).getHexString()); - - assertEquals(11, green.getCellStyle().getFillForegroundColor()); - assertEquals(64, green.getCellStyle().getFillBackgroundColor()); - assertEquals("0:FFFF:0", p.getColor(11).getHexString()); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("45492.xls")) { + HSSFSheet s = wb.getSheetAt(0); + HSSFRow r = s.getRow(0); + HSSFPalette p = wb.getCustomPalette(); + + HSSFCell auto = r.getCell(0); + HSSFCell grey = r.getCell(1); + HSSFCell red = r.getCell(2); + HSSFCell blue = r.getCell(3); + HSSFCell green = r.getCell(4); + + assertEquals(64, auto.getCellStyle().getFillForegroundColor()); + assertEquals(64, auto.getCellStyle().getFillBackgroundColor()); + assertEquals("0:0:0", p.getColor(64).getHexString()); + + assertEquals(22, grey.getCellStyle().getFillForegroundColor()); + assertEquals(64, grey.getCellStyle().getFillBackgroundColor()); + assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString()); + + assertEquals(10, red.getCellStyle().getFillForegroundColor()); + assertEquals(64, red.getCellStyle().getFillBackgroundColor()); + assertEquals("FFFF:0:0", p.getColor(10).getHexString()); + + assertEquals(12, blue.getCellStyle().getFillForegroundColor()); + assertEquals(64, blue.getCellStyle().getFillBackgroundColor()); + assertEquals("0:0:FFFF", p.getColor(12).getHexString()); + + assertEquals(11, green.getCellStyle().getFillForegroundColor()); + assertEquals(64, green.getCellStyle().getFillBackgroundColor()); + assertEquals("0:FFFF:0", p.getColor(11).getHexString()); + } } /** @@ -1477,11 +1367,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug46137() throws Exception { - // This used to break - HSSFWorkbook wb = openSample("46137.xls"); - assertEquals(7, wb.getNumberOfSheets()); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("46137.xls", wb -> assertEquals(7, wb.getNumberOfSheets())); } /** @@ -1490,9 +1376,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45290() throws Exception { - HSSFWorkbook wb = openSample("45290.xls"); - assertEquals(1, wb.getNumberOfSheets()); - wb.close(); + simpleTest("45290.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -1501,20 +1385,21 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug30070() throws Exception { - HSSFWorkbook wb = openSample("30070.xls"); //contains custom VBA function 'Commission' - HSSFSheet sh = wb.getSheetAt(0); - HSSFCell cell = sh.getRow(0).getCell(1); + //contains custom VBA function 'Commission' + try (HSSFWorkbook wb = openSampleWorkbook("30070.xls")) { + HSSFSheet sh = wb.getSheetAt(0); + HSSFCell cell = sh.getRow(0).getCell(1); - //B1 uses VBA in the formula - assertEquals("Commission(A1)", cell.getCellFormula()); + //B1 uses VBA in the formula + assertEquals("Commission(A1)", cell.getCellFormula()); - //name sales_1 refers to Commission(Sheet0!$A$1) - int idx = wb.getNameIndex("sales_1"); - assertTrue(idx != -1); + //name sales_1 refers to Commission(Sheet0!$A$1) + int idx = wb.getNameIndex("sales_1"); + assertTrue(idx != -1); - HSSFName name = wb.getNameAt(idx); - assertEquals("Commission(Sheet0!$A$1)", name.getRefersToFormula()); - wb.close(); + HSSFName name = wb.getNameAt(idx); + assertEquals("Commission(Sheet0!$A$1)", name.getRefersToFormula()); + } } /** @@ -1533,13 +1418,13 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug27364() throws Exception { - HSSFWorkbook wb = openSample("27364.xls"); - HSSFSheet sheet = wb.getSheetAt(0); + try (HSSFWorkbook wb = openSampleWorkbook("27364.xls")) { + HSSFSheet sheet = wb.getSheetAt(0); - assertEquals("[link_sub.xls]Sheet1!$A$1", sheet.getRow(0).getCell(0).getCellFormula()); - assertEquals("[link_sub.xls]Sheet1!$A$2", sheet.getRow(1).getCell(0).getCellFormula()); - assertEquals("[link_sub.xls]Sheet1!$A$3", sheet.getRow(2).getCell(0).getCellFormula()); - wb.close(); + assertEquals("[link_sub.xls]Sheet1!$A$1", sheet.getRow(0).getCell(0).getCellFormula()); + assertEquals("[link_sub.xls]Sheet1!$A$2", sheet.getRow(1).getCell(0).getCellFormula()); + assertEquals("[link_sub.xls]Sheet1!$A$3", sheet.getRow(2).getCell(0).getCellFormula()); + } } /** @@ -1548,11 +1433,11 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug31661() throws Exception { - HSSFWorkbook wb = openSample("31661.xls"); - HSSFSheet sheet = wb.getSheetAt(0); - HSSFCell cell = sheet.getRow(11).getCell(10); //K11 - assertEquals("+'[GM Budget.xls]8085.4450'!$B$2", cell.getCellFormula()); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("31661.xls")) { + HSSFSheet sheet = wb.getSheetAt(0); + HSSFCell cell = sheet.getRow(11).getCell(10); //K11 + assertEquals("+'[GM Budget.xls]8085.4450'!$B$2", cell.getCellFormula()); + } } /** @@ -1560,14 +1445,14 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug27394() throws Exception { - HSSFWorkbook wb = openSample("27394.xls"); - assertEquals("\u0161\u017E", wb.getSheetName(0)); - assertEquals("\u0161\u017E\u010D\u0148\u0159", wb.getSheetName(1)); - HSSFSheet sheet = wb.getSheetAt(0); + try (HSSFWorkbook wb = openSampleWorkbook("27394.xls")) { + assertEquals("\u0161\u017E", wb.getSheetName(0)); + assertEquals("\u0161\u017E\u010D\u0148\u0159", wb.getSheetName(1)); + HSSFSheet sheet = wb.getSheetAt(0); - assertEquals("\u0161\u017E", sheet.getRow(0).getCell(0).getStringCellValue()); - assertEquals("\u0161\u017E\u010D\u0148\u0159", sheet.getRow(1).getCell(0).getStringCellValue()); - wb.close(); + assertEquals("\u0161\u017E", sheet.getRow(0).getCell(0).getStringCellValue()); + assertEquals("\u0161\u017E\u010D\u0148\u0159", sheet.getRow(1).getCell(0).getStringCellValue()); + } } /** @@ -1575,25 +1460,16 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug32191() throws IOException { - HSSFWorkbook wb = openSample("27394.xls"); - - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - int size1 = out.size(); - - out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - int size2 = out.size(); - - assertEquals(size1, size2); - out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - int size3 = out.size(); - assertEquals(size2, size3); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("27394.xls"); + ByteArrayOutputStream out1 = new ByteArrayOutputStream(); + ByteArrayOutputStream out2 = new ByteArrayOutputStream(); + ByteArrayOutputStream out3 = new ByteArrayOutputStream()) { + wb.write(out1); + wb.write(out2); + wb.write(out3); + assertArrayEquals(out1.toByteArray(), out2.toByteArray()); + assertArrayEquals(out2.toByteArray(), out3.toByteArray()); + } } /** @@ -1601,28 +1477,10 @@ public final class TestBugs extends BaseTestBugzillaIssues { * (is an excel 95 file though) */ @Test - public void bug46904() throws Exception { - try { - POIFSFileSystem fs = new POIFSFileSystem( - HSSFITestDataProvider.instance.openWorkbookStream("46904.xls")); - new HSSFWorkbook(fs.getRoot(), false).close(); - fail("Should catch exception here"); - } catch (OldExcelFormatException e) { - assertTrue(e.getMessage().startsWith( - "The supplied spreadsheet seems to be Excel" - )); - } - try { - try (POIFSFileSystem fs = new POIFSFileSystem( - HSSFITestDataProvider.instance.openWorkbookStream("46904.xls"))) { - new HSSFWorkbook(fs.getRoot(), false).close(); - fail("Should catch exception here"); - } - } catch (OldExcelFormatException e) { - assertTrue(e.getMessage().startsWith( - "The supplied spreadsheet seems to be Excel" - )); - } + public void bug46904a() throws Exception { + thrown.expect(OldExcelFormatException.class); + thrown.expectMessage("The supplied spreadsheet seems to be Excel"); + simpleTest("46904.xls"); } /** @@ -1631,10 +1489,10 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug47034() throws Exception { - HSSFWorkbook wb = openSample("47034.xls"); - assertEquals(893, wb.getNumberOfNames()); - assertEquals("Matthew\\Matthew11_1\\Matthew2331_1\\Matthew2351_1\\Matthew2361_1___lab", wb.getNameName(300)); - wb.close(); + simpleTest("47034.xls", wb -> { + assertEquals(893, wb.getNumberOfNames()); + assertEquals("Matthew\\Matthew11_1\\Matthew2331_1\\Matthew2351_1\\Matthew2361_1___lab", wb.getNameName(300)); + }); } /** @@ -1643,14 +1501,14 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug46368() throws Exception { - HSSFWorkbook wb = openSample("46368.xls"); - HSSFSheet s = wb.getSheetAt(0); - HSSFCell cell1 = s.getRow(0).getCell(0); - assertEquals(32770, cell1.getStringCellValue().length()); + try (HSSFWorkbook wb = openSampleWorkbook("46368.xls")) { + HSSFSheet s = wb.getSheetAt(0); + HSSFCell cell1 = s.getRow(0).getCell(0); + assertEquals(32770, cell1.getStringCellValue().length()); - HSSFCell cell2 = s.getRow(2).getCell(0); - assertEquals(32766, cell2.getStringCellValue().length()); - wb.close(); + HSSFCell cell2 = s.getRow(2).getCell(0); + assertEquals(32766, cell2.getStringCellValue().length()); + } } /** @@ -1658,37 +1516,37 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug48180() throws Exception { - HSSFWorkbook wb = openSample("48180.xls"); + try (HSSFWorkbook wb = openSampleWorkbook("48180.xls")) { - HSSFSheet s = wb.getSheetAt(0); - HSSFCell cell1 = s.getRow(0).getCell(0); - assertEquals("test ", cell1.getStringCellValue()); + HSSFSheet s = wb.getSheetAt(0); + HSSFCell cell1 = s.getRow(0).getCell(0); + assertEquals("test ", cell1.getStringCellValue()); - HSSFCell cell2 = s.getRow(0).getCell(1); - assertEquals(1.0, cell2.getNumericCellValue(), 0.0); - wb.close(); + HSSFCell cell2 = s.getRow(0).getCell(1); + assertEquals(1.0, cell2.getNumericCellValue(), 0.0); + } } /** * POI 3.5 beta 7 can not read excel file contain list box (Form Control) */ @Test - public void bug47701() { - openSample("47701.xls"); + public void bug47701() throws IOException { + simpleTest("47701.xls"); } @Test - public void bug48026() { - openSample("48026.xls"); + public void bug48026() throws IOException { + simpleTest("48026.xls"); } @Test - public void bug47251() { + public void bug47251() throws IOException { // Firstly, try with one that triggers on InterfaceHdrRecord - openSample("47251.xls"); + simpleTest("47251.xls"); // Now with one that triggers on NoteRecord - openSample("47251_1.xls"); + simpleTest("47251_1.xls"); } /** @@ -1696,37 +1554,37 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug47847() throws Exception { - HSSFWorkbook wb1 = openSample("47847.xls"); - assertEquals(3, wb1.getNumberOfSheets()); + try (HSSFWorkbook wb1 = openSampleWorkbook("47847.xls")) { + assertEquals(3, wb1.getNumberOfSheets()); - // Find the SST record - UnicodeString withExt = wb1.getWorkbook().getSSTString(0); - UnicodeString withoutExt = wb1.getWorkbook().getSSTString(31); + // Find the SST record + UnicodeString withExt = wb1.getWorkbook().getSSTString(0); + UnicodeString withoutExt = wb1.getWorkbook().getSSTString(31); - assertEquals("O:Alloc:Qty", withExt.getString()); - assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); + assertEquals("O:Alloc:Qty", withExt.getString()); + assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); - assertEquals("RT", withoutExt.getString()); - assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); + assertEquals("RT", withoutExt.getString()); + assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); - // Something about continues... + // Something about continues... - // Write out and re-read - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - assertEquals(3, wb2.getNumberOfSheets()); + // Write out and re-read + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + assertEquals(3, wb2.getNumberOfSheets()); - // Check it's the same now - withExt = wb2.getWorkbook().getSSTString(0); - withoutExt = wb2.getWorkbook().getSSTString(31); + // Check it's the same now + withExt = wb2.getWorkbook().getSSTString(0); + withoutExt = wb2.getWorkbook().getSSTString(31); - assertEquals("O:Alloc:Qty", withExt.getString()); - assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); + assertEquals("O:Alloc:Qty", withExt.getString()); + assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); - assertEquals("RT", withoutExt.getString()); - assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); - wb2.close(); + assertEquals("RT", withoutExt.getString()); + assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); + } + } } /** @@ -1735,17 +1593,17 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug49096() throws Exception { - HSSFWorkbook wb1 = openSample("49096.xls"); - assertEquals(1, wb1.getNumberOfSheets()); + try (HSSFWorkbook wb1 = openSampleWorkbook("49096.xls")) { + assertEquals(1, wb1.getNumberOfSheets()); - assertNotNull(wb1.getSheetAt(0)); - wb1.cloneSheet(0); - assertEquals(2, wb1.getNumberOfSheets()); + assertNotNull(wb1.getSheetAt(0)); + wb1.cloneSheet(0); + assertEquals(2, wb1.getNumberOfSheets()); - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - assertEquals(2, wb2.getNumberOfSheets()); - wb2.close(); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + assertEquals(2, wb2.getNumberOfSheets()); + } + } } /** @@ -1757,57 +1615,56 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug46664() throws Exception { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet sheet = wb1.createSheet("new_sheet"); - HSSFRow row = sheet.createRow((short) 0); - row.createCell(0).setCellValue(new HSSFRichTextString("Column A")); - row.createCell(1).setCellValue(new HSSFRichTextString("Column B")); - row.createCell(2).setCellValue(new HSSFRichTextString("Column C")); - row.createCell(3).setCellValue(new HSSFRichTextString("Column D")); - row.createCell(4).setCellValue(new HSSFRichTextString("Column E")); - row.createCell(5).setCellValue(new HSSFRichTextString("Column F")); - - //set print area from column a to column c (on first row) - wb1.setPrintArea( - 0, //sheet index - 0, //start column - 2, //end column - 0, //start row - 0 //end row - ); - - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - - // Ensure the tab index - TabIdRecord tr = null; - for (Record r : wb2.getWorkbook().getRecords()) { - if (r instanceof TabIdRecord) { - tr = (TabIdRecord) r; + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet sheet = wb1.createSheet("new_sheet"); + HSSFRow row = sheet.createRow((short) 0); + row.createCell(0).setCellValue(new HSSFRichTextString("Column A")); + row.createCell(1).setCellValue(new HSSFRichTextString("Column B")); + row.createCell(2).setCellValue(new HSSFRichTextString("Column C")); + row.createCell(3).setCellValue(new HSSFRichTextString("Column D")); + row.createCell(4).setCellValue(new HSSFRichTextString("Column E")); + row.createCell(5).setCellValue(new HSSFRichTextString("Column F")); + + //set print area from column a to column c (on first row) + wb1.setPrintArea( + 0, //sheet index + 0, //start column + 2, //end column + 0, //start row + 0 //end row + ); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + // Ensure the tab index + TabIdRecord tr = null; + for (Record r : wb2.getWorkbook().getRecords()) { + if (r instanceof TabIdRecord) { + tr = (TabIdRecord) r; + } + } + assertNotNull(tr); + assertEquals(1, tr._tabids.length); + assertEquals(0, tr._tabids[0]); + + // Ensure the print setup + assertEquals("new_sheet!$A$1:$C$1", wb2.getPrintArea(0)); + HSSFName printArea = wb2.getName("Print_Area"); + assertNotNull(printArea); + assertEquals("new_sheet!$A$1:$C$1", printArea.getRefersToFormula()); + + // Needs reference not value + NameRecord nr = wb2.getWorkbook().getNameRecord( + wb2.getNameIndex("Print_Area") + ); + assertEquals("Print_Area", nr.getNameText()); + assertEquals(1, nr.getNameDefinition().length); + assertEquals( + "new_sheet!$A$1:$C$1", + ((Area3DPtg) nr.getNameDefinition()[0]).toFormulaString(HSSFEvaluationWorkbook.create(wb2)) + ); + assertEquals('R', nr.getNameDefinition()[0].getRVAType()); } } - assertNotNull(tr); - assertEquals(1, tr._tabids.length); - assertEquals(0, tr._tabids[0]); - - // Ensure the print setup - assertEquals("new_sheet!$A$1:$C$1", wb2.getPrintArea(0)); - HSSFName printArea = wb2.getName("Print_Area"); - assertNotNull(printArea); - assertEquals("new_sheet!$A$1:$C$1", printArea.getRefersToFormula()); - - // Needs reference not value - NameRecord nr = wb2.getWorkbook().getNameRecord( - wb2.getNameIndex("Print_Area") - ); - assertEquals("Print_Area", nr.getNameText()); - assertEquals(1, nr.getNameDefinition().length); - assertEquals( - "new_sheet!$A$1:$C$1", - ((Area3DPtg) nr.getNameDefinition()[0]).toFormulaString(HSSFEvaluationWorkbook.create(wb2)) - ); - assertEquals('R', nr.getNameDefinition()[0].getRVAType()); - wb2.close(); } /** @@ -1816,91 +1673,91 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug45970() throws Exception { - HSSFWorkbook wb1 = openSample("FormulaRefs.xls"); - assertEquals(3, wb1.getNumberOfSheets()); - - HSSFSheet s = wb1.getSheetAt(0); - HSSFRow row; - - row = s.getRow(0); - assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(1); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("B1", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(2); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(3); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(4); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("'[$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - // Link our new workbook - Workbook externalWb1 = new HSSFWorkbook(); - externalWb1.createSheet("Sheet1"); - assertEquals(4, wb1.linkExternalWorkbook("$http://gagravarr.org/FormulaRefs2.xls", externalWb1)); - - // Change 4 - row.getCell(1).setCellFormula("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2"); - row.getCell(1).setCellValue(123.0); - - // Link our new workbook - Workbook externalWb2 = new HSSFWorkbook(); - externalWb2.createSheet("Sheet1"); - assertEquals(5, wb1.linkExternalWorkbook("$http://example.com/FormulaRefs.xls", externalWb2)); - - // Add 5 - row = s.createRow(5); - row.createCell(1, CellType.FORMULA); - row.getCell(1).setCellFormula("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1"); - row.getCell(1).setCellValue(234.0); - - // Re-test - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - - row = s.getRow(0); - assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(1); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("B1", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(2); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(3); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); - assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(4); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula()); - assertEquals(123.0, row.getCell(1).getNumericCellValue(), 0); - - row = s.getRow(5); - assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); - assertEquals("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); - assertEquals(234.0, row.getCell(1).getNumericCellValue(), 0); + try (HSSFWorkbook wb1 = openSampleWorkbook("FormulaRefs.xls")) { + assertEquals(3, wb1.getNumberOfSheets()); + + HSSFSheet s = wb1.getSheetAt(0); + HSSFRow row; + + row = s.getRow(0); + assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(1); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(2); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(3); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(4); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("'[$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + // Link our new workbook + Workbook externalWb1 = new HSSFWorkbook(); + externalWb1.createSheet("Sheet1"); + assertEquals(4, wb1.linkExternalWorkbook("$http://gagravarr.org/FormulaRefs2.xls", externalWb1)); + + // Change 4 + row.getCell(1).setCellFormula("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2"); + row.getCell(1).setCellValue(123.0); + + // Link our new workbook + Workbook externalWb2 = new HSSFWorkbook(); + externalWb2.createSheet("Sheet1"); + assertEquals(5, wb1.linkExternalWorkbook("$http://example.com/FormulaRefs.xls", externalWb2)); + + // Add 5 + row = s.createRow(5); + row.createCell(1, CellType.FORMULA); + row.getCell(1).setCellFormula("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1"); + row.getCell(1).setCellValue(234.0); + + // Re-test + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + + row = s.getRow(0); + assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(1); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(2); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(3); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(4); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula()); + assertEquals(123.0, row.getCell(1).getNumericCellValue(), 0); + + row = s.getRow(5); + assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); + assertEquals("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); + assertEquals(234.0, row.getCell(1).getNumericCellValue(), 0); - wb2.close(); + } + } } /** @@ -1908,73 +1765,70 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug49185() throws Exception { - HSSFWorkbook wb1 = openSample("49185.xls"); - Name name = wb1.getName("foobarName"); - assertNotNull(name); - assertEquals("This is a comment", name.getComment()); - - // Rename the name, comment comes with it - name.setNameName("ChangedName"); - assertEquals("This is a comment", name.getComment()); - - // Save and re-check - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - name = wb2.getName("ChangedName"); - assertNotNull(name); - assertEquals("This is a comment", name.getComment()); - - // Now try to change it - name.setComment("Changed Comment"); - assertEquals("Changed Comment", name.getComment()); - - // Save and re-check - HSSFWorkbook wb3 = writeOutAndReadBack(wb2); - wb2.close(); - name = wb3.getName("ChangedName"); - assertNotNull(name); - assertEquals("Changed Comment", name.getComment()); - wb3.close(); + try (HSSFWorkbook wb1 = openSampleWorkbook("49185.xls")) { + Name name = wb1.getName("foobarName"); + assertNotNull(name); + assertEquals("This is a comment", name.getComment()); + + // Rename the name, comment comes with it + name.setNameName("ChangedName"); + assertEquals("This is a comment", name.getComment()); + + // Save and re-check + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + name = wb2.getName("ChangedName"); + assertNotNull(name); + assertEquals("This is a comment", name.getComment()); + + // Now try to change it + name.setComment("Changed Comment"); + assertEquals("Changed Comment", name.getComment()); + + // Save and re-check + try (HSSFWorkbook wb3 = writeOutAndReadBack(wb2)) { + name = wb3.getName("ChangedName"); + assertNotNull(name); + assertEquals("Changed Comment", name.getComment()); + } + } + } } - /** - * Vertically aligned text - */ + /** Vertically aligned text */ @Test public void bug49524() throws Exception { - HSSFWorkbook wb1 = openSample("49524.xls"); - Sheet s = wb1.getSheetAt(0); - Row r = s.getRow(0); - Cell rotated = r.getCell(0); - Cell normal = r.getCell(1); - - // Check the current ones - assertEquals(0, normal.getCellStyle().getRotation()); - assertEquals(0xff, rotated.getCellStyle().getRotation()); - - // Add a new style, also rotated - CellStyle cs = wb1.createCellStyle(); - cs.setRotation((short) 0xff); - Cell nc = r.createCell(2); - nc.setCellValue("New Rotated Text"); - nc.setCellStyle(cs); - assertEquals(0xff, nc.getCellStyle().getRotation()); - - // Write out and read back - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - - // Re-check - s = wb2.getSheetAt(0); - r = s.getRow(0); - rotated = r.getCell(0); - normal = r.getCell(1); - nc = r.getCell(2); - - assertEquals(0, normal.getCellStyle().getRotation()); - assertEquals(0xff, rotated.getCellStyle().getRotation()); - assertEquals(0xff, nc.getCellStyle().getRotation()); - wb2.close(); + try (HSSFWorkbook wb1 = openSampleWorkbook("49524.xls")) { + Sheet s = wb1.getSheetAt(0); + Row r = s.getRow(0); + Cell rotated = r.getCell(0); + Cell normal = r.getCell(1); + + // Check the current ones + assertEquals(0, normal.getCellStyle().getRotation()); + assertEquals(0xff, rotated.getCellStyle().getRotation()); + + // Add a new style, also rotated + CellStyle cs = wb1.createCellStyle(); + cs.setRotation((short) 0xff); + Cell nc = r.createCell(2); + nc.setCellValue("New Rotated Text"); + nc.setCellStyle(cs); + assertEquals(0xff, nc.getCellStyle().getRotation()); + + // Write out and read back + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + // Re-check + s = wb2.getSheetAt(0); + r = s.getRow(0); + rotated = r.getCell(0); + normal = r.getCell(1); + nc = r.getCell(2); + + assertEquals(0, normal.getCellStyle().getRotation()); + assertEquals(0xff, rotated.getCellStyle().getRotation()); + assertEquals(0xff, nc.getCellStyle().getRotation()); + } + } } /** @@ -1982,62 +1836,57 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug49689() throws Exception { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet("Test"); - HSSFRow r = s.createRow(0); - HSSFCell c = r.createCell(0); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("Test"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); - HSSFCellStyle cs1 = wb1.createCellStyle(); - HSSFCellStyle cs2 = wb1.createCellStyle(); - HSSFCellStyle cs3 = wb1.createCellStyle(); + HSSFCellStyle cs1 = wb1.createCellStyle(); + HSSFCellStyle cs2 = wb1.createCellStyle(); + HSSFCellStyle cs3 = wb1.createCellStyle(); - assertEquals(21, cs1.getIndex()); - cs1.setUserStyleName("Testing"); + assertEquals(21, cs1.getIndex()); + cs1.setUserStyleName("Testing"); - assertEquals(22, cs2.getIndex()); - cs2.setUserStyleName("Testing 2"); + assertEquals(22, cs2.getIndex()); + cs2.setUserStyleName("Testing 2"); - assertEquals(23, cs3.getIndex()); - cs3.setUserStyleName("Testing 3"); + assertEquals(23, cs3.getIndex()); + cs3.setUserStyleName("Testing 3"); - // Set one - c.setCellStyle(cs1); + // Set one + c.setCellStyle(cs1); - // Write out and read back - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - - // Re-check - assertEquals("Testing", wb2.getCellStyleAt((short) 21).getUserStyleName()); - assertEquals("Testing 2", wb2.getCellStyleAt((short) 22).getUserStyleName()); - assertEquals("Testing 3", wb2.getCellStyleAt((short) 23).getUserStyleName()); - - wb2.close(); + // Write out and read back + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + // Re-check + assertEquals("Testing", wb2.getCellStyleAt((short) 21).getUserStyleName()); + assertEquals("Testing 2", wb2.getCellStyleAt((short) 22).getUserStyleName()); + assertEquals("Testing 3", wb2.getCellStyleAt((short) 23).getUserStyleName()); + } + } } @Test public void bug49751() throws Exception { - HSSFWorkbook wb = openSample("49751.xls"); - int numCellStyles = wb.getNumCellStyles(); - List namedStyles = Arrays.asList( - "20% - Accent1", "20% - Accent2", "20% - Accent3", "20% - Accent4", "20% - Accent5", - "20% - Accent6", "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", - "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", "60% - Accent3", - "60% - Accent4", "60% - Accent5", "60% - Accent6", "Accent1", "Accent2", "Accent3", - "Accent4", "Accent5", "Accent6", "Bad", "Calculation", "Check Cell", "Explanatory Text", - "Good", "Heading 1", "Heading 2", "Heading 3", "Heading 4", "Input", "Linked Cell", - "Neutral", "Note", "Output", "Title", "Total", "Warning Text"); - - List collecteddStyles = new ArrayList<>(); - for (int i = 0; i < numCellStyles; i++) { - HSSFCellStyle cellStyle = wb.getCellStyleAt(i); - String styleName = cellStyle.getUserStyleName(); - if (styleName != null) { - collecteddStyles.add(styleName); - } + Set exp = new HashSet<>(Arrays.asList( + "20% - Accent1", "20% - Accent2", "20% - Accent3", "20% - Accent4", "20% - Accent5", + "20% - Accent6", "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", + "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", "60% - Accent3", + "60% - Accent4", "60% - Accent5", "60% - Accent6", "Accent1", "Accent2", "Accent3", + "Accent4", "Accent5", "Accent6", "Bad", "Calculation", "Check Cell", "Explanatory Text", + "Good", "Heading 1", "Heading 2", "Heading 3", "Heading 4", "Input", "Linked Cell", + "Neutral", "Note", "Output", "Title", "Total", "Warning Text")); + + try (HSSFWorkbook wb = openSampleWorkbook("49751.xls")) { + Set act = IntStream + .range(0, wb.getNumCellStyles()) + .mapToObj(wb::getCellStyleAt) + .map(HSSFCellStyle::getUserStyleName) + .filter(Objects::nonNull) + .collect(Collectors.toSet()); + assertEquals(exp, act); } - assertTrue(namedStyles.containsAll(collecteddStyles)); - wb.close(); } /** @@ -2045,10 +1894,10 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug49931() throws Exception { - HSSFWorkbook wb = openSample("49931.xls"); - assertEquals(1, wb.getNumberOfSheets()); - assertEquals("Foo", wb.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().toString()); - wb.close(); + simpleTest("49931.xls", wb -> { + assertEquals(1, wb.getNumberOfSheets()); + assertEquals("Foo", wb.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().toString()); + }); } /** @@ -2056,19 +1905,19 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug48325() throws Exception { - HSSFWorkbook wb = openSample("48325.xls"); - HSSFSheet sh = wb.getSheetAt(0); - HSSFFooter f = sh.getFooter(); - - // Will show as the center, as that is what excel does - // with an invalid footer lacking left/right/center details - assertEquals("Left text should be empty", "", f.getLeft()); - assertEquals("Right text should be empty", "", f.getRight()); - assertEquals( - "Center text should contain the illegal value", - "BlahBlah blah blah ", f.getCenter() - ); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("48325.xls")) { + HSSFSheet sh = wb.getSheetAt(0); + HSSFFooter f = sh.getFooter(); + + // Will show as the center, as that is what excel does + // with an invalid footer lacking left/right/center details + assertEquals("Left text should be empty", "", f.getLeft()); + assertEquals("Right text should be empty", "", f.getRight()); + assertEquals( + "Center text should contain the illegal value", + "BlahBlah blah blah ", f.getCenter() + ); + } } /** @@ -2076,16 +1925,12 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug50020() throws Exception { - HSSFWorkbook wb = openSample("50020.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("50020.xls"); } @Test public void bug50426() throws Exception { - HSSFWorkbook wb = openSample("50426.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("50426.xls"); } /** @@ -2094,55 +1939,55 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void bug50416LastRowNumber() throws IOException { // Create the workbook with 1 sheet which contains 3 rows - HSSFWorkbook workbook = new HSSFWorkbook(); - Sheet sheet = workbook.createSheet("Bug50416"); - Row row1 = sheet.createRow(0); - Cell cellA_1 = row1.createCell(0, CellType.STRING); - cellA_1.setCellValue("Cell A,1"); - Row row2 = sheet.createRow(1); - Cell cellA_2 = row2.createCell(0, CellType.STRING); - cellA_2.setCellValue("Cell A,2"); - Row row3 = sheet.createRow(2); - Cell cellA_3 = row3.createCell(0, CellType.STRING); - cellA_3.setCellValue("Cell A,3"); - - // Test the last Row number it currently correct - assertEquals(2, sheet.getLastRowNum()); - - // Shift the first row to the end - sheet.shiftRows(0, 0, 3); - assertEquals(3, sheet.getLastRowNum()); - assertEquals(-1, sheet.getRow(0).getLastCellNum()); - assertEquals("Cell A,2", sheet.getRow(1).getCell(0).getStringCellValue()); - assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); - assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); - - // Shift the 2nd row up to the first one - sheet.shiftRows(1, 1, -1); - assertEquals(3, sheet.getLastRowNum()); - assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); - assertEquals(-1, sheet.getRow(1).getLastCellNum()); - assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); - assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); - - // Shift the 4th row up into the gap in the 3rd row - sheet.shiftRows(3, 3, -2); - assertEquals(2, sheet.getLastRowNum()); - assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); - assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); - assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); - assertEquals(-1, sheet.getRow(3).getLastCellNum()); - - // Now zap the empty 4th row - won't do anything - sheet.removeRow(sheet.getRow(3)); - - // Test again the last row number which should be 2 - assertEquals(2, sheet.getLastRowNum()); - assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); - assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); - assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); - - workbook.close(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + Sheet sheet = wb.createSheet("Bug50416"); + Row row1 = sheet.createRow(0); + Cell cellA_1 = row1.createCell(0, CellType.STRING); + cellA_1.setCellValue("Cell A,1"); + Row row2 = sheet.createRow(1); + Cell cellA_2 = row2.createCell(0, CellType.STRING); + cellA_2.setCellValue("Cell A,2"); + Row row3 = sheet.createRow(2); + Cell cellA_3 = row3.createCell(0, CellType.STRING); + cellA_3.setCellValue("Cell A,3"); + + // Test the last Row number it currently correct + assertEquals(2, sheet.getLastRowNum()); + + // Shift the first row to the end + sheet.shiftRows(0, 0, 3); + assertEquals(3, sheet.getLastRowNum()); + assertEquals(-1, sheet.getRow(0).getLastCellNum()); + assertEquals("Cell A,2", sheet.getRow(1).getCell(0).getStringCellValue()); + assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); + assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); + + // Shift the 2nd row up to the first one + sheet.shiftRows(1, 1, -1); + assertEquals(3, sheet.getLastRowNum()); + assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); + assertEquals(-1, sheet.getRow(1).getLastCellNum()); + assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); + assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); + + // Shift the 4th row up into the gap in the 3rd row + sheet.shiftRows(3, 3, -2); + assertEquals(2, sheet.getLastRowNum()); + assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); + assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); + assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); + assertEquals(-1, sheet.getRow(3).getLastCellNum()); + + // Now zap the empty 4th row - won't do anything + sheet.removeRow(sheet.getRow(3)); + + // Test again the last row number which should be 2 + assertEquals(2, sheet.getLastRowNum()); + assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); + assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); + assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); + + } } /** @@ -2151,32 +1996,32 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug50756() throws Exception { - HSSFWorkbook wb = openSample("50756.xls"); - HSSFSheet s = wb.getSheetAt(0); - HSSFRow r17 = s.getRow(16); - HSSFRow r18 = s.getRow(17); - HSSFDataFormatter df = new HSSFDataFormatter(); - - assertEquals(10.0, r17.getCell(1).getNumericCellValue(), 0); - assertEquals(20.0, r17.getCell(2).getNumericCellValue(), 0); - assertEquals(20.0, r17.getCell(3).getNumericCellValue(), 0); - assertEquals("GENERAL", r17.getCell(1).getCellStyle().getDataFormatString()); - assertEquals("GENERAL", r17.getCell(2).getCellStyle().getDataFormatString()); - assertEquals("GENERAL", r17.getCell(3).getCellStyle().getDataFormatString()); - assertEquals("10", df.formatCellValue(r17.getCell(1))); - assertEquals("20", df.formatCellValue(r17.getCell(2))); - assertEquals("20", df.formatCellValue(r17.getCell(3))); - - assertEquals(16.0, r18.getCell(1).getNumericCellValue(), 0); - assertEquals(35.0, r18.getCell(2).getNumericCellValue(), 0); - assertEquals(123.0, r18.getCell(3).getNumericCellValue(), 0); - assertEquals("GENERAL", r18.getCell(1).getCellStyle().getDataFormatString()); - assertEquals("GENERAL", r18.getCell(2).getCellStyle().getDataFormatString()); - assertEquals("GENERAL", r18.getCell(3).getCellStyle().getDataFormatString()); - assertEquals("16", df.formatCellValue(r18.getCell(1))); - assertEquals("35", df.formatCellValue(r18.getCell(2))); - assertEquals("123", df.formatCellValue(r18.getCell(3))); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("50756.xls")) { + HSSFSheet s = wb.getSheetAt(0); + HSSFRow r17 = s.getRow(16); + HSSFRow r18 = s.getRow(17); + HSSFDataFormatter df = new HSSFDataFormatter(); + + assertEquals(10.0, r17.getCell(1).getNumericCellValue(), 0); + assertEquals(20.0, r17.getCell(2).getNumericCellValue(), 0); + assertEquals(20.0, r17.getCell(3).getNumericCellValue(), 0); + assertEquals("GENERAL", r17.getCell(1).getCellStyle().getDataFormatString()); + assertEquals("GENERAL", r17.getCell(2).getCellStyle().getDataFormatString()); + assertEquals("GENERAL", r17.getCell(3).getCellStyle().getDataFormatString()); + assertEquals("10", df.formatCellValue(r17.getCell(1))); + assertEquals("20", df.formatCellValue(r17.getCell(2))); + assertEquals("20", df.formatCellValue(r17.getCell(3))); + + assertEquals(16.0, r18.getCell(1).getNumericCellValue(), 0); + assertEquals(35.0, r18.getCell(2).getNumericCellValue(), 0); + assertEquals(123.0, r18.getCell(3).getNumericCellValue(), 0); + assertEquals("GENERAL", r18.getCell(1).getCellStyle().getDataFormatString()); + assertEquals("GENERAL", r18.getCell(2).getCellStyle().getDataFormatString()); + assertEquals("GENERAL", r18.getCell(3).getCellStyle().getDataFormatString()); + assertEquals("16", df.formatCellValue(r18.getCell(1))); + assertEquals("35", df.formatCellValue(r18.getCell(2))); + assertEquals("123", df.formatCellValue(r18.getCell(3))); + } } /** @@ -2187,7 +2032,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug50833() throws Exception { - HSSFWorkbook wb1 = openSample("50833.xls"); + HSSFWorkbook wb1 = openSampleWorkbook("50833.xls"); HSSFSheet s = wb1.getSheetAt(0); assertEquals("Sheet1", s.getSheetName()); assertFalse(s.getProtect()); @@ -2218,13 +2063,9 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void bug50779() throws Exception { - HSSFWorkbook wb1 = openSample("50779_1.xls"); - writeOutAndReadBack(wb1).close(); - wb1.close(); + simpleTest("50779_1.xls"); - HSSFWorkbook wb2 = openSample("50779_2.xls"); - writeOutAndReadBack(wb2).close(); - wb2.close(); + simpleTest("50779_2.xls"); } /** @@ -2233,17 +2074,15 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug50939() throws Exception { - HSSFWorkbook wb = openSample("50939.xls"); - assertEquals(2, wb.getNumberOfSheets()); - wb.close(); + simpleTest("50939.xls", wb -> assertEquals(2, wb.getNumberOfSheets())); } @Test public void bug49219() throws Exception { - HSSFWorkbook wb = openSample("49219.xls"); - assertEquals(1, wb.getNumberOfSheets()); - assertEquals("DGATE", wb.getSheetAt(0).getRow(1).getCell(0).getStringCellValue()); - wb.close(); + simpleTest("49219.xls", wb -> { + assertEquals(1, wb.getNumberOfSheets()); + assertEquals("DGATE", wb.getSheetAt(0).getRow(1).getCell(0).getStringCellValue()); + }); } @Test @@ -2251,7 +2090,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { TimeZone userTimeZone = LocaleUtil.getUserTimeZone(); LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET")); try { - HSSFWorkbook wb = openSample("48968.xls"); + HSSFWorkbook wb = openSampleWorkbook("48968.xls"); assertEquals(1, wb.getNumberOfSheets()); DataFormatter fmt = new DataFormatter(); @@ -2309,12 +2148,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug51143() throws Exception { - HSSFWorkbook wb1 = openSample("51143.xls"); - assertEquals(1, wb1.getNumberOfSheets()); - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - assertEquals(1, wb2.getNumberOfSheets()); - wb2.close(); + simpleTest("51143.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); } /** @@ -2336,14 +2170,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void bug51535() throws Exception { - byte[] data = HSSFITestDataProvider.instance.getTestDataFileContent("51535.xls"); - - HSSFWorkbook wbPOIFS = new HSSFWorkbook(new POIFSFileSystem( - new ByteArrayInputStream(data)).getRoot(), false); - HSSFWorkbook wbPOIFS2 = new HSSFWorkbook(new POIFSFileSystem( - new ByteArrayInputStream(data)).getRoot(), false); - - for (HSSFWorkbook wb : new HSSFWorkbook[]{wbPOIFS, wbPOIFS2}) { + try (HSSFWorkbook wb = openSampleWorkbook("51535.xls")) { assertEquals(3, wb.getNumberOfSheets()); // Check directly @@ -2354,21 +2181,19 @@ public final class TestBugs extends BaseTestBugzillaIssues { assertEquals("Bottom Right Cell", s.getRow(65535).getCell(255).getStringCellValue()); // Extract and check - ExcelExtractor ex = new ExcelExtractor(wb); - String text = ex.getText(); - assertContains(text, "Top Left Cell"); - assertContains(text, "Top Right Cell"); - assertContains(text, "Bottom Left Cell"); - assertContains(text, "Bottom Right Cell"); - ex.close(); + try (ExcelExtractor ex = new ExcelExtractor(wb)) { + String text = ex.getText(); + assertContains(text, "Top Left Cell"); + assertContains(text, "Top Right Cell"); + assertContains(text, "Bottom Left Cell"); + assertContains(text, "Bottom Right Cell"); + } } } @Test public void bug51670() throws Exception { - HSSFWorkbook wb = openSample("51670.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("51670.xls"); } /** @@ -2377,22 +2202,22 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void test48703() throws Exception { - HSSFWorkbook wb = openSample("48703.xls"); - assertEquals(3, wb.getNumberOfSheets()); + try (HSSFWorkbook wb = openSampleWorkbook("48703.xls")) { + assertEquals(3, wb.getNumberOfSheets()); - // Check reading the formula - Sheet sheet = wb.getSheetAt(0); - Row r = sheet.getRow(0); - Cell c = r.getCell(0); + // Check reading the formula + Sheet sheet = wb.getSheetAt(0); + Row r = sheet.getRow(0); + Cell c = r.getCell(0); - assertEquals("SUM(Sheet2:Sheet3!A1)", c.getCellFormula()); - assertEquals(4.0, c.getNumericCellValue(), 0); + assertEquals("SUM(Sheet2:Sheet3!A1)", c.getCellFormula()); + assertEquals(4.0, c.getNumericCellValue(), 0); - // Check the evaluated result - HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); - eval.evaluateFormulaCell(c); - assertEquals(4.0, c.getNumericCellValue(), 0); - wb.close(); + // Check the evaluated result + HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); + eval.evaluateFormulaCell(c); + assertEquals(4.0, c.getNumericCellValue(), 0); + } } /** @@ -2400,155 +2225,137 @@ public final class TestBugs extends BaseTestBugzillaIssues { * some may squeeze a WRITEPROTECT in the middle */ @Test(expected = EncryptedDocumentException.class) - public void bug51832() { - openSample("51832.xls"); + public void bug51832() throws IOException { + simpleTest("51832.xls"); } @Test public void bug49896() throws Exception { - HSSFWorkbook wb = openSample("49896.xls"); - HSSFCell cell = wb.getSheetAt(0).getRow(1).getCell(1); - String PATH_SEPARATOR = System.getProperty("file.separator"); - assertEquals("VLOOKUP(A2,'[C:Documents and Settings" + PATH_SEPARATOR + "Yegor" + PATH_SEPARATOR - + "My Documents" + PATH_SEPARATOR + "csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)", - cell.getCellFormula()); - wb.close(); + String exp = "VLOOKUP(A2,'[C:Documents and Settings/Yegor/My Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)" + .replace("/", File.separator); + simpleTest("49896.xls", wb -> assertEquals(exp, wb.getSheetAt(0).getRow(1).getCell(1).getCellFormula())); } @Test public void bug49529() throws Exception { // user code reported in Bugzilla #49529 - HSSFWorkbook wb = openSample("49529.xls"); - wb.getSheetAt(0).createDrawingPatriarch(); - // prior to the fix the line below failed with - // java.lang.IllegalStateException: EOF - next record not available - wb.cloneSheet(0); - - // make sure we are still readable - writeOutAndReadBack(wb).close(); - - wb.close(); + simpleTest("49529.xls", wb -> { + wb.getSheetAt(0).createDrawingPatriarch(); + // prior to the fix the line below failed with + // java.lang.IllegalStateException: EOF - next record not available + wb.cloneSheet(0); + }); } @Test public void bug49612_part() throws IOException { - HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("49612.xls"); - HSSFSheet sh = wb.getSheetAt(0); - HSSFRow row = sh.getRow(0); - HSSFCell c1 = row.getCell(2); - HSSFCell d1 = row.getCell(3); - HSSFCell e1 = row.getCell(2); - - assertEquals("SUM(BOB+JIM)", c1.getCellFormula()); - - // Problem 1: See TestUnfixedBugs#test49612() - // Problem 2: TestUnfixedBugs#test49612() - - // Problem 3: These used to fail, now pass - HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); - assertEquals("evaluating c1", 30.0, eval.evaluate(c1).getNumberValue(), 0.001); - assertEquals("evaluating d1", 30.0, eval.evaluate(d1).getNumberValue(), 0.001); - assertEquals("evaluating e1", 30.0, eval.evaluate(e1).getNumberValue(), 0.001); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("49612.xls")) { + HSSFSheet sh = wb.getSheetAt(0); + HSSFRow row = sh.getRow(0); + HSSFCell c1 = row.getCell(2); + HSSFCell d1 = row.getCell(3); + HSSFCell e1 = row.getCell(2); + + assertEquals("SUM(BOB+JIM)", c1.getCellFormula()); + + // Problem 1: See TestUnfixedBugs#test49612() + // Problem 2: TestUnfixedBugs#test49612() + + // Problem 3: These used to fail, now pass + HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); + assertEquals("evaluating c1", 30.0, eval.evaluate(c1).getNumberValue(), 0.001); + assertEquals("evaluating d1", 30.0, eval.evaluate(d1).getNumberValue(), 0.001); + assertEquals("evaluating e1", 30.0, eval.evaluate(e1).getNumberValue(), 0.001); + } } @Test public void bug51675() throws Exception { - final List list = new ArrayList<>(); - HSSFWorkbook wb = openSample("51675.xls"); - HSSFSheet sh = wb.getSheetAt(0); - InternalSheet ish = HSSFTestHelper.getSheetForTest(sh); - PageSettingsBlock psb = (PageSettingsBlock) ish.getRecords().get(13); - psb.visitContainedRecords(r -> list.add(r.getSid())); - assertEquals(UnknownRecord.BITMAP_00E9, list.get(list.size() - 1).intValue()); - assertEquals(UnknownRecord.HEADER_FOOTER_089C, list.get(list.size() - 2).intValue()); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("51675.xls")) { + HSSFSheet sh = wb.getSheetAt(0); + InternalSheet ish = HSSFTestHelper.getSheetForTest(sh); + PageSettingsBlock psb = (PageSettingsBlock) ish.getRecords().get(13); + List list = new ArrayList<>(); + psb.visitContainedRecords(r -> list.add(r.getSid())); + assertEquals(UnknownRecord.BITMAP_00E9, list.get(list.size() - 1).intValue()); + assertEquals(UnknownRecord.HEADER_FOOTER_089C, list.get(list.size() - 2).intValue()); + } } @Test public void bug52272() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sh = wb.createSheet(); - HSSFPatriarch p = sh.createDrawingPatriarch(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sh = wb.createSheet(); + HSSFPatriarch p = sh.createDrawingPatriarch(); - HSSFSimpleShape s = p.createSimpleShape(new HSSFClientAnchor()); - s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); + HSSFSimpleShape s = p.createSimpleShape(new HSSFClientAnchor()); + s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); - HSSFSheet sh2 = wb.cloneSheet(0); - assertNotNull(sh2.getDrawingPatriarch()); - - wb.close(); + HSSFSheet sh2 = wb.cloneSheet(0); + assertNotNull(sh2.getDrawingPatriarch()); + } } @Test public void bug53432() throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); //or new HSSFWorkbook(); - wb1.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); - assertEquals(wb1.getAllPictures().size(), 1); - wb1.close(); - - wb1.close(); - wb1 = new HSSFWorkbook(); - - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - wb1.close(); - assertEquals(wb2.getAllPictures().size(), 0); - wb2.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); - assertEquals(wb2.getAllPictures().size(), 1); - - HSSFWorkbook wb3 = writeOutAndReadBack(wb2); - wb2.close(); - assertEquals(wb3.getAllPictures().size(), 1); - - wb3.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + wb1.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); + assertEquals(wb1.getAllPictures().size(), 1); + } + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + assertEquals(wb2.getAllPictures().size(), 0); + wb2.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); + assertEquals(wb2.getAllPictures().size(), 1); + + try (HSSFWorkbook wb3 = writeOutAndReadBack(wb2)) { + assertEquals(wb3.getAllPictures().size(), 1); + } + } + } } @Test public void bug46250() throws Exception { - HSSFWorkbook wb = openSample("46250.xls"); - Sheet sh = wb.getSheet("Template"); - Sheet cSh = wb.cloneSheet(wb.getSheetIndex(sh)); - - HSSFPatriarch patriarch = (HSSFPatriarch) cSh.createDrawingPatriarch(); - HSSFTextbox tb = (HSSFTextbox) patriarch.getChildren().get(2); + try (HSSFWorkbook wb = openSampleWorkbook("46250.xls")) { + Sheet sh = wb.getSheet("Template"); + Sheet cSh = wb.cloneSheet(wb.getSheetIndex(sh)); - tb.setString(new HSSFRichTextString("POI test")); - tb.setAnchor(new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 10, 10)); + HSSFPatriarch patriarch = (HSSFPatriarch) cSh.createDrawingPatriarch(); + HSSFTextbox tb = (HSSFTextbox) patriarch.getChildren().get(2); - writeOutAndReadBack(wb).close(); + tb.setString(new HSSFRichTextString("POI test")); + tb.setAnchor(new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 10, 10)); - wb.close(); + writeOutAndReadBack(wb).close(); + } } @Test public void bug53404() throws Exception { - HSSFWorkbook wb = openSample("53404.xls"); - Sheet sheet = wb.getSheet("test-sheet"); - int rowCount = sheet.getLastRowNum() + 1; - int newRows = 5; - Calendar cal = LocaleUtil.getLocaleCalendar(); - for (int r = rowCount; r < rowCount + newRows; r++) { - Row row = sheet.createRow((short) r); - row.createCell(0).setCellValue(1.03 * (r + 7)); - row.createCell(1).setCellValue(cal.getTime()); - row.createCell(2).setCellValue(cal); - row.createCell(3).setCellValue(String.format(Locale.ROOT, "row:%d/col:%d", r, 3)); - row.createCell(4).setCellValue(true); - row.createCell(5).setCellErrorValue(FormulaError.NUM.getCode()); - row.createCell(6).setCellValue("added cells."); - } - - writeOutAndReadBack(wb).close(); + try (HSSFWorkbook wb = openSampleWorkbook("53404.xls")) { + Sheet sheet = wb.getSheet("test-sheet"); + int rowCount = sheet.getLastRowNum() + 1; + int newRows = 5; + Calendar cal = LocaleUtil.getLocaleCalendar(); + for (int r = rowCount; r < rowCount + newRows; r++) { + Row row = sheet.createRow((short) r); + row.createCell(0).setCellValue(1.03 * (r + 7)); + row.createCell(1).setCellValue(cal.getTime()); + row.createCell(2).setCellValue(cal); + row.createCell(3).setCellValue(String.format(Locale.ROOT, "row:%d/col:%d", r, 3)); + row.createCell(4).setCellValue(true); + row.createCell(5).setCellErrorValue(FormulaError.NUM.getCode()); + row.createCell(6).setCellValue("added cells."); + } - wb.close(); + writeOutAndReadBack(wb).close(); + } } @Test public void bug54016() throws Exception { - // This used to break - HSSFWorkbook wb = openSample("54016.xls"); - writeOutAndReadBack(wb).close(); - wb.close(); + simpleTest("54016.xls"); } /** @@ -2556,48 +2363,50 @@ public final class TestBugs extends BaseTestBugzillaIssues { */ @Test public void bug49237() throws Exception { - Workbook wb = openSample("49237.xls"); - Sheet sheet = wb.getSheetAt(0); - Row row = sheet.getRow(0); - CellStyle rstyle = row.getRowStyle(); - assertNotNull(rstyle); - assertEquals(BorderStyle.DOUBLE, rstyle.getBorderBottom()); - wb.close(); + try (Workbook wb = openSampleWorkbook("49237.xls")) { + Sheet sheet = wb.getSheetAt(0); + Row row = sheet.getRow(0); + CellStyle rstyle = row.getRowStyle(); + assertNotNull(rstyle); + assertEquals(BorderStyle.DOUBLE, rstyle.getBorderBottom()); + } } /** - * POI doesn't currently support the RC4 CryptoAPI encryption header structure + * POI does now support the RC4 CryptoAPI encryption header structure */ - @Test(expected = EncryptedDocumentException.class) + @Test public void bug35897() throws Exception { - // password is abc - Biff8EncryptionKey.setCurrentUserPassword("abc"); - try { - openSample("xor-encryption-abc.xls").close(); - } finally { - Biff8EncryptionKey.setCurrentUserPassword(null); - } + String[] encrypted = { + "xor-encryption-abc.xls", "abc", + "35897-type4.xls", "freedom" + }; - // One using the only-recently-documented encryption header type 4, - // and the RC4 CryptoAPI encryption header structure - openSample("35897-type4.xls").close(); + for (int i=0; i assertEquals(refLocal, wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula())); - HSSFWorkbook wb2 = openSample("46670_http.xls"); - s = wb2.getSheetAt(0); - assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula()); - wb2.close(); + simpleTest("46670_http.xls", + wb -> assertEquals(refHttp, wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula())); // Now try to set them to the same values, and ensure that // they end up as they did before, even with a save and re-load - HSSFWorkbook wb3 = openSample("46670_local.xls"); - s = wb3.getSheetAt(0); - Cell c = s.getRow(0).getCell(0); - c.setCellFormula(refLocal); - assertEquals(refLocal, c.getCellFormula()); - - HSSFWorkbook wb4 = HSSFTestDataSamples.writeOutAndReadBack(wb3); - wb3.close(); - s = wb4.getSheetAt(0); - assertEquals(refLocal, s.getRow(0).getCell(0).getCellFormula()); - wb4.close(); - - HSSFWorkbook wb5 = openSample("46670_http.xls"); - s = wb5.getSheetAt(0); - c = s.getRow(0).getCell(0); - c.setCellFormula(refHttp); - assertEquals(refHttp, c.getCellFormula()); + try (HSSFWorkbook wb3 = openSampleWorkbook("46670_local.xls")) { + Sheet s = wb3.getSheetAt(0); + Cell c = s.getRow(0).getCell(0); + c.setCellFormula(refLocal); + assertEquals(refLocal, c.getCellFormula()); + + try (HSSFWorkbook wb4 = writeOutAndReadBack(wb3)) { + s = wb4.getSheetAt(0); + assertEquals(refLocal, s.getRow(0).getCell(0).getCellFormula()); + } + } + + try (HSSFWorkbook wb5 = openSampleWorkbook("46670_http.xls")) { + Sheet s = wb5.getSheetAt(0); + Cell c = s.getRow(0).getCell(0); + c.setCellFormula(refHttp); + assertEquals(refHttp, c.getCellFormula()); - Workbook wb6 = HSSFTestDataSamples.writeOutAndReadBack(wb5); - wb5.close(); - s = wb6.getSheetAt(0); - assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula()); - wb6.close(); + try (Workbook wb6 = writeOutAndReadBack(wb5)) { + s = wb6.getSheetAt(0); + assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula()); + } + } } @Test public void test57456() throws IOException { - Workbook wb = openSample("57456.xls"); - wb.close(); + simpleTest("57456.xls"); } @Test public void test57163() throws IOException { - Workbook wb = openSample("57163.xls"); - - while (wb.getNumberOfSheets() > 1) { - wb.removeSheetAt(1); - } - wb.close(); + simpleTest("57163.xls", wb -> { + while (wb.getNumberOfSheets() > 1) { + wb.removeSheetAt(1); + } + }); } @Test public void test53109() throws IOException { - HSSFWorkbook wb1 = openSample("53109.xls"); - - HSSFWorkbook wb2 = writeOutAndReadBack(wb1); - assertNotNull(wb2); - wb2.close(); - - wb1.close(); + simpleTest("53109.xls"); } @Test public void test53109a() throws IOException { - HSSFWorkbook wb1 = openSample("com.aida-tour.www_SPO_files_maldives%20august%20october.xls"); - - Workbook wb2 = writeOutAndReadBack(wb1); - assertNotNull(wb2); - wb2.close(); - - wb1.close(); + simpleTest("com.aida-tour.www_SPO_files_maldives%20august%20october.xls"); } @Test public void test48043() throws IOException { - HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("56325a.xls"); + try (HSSFWorkbook wb = openSampleWorkbook("56325a.xls")) { - wb.removeSheetAt(2); - wb.removeSheetAt(1); + wb.removeSheetAt(2); + wb.removeSheetAt(1); - //Sheet s = wb.createSheet("sheetname"); - Sheet s = wb.getSheetAt(0); - Row row = s.createRow(0); - Cell cell = row.createCell(0); + //Sheet s = wb.createSheet("sheetname"); + Sheet s = wb.getSheetAt(0); + Row row = s.createRow(0); + Cell cell = row.createCell(0); - cell.setCellFormula( + cell.setCellFormula( "IF(AND(ISBLANK(A10)," + - "ISBLANK(B10)),\"\"," + - "CONCATENATE(A10,\"-\",B10))"); - - FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); - - eval.evaluateAll(); - - /*OutputStream out = new FileOutputStream("C:\\temp\\48043.xls"); - try { - wb.write(out); - } finally { - out.close(); - }*/ + "ISBLANK(B10)),\"\"," + + "CONCATENATE(A10,\"-\",B10))"); - Workbook wbBack = HSSFTestDataSamples.writeOutAndReadBack(wb); - assertNotNull(wbBack); - wbBack.close(); + FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); - wb.close(); + eval.evaluateAll(); + try (Workbook wbBack = writeOutAndReadBack(wb)) { + assertNotNull(wbBack); + } + } } @Test public void test57925() throws IOException { - Workbook wb = HSSFTestDataSamples.openSampleWorkbook("57925.xls"); - - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); + try (Workbook wb = openSampleWorkbook("57925.xls")) { + wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - for (int i = 0; i < wb.getNumberOfSheets(); i++) { - Sheet sheet = wb.getSheetAt(i); - for (Row row : sheet) { - for (Cell cell : row) { - new DataFormatter().formatCellValue(cell); + for (int i = 0; i < wb.getNumberOfSheets(); i++) { + Sheet sheet = wb.getSheetAt(i); + for (Row row : sheet) { + for (Cell cell : row) { + new DataFormatter().formatCellValue(cell); + } } } } - - wb.close(); } @Test public void test46515() throws IOException { - try (Workbook wb = HSSFTestDataSamples.openSampleWorkbook("46515.xls")) { + try (Workbook wb = openSampleWorkbook("46515.xls")) { // Get structure from webservice String urlString = "http://poi.apache.org/components/spreadsheet/images/calendar.jpg"; @@ -2942,45 +2714,32 @@ public final class TestBugs extends BaseTestBugzillaIssues { HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 10, 22); anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE); patriarch.createPicture(anchor, pict); - - // Write out destination file -// FileOutputStream fileOut = new FileOutputStream("/tmp/46515.xls"); -// wb.write(fileOut); -// fileOut.close(); } } @Test public void test55668() throws IOException { - Workbook wb = HSSFTestDataSamples.openSampleWorkbook("55668.xls"); + try (Workbook wb = openSampleWorkbook("55668.xls")) { - Sheet sheet = wb.getSheetAt(0); - Row row = sheet.getRow(0); - Cell cell = row.getCell(0); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(TRUE,\"\",\"\")", cell.getCellFormula()); - assertEquals("", cell.getStringCellValue()); - //noinspection deprecation - cell.setCellType(CellType.STRING); - - assertEquals(CellType.BLANK, cell.getCellType()); - try { + Sheet sheet = wb.getSheetAt(0); + Row row = sheet.getRow(0); + Cell cell = row.getCell(0); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(TRUE,\"\",\"\")", cell.getCellFormula()); + assertEquals("", cell.getStringCellValue()); + //noinspection deprecation + cell.setCellType(CellType.STRING); + + assertEquals(CellType.BLANK, cell.getCellType()); + assertEquals("", cell.getStringCellValue()); + thrown.expect(IllegalStateException.class); assertNull(cell.getCellFormula()); - fail("Should throw an exception here"); - } catch (IllegalStateException e) { - // expected here } - assertEquals("", cell.getStringCellValue()); - - wb.close(); } @Test public void test55982() throws IOException { - Workbook wb = HSSFTestDataSamples.openSampleWorkbook("55982.xls"); - Sheet newSheet = wb.cloneSheet(1); - assertNotNull(newSheet); - wb.close(); + simpleTest("55982.xls", wb -> assertNotNull(wb.cloneSheet(1))); } /** @@ -3012,83 +2771,76 @@ public final class TestBugs extends BaseTestBugzillaIssues { // I have not checked Excel to know if this is a LibreOffice or a POI problem. @Test public void test53564() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sheet = wb.createSheet("Page 1"); - final short BLUE = 30; - - HSSFSheetConditionalFormatting scf = sheet.getSheetConditionalFormatting(); - HSSFConditionalFormattingRule rule = scf.createConditionalFormattingRule(ComparisonOperator.GT, "10"); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet("Page 1"); + final short BLUE = 30; - HSSFBorderFormatting bord = rule.createBorderFormatting(); - bord.setBorderDiagonal(BorderStyle.THICK); - assertEquals(BorderStyle.THICK, bord.getBorderDiagonal()); + HSSFSheetConditionalFormatting scf = sheet.getSheetConditionalFormatting(); + HSSFConditionalFormattingRule rule = scf.createConditionalFormattingRule(ComparisonOperator.GT, "10"); - bord.setBackwardDiagonalOn(true); - assertTrue(bord.isBackwardDiagonalOn()); + HSSFBorderFormatting bord = rule.createBorderFormatting(); + bord.setBorderDiagonal(BorderStyle.THICK); + assertEquals(BorderStyle.THICK, bord.getBorderDiagonal()); - bord.setForwardDiagonalOn(true); - assertTrue(bord.isForwardDiagonalOn()); + bord.setBackwardDiagonalOn(true); + assertTrue(bord.isBackwardDiagonalOn()); - bord.setDiagonalBorderColor(BLUE); - assertEquals(BLUE, bord.getDiagonalBorderColor()); + bord.setForwardDiagonalOn(true); + assertTrue(bord.isForwardDiagonalOn()); - // Create the bottom border style so we know what a border is supposed to look like - bord.setBorderBottom(BorderStyle.THICK); - assertEquals(BorderStyle.THICK, bord.getBorderBottom()); - bord.setBottomBorderColor(BLUE); - assertEquals(BLUE, bord.getBottomBorderColor()); + bord.setDiagonalBorderColor(BLUE); + assertEquals(BLUE, bord.getDiagonalBorderColor()); - CellRangeAddress[] A2_D4 = {new CellRangeAddress(1, 3, 0, 3)}; - scf.addConditionalFormatting(A2_D4, rule); + // Create the bottom border style so we know what a border is supposed to look like + bord.setBorderBottom(BorderStyle.THICK); + assertEquals(BorderStyle.THICK, bord.getBorderBottom()); + bord.setBottomBorderColor(BLUE); + assertEquals(BLUE, bord.getBottomBorderColor()); - // Set a cell value within the conditional formatting range whose rule would resolve to True. - Cell C3 = sheet.createRow(2).createCell(2); - C3.setCellValue(30.0); + CellRangeAddress[] A2_D4 = {new CellRangeAddress(1, 3, 0, 3)}; + scf.addConditionalFormatting(A2_D4, rule); - // Manually check the output file with Excel to see if the diagonal border is present - //OutputStream fos = new FileOutputStream("/tmp/53564.xls"); - //wb.write(fos); - //fos.close(); - wb.close(); + // Set a cell value within the conditional formatting range whose rule would resolve to True. + Cell C3 = sheet.createRow(2).createCell(2); + C3.setCellValue(30.0); + } } @Test public void test45353a() throws IOException { - final Workbook wb = HSSFTestDataSamples.openSampleWorkbook("named-cell-in-formula-test.xls"); - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - wb.close(); + simpleTest("named-cell-in-formula-test.xls"); } @Test public void test45353b() throws IOException { - final Workbook wb = HSSFTestDataSamples.openSampleWorkbook("named-cell-test.xls"); - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - wb.close(); + simpleTest("named-cell-test.xls"); } @Test public void test61287() throws IOException { - final HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("61287.xls"); - ExcelExtractor ex = new ExcelExtractor(wb); - String text = ex.getText(); - assertContains(text, "\u8D44\u4EA7\u8D1F\u503A\u8868"); - wb.close(); + try (HSSFWorkbook wb = openSampleWorkbook("61287.xls"); + ExcelExtractor ex = new ExcelExtractor(wb)) { + String text = ex.getText(); + assertContains(text, "\u8D44\u4EA7\u8D1F\u503A\u8868"); + } } - @Test(expected = RuntimeException.class) + @Test public void test61300() throws Exception { - POIFSFileSystem poifs = new POIFSFileSystem(HSSFTestDataSamples.openSampleFileStream("61300.xls")); + try (POIFSFileSystem poifs = new POIFSFileSystem(HSSFTestDataSamples.openSampleFileStream("61300.xls"))) { - DocumentEntry entry = - (DocumentEntry) poifs.getRoot().getEntry(SummaryInformation.DEFAULT_STREAM_NAME); + DocumentEntry entry = + (DocumentEntry) poifs.getRoot().getEntry(SummaryInformation.DEFAULT_STREAM_NAME); - // this will throw an Exception "RuntimeException: Can't read negative number of bytes" - new PropertySet(new DocumentInputStream(entry)); + thrown.expect(RuntimeException.class); + thrown.expectMessage("Can't read negative number of bytes"); + new PropertySet(new DocumentInputStream(entry)); + } } @Test public void test51262() throws IOException { - try (HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("51262.xls")) { + try (HSSFWorkbook wb = openSampleWorkbook("51262.xls")) { Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); @@ -3116,7 +2868,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void test60460() throws IOException { - try (final Workbook wb = HSSFTestDataSamples.openSampleWorkbook("60460.xls")) { + try (final Workbook wb = openSampleWorkbook("60460.xls")) { assertEquals(2, wb.getAllNames().size()); Name rangedName = wb.getAllNames().get(0); @@ -3133,8 +2885,47 @@ public final class TestBugs extends BaseTestBugzillaIssues { @Test public void test63940() throws IOException { - try (final Workbook wb = HSSFTestDataSamples.openSampleWorkbook("SUBSTITUTE.xls")) { - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); + simpleTest("SUBSTITUTE.xls"); + } + + // a simple test which rewrites the file once and evaluates its formulas + private void simpleTest(String fileName) throws IOException { + simpleTest(fileName, null); + } + + private void simpleTest(String fileName, Consumer addTest) throws IOException { + try (HSSFWorkbook wb1 = openSampleWorkbook(fileName)) { + if (addTest != null) { + addTest.accept(wb1); + } + + HSSFSheet s = wb1.getSheetAt(0); + HSSFRow r = s.createRow(10_000); + HSSFCell c = r.createCell(0); + c.setCellValue(10); + simpleTestHelper(wb1, fileName); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + if (addTest != null) { + addTest.accept(wb2); + } + + double act = wb2.getSheetAt(0).getRow(10_000).getCell(0).getNumericCellValue(); + assertEquals(10, act, 0); + simpleTestHelper(wb2, fileName); + } } } + + private void simpleTestHelper(HSSFWorkbook wb, String fileName) { + List files = new ArrayList<>(); + files.add(fileName); + files.addAll(SIMPLE_REFS.keySet()); + List evals = new ArrayList<>(); + evals.add(wb.getCreationHelper().createFormulaEvaluator()); + evals.addAll(SIMPLE_REFS.values()); + + HSSFFormulaEvaluator.setupEnvironment( files.toArray(new String[0]), evals.toArray(new HSSFFormulaEvaluator[0]) ); + evals.get(0).evaluateAll(); + } } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java index 68e32bbb79..1264a15ca9 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java @@ -50,8 +50,6 @@ import org.junit.Test; /** * A base class for bugzilla issues that can be described in terms of common ss interfaces. - * - * @author Yegor Kozlov */ public abstract class BaseTestBugzillaIssues { private static final POILogger logger = POILogFactory.getLogger(BaseTestBugzillaIssues.class); @@ -73,7 +71,7 @@ public abstract class BaseTestBugzillaIssues { * where delta is an absolute error value, this function's factor is a relative error, * so it's easier to express "actual is within 5% of expected". */ - public static void assertAlmostEquals(double expected, double actual, float factor) { + private static void assertAlmostEquals(double expected, double actual, float factor) { double diff = Math.abs(expected - actual); double fuzz = expected * factor; if (diff > fuzz) { @@ -89,22 +87,21 @@ public abstract class BaseTestBugzillaIssues { */ @Test public final void bug23094() throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - Sheet s = wb1.createSheet(); - Row r = s.createRow(0); - r.createCell(0).setCellFormula("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")"); - r.createCell(1).setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")"); - - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); - wb1.close(); - r = wb2.getSheetAt(0).getRow(0); - - Cell cell_0 = r.getCell(0); - assertEquals("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.getCellFormula()); - Cell cell_1 = r.getCell(1); - assertEquals("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.getCellFormula()); - - wb2.close(); + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + Sheet s = wb1.createSheet(); + Row r = s.createRow(0); + r.createCell(0).setCellFormula("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")"); + r.createCell(1).setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")"); + + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + r = wb2.getSheetAt(0).getRow(0); + + Cell cell_0 = r.getCell(0); + assertEquals("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.getCellFormula()); + Cell cell_1 = r.getCell(1); + assertEquals("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.getCellFormula()); + } + } } /** @@ -113,40 +110,40 @@ public abstract class BaseTestBugzillaIssues { * @param num the number of strings to generate */ public final void bug15375(int num) throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - Sheet sheet = wb1.createSheet(); - CreationHelper factory = wb1.getCreationHelper(); - - for (int i = 0; i < num; i++) { - String tmp1 = "Test1" + i; - String tmp2 = "Test2" + i; - String tmp3 = "Test3" + i; - - Row row = sheet.createRow(i); - - Cell cell = row.createCell(0); - cell.setCellValue(factory.createRichTextString(tmp1)); - cell = row.createCell(1); - cell.setCellValue(factory.createRichTextString(tmp2)); - cell = row.createCell(2); - cell.setCellValue(factory.createRichTextString(tmp3)); - } - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); - wb1.close(); + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + Sheet sheet = wb1.createSheet(); + CreationHelper factory = wb1.getCreationHelper(); + + for (int i = 0; i < num; i++) { + String tmp1 = "Test1" + i; + String tmp2 = "Test2" + i; + String tmp3 = "Test3" + i; + + Row row = sheet.createRow(i); + + Cell cell = row.createCell(0); + cell.setCellValue(factory.createRichTextString(tmp1)); + cell = row.createCell(1); + cell.setCellValue(factory.createRichTextString(tmp2)); + cell = row.createCell(2); + cell.setCellValue(factory.createRichTextString(tmp3)); + } - sheet = wb2.getSheetAt(0); - for (int i = 0; i < num; i++) { - String tmp1 = "Test1" + i; - String tmp2 = "Test2" + i; - String tmp3 = "Test3" + i; + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + sheet = wb2.getSheetAt(0); + for (int i = 0; i < num; i++) { + String tmp1 = "Test1" + i; + String tmp2 = "Test2" + i; + String tmp3 = "Test3" + i; - Row row = sheet.getRow(i); + Row row = sheet.getRow(i); - assertEquals(tmp1, row.getCell(0).getStringCellValue()); - assertEquals(tmp2, row.getCell(1).getStringCellValue()); - assertEquals(tmp3, row.getCell(2).getStringCellValue()); + assertEquals(tmp1, row.getCell(0).getStringCellValue()); + assertEquals(tmp2, row.getCell(1).getStringCellValue()); + assertEquals(tmp3, row.getCell(2).getStringCellValue()); + } + } } - wb2.close(); } /** @@ -154,51 +151,49 @@ public abstract class BaseTestBugzillaIssues { */ @Test public void bug22720() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - wb.createSheet("TEST"); - Sheet template = wb.getSheetAt(0); + try (Workbook wb = _testDataProvider.createWorkbook()) { + wb.createSheet("TEST"); + Sheet template = wb.getSheetAt(0); - assertEquals(0, template.addMergedRegion(new CellRangeAddress(0, 1, 0, 2))); - assertEquals(1, template.addMergedRegion(new CellRangeAddress(2, 3, 0, 2))); + assertEquals(0, template.addMergedRegion(new CellRangeAddress(0, 1, 0, 2))); + assertEquals(1, template.addMergedRegion(new CellRangeAddress(2, 3, 0, 2))); - Sheet clone = wb.cloneSheet(0); - int originalMerged = template.getNumMergedRegions(); - assertEquals("2 merged regions", 2, originalMerged); + Sheet clone = wb.cloneSheet(0); + int originalMerged = template.getNumMergedRegions(); + assertEquals("2 merged regions", 2, originalMerged); - //remove merged regions from clone - for (int i=template.getNumMergedRegions()-1; i>=0; i--) { - clone.removeMergedRegion(i); - } + //remove merged regions from clone + for (int i = template.getNumMergedRegions() - 1; i >= 0; i--) { + clone.removeMergedRegion(i); + } - assertEquals("Original Sheet's Merged Regions were removed", originalMerged, template.getNumMergedRegions()); - //check if template's merged regions are OK - if (template.getNumMergedRegions()>0) { - // fetch the first merged region...EXCEPTION OCCURS HERE - template.getMergedRegion(0); + assertEquals("Original Sheet's Merged Regions were removed", originalMerged, template.getNumMergedRegions()); + //check if template's merged regions are OK + if (template.getNumMergedRegions() > 0) { + // fetch the first merged region...EXCEPTION OCCURS HERE + template.getMergedRegion(0); + } } - - wb.close(); } @Test public final void bug28031() throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - Sheet sheet = wb1.createSheet(); - wb1.setSheetName(0, "Sheet1"); - - Row row = sheet.createRow(0); - Cell cell = row.createCell(0); - String formulaText = - "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))"; - cell.setCellFormula(formulaText); + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + Sheet sheet = wb1.createSheet(); + wb1.setSheetName(0, "Sheet1"); - assertEquals(formulaText, cell.getCellFormula()); - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); - wb1.close(); - - cell = wb2.getSheetAt(0).getRow(0).getCell(0); - assertEquals("IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))", cell.getCellFormula()); - wb2.close(); + Row row = sheet.createRow(0); + Cell cell = row.createCell(0); + String formulaText = + "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))"; + cell.setCellFormula(formulaText); + + assertEquals(formulaText, cell.getCellFormula()); + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + cell = wb2.getSheetAt(0).getRow(0).getCell(0); + assertEquals("IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))", cell.getCellFormula()); + } + } } /** @@ -208,64 +203,64 @@ public abstract class BaseTestBugzillaIssues { */ @Test public final void bug21334() throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - Sheet sh = wb1.createSheet(); - Cell cell = sh.createRow(0).createCell(0); - String formula = "SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"))>0,1))"; - cell.setCellFormula(formula); - - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); - wb1.close(); - Cell cell_sv = wb2.getSheetAt(0).getRow(0).getCell(0); - assertEquals(formula, cell_sv.getCellFormula()); - wb2.close(); + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + Sheet sh = wb1.createSheet(); + Cell cell = sh.createRow(0).createCell(0); + String formula = "SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"))>0,1))"; + cell.setCellFormula(formula); + + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + Cell cell_sv = wb2.getSheetAt(0).getRow(0).getCell(0); + assertEquals(formula, cell_sv.getCellFormula()); + } + } } /** another test for the number of unique strings issue *test opening the resulting file in Excel*/ @Test public final void bug22568() throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - Sheet sheet = wb1.createSheet("ExcelTest") ; - - int col_cnt = 3; - int rw_cnt = 2000; - - Row rw ; - rw = sheet.createRow(0) ; - //Header row - for(int j=0; j 0); // } - double widthManual = computeCellWidthManually(cell0, font); - double widthBeforeCell = SheetUtil.getCellWidth(cell0, 8, null, false); - double widthBeforeCol = SheetUtil.getColumnWidth(sheet, 0, false); - - String info = widthManual + "/" + widthBeforeCell + "/" + widthBeforeCol + "/" + - SheetUtil.canComputeColumnWidth(font) + "/" + computeCellWidthFixed(font, "1") + "/" + computeCellWidthFixed(font, "w") + "/" + - computeCellWidthFixed(font, "1w") + "/" + computeCellWidthFixed(font, "0000") + "/" + computeCellWidthFixed(font, longValue); - assertTrue("Expected to have cell width > 0 when computing manually, but had " + info, widthManual > 0); - assertTrue("Expected to have cell width > 0 BEFORE auto-size, but had " + info, widthBeforeCell > 0); - assertTrue("Expected to have column width > 0 BEFORE auto-size, but had " + info, widthBeforeCol > 0); - - sheet.autoSizeColumn(0); - - double width = SheetUtil.getColumnWidth(sheet, 0, false); - assertTrue("Expected to have column width > 0 AFTER auto-size, but had " + width, width > 0); - width = SheetUtil.getCellWidth(cell0, 8, null, false); - assertTrue("Expected to have cell width > 0 AFTER auto-size, but had " + width, width > 0); - - assertEquals(255*256, sheet.getColumnWidth(0)); // maximum column width is 255 characters - sheet.setColumnWidth(0, sheet.getColumnWidth(0)); // Bug 50681 reports exception at this point - wb.close(); + double widthManual = computeCellWidthManually(cell0, font); + double widthBeforeCell = SheetUtil.getCellWidth(cell0, 8, null, false); + double widthBeforeCol = SheetUtil.getColumnWidth(sheet, 0, false); + + String info = widthManual + "/" + widthBeforeCell + "/" + widthBeforeCol + "/" + + SheetUtil.canComputeColumnWidth(font) + "/" + computeCellWidthFixed(font, "1") + "/" + computeCellWidthFixed(font, "w") + "/" + + computeCellWidthFixed(font, "1w") + "/" + computeCellWidthFixed(font, "0000") + "/" + computeCellWidthFixed(font, longValue); + assertTrue("Expected to have cell width > 0 when computing manually, but had " + info, widthManual > 0); + assertTrue("Expected to have cell width > 0 BEFORE auto-size, but had " + info, widthBeforeCell > 0); + assertTrue("Expected to have column width > 0 BEFORE auto-size, but had " + info, widthBeforeCol > 0); + + sheet.autoSizeColumn(0); + + double width = SheetUtil.getColumnWidth(sheet, 0, false); + assertTrue("Expected to have column width > 0 AFTER auto-size, but had " + width, width > 0); + width = SheetUtil.getCellWidth(cell0, 8, null, false); + assertTrue("Expected to have cell width > 0 AFTER auto-size, but had " + width, width > 0); + + assertEquals(255 * 256, sheet.getColumnWidth(0)); // maximum column width is 255 characters + sheet.setColumnWidth(0, sheet.getColumnWidth(0)); // Bug 50681 reports exception at this point + } } - + @Test public final void bug51622_testAutoSizeShouldRecognizeLeadingSpaces() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - Sheet sheet = wb.createSheet(); - _testDataProvider.trackAllColumnsForAutosizing(sheet); - Row row = sheet.createRow(0); - Cell cell0 = row.createCell(0); - Cell cell1 = row.createCell(1); - Cell cell2 = row.createCell(2); - - cell0.setCellValue("Test Column AutoSize"); - cell1.setCellValue(" Test Column AutoSize"); - cell2.setCellValue("Test Column AutoSize "); - - sheet.autoSizeColumn(0); - sheet.autoSizeColumn(1); - sheet.autoSizeColumn(2); - - int noWhitespaceColWidth = sheet.getColumnWidth(0); - int leadingWhitespaceColWidth = sheet.getColumnWidth(1); - int trailingWhitespaceColWidth = sheet.getColumnWidth(2); - - // Based on the amount of text and whitespace used, and the default font - // assume that the cell with whitespace should be at least 20% wider than - // the cell without whitespace. This number is arbitrary, but should be large - // enough to guarantee that the whitespace cell isn't wider due to chance. - // Experimentally, I calculated the ratio as 1.2478181, though this ratio may change - // if the default font or margins change. - final double expectedRatioThreshold = 1.2f; - double leadingWhitespaceRatio = ((double) leadingWhitespaceColWidth)/noWhitespaceColWidth; - double trailingWhitespaceRatio = ((double) leadingWhitespaceColWidth)/noWhitespaceColWidth; - - assertGreaterThan("leading whitespace is longer than no whitespace", - leadingWhitespaceRatio, expectedRatioThreshold); - assertGreaterThan("trailing whitespace is longer than no whitespace", - trailingWhitespaceRatio, expectedRatioThreshold); - assertEquals("cells with equal leading and trailing whitespace have equal width", - leadingWhitespaceColWidth, trailingWhitespaceColWidth); - - wb.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + Sheet sheet = wb.createSheet(); + _testDataProvider.trackAllColumnsForAutosizing(sheet); + Row row = sheet.createRow(0); + Cell cell0 = row.createCell(0); + Cell cell1 = row.createCell(1); + Cell cell2 = row.createCell(2); + + cell0.setCellValue("Test Column AutoSize"); + cell1.setCellValue(" Test Column AutoSize"); + cell2.setCellValue("Test Column AutoSize "); + + sheet.autoSizeColumn(0); + sheet.autoSizeColumn(1); + sheet.autoSizeColumn(2); + + int noWhitespaceColWidth = sheet.getColumnWidth(0); + int leadingWhitespaceColWidth = sheet.getColumnWidth(1); + int trailingWhitespaceColWidth = sheet.getColumnWidth(2); + + // Based on the amount of text and whitespace used, and the default font + // assume that the cell with whitespace should be at least 20% wider than + // the cell without whitespace. This number is arbitrary, but should be large + // enough to guarantee that the whitespace cell isn't wider due to chance. + // Experimentally, I calculated the ratio as 1.2478181, though this ratio may change + // if the default font or margins change. + final double expectedRatioThreshold = 1.2f; + double leadingWhitespaceRatio = ((double) leadingWhitespaceColWidth) / noWhitespaceColWidth; + double trailingWhitespaceRatio = ((double) leadingWhitespaceColWidth) / noWhitespaceColWidth; + + assertGreaterThan("leading whitespace is longer than no whitespace", + leadingWhitespaceRatio, expectedRatioThreshold); + assertGreaterThan("trailing whitespace is longer than no whitespace", + trailingWhitespaceRatio, expectedRatioThreshold); + assertEquals("cells with equal leading and trailing whitespace have equal width", + leadingWhitespaceColWidth, trailingWhitespaceColWidth); + + } } - + /** * Test if a > b. Fails if false. */ @@ -484,7 +486,7 @@ public abstract class BaseTestBugzillaIssues { } // FIXME: this function is a self-fulfilling prophecy: this test will always pass as long - // as the code-under-test and the testcase code are written the same way (have the same bugs). + // as the code-under-test and the testcase code are written the same way (have the same bugs). private double computeCellWidthManually(Cell cell0, Font font) { final FontRenderContext fontRenderContext = new FontRenderContext(null, true, true); RichTextString rt = cell0.getRichStringCellValue(); @@ -503,7 +505,7 @@ public abstract class BaseTestBugzillaIssues { double frameWidth = getFrameWidth(layout); return ((frameWidth / 1) / 8); } - + private double getFrameWidth(TextLayout layout) { Rectangle2D bounds = layout.getBounds(); return bounds.getX() + bounds.getWidth(); @@ -537,52 +539,52 @@ public abstract class BaseTestBugzillaIssues { */ @Test public void bug49381() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - int colSplit = 1; - int rowSplit = 2; - int leftmostColumn = 3; - int topRow = 4; - - Sheet s = wb.createSheet(); - - // Populate - for(int rn=0; rn<= topRow; rn++) { - Row r = s.createRow(rn); - for(int cn=0; cn drawing = sheet.createDrawingPatriarch(); - ClientAnchor anchor = factory.createClientAnchor(); - - Cell cell0 = sheet.createRow(0).createCell(0); - cell0.setCellValue("Cell0"); - - Comment comment0 = drawing.createCellComment(anchor); - RichTextString str0 = factory.createRichTextString("Hello, World1!"); - comment0.setString(str0); - comment0.setAuthor("Apache POI"); - cell0.setCellComment(comment0); - - anchor = factory.createClientAnchor(); - anchor.setCol1(1); - anchor.setCol2(1); - anchor.setRow1(1); - anchor.setRow2(1); - Cell cell1 = sheet.createRow(3).createCell(5); - cell1.setCellValue("F4"); - Comment comment1 = drawing.createCellComment(anchor); - RichTextString str1 = factory.createRichTextString("Hello, World2!"); - comment1.setString(str1); - comment1.setAuthor("Apache POI"); - cell1.setCellComment(comment1); - - Cell cell2 = sheet.createRow(2).createCell(2); - cell2.setCellValue("C3"); - - anchor = factory.createClientAnchor(); - anchor.setCol1(2); - anchor.setCol2(2); - anchor.setRow1(2); - anchor.setRow2(2); - - Comment comment2 = drawing.createCellComment(anchor); - RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); - //apply custom font to the text in the comment - Font font = wb.createFont(); - font.setFontName("Arial"); - font.setFontHeightInPoints((short)14); - font.setBold(true); - font.setColor(IndexedColors.RED.getIndex()); - str2.applyFont(font); - - comment2.setString(str2); - comment2.setAuthor("Apache POI"); - comment2.setColumn(2); - comment2.setRow(2); - - wb.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + + CreationHelper factory = wb.getCreationHelper(); + + Sheet sheet = wb.createSheet(); + Drawing drawing = sheet.createDrawingPatriarch(); + ClientAnchor anchor = factory.createClientAnchor(); + + Cell cell0 = sheet.createRow(0).createCell(0); + cell0.setCellValue("Cell0"); + + Comment comment0 = drawing.createCellComment(anchor); + RichTextString str0 = factory.createRichTextString("Hello, World1!"); + comment0.setString(str0); + comment0.setAuthor("Apache POI"); + cell0.setCellComment(comment0); + + anchor = factory.createClientAnchor(); + anchor.setCol1(1); + anchor.setCol2(1); + anchor.setRow1(1); + anchor.setRow2(1); + Cell cell1 = sheet.createRow(3).createCell(5); + cell1.setCellValue("F4"); + Comment comment1 = drawing.createCellComment(anchor); + RichTextString str1 = factory.createRichTextString("Hello, World2!"); + comment1.setString(str1); + comment1.setAuthor("Apache POI"); + cell1.setCellComment(comment1); + + Cell cell2 = sheet.createRow(2).createCell(2); + cell2.setCellValue("C3"); + + anchor = factory.createClientAnchor(); + anchor.setCol1(2); + anchor.setCol2(2); + anchor.setRow1(2); + anchor.setRow2(2); + + Comment comment2 = drawing.createCellComment(anchor); + RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); + //apply custom font to the text in the comment + Font font = wb.createFont(); + font.setFontName("Arial"); + font.setFontHeightInPoints((short) 14); + font.setBold(true); + font.setColor(IndexedColors.RED.getIndex()); + str2.applyFont(font); + + comment2.setString(str2); + comment2.setAuthor("Apache POI"); + comment2.setColumn(2); + comment2.setRow(2); + } } /** @@ -1136,108 +1128,106 @@ public abstract class BaseTestBugzillaIssues { */ @Test public void bug47815() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - Sheet s = wb.createSheet(); - Row r = s.createRow(0); - - // Setup - Cell cn = r.createCell(0, CellType.NUMERIC); - cn.setCellValue(1.2); - Cell cs = r.createCell(1, CellType.STRING); - cs.setCellValue("Testing"); - - Cell cfn = r.createCell(2, CellType.FORMULA); - cfn.setCellFormula("A1"); - Cell cfs = r.createCell(3, CellType.FORMULA); - cfs.setCellFormula("B1"); - - FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); - assertEquals(CellType.NUMERIC, fe.evaluate(cfn).getCellType()); - assertEquals(CellType.STRING, fe.evaluate(cfs).getCellType()); - fe.evaluateFormulaCell(cfn); - fe.evaluateFormulaCell(cfs); - - // Now test - assertEquals(CellType.NUMERIC, cn.getCellType()); - assertEquals(CellType.STRING, cs.getCellType()); - assertEquals(CellType.FORMULA, cfn.getCellType()); - assertEquals(CellType.NUMERIC, cfn.getCachedFormulaResultType()); - assertEquals(CellType.FORMULA, cfs.getCellType()); - assertEquals(CellType.STRING, cfs.getCachedFormulaResultType()); - - // Different ways of retrieving - assertEquals(1.2, cn.getNumericCellValue(), 0); - try { - cn.getRichStringCellValue(); - fail(); - } catch(IllegalStateException e) { - // expected here - } + try (Workbook wb = _testDataProvider.createWorkbook()) { + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + + // Setup + Cell cn = r.createCell(0, CellType.NUMERIC); + cn.setCellValue(1.2); + Cell cs = r.createCell(1, CellType.STRING); + cs.setCellValue("Testing"); + + Cell cfn = r.createCell(2, CellType.FORMULA); + cfn.setCellFormula("A1"); + Cell cfs = r.createCell(3, CellType.FORMULA); + cfs.setCellFormula("B1"); + + FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); + assertEquals(CellType.NUMERIC, fe.evaluate(cfn).getCellType()); + assertEquals(CellType.STRING, fe.evaluate(cfs).getCellType()); + fe.evaluateFormulaCell(cfn); + fe.evaluateFormulaCell(cfs); + + // Now test + assertEquals(CellType.NUMERIC, cn.getCellType()); + assertEquals(CellType.STRING, cs.getCellType()); + assertEquals(CellType.FORMULA, cfn.getCellType()); + assertEquals(CellType.NUMERIC, cfn.getCachedFormulaResultType()); + assertEquals(CellType.FORMULA, cfs.getCellType()); + assertEquals(CellType.STRING, cfs.getCachedFormulaResultType()); + + // Different ways of retrieving + assertEquals(1.2, cn.getNumericCellValue(), 0); + try { + cn.getRichStringCellValue(); + fail(); + } catch (IllegalStateException e) { + // expected here + } - assertEquals("Testing", cs.getStringCellValue()); - try { - cs.getNumericCellValue(); - fail(); - } catch(IllegalStateException e) { - // expected here - } + assertEquals("Testing", cs.getStringCellValue()); + try { + cs.getNumericCellValue(); + fail(); + } catch (IllegalStateException e) { + // expected here + } - assertEquals(1.2, cfn.getNumericCellValue(), 0); - try { - cfn.getRichStringCellValue(); - fail(); - } catch(IllegalStateException e) { - // expected here - } + assertEquals(1.2, cfn.getNumericCellValue(), 0); + try { + cfn.getRichStringCellValue(); + fail(); + } catch (IllegalStateException e) { + // expected here + } - assertEquals("Testing", cfs.getStringCellValue()); - try { - cfs.getNumericCellValue(); - fail(); - } catch(IllegalStateException e) { - // expected here + assertEquals("Testing", cfs.getStringCellValue()); + try { + cfs.getNumericCellValue(); + fail(); + } catch (IllegalStateException e) { + // expected here + } } - - wb.close(); } @Test public void test58113() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - Sheet sheet = wb.createSheet( "Test" ); + try (Workbook wb = _testDataProvider.createWorkbook()) { + Sheet sheet = wb.createSheet("Test"); - Row row = sheet.createRow(0); + Row row = sheet.createRow(0); - Cell cell = row.createCell(0); - // verify that null-values can be set, this was possible up to 3.11, but broken in 3.12 - cell.setCellValue((String)null); - String value = cell.getStringCellValue(); - assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, - value == null || value.length() == 0); + Cell cell = row.createCell(0); + // verify that null-values can be set, this was possible up to 3.11, but broken in 3.12 + cell.setCellValue((String) null); + String value = cell.getStringCellValue(); + assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, + value == null || value.length() == 0); - cell = row.createCell(1); - cell.setCellFormula("0"); - cell.setCellValue((String)null); + cell = row.createCell(1); + cell.setCellFormula("0"); + cell.setCellValue((String) null); - wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); + wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - value = cell.getStringCellValue(); - assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, - value == null || value.length() == 0); + value = cell.getStringCellValue(); + assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, + value == null || value.length() == 0); - // set some value - cell.setCellValue("somevalue"); + // set some value + cell.setCellValue("somevalue"); - value = cell.getStringCellValue(); - assertEquals("can set value afterwards: " + value, "somevalue", value); + value = cell.getStringCellValue(); + assertEquals("can set value afterwards: " + value, "somevalue", value); - // verify that the null-value is actually set even if there was some value in the cell before - cell.setCellValue((String)null); - value = cell.getStringCellValue(); - assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, - value == null || value.length() == 0); - - wb.close(); + // verify that the null-value is actually set even if there was some value in the cell before + cell.setCellValue((String) null); + value = cell.getStringCellValue(); + assertTrue("HSSF will currently return empty string, XSSF/SXSSF will return null, but had: " + value, + value == null || value.length() == 0); + } } /** @@ -1246,277 +1236,268 @@ public abstract class BaseTestBugzillaIssues { */ @Test public void bug55747() throws IOException { - Workbook wb1 = _testDataProvider.createWorkbook(); - FormulaEvaluator ev = wb1.getCreationHelper().createFormulaEvaluator(); - Sheet s = wb1.createSheet(); - - Row row = s.createRow(0); - row.createCell(0).setCellValue("abc"); - row.createCell(1).setCellValue(""); - row.createCell(2).setCellValue(3); - - Cell cell = row.createCell(5); - cell.setCellFormula("IF(A1<>\"\",MID(A1,1,2),\" \")"); - ev.evaluateAll(); - assertEquals("ab", cell.getStringCellValue()); - - cell = row.createCell(6); - cell.setCellFormula("IF(B1<>\"\",MID(A1,1,2),\"empty\")"); - ev.evaluateAll(); - assertEquals("empty", cell.getStringCellValue()); - - cell = row.createCell(7); - cell.setCellFormula("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")"); - ev.evaluateAll(); - assertEquals("ab", cell.getStringCellValue()); - - // Write it back out, and re-read - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); - wb1.close(); - ev = wb2.getCreationHelper().createFormulaEvaluator(); - s = wb2.getSheetAt(0); - row = s.getRow(0); - - // Check read ok, and re-evaluate fine - cell = row.getCell(5); - assertEquals("ab", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula()); - ev.evaluateFormulaCell(cell); - assertEquals("ab", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula()); - - cell = row.getCell(6); - assertEquals("empty", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula()); - ev.evaluateFormulaCell(cell); - assertEquals("empty", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula()); - - cell = row.getCell(7); - assertEquals("ab", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula()); - ev.evaluateFormulaCell(cell); - assertEquals("ab", cell.getStringCellValue()); - assertEquals(CellType.FORMULA, cell.getCellType()); - assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula()); - wb2.close(); + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + FormulaEvaluator ev = wb1.getCreationHelper().createFormulaEvaluator(); + Sheet s = wb1.createSheet(); + + Row row = s.createRow(0); + row.createCell(0).setCellValue("abc"); + row.createCell(1).setCellValue(""); + row.createCell(2).setCellValue(3); + + Cell cell = row.createCell(5); + cell.setCellFormula("IF(A1<>\"\",MID(A1,1,2),\" \")"); + ev.evaluateAll(); + assertEquals("ab", cell.getStringCellValue()); + + cell = row.createCell(6); + cell.setCellFormula("IF(B1<>\"\",MID(A1,1,2),\"empty\")"); + ev.evaluateAll(); + assertEquals("empty", cell.getStringCellValue()); + + cell = row.createCell(7); + cell.setCellFormula("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")"); + ev.evaluateAll(); + assertEquals("ab", cell.getStringCellValue()); + + // Write it back out, and re-read + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + ev = wb2.getCreationHelper().createFormulaEvaluator(); + s = wb2.getSheetAt(0); + row = s.getRow(0); + + // Check read ok, and re-evaluate fine + cell = row.getCell(5); + assertEquals("ab", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula()); + ev.evaluateFormulaCell(cell); + assertEquals("ab", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula()); + + cell = row.getCell(6); + assertEquals("empty", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula()); + ev.evaluateFormulaCell(cell); + assertEquals("empty", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula()); + + cell = row.getCell(7); + assertEquals("ab", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula()); + ev.evaluateFormulaCell(cell); + assertEquals("ab", cell.getStringCellValue()); + assertEquals(CellType.FORMULA, cell.getCellType()); + assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula()); + } + } } @Test public void bug58260() throws IOException { //Create workbook and worksheet - Workbook wb = _testDataProvider.createWorkbook(); - //Sheet worksheet = wb.createSheet("sample"); - - //Loop through and add all values from array list - // use a fixed seed to always produce the same file which makes comparing stuff easier - //Random rnd = new Random(4352345); - int maxStyles = (wb instanceof HSSFWorkbook) ? 4009 : 64000; - for(int i = 0;i < maxStyles;i++) { - //Create new row - //Row row = worksheet.createRow(i); - - //Create cell style - CellStyle style = null; - try { - style = wb.createCellStyle(); - } catch (IllegalStateException e) { - fail("Failed for row " + i); - } - style.setAlignment(HorizontalAlignment.RIGHT); - if((wb instanceof HSSFWorkbook)) { - // there are some predefined styles - assertEquals(i+21, style.getIndex()); - } else { - // getIndex() returns short, which is not sufficient for > 32767 - // we should really change the API to be "int" for getIndex() but - // that needs API changes - assertEquals(i+1, style.getIndex() & 0xffff); - } + try (Workbook wb = _testDataProvider.createWorkbook()) { + //Sheet worksheet = wb.createSheet("sample"); + + //Loop through and add all values from array list + // use a fixed seed to always produce the same file which makes comparing stuff easier + //Random rnd = new Random(4352345); + int maxStyles = (wb instanceof HSSFWorkbook) ? 4009 : 64000; + for (int i = 0; i < maxStyles; i++) { + //Create new row + //Row row = worksheet.createRow(i); + + //Create cell style + CellStyle style = null; + try { + style = wb.createCellStyle(); + } catch (IllegalStateException e) { + fail("Failed for row " + i); + } + style.setAlignment(HorizontalAlignment.RIGHT); + if ((wb instanceof HSSFWorkbook)) { + // there are some predefined styles + assertEquals(i + 21, style.getIndex()); + } else { + // getIndex() returns short, which is not sufficient for > 32767 + // we should really change the API to be "int" for getIndex() but + // that needs API changes + assertEquals(i + 1, style.getIndex() & 0xffff); + } - //Create cell - //Cell cell = row.createCell(0); + //Create cell + //Cell cell = row.createCell(0); - //Set cell style - //cell.setCellStyle(style); + //Set cell style + //cell.setCellStyle(style); - //Set cell value - //cell.setCellValue("r" + rnd.nextInt()); - } + //Set cell value + //cell.setCellValue("r" + rnd.nextInt()); + } - // should fail if we try to add more now - try { - wb.createCellStyle(); - fail("Should fail after " + maxStyles + " styles, but did not fail"); - } catch (IllegalStateException e) { - // expected here - } + // should fail if we try to add more now + try { + wb.createCellStyle(); + fail("Should fail after " + maxStyles + " styles, but did not fail"); + } catch (IllegalStateException e) { + // expected here + } - /*//add column width for appearance sake - worksheet.setColumnWidth(0, 5000); + /*//add column width for appearance sake + worksheet.setColumnWidth(0, 5000); - // Write the output to a file - System.out.println("Writing..."); - OutputStream fileOut = new FileOutputStream("C:\\temp\\58260." + _testDataProvider.getStandardFileNameExtension()); + // Write the output to a file + System.out.println("Writing..."); + OutputStream fileOut = new FileOutputStream("C:\\temp\\58260." + _testDataProvider.getStandardFileNameExtension()); - // the resulting file can be compressed nicely, so we need to disable the zip bomb detection here - double before = ZipSecureFile.getMinInflateRatio(); - try { - ZipSecureFile.setMinInflateRatio(0.00001); - wb.write(fileOut); - } finally { - fileOut.close(); - ZipSecureFile.setMinInflateRatio(before); - }*/ + // the resulting file can be compressed nicely, so we need to disable the zip bomb detection here + double before = ZipSecureFile.getMinInflateRatio(); + try { + ZipSecureFile.setMinInflateRatio(0.00001); + wb.write(fileOut); + } finally { + fileOut.close(); + ZipSecureFile.setMinInflateRatio(before); + }*/ - wb.close(); + } } @Test public void test50319() throws IOException { - Workbook wb = new HSSFWorkbook(); - Sheet sheet = wb.createSheet("Test"); - sheet.createRow(0); - sheet.groupRow(0, 0); - sheet.setRowGroupCollapsed(0, true); - - sheet.groupColumn(0, 0); - sheet.setColumnGroupCollapsed(0, true); - - wb.close(); + try (Workbook wb = new HSSFWorkbook()) { + Sheet sheet = wb.createSheet("Test"); + sheet.createRow(0); + sheet.groupRow(0, 0); + sheet.setRowGroupCollapsed(0, true); + + sheet.groupColumn(0, 0); + sheet.setColumnGroupCollapsed(0, true); + } } - + // Bug 58648: FormulaParser throws exception in parseSimpleFactor() when getCellFormula() // is called on a cell and the formula contains spaces between closing parentheses ") )" - // https://bz.apache.org/bugzilla/show_bug.cgi?id=58648 @Test public void test58648() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - Cell cell = wb.createSheet().createRow(0).createCell(0); - cell.setCellFormula("((1 + 1) )"); - // fails with - // org.apache.poi.ss.formula.FormulaParseException: Parse error near char ... ')' - // in specified formula '((1 + 1) )'. Expected cell ref or constant literal - - wb.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + Cell cell = wb.createSheet().createRow(0).createCell(0); + cell.setCellFormula("((1 + 1) )"); + } } - + /** * If someone sets a null string as a cell value, treat * it as an empty cell, and avoid a NPE on auto-sizing */ @Test public void test57034() throws Exception { - Workbook wb = _testDataProvider.createWorkbook(); - Sheet s = wb.createSheet(); - Cell cell = s.createRow(0).createCell(0); - cell.setCellValue((String)null); - assertEquals(CellType.BLANK, cell.getCellType()); - - _testDataProvider.trackAllColumnsForAutosizing(s); - - s.autoSizeColumn(0); - assertEquals(2048, s.getColumnWidth(0)); - - s.autoSizeColumn(0, true); - assertEquals(2048, s.getColumnWidth(0)); - - wb.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + Sheet s = wb.createSheet(); + Cell cell = s.createRow(0).createCell(0); + cell.setCellValue((String) null); + assertEquals(CellType.BLANK, cell.getCellType()); + + _testDataProvider.trackAllColumnsForAutosizing(s); + + s.autoSizeColumn(0); + assertEquals(2048, s.getColumnWidth(0)); + + s.autoSizeColumn(0, true); + assertEquals(2048, s.getColumnWidth(0)); + } } @Test public void test52684() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - - Sheet sheet = wb.createSheet("test"); - Row row = sheet.createRow(0); - Cell cell = row.createCell(0); + try (Workbook wb = _testDataProvider.createWorkbook()) { - cell.setCellValue(12312345123L); + Sheet sheet = wb.createSheet("test"); + Row row = sheet.createRow(0); + Cell cell = row.createCell(0); - DataFormat format = wb.createDataFormat(); - CellStyle style = wb.createCellStyle(); - style.setDataFormat(format.getFormat("000-00000-000")); - cell.setCellStyle(style); + cell.setCellValue(12312345123L); - assertEquals("000-00000-000", - cell.getCellStyle().getDataFormatString()); - assertEquals(164, cell.getCellStyle().getDataFormat()); + DataFormat format = wb.createDataFormat(); + CellStyle style = wb.createCellStyle(); + style.setDataFormat(format.getFormat("000-00000-000")); + cell.setCellStyle(style); - DataFormatter formatter = new DataFormatter(); + assertEquals("000-00000-000", + cell.getCellStyle().getDataFormatString()); + assertEquals(164, cell.getCellStyle().getDataFormat()); - assertEquals("12-312-345-123", formatter.formatCellValue(cell)); + DataFormatter formatter = new DataFormatter(); - wb.close(); + assertEquals("12-312-345-123", formatter.formatCellValue(cell)); + } } - + @Test public void test58896() throws IOException { final int nrows = 160; final int ncols = 139; - + // Create a workbook - final Workbook wb = _testDataProvider.createWorkbook(nrows+1); - final Sheet sh = wb.createSheet(); - if (logger.check(POILogger.DEBUG)) { - logger.log(POILogger.DEBUG, wb.getClass().getName() + " column autosizing timing..."); - } + try (Workbook wb = _testDataProvider.createWorkbook(nrows+1)) { + final Sheet sh = wb.createSheet(); + if (logger.check(POILogger.DEBUG)) { + logger.log(POILogger.DEBUG, wb.getClass().getName() + " column autosizing timing..."); + } - final long t0 = time(); - _testDataProvider.trackAllColumnsForAutosizing(sh); - for (int r=0; r 50000) { // fail("Best fit width time per cell exceeded 50000 ns: " + bestFitWidthTimePerCell_ns + " ns"); //} - + //if (totalTime_s > 10) { // fail("Total time exceeded 10 seconds: " + totalTime_s + " s"); //} } - + protected long time() { return System.currentTimeMillis(); } @@ -1524,117 +1505,116 @@ public abstract class BaseTestBugzillaIssues { protected double delta(long startTimeMillis) { return time() - startTimeMillis; } - + @Ignore("bug 59393") @Test public void bug59393_commentsCanHaveSameAnchor() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - - Sheet sheet = wb.createSheet(); - - CreationHelper helper = wb.getCreationHelper(); - ClientAnchor anchor = helper.createClientAnchor(); - Drawing drawing = sheet.createDrawingPatriarch(); - - Row row = sheet.createRow(0); - - Cell cell1 = row.createCell(0); - Cell cell2 = row.createCell(1); - Cell cell3 = row.createCell(2); - - Comment comment1 = drawing.createCellComment(anchor); - RichTextString richTextString1 = helper.createRichTextString("comment1"); - comment1.setString(richTextString1); - cell1.setCellComment(comment1); - - // fails with IllegalArgumentException("Multiple cell comments in one cell are not allowed, cell: A1") - // because createCellComment tries to create a cell at A1 - // (from CellAddress(anchor.getRow1(), anchor.getCell1())), - // but cell A1 already has a comment (comment1). - // Need to atomically create a comment and attach it to a cell. - // Current workaround: change anchor between each usage - // anchor.setCol1(1); - Comment comment2 = drawing.createCellComment(anchor); - RichTextString richTextString2 = helper.createRichTextString("comment2"); - comment2.setString(richTextString2); - cell2.setCellComment(comment2); - - // anchor.setCol1(2); - Comment comment3 = drawing.createCellComment(anchor); - RichTextString richTextString3 = helper.createRichTextString("comment3"); - comment3.setString(richTextString3); - cell3.setCellComment(comment3); - - wb.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + + Sheet sheet = wb.createSheet(); + + CreationHelper helper = wb.getCreationHelper(); + ClientAnchor anchor = helper.createClientAnchor(); + Drawing drawing = sheet.createDrawingPatriarch(); + + Row row = sheet.createRow(0); + + Cell cell1 = row.createCell(0); + Cell cell2 = row.createCell(1); + Cell cell3 = row.createCell(2); + + Comment comment1 = drawing.createCellComment(anchor); + RichTextString richTextString1 = helper.createRichTextString("comment1"); + comment1.setString(richTextString1); + cell1.setCellComment(comment1); + + // fails with IllegalArgumentException("Multiple cell comments in one cell are not allowed, cell: A1") + // because createCellComment tries to create a cell at A1 + // (from CellAddress(anchor.getRow1(), anchor.getCell1())), + // but cell A1 already has a comment (comment1). + // Need to atomically create a comment and attach it to a cell. + // Current workaround: change anchor between each usage + // anchor.setCol1(1); + Comment comment2 = drawing.createCellComment(anchor); + RichTextString richTextString2 = helper.createRichTextString("comment2"); + comment2.setString(richTextString2); + cell2.setCellComment(comment2); + + // anchor.setCol1(2); + Comment comment3 = drawing.createCellComment(anchor); + RichTextString richTextString3 = helper.createRichTextString("comment3"); + comment3.setString(richTextString3); + cell3.setCellComment(comment3); + + } } @Test public void bug57798() throws Exception { String fileName = "57798." + _testDataProvider.getStandardFileNameExtension(); - Workbook workbook = _testDataProvider.openSampleWorkbook(fileName); + try (Workbook workbook = _testDataProvider.openSampleWorkbook(fileName)) { - Sheet sheet = workbook.getSheet("Sheet1"); + Sheet sheet = workbook.getSheet("Sheet1"); - // ******************************* - // First cell of array formula, OK - int rowId = 0; - int cellId = 1; + // ******************************* + // First cell of array formula, OK + int rowId = 0; + int cellId = 1; - Row row = sheet.getRow(rowId); - Cell cell = row.getCell(cellId); + Row row = sheet.getRow(rowId); + Cell cell = row.getCell(cellId); - assertEquals("A1", cell.getCellFormula()); - if (CellType.FORMULA == cell.getCellType()) { - CellType formulaResultType = cell.getCachedFormulaResultType(); - assertEquals(CellType.STRING, formulaResultType); - } + assertEquals("A1", cell.getCellFormula()); + if (CellType.FORMULA == cell.getCellType()) { + CellType formulaResultType = cell.getCachedFormulaResultType(); + assertEquals(CellType.STRING, formulaResultType); + } - // ******************************* - // Second cell of array formula, NOT OK for xlsx files - rowId = 1; - cellId = 1; + // ******************************* + // Second cell of array formula, NOT OK for xlsx files + rowId = 1; + cellId = 1; - row = sheet.getRow(rowId); - cell = row.getCell(cellId); - assertEquals("A1", cell.getCellFormula()); + row = sheet.getRow(rowId); + cell = row.getCell(cellId); + assertEquals("A1", cell.getCellFormula()); - if (CellType.FORMULA == cell.getCellType()) { - CellType formulaResultType = cell.getCachedFormulaResultType(); - assertEquals(CellType.STRING, formulaResultType); + if (CellType.FORMULA == cell.getCellType()) { + CellType formulaResultType = cell.getCachedFormulaResultType(); + assertEquals(CellType.STRING, formulaResultType); + } } - - workbook.close(); } @Ignore @Test public void test57929() throws IOException { // Create a workbook with print areas on 2 sheets - Workbook wb = _testDataProvider.createWorkbook(); - wb.createSheet("Sheet0"); - wb.createSheet("Sheet1"); - wb.setPrintArea(0, "$A$1:$C$6"); - wb.setPrintArea(1, "$B$1:$C$5"); - - // Verify the print areas were set correctly - assertEquals("Sheet0!$A$1:$C$6", wb.getPrintArea(0)); - assertEquals("Sheet1!$B$1:$C$5", wb.getPrintArea(1)); - - // Remove the print area on Sheet0 and change the print area on Sheet1 - wb.removePrintArea(0); - wb.setPrintArea(1, "$A$1:$A$1"); - - // Verify that the changes were made - assertNull("Sheet0 before write", wb.getPrintArea(0)); - assertEquals("Sheet1 before write", "Sheet1!$A$1:$A$1", wb.getPrintArea(1)); - - // Verify that the changes are non-volatile - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb); - wb.close(); - - assertNull("Sheet0 after write", wb2.getPrintArea(0)); // CURRENTLY FAILS with "Sheet0!$A$1:$C$6" - assertEquals("Sheet1 after write", "Sheet1!$A$1:$A$1", wb2.getPrintArea(1)); + try (Workbook wb = _testDataProvider.createWorkbook()) { + wb.createSheet("Sheet0"); + wb.createSheet("Sheet1"); + wb.setPrintArea(0, "$A$1:$C$6"); + wb.setPrintArea(1, "$B$1:$C$5"); + + // Verify the print areas were set correctly + assertEquals("Sheet0!$A$1:$C$6", wb.getPrintArea(0)); + assertEquals("Sheet1!$B$1:$C$5", wb.getPrintArea(1)); + + // Remove the print area on Sheet0 and change the print area on Sheet1 + wb.removePrintArea(0); + wb.setPrintArea(1, "$A$1:$A$1"); + + // Verify that the changes were made + assertNull("Sheet0 before write", wb.getPrintArea(0)); + assertEquals("Sheet1 before write", "Sheet1!$A$1:$A$1", wb.getPrintArea(1)); + + // Verify that the changes are non-volatile + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb)) { + assertNull("Sheet0 after write", wb2.getPrintArea(0)); // CURRENTLY FAILS with "Sheet0!$A$1:$C$6" + assertEquals("Sheet1 after write", "Sheet1!$A$1:$A$1", wb2.getPrintArea(1)); + } + } } @@ -1672,18 +1652,9 @@ public abstract class BaseTestBugzillaIssues { assertEquals("SUM(B1:B10)", cell2.getCellFormula()); assertEquals("SUM(C1:C10)", cell3.getCellFormula()); - /*String name = wb.getClass().getCanonicalName(); - String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx"; - OutputStream output = new FileOutputStream("/tmp" + name + ext); - try { - wb.write(output); - } finally { - output.close(); - }*/ - - Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb); - checkFormulaPreevaluatedString(wbBack); - wbBack.close(); + try (Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb)) { + checkFormulaPreevaluatedString(wbBack); + } } } @@ -1710,151 +1681,140 @@ public abstract class BaseTestBugzillaIssues { assertNotNull(cellValue); } } - + // bug 60197: setSheetOrder should update sheet-scoped named ranges to maintain references to the sheets before the re-order @Test public void bug60197_NamedRangesReferToCorrectSheetWhenSheetOrderIsChanged() throws Exception { - Workbook wb = _testDataProvider.createWorkbook(); - Sheet sheet1 = wb.createSheet("Sheet1"); - Sheet sheet2 = wb.createSheet("Sheet2"); - Sheet sheet3 = wb.createSheet("Sheet3"); - - Name nameOnSheet1 = wb.createName(); - nameOnSheet1.setSheetIndex(wb.getSheetIndex(sheet1)); - nameOnSheet1.setNameName("NameOnSheet1"); - nameOnSheet1.setRefersToFormula("Sheet1!A1"); - - Name nameOnSheet2 = wb.createName(); - nameOnSheet2.setSheetIndex(wb.getSheetIndex(sheet2)); - nameOnSheet2.setNameName("NameOnSheet2"); - nameOnSheet2.setRefersToFormula("Sheet2!A1"); - - Name nameOnSheet3 = wb.createName(); - nameOnSheet3.setSheetIndex(wb.getSheetIndex(sheet3)); - nameOnSheet3.setNameName("NameOnSheet3"); - nameOnSheet3.setRefersToFormula("Sheet3!A1"); - - // workbook-scoped name - Name name = wb.createName(); - name.setNameName("WorkbookScopedName"); - name.setRefersToFormula("Sheet2!A1"); - - assertEquals("Sheet1", nameOnSheet1.getSheetName()); - assertEquals("Sheet2", nameOnSheet2.getSheetName()); - assertEquals("Sheet3", nameOnSheet3.getSheetName()); - assertEquals(-1, name.getSheetIndex()); - assertEquals("Sheet2!A1", name.getRefersToFormula()); - - // rearrange the sheets several times to make sure the names always refer to the right sheet - for (int i=0; i<=9; i++) { - wb.setSheetOrder("Sheet3", i % 3); - - // Current bug in XSSF: - // Call stack: - // XSSFWorkbook.write(OutputStream) - // XSSFWorkbook.commit() - // XSSFWorkbook.saveNamedRanges() - // This dumps the current namedRanges to CTDefinedName and writes these to the CTWorkbook - // Then the XSSFName namedRanges list is cleared and rebuilt - // Thus, any XSSFName object becomes invalid after a write - // This re-assignment to the XSSFNames is not necessary if wb.write is not called. - nameOnSheet1 = wb.getName("NameOnSheet1"); - nameOnSheet2 = wb.getName("NameOnSheet2"); - nameOnSheet3 = wb.getName("NameOnSheet3"); - name = wb.getName("WorkbookScopedName"); - - // The name should still refer to the same sheet after the sheets are re-ordered - assertEquals(i % 3, wb.getSheetIndex("Sheet3")); - assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); - assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); - assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); - assertEquals(name.getNameName(), -1, name.getSheetIndex()); - assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); - - // make sure the changes to the names stick after writing out the workbook - Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb); - - // See note above. XSSFNames become invalid after workbook write - // Without reassignment here, an XmlValueDisconnectedException may occur - nameOnSheet1 = wb.getName("NameOnSheet1"); - nameOnSheet2 = wb.getName("NameOnSheet2"); - nameOnSheet3 = wb.getName("NameOnSheet3"); - name = wb.getName("WorkbookScopedName"); - - // Saving the workbook should not change the sheet names - assertEquals(i % 3, wb.getSheetIndex("Sheet3")); - assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); - assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); - assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); - assertEquals(name.getNameName(), -1, name.getSheetIndex()); - assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); - - // Verify names in wb2 - nameOnSheet1 = wb2.getName("NameOnSheet1"); - nameOnSheet2 = wb2.getName("NameOnSheet2"); - nameOnSheet3 = wb2.getName("NameOnSheet3"); - name = wb2.getName("WorkbookScopedName"); - - assertEquals(i % 3, wb2.getSheetIndex("Sheet3")); - assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); - assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); - assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); - assertEquals(name.getNameName(), -1, name.getSheetIndex()); - assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); - - wb2.close(); + try (Workbook wb = _testDataProvider.createWorkbook()) { + Sheet sheet1 = wb.createSheet("Sheet1"); + Sheet sheet2 = wb.createSheet("Sheet2"); + Sheet sheet3 = wb.createSheet("Sheet3"); + + Name nameOnSheet1 = wb.createName(); + nameOnSheet1.setSheetIndex(wb.getSheetIndex(sheet1)); + nameOnSheet1.setNameName("NameOnSheet1"); + nameOnSheet1.setRefersToFormula("Sheet1!A1"); + + Name nameOnSheet2 = wb.createName(); + nameOnSheet2.setSheetIndex(wb.getSheetIndex(sheet2)); + nameOnSheet2.setNameName("NameOnSheet2"); + nameOnSheet2.setRefersToFormula("Sheet2!A1"); + + Name nameOnSheet3 = wb.createName(); + nameOnSheet3.setSheetIndex(wb.getSheetIndex(sheet3)); + nameOnSheet3.setNameName("NameOnSheet3"); + nameOnSheet3.setRefersToFormula("Sheet3!A1"); + + // workbook-scoped name + Name name = wb.createName(); + name.setNameName("WorkbookScopedName"); + name.setRefersToFormula("Sheet2!A1"); + + assertEquals("Sheet1", nameOnSheet1.getSheetName()); + assertEquals("Sheet2", nameOnSheet2.getSheetName()); + assertEquals("Sheet3", nameOnSheet3.getSheetName()); + assertEquals(-1, name.getSheetIndex()); + assertEquals("Sheet2!A1", name.getRefersToFormula()); + + // rearrange the sheets several times to make sure the names always refer to the right sheet + for (int i = 0; i <= 9; i++) { + wb.setSheetOrder("Sheet3", i % 3); + + // Current bug in XSSF: + // Call stack: + // XSSFWorkbook.write(OutputStream) + // XSSFWorkbook.commit() + // XSSFWorkbook.saveNamedRanges() + // This dumps the current namedRanges to CTDefinedName and writes these to the CTWorkbook + // Then the XSSFName namedRanges list is cleared and rebuilt + // Thus, any XSSFName object becomes invalid after a write + // This re-assignment to the XSSFNames is not necessary if wb.write is not called. + nameOnSheet1 = wb.getName("NameOnSheet1"); + nameOnSheet2 = wb.getName("NameOnSheet2"); + nameOnSheet3 = wb.getName("NameOnSheet3"); + name = wb.getName("WorkbookScopedName"); + + // The name should still refer to the same sheet after the sheets are re-ordered + assertEquals(i % 3, wb.getSheetIndex("Sheet3")); + assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); + assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); + assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); + assertEquals(name.getNameName(), -1, name.getSheetIndex()); + assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); + + // make sure the changes to the names stick after writing out the workbook + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb)) { + + // See note above. XSSFNames become invalid after workbook write + // Without reassignment here, an XmlValueDisconnectedException may occur + nameOnSheet1 = wb2.getName("NameOnSheet1"); + nameOnSheet2 = wb2.getName("NameOnSheet2"); + nameOnSheet3 = wb2.getName("NameOnSheet3"); + name = wb2.getName("WorkbookScopedName"); + + // Saving the workbook should not change the sheet names + assertEquals(i % 3, wb2.getSheetIndex("Sheet3")); + assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); + assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); + assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); + assertEquals(name.getNameName(), -1, name.getSheetIndex()); + assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); + + // Verify names in wb2 + nameOnSheet1 = wb2.getName("NameOnSheet1"); + nameOnSheet2 = wb2.getName("NameOnSheet2"); + nameOnSheet3 = wb2.getName("NameOnSheet3"); + name = wb2.getName("WorkbookScopedName"); + + assertEquals(i % 3, wb2.getSheetIndex("Sheet3")); + assertEquals(nameOnSheet1.getNameName(), "Sheet1", nameOnSheet1.getSheetName()); + assertEquals(nameOnSheet2.getNameName(), "Sheet2", nameOnSheet2.getSheetName()); + assertEquals(nameOnSheet3.getNameName(), "Sheet3", nameOnSheet3.getSheetName()); + assertEquals(name.getNameName(), -1, name.getSheetIndex()); + assertEquals(name.getNameName(), "Sheet2!A1", name.getRefersToFormula()); + } + } } - - wb.close(); } @Test public void test59200() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - final Sheet sheet = wb.createSheet(); - - DataValidation dataValidation; - CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1); - DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\""); - - dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell); + try (Workbook wb = _testDataProvider.createWorkbook()) { + final Sheet sheet = wb.createSheet(); - // HSSF has 32/255 limits as part of the Spec, XSSF has no limit in the spec, but Excel applies a 255 length limit! - // more than 255 fail for all - checkFailures(dataValidation, TEST_256, TEST_32, true); - checkFailures(dataValidation, TEST_32, TEST_256, true); + DataValidation dataValidation; + CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1); + DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\""); - // null does work - checkFailures(dataValidation, null, null, false); + dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell); - // more than 32 title fail for HSSFWorkbook - checkFailures(dataValidation, TEST_255, TEST_32, wb instanceof HSSFWorkbook); + // HSSF has 32/255 limits as part of the Spec, XSSF has no limit in the spec, but Excel applies a 255 length limit! + // more than 255 fail for all + checkFailures(dataValidation, TEST_256, TEST_32, true); + checkFailures(dataValidation, TEST_32, TEST_256, true); - // special characters work - checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false); + // null does work + checkFailures(dataValidation, null, null, false); - // 32 length title and 255 length text work for both - checkFailures(dataValidation, TEST_32, TEST_255, false); + // more than 32 title fail for HSSFWorkbook + checkFailures(dataValidation, TEST_255, TEST_32, wb instanceof HSSFWorkbook); - dataValidation.setShowErrorBox(false); - sheet.addValidationData(dataValidation); + // special characters work + checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false); - // write out and read back in to trigger some more validation - final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb); + // 32 length title and 255 length text work for both + checkFailures(dataValidation, TEST_32, TEST_255, false); - final Sheet sheetBack = wbBack.getSheetAt(0); - final List dataValidations = sheetBack.getDataValidations(); - assertEquals(1, dataValidations.size()); + dataValidation.setShowErrorBox(false); + sheet.addValidationData(dataValidation); - /*String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx"; - OutputStream str = new FileOutputStream("C:\\temp\\59200" + ext); - try { - wb.write(str); - } finally { - str.close(); - }*/ + // write out and read back in to trigger some more validation + final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb); - wb.close(); + final Sheet sheetBack = wbBack.getSheetAt(0); + final List dataValidations = sheetBack.getDataValidations(); + assertEquals(1, dataValidations.size()); + } } private void checkFailures(DataValidation dataValidation, String title, String text, boolean shouldFail) { @@ -1875,36 +1835,27 @@ public abstract class BaseTestBugzillaIssues { @Test public void test60370() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - final Sheet sheet = wb.createSheet(); - - DataValidation dataValidation; - CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1); - DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\""); - - dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell); - checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false); + try (Workbook wb = _testDataProvider.createWorkbook()) { + final Sheet sheet = wb.createSheet(); - dataValidation.setShowErrorBox(true); - dataValidation.setShowPromptBox(true); - sheet.addValidationData(dataValidation); + DataValidation dataValidation; + CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1); + DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\""); - // write out and read back in to trigger some more validation - final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb); + dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell); + checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false); - final Sheet sheetBack = wbBack.getSheetAt(0); - final List dataValidations = sheetBack.getDataValidations(); - assertEquals(1, dataValidations.size()); + dataValidation.setShowErrorBox(true); + dataValidation.setShowPromptBox(true); + sheet.addValidationData(dataValidation); - /*String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx"; - OutputStream str = new FileOutputStream("/tmp/60370" + ext); - try { - wb.write(str); - } finally { - str.close(); - }*/ + // write out and read back in to trigger some more validation + final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb); - wb.close(); + final Sheet sheetBack = wbBack.getSheetAt(0); + final List dataValidations = sheetBack.getDataValidations(); + assertEquals(1, dataValidations.size()); + } } protected void assertFormula(Workbook wb, Cell intF, String expectedFormula, String expectedResultOrNull) { -- 2.39.5