Examples with HSSFWorkbook org.apache.poi.hssf.usermodel.HSSFWorkbook used on opensource projects

Example 26 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestHPSFBugs method test48832.

/**
    * Ensure that we can create a new HSSF Workbook,
    *  then add some properties to it, save +
    *  reload, and still access & change them.
    */
@Test
public void test48832() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    // Starts empty
    assertNull(wb1.getDocumentSummaryInformation());
    assertNull(wb1.getSummaryInformation());
    // Add new properties
    wb1.createInformationProperties();
    assertNotNull(wb1.getDocumentSummaryInformation());
    assertNotNull(wb1.getSummaryInformation());
    // Set initial values
    wb1.getSummaryInformation().setAuthor("Apache POI");
    wb1.getSummaryInformation().setKeywords("Testing POI");
    wb1.getSummaryInformation().setCreateDateTime(new Date(12345));
    wb1.getDocumentSummaryInformation().setCompany("Apache");
    // Save and reload
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    // Ensure changes were taken
    assertNotNull(wb2.getDocumentSummaryInformation());
    assertNotNull(wb2.getSummaryInformation());
    assertEquals("Apache POI", wb2.getSummaryInformation().getAuthor());
    assertEquals("Testing POI", wb2.getSummaryInformation().getKeywords());
    assertEquals(12345, wb2.getSummaryInformation().getCreateDateTime().getTime());
    assertEquals("Apache", wb2.getDocumentSummaryInformation().getCompany());
    // Set some more, save + reload
    wb2.getSummaryInformation().setComments("Resaved");
    HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
    wb2.close();
    // Check again
    assertNotNull(wb3.getDocumentSummaryInformation());
    assertNotNull(wb3.getSummaryInformation());
    assertEquals("Apache POI", wb3.getSummaryInformation().getAuthor());
    assertEquals("Testing POI", wb3.getSummaryInformation().getKeywords());
    assertEquals("Resaved", wb3.getSummaryInformation().getComments());
    assertEquals(12345, wb3.getSummaryInformation().getCreateDateTime().getTime());
    assertEquals("Apache", wb3.getDocumentSummaryInformation().getCompany());
    wb3.close();
}

Example 27 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestFormulaParser method testParseComplexName.

/**
     * In bug 47078, POI had trouble evaluating a defined name flagged as 'complex'.
     * POI should also be able to parse such defined names.
     */
@Test
public void testParseComplexName() throws IOException {
    // Mock up a spreadsheet to match the critical details of the sample
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Sheet1");
    HSSFName definedName = wb.createName();
    definedName.setNameName("foo");
    definedName.setRefersToFormula("Sheet1!B2");
    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    // 0x10 -> complex
    nameRec.setOptionFlag((short) 0x10);
    Ptg[] result;
    try {
        result = HSSFFormulaParser.parse("1+foo", wb);
    } catch (FormulaParseException e) {
        if (e.getMessage().equals("Specified name 'foo' is not a range as expected.")) {
            fail("Identified bug 47078c");
        }
        wb.close();
        throw e;
    }
    confirmTokenClasses(result, IntPtg.class, NamePtg.class, AddPtg.class);
    wb.close();
}

Example 28 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestFormulaParser method testUnderscore.

/** bug 35027, underscore in sheet name */
@Test
public void testUnderscore() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Cash_Flow");
    HSSFSheet sheet = wb.createSheet("Test");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell;
    cell = row.createCell(0);
    cell.setCellFormula("Cash_Flow!A1");
    wb.close();
}

Example 29 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestFormulaParser method testRangeOperator.

@Test
public void testRangeOperator() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFCell cell = sheet.createRow(0).createCell(0);
    wb.setSheetName(0, "Sheet1");
    // explicit range ':' operator
    cell.setCellFormula("Sheet1!B$4:Sheet1!$C1");
    assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula());
    // plain area ref
    cell.setCellFormula("Sheet1!B$4:$C1");
    // note - area ref is normalised
    assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());
    // different syntax for plain area ref
    cell.setCellFormula("Sheet1!$C1...B$4");
    assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());
    // with funny sheet name
    wb.setSheetName(0, "A1...A2");
    cell.setCellFormula("A1...A2!B1");
    assertEquals("A1...A2!B1", cell.getCellFormula());
    wb.close();
}

Example 30 with HSSFWorkbook

use of org.apache.poi.hssf.usermodel.HSSFWorkbook in project poi by apache.

the class TestFormulaParser method testParseExternalWorkbookReference.

@Test
public void testParseExternalWorkbookReference() throws IOException {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFCell cell = wbA.getSheetAt(0).getRow(0).getCell(0);
    // make sure formula in sample is as expected
    assertEquals("[multibookFormulaB.xls]BSheet1!B1", cell.getCellFormula());
    Ptg[] expectedPtgs = FormulaExtractor.getPtgs(cell);
    confirmSingle3DRef(expectedPtgs, 1);
    // now try (re-)parsing the formula
    Ptg[] actualPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]BSheet1!B1", wbA);
    // externalSheetIndex 1 -> BSheet1
    confirmSingle3DRef(actualPtgs, 1);
    // try parsing a formula pointing to a different external sheet
    Ptg[] otherPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]AnotherSheet!B1", wbA);
    // externalSheetIndex 0 -> AnotherSheet
    confirmSingle3DRef(otherPtgs, 0);
    // try setting the same formula in a cell
    cell.setCellFormula("[multibookFormulaB.xls]AnotherSheet!B1");
    assertEquals("[multibookFormulaB.xls]AnotherSheet!B1", cell.getCellFormula());
    wbA.close();
}