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

Example 71 with HSSFWorkbook

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

the class TestEventWorkbookBuilder method testFormulas.

public void testFormulas() {
    FormulaRecord[] fRecs = mockListen.getFormulaRecords();
    // Check our formula records
    assertEquals(6, fRecs.length);
    InternalWorkbook stubWB = listener.getStubWorkbook();
    assertNotNull(stubWB);
    HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
    assertNotNull(stubHSSF);
    // Check these stubs have the right stuff on them
    assertEquals("Sheet1", stubWB.getSheetName(0));
    assertEquals("Sheet1", stubHSSF.getSheetName(0));
    assertEquals("S2", stubWB.getSheetName(1));
    assertEquals("S2", stubHSSF.getSheetName(1));
    assertEquals("Sh3", stubWB.getSheetName(2));
    assertEquals("Sh3", stubHSSF.getSheetName(2));
    // Check we can get the formula without breaking
    for (FormulaRecord fRec : fRecs) {
        HSSFFormulaParser.toFormulaString(stubHSSF, fRec.getParsedExpression());
    }
    // Peer into just one formula, and check that
    //  all the ptgs give back the right things
    Ptg[] ptgs = fRecs[0].getParsedExpression();
    assertEquals(1, ptgs.length);
    assertTrue(ptgs[0] instanceof Ref3DPtg);
    Ref3DPtg ptg = (Ref3DPtg) ptgs[0];
    HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(stubHSSF);
    assertEquals("Sheet1!A1", ptg.toFormulaString(book));
    // Now check we get the right formula back for
    //  a few sample ones
    FormulaRecord fr;
    // Sheet 1 A2 is on same sheet
    fr = fRecs[0];
    assertEquals(1, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals("Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
    // Sheet 1 A5 is to another sheet
    fr = fRecs[3];
    assertEquals(4, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
    // Sheet 1 A7 is to another sheet, range
    fr = fRecs[5];
    assertEquals(6, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals("SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
    // Now, load via Usermodel and re-check
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("3dFormulas.xls");
    assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
    assertEquals("SUM(Sh3!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
}

Example 72 with HSSFWorkbook

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

the class TestExcelExtractor method testWithEmbededInOwn.

/**
	 * Excel embeded in excel
	 */
@Test
public void testWithEmbededInOwn() throws Exception {
    POIDataSamples ssSamples = POIDataSamples.getSpreadSheetInstance();
    POIFSFileSystem fs = null;
    HSSFWorkbook wbA = null, wbB = null;
    ExcelExtractor exA = null, exB = null, ex = null;
    try {
        fs = new POIFSFileSystem(ssSamples.getFile("excel_with_embeded.xls"));
        DirectoryNode dirA = (DirectoryNode) fs.getRoot().getEntry("MBD0000A3B5");
        DirectoryNode dirB = (DirectoryNode) fs.getRoot().getEntry("MBD0000A3B4");
        wbA = new HSSFWorkbook(dirA, fs, true);
        wbB = new HSSFWorkbook(dirB, fs, true);
        exA = new ExcelExtractor(wbA);
        exB = new ExcelExtractor(wbB);
        assertEquals("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n", exA.getText());
        assertEquals("Sample Excel", exA.getSummaryInformation().getTitle());
        assertEquals("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n", exB.getText());
        assertEquals("Sample Excel 2", exB.getSummaryInformation().getTitle());
        // And the base file too
        ex = new ExcelExtractor(fs);
        assertEquals("Sheet1\nI have lots of embeded files in me\nSheet2\nSheet3\n", ex.getText());
        assertEquals("Excel With Embeded", ex.getSummaryInformation().getTitle());
    } finally {
        if (ex != null)
            ex.close();
        if (exB != null)
            exB.close();
        if (exA != null)
            exA.close();
        if (wbB != null)
            wbB.close();
        if (wbA != null)
            wbA.close();
        if (fs != null)
            fs.close();
    }
}

Example 73 with HSSFWorkbook

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

the class TestExcelExtractor method testWithEmbeded.

/**
	 * Embeded in a non-excel file
	 */
@Test
public void testWithEmbeded() throws Exception {
    POIFSFileSystem fs = null;
    HSSFWorkbook wbA = null, wbB = null;
    ExcelExtractor exA = null, exB = null;
    try {
        fs = new POIFSFileSystem(POIDataSamples.getDocumentInstance().getFile("word_with_embeded.doc"));
        DirectoryNode objPool = (DirectoryNode) fs.getRoot().getEntry("ObjectPool");
        DirectoryNode dirA = (DirectoryNode) objPool.getEntry("_1269427460");
        DirectoryNode dirB = (DirectoryNode) objPool.getEntry("_1269427461");
        wbA = new HSSFWorkbook(dirA, fs, true);
        exA = new ExcelExtractor(wbA);
        wbB = new HSSFWorkbook(dirB, fs, true);
        exB = new ExcelExtractor(wbB);
        assertEquals("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n", exA.getText());
        assertEquals("Sample Excel", exA.getSummaryInformation().getTitle());
        assertEquals("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n", exB.getText());
        assertEquals("Sample Excel 2", exB.getSummaryInformation().getTitle());
    } finally {
        if (exB != null)
            exB.close();
        if (wbB != null)
            wbB.close();
        if (exA != null)
            exA.close();
        if (wbA != null)
            wbA.close();
        if (fs != null)
            fs.close();
    }
}

Example 74 with HSSFWorkbook

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

the class HSSFTestDataSamples method writeOutAndReadBack.

/**
	 * Writes a spreadsheet to a <tt>ByteArrayOutputStream</tt> and reads it back
	 * from a <tt>ByteArrayInputStream</tt>.<p/>
	 * Useful for verifying that the serialisation round trip
	 */
public static HSSFWorkbook writeOutAndReadBack(HSSFWorkbook original) {
    try {
        ByteArrayOutputStream baos = new ByteArrayOutputStream(4096);
        original.write(baos);
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        return new HSSFWorkbook(bais);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

Example 75 with HSSFWorkbook

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

the class BaseXLSIteratingTest method testMain.

@Test
public void testMain() throws Exception {
    // we had intermittent problems when this was set differently somehow, let's try to set it here so it always is set correctly for these tests
    Biff8EncryptionKey.setCurrentUserPassword(null);
    String fileName = file.getName();
    if (EXCLUDED.containsKey(fileName)) {
        thrown.expect(EXCLUDED.get(fileName));
    }
    try {
        runOneFile(file);
    } catch (Exception e) {
        // try to read it in HSSFWorkbook to quickly fail if we cannot read the file there at all and thus probably should use EXCLUDED instead
        FileInputStream stream = new FileInputStream(file);
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(stream);
            assertNotNull(wb);
        } finally {
            if (wb != null) {
                wb.close();
            }
            stream.close();
        }
        throw e;
    }
}