Examples with XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook used on opensource projects

Example 31 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestPOIXMLProperties method testWorkbookExtendedProperties.

@Test
public void testWorkbookExtendedProperties() throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    POIXMLProperties props = workbook.getProperties();
    assertNotNull(props);
    org.apache.poi.POIXMLProperties.ExtendedProperties properties = props.getExtendedProperties();
    org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.CTProperties ctProps = properties.getUnderlyingProperties();
    String appVersion = "3.5 beta";
    String application = "POI";
    ctProps.setApplication(application);
    ctProps.setAppVersion(appVersion);
    XSSFWorkbook newWorkbook = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    workbook.close();
    assertTrue(workbook != newWorkbook);
    POIXMLProperties newProps = newWorkbook.getProperties();
    assertNotNull(newProps);
    org.apache.poi.POIXMLProperties.ExtendedProperties newProperties = newProps.getExtendedProperties();
    assertEquals(application, newProperties.getApplication());
    assertEquals(appVersion, newProperties.getAppVersion());
    org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.CTProperties newCtProps = newProperties.getUnderlyingProperties();
    assertEquals(application, newCtProps.getApplication());
    assertEquals(appVersion, newCtProps.getAppVersion());
    newWorkbook.close();
}

Example 32 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestXMLPropertiesTextExtractor method testGetFromMainExtractor.

public void testGetFromMainExtractor() throws Exception {
    OPCPackage pkg = PackageHelper.open(_ssSamples.openResourceAsStream("ExcelWithAttachments.xlsm"));
    XSSFWorkbook wb = new XSSFWorkbook(pkg);
    XSSFExcelExtractor ext = new XSSFExcelExtractor(wb);
    POIXMLPropertiesTextExtractor textExt = ext.getMetadataTextExtractor();
    // Check basics
    assertNotNull(textExt);
    assertTrue(textExt.getText().length() > 0);
    // Check some of the content
    String text = textExt.getText();
    String cText = textExt.getCorePropertiesText();
    assertContains(text, "LastModifiedBy = Yury Batrakov");
    assertContains(cText, "LastModifiedBy = Yury Batrakov");
    textExt.close();
    ext.close();
}

Example 33 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestXMLPropertiesTextExtractor method testCore.

public void testCore() throws Exception {
    OPCPackage pkg = PackageHelper.open(_ssSamples.openResourceAsStream("ExcelWithAttachments.xlsm"));
    XSSFWorkbook wb = new XSSFWorkbook(pkg);
    POIXMLPropertiesTextExtractor ext = new POIXMLPropertiesTextExtractor(wb);
    ext.getText();
    // Now check
    String text = ext.getText();
    String cText = ext.getCorePropertiesText();
    assertContains(text, "LastModifiedBy = Yury Batrakov");
    assertContains(cText, "LastModifiedBy = Yury Batrakov");
    ext.close();
}

Example 34 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestFormulaParser method testMacroFunction.

// copied from org.apache.poi.hssf.model.TestFormulaParser
@Test
public void testMacroFunction() throws Exception {
    // testNames.xlsm contains a VB function called 'myFunc'
    final String testFile = "testNames.xlsm";
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(testFile);
    try {
        XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb);
        //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)]
        Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", workbook, FormulaType.CELL, -1);
        assertEquals(3, ptg.length);
        // the name gets encoded as the first operand on the stack
        NameXPxg tname = (NameXPxg) ptg[0];
        assertEquals("myFunc", tname.toFormulaString());
        // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
        StringPtg arg = (StringPtg) ptg[1];
        assertEquals("arg", arg.getValue());
        // The external FunctionPtg is the last Ptg added to the stack
        // During formula evaluation, this Ptg pops off the the appropriate number of
        // arguments (getNumberOfOperands()) and pushes the result on the stack 
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2];
        assertTrue(tfunc.isExternalFunction());
        // confirm formula parsing is case-insensitive
        FormulaParser.parse("mYfUnC(\"arg\")", workbook, FormulaType.CELL, -1);
        // confirm formula parsing doesn't care about argument count or type
        // this should only throw an error when evaluating the formula.
        FormulaParser.parse("myFunc()", workbook, FormulaType.CELL, -1);
        FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", workbook, FormulaType.CELL, -1);
        // A completely unknown formula name (not saved in workbook) should still be parseable and renderable
        // but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
        FormulaParser.parse("yourFunc(\"arg\")", workbook, FormulaType.CELL, -1);
        // Make sure workbook can be written and read
        XSSFTestDataSamples.writeOutAndReadBack(wb).close();
    // Manually check to make sure file isn't corrupted
    // TODO: develop a process for occasionally manually reviewing workbooks
    // to verify workbooks are not corrupted
    /*
            final File fileIn = XSSFTestDataSamples.getSampleFile(testFile);
            final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xlsm", "-saved.xlsm"));
            final FileOutputStream fos = new FileOutputStream(reSavedFile);
            wb.write(fos);
            fos.close();
            */
    } finally {
        wb.close();
    }
}

Example 35 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestFormulaParser method testParseExternalReferencesWithQuotedSheetName.

// bug 60219: FormulaParser can't parse external references when sheet name is quoted
@Test
public void testParseExternalReferencesWithQuotedSheetName() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFEvaluationWorkbook fpwb = XSSFEvaluationWorkbook.create(wb);
    Ptg[] ptgs = FormulaParser.parse("'[1]Sheet 1'!A1", fpwb, FormulaType.CELL, -1);
    // org.apache.poi.ss.formula.ptg.Ref3DPxg [ [workbook=1] sheet=Sheet 1 ! A1]
    assertEquals("Ptgs length", 1, ptgs.length);
    assertTrue("Ptg class", ptgs[0] instanceof Ref3DPxg);
    Ref3DPxg pxg = (Ref3DPxg) ptgs[0];
    assertEquals("External workbook number", 1, pxg.getExternalWorkbookNumber());
    assertEquals("Sheet name", "Sheet 1", pxg.getSheetName());
    assertEquals("Row", 0, pxg.getRow());
    assertEquals("Column", 0, pxg.getColumn());
    wb.close();
}