Move more tests into base class and add a TestSXSSFBugs class to verify some things that are supported against SXSSFWorkbook as well

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1686035 13f79535-47bb-0310-9956-ffa450edef68

2 files changed, 277 insertions, 24 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index 659bb049bc..0dbd00fa49 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -32,6 +32,7 @@ import java.io.FileNotFoundException;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.io.InputStream;
+import java.io.OutputStream;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Calendar;
@@ -291,7 +292,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell(0);
         HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell(1);
 
-        if (false) {
+        /*
             // THAI code page
             System.out.println("a1="+unicodeString(a1));
             System.out.println("a2="+unicodeString(a2));
@@ -304,7 +305,8 @@ public final class TestBugs extends BaseTestBugzillaIssues {
             // US+THAI
             System.out.println("d1="+unicodeString(d1));
             System.out.println("d2="+unicodeString(d2));
-        }
+        */
+
         confirmSameCellText(a1, a2);
         confirmSameCellText(b1, b2);
         confirmSameCellText(c1, c2);
@@ -336,7 +338,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         assertEquals(a.getRichStringCellValue().getString(), b.getRichStringCellValue().getString());
     }
 
-    private static String unicodeString(HSSFCell cell) {
+    /*private static String unicodeString(HSSFCell cell) {
         String ss = cell.getRichStringCellValue().getString();
         char s[] = ss.toCharArray();
         StringBuffer sb = new StringBuffer();
@@ -344,7 +346,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
             sb.append("\\u").append(Integer.toHexString(s[x]));
         }
         return sb.toString();
-    }
+    }*/
 
     /** Error in opening wb*/
     @Test
@@ -886,7 +888,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         List<HSSFObjectData> objects = wb.getAllEmbeddedObjects();
         assertEquals(1, objects.size());
 
-        HSSFObjectData obj = (HSSFObjectData)objects.get(0);
+        HSSFObjectData obj = objects.get(0);
         assertNotNull(obj);
 
         // Peek inside the underlying record
@@ -975,9 +977,10 @@ public final class TestBugs extends BaseTestBugzillaIssues {
 
     /**
      * Test that fonts get added properly
+     * @throws IOException 
      */
     @Test
-    public void bug45338() {
+    public void bug45338() throws IOException {
         HSSFWorkbook wb = new HSSFWorkbook();
         assertEquals(4, wb.getNumberOfFonts());
 
@@ -1056,6 +1059,8 @@ public final class TestBugs extends BaseTestBugzillaIssues {
                    "Thingy", false, true, (short)2, (byte)2
                )
         );
+        
+        wb.close();
     }
 
     /**
@@ -1200,9 +1205,10 @@ public final class TestBugs extends BaseTestBugzillaIssues {
     /**
      * People are all getting confused about the last
      *  row and cell number
+     * @throws IOException 
      */
     @Test
-    public void bug30635() {
+    public void bug30635() throws IOException {
         HSSFWorkbook wb = new HSSFWorkbook();
         HSSFSheet s = wb.createSheet();
 
@@ -1245,6 +1251,8 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         assertEquals(0, r.getFirstCellNum());
         assertEquals(5, r.getLastCellNum()); // last cell # + 1
         assertEquals(3, r.getPhysicalNumberOfCells());
+        
+        wb.close();
     }
 
     /**
@@ -1571,7 +1579,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         try {
             OPOIFSFileSystem fs = new OPOIFSFileSystem(
                     HSSFITestDataProvider.instance.openWorkbookStream("46904.xls"));
-            new HSSFWorkbook(fs.getRoot(), false);
+            new HSSFWorkbook(fs.getRoot(), false).close();
             fail();
         } catch(OldExcelFormatException e) {
             assertTrue(e.getMessage().startsWith(
@@ -1581,8 +1589,12 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         try {
             NPOIFSFileSystem fs = new NPOIFSFileSystem(
                     HSSFITestDataProvider.instance.openWorkbookStream("46904.xls"));
-            new HSSFWorkbook(fs.getRoot(), false);
-            fail();
+            try {
+                new HSSFWorkbook(fs.getRoot(), false).close();
+                fail();
+            } finally {
+                fs.close();
+            }
         } catch(OldExcelFormatException e) {
             assertTrue(e.getMessage().startsWith(
                     "The supplied spreadsheet seems to be Excel"
@@ -2020,9 +2032,10 @@ public final class TestBugs extends BaseTestBugzillaIssues {
     
     /**
      * Last row number when shifting rows
+     * @throws IOException 
      */
     @Test
-    public void bug50416LastRowNumber() {
+    public void bug50416LastRowNumber() throws IOException {
        // Create the workbook with 1 sheet which contains 3 rows
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("Bug50416");
@@ -2071,6 +2084,8 @@ public final class TestBugs extends BaseTestBugzillaIssues {
        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();
     }
     
     /**
@@ -2376,6 +2391,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         InternalSheet ish = HSSFTestHelper.getSheetForTest(sh);
         PageSettingsBlock psb = (PageSettingsBlock) ish.getRecords().get(13);
         psb.visitContainedRecords(new RecordAggregate.RecordVisitor() {
+            @Override
             public void visitRecord(Record r) {
                 list.add(r.getSid());
             }
@@ -2385,7 +2401,7 @@ public final class TestBugs extends BaseTestBugzillaIssues {
     }
     
     @Test
-    public void bug52272(){
+    public void bug52272() throws IOException{
         HSSFWorkbook wb = new HSSFWorkbook();
         HSSFSheet sh = wb.createSheet();
         HSSFPatriarch p = sh.createDrawingPatriarch();
@@ -2395,14 +2411,17 @@ public final class TestBugs extends BaseTestBugzillaIssues {
 
         HSSFSheet sh2 = wb.cloneSheet(0);
         assertNotNull(sh2.getDrawingPatriarch());
+        
+        wb.close();
     }
 
     @Test
-    public void bug53432(){
+    public void bug53432() throws IOException{
         Workbook wb = new HSSFWorkbook(); //or new HSSFWorkbook();
         wb.addPicture(new byte[]{123,22}, Workbook.PICTURE_TYPE_JPEG);
         assertEquals(wb.getAllPictures().size(), 1);
 
+        wb.close();
         wb = new HSSFWorkbook();
         wb = writeOutAndReadBack((HSSFWorkbook) wb);
         assertEquals(wb.getAllPictures().size(), 0);
@@ -2411,6 +2430,8 @@ public final class TestBugs extends BaseTestBugzillaIssues {
 
         wb = writeOutAndReadBack((HSSFWorkbook) wb);
         assertEquals(wb.getAllPictures().size(), 1);
+        
+        wb.close();
     }
 
     @Test
@@ -2560,12 +2581,12 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         Workbook wbBack = HSSFTestDataSamples.writeOutAndReadBack(wb);
         assertEquals(4, wbBack.getNumberOfSheets());
         
-//        OutputStream fOut = new FileOutputStream("/tmp/56325a.xls");
-//        try {
-//        	wb.write(fOut);
-//        } finally {
-//        	fOut.close();
-//        }
+        OutputStream fOut = new FileOutputStream("C:\\temp\\56325a.xls");
+        try {
+        	wb.write(fOut);
+        } finally {
+        	fOut.close();
+        }
     }
     
     /**
@@ -2758,4 +2779,39 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         
         wb.close();
     }
+    
+    @Test
+    public void test48043() throws IOException {
+        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("56325a.xls");
+        
+        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);
+
+        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();
+        }*/
+        
+        Workbook wbBack = HSSFTestDataSamples.writeOutAndReadBack(wb);
+        assertNotNull(wbBack);
+        wbBack.close();
+
+        wb.close();
+    }
 }
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
index 8b6732ca45..0051e82dd4 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
@@ -123,7 +123,7 @@ public abstract class BaseTestBugzillaIssues {
      * Merged regions were being removed from the parent in cloned sheets
      */
     @Test
-    public final void bug22720() {
+    public void bug22720() {
        Workbook workBook = _testDataProvider.createWorkbook();
        workBook.createSheet("TEST");
        Sheet template = workBook.getSheetAt(0);
@@ -247,7 +247,7 @@ public abstract class BaseTestBugzillaIssues {
     }
 
     @Test
-    public final void bug18800() {
+    public void bug18800() {
        Workbook book = _testDataProvider.createWorkbook();
        book.createSheet("TEST");
        Sheet sheet = book.cloneSheet(0);
@@ -276,7 +276,7 @@ public abstract class BaseTestBugzillaIssues {
     }
 
     @Test
-    public final void bug43093() {
+    public void bug43093() {
         Workbook xlw = _testDataProvider.createWorkbook();
 
         addNewSheetWithCellsA1toD4(xlw, 1);
@@ -296,7 +296,7 @@ public abstract class BaseTestBugzillaIssues {
     }
 
     @Test
-    public final void bug46729_testMaxFunctionArguments(){
+    public void bug46729_testMaxFunctionArguments(){
         String[] func = {"COUNT", "AVERAGE", "MAX", "MIN", "OR", "SUBTOTAL", "SKEW"};
 
         SpreadsheetVersion ssVersion = _testDataProvider.getSpreadsheetVersion();
@@ -534,7 +534,7 @@ public abstract class BaseTestBugzillaIssues {
         assertAlmostEquals(1950, s.getColumnWidth(10), fontAccuracy);
         assertAlmostEquals(2225, s.getColumnWidth(11), fontAccuracy);
     }
-    
+
     /**
      * =ISNUMBER(SEARCH("AM",A1)) evaluation 
      */
@@ -806,4 +806,201 @@ public abstract class BaseTestBugzillaIssues {
 
         wb.close();
     }
+
+    /**
+     * With HSSF, if you create a font, don't change it, and
+     *  create a 2nd, you really do get two fonts that you 
+     *  can alter as and when you want.
+     * With XSSF, that wasn't the case, but this verfies
+     *  that it now is again
+     */
+    @Test
+    public void bug48718() throws Exception {
+        Workbook wb = _testDataProvider.createWorkbook();
+        int startingFonts = wb instanceof HSSFWorkbook ? 4 : 1;
+
+        assertEquals(startingFonts, wb.getNumberOfFonts());
+
+        // Get a font, and slightly change it
+        Font a = wb.createFont();
+        assertEquals(startingFonts+1, wb.getNumberOfFonts());
+        a.setFontHeightInPoints((short)23);
+        assertEquals(startingFonts+1, wb.getNumberOfFonts());
+
+        // Get two more, unchanged
+        /*Font b =*/ wb.createFont();
+        assertEquals(startingFonts+2, wb.getNumberOfFonts());
+        /*Font c =*/ wb.createFont();
+        assertEquals(startingFonts+3, wb.getNumberOfFonts());
+    }
+
+    @Test
+    public void bug57430() throws Exception {
+        Workbook wb = _testDataProvider.createWorkbook();
+        try {
+            wb.createSheet("Sheet1");
+
+            Name name1 = wb.createName();
+            name1.setNameName("FMLA");
+            name1.setRefersToFormula("Sheet1!$B$3");
+        } finally {
+            wb.close();
+        }
+    }
+
+    @Test
+    public void bug56981() throws IOException {
+        Workbook wb = _testDataProvider.createWorkbook();
+        CellStyle vertTop = wb.createCellStyle();
+        vertTop.setVerticalAlignment(CellStyle.VERTICAL_TOP);
+        CellStyle vertBottom = wb.createCellStyle();
+        vertBottom.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
+        Sheet sheet = wb.createSheet("Sheet 1");
+        Row row = sheet.createRow(0);
+        Cell top = row.createCell(0);
+        Cell bottom = row.createCell(1);
+        top.setCellValue("Top");
+        top.setCellStyle(vertTop); // comment this out to get all bottom-aligned
+                                   // cells
+        bottom.setCellValue("Bottom");
+        bottom.setCellStyle(vertBottom);
+        row.setHeightInPoints(85.75f); // make it obvious
+
+        /*FileOutputStream out = new FileOutputStream("c:\\temp\\56981.xlsx");
+        try {
+            wb.write(out);
+        } finally {
+            out.close();
+        }*/
+        
+        wb.close();
+    }
+
+    @Test
+    public void test57973() throws IOException {
+        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.setBoldweight(Font.BOLDWEIGHT_BOLD);
+        font.setColor(IndexedColors.RED.getIndex());
+        str2.applyFont(font);
+
+        comment2.setString(str2);
+        comment2.setAuthor("Apache POI");
+        comment2.setColumn(2);
+        comment2.setRow(2);
+
+        /*OutputStream out = new FileOutputStream("C:\\temp\\57973.xlsx");
+        try {
+            wb.write(out);
+        } finally {
+            out.close();
+        }*/
+        
+        wb.close();
+    }
+
+    /**
+     * Ensures that XSSF and HSSF agree with each other,
+     *  and with the docs on when fetching the wrong
+     *  kind of value from a Formula cell
+     */
+    @Test
+    public void bug47815() {
+        Workbook wb = _testDataProvider.createWorkbook();
+        Sheet s = wb.createSheet();
+        Row r = s.createRow(0);
+
+        // Setup
+        Cell cn = r.createCell(0, Cell.CELL_TYPE_NUMERIC);
+        cn.setCellValue(1.2);
+        Cell cs = r.createCell(1, Cell.CELL_TYPE_STRING);
+        cs.setCellValue("Testing");
+
+        Cell cfn = r.createCell(2, Cell.CELL_TYPE_FORMULA);
+        cfn.setCellFormula("A1");  
+        Cell cfs = r.createCell(3, Cell.CELL_TYPE_FORMULA);
+        cfs.setCellFormula("B1");
+
+        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+        assertEquals(Cell.CELL_TYPE_NUMERIC, fe.evaluate(cfn).getCellType());
+        assertEquals(Cell.CELL_TYPE_STRING, fe.evaluate(cfs).getCellType());
+        fe.evaluateFormulaCell(cfn);
+        fe.evaluateFormulaCell(cfs);
+
+        // Now test
+        assertEquals(Cell.CELL_TYPE_NUMERIC, cn.getCellType());
+        assertEquals(Cell.CELL_TYPE_STRING, cs.getCellType());
+        assertEquals(Cell.CELL_TYPE_FORMULA, cfn.getCellType());
+        assertEquals(Cell.CELL_TYPE_NUMERIC, cfn.getCachedFormulaResultType());
+        assertEquals(Cell.CELL_TYPE_FORMULA, cfs.getCellType());
+        assertEquals(Cell.CELL_TYPE_STRING, cfs.getCachedFormulaResultType());
+
+        // Different ways of retrieving
+        assertEquals(1.2, cn.getNumericCellValue(), 0);
+        try {
+            cn.getRichStringCellValue();
+            fail();
+        } catch(IllegalStateException e) {}
+
+        assertEquals("Testing", cs.getStringCellValue());
+        try {
+            cs.getNumericCellValue();
+            fail();
+        } catch(IllegalStateException e) {}
+
+        assertEquals(1.2, cfn.getNumericCellValue(), 0);
+        try {
+            cfn.getRichStringCellValue();
+            fail();
+        } catch(IllegalStateException e) {}
+
+        assertEquals("Testing", cfs.getStringCellValue());
+        try {
+            cfs.getNumericCellValue();
+            fail();
+        } catch(IllegalStateException e) {}
+    }
 }