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

Example 61 with XSSFWorkbook

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

the class XSSFRowShifter method updateNamedRanges.

/**
     * Updated named ranges
     */
public void updateNamedRanges(FormulaShifter shifter) {
    Workbook wb = sheet.getWorkbook();
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
    for (Name name : wb.getAllNames()) {
        String formula = name.getRefersToFormula();
        int sheetIndex = name.getSheetIndex();
        //don't care, named ranges are not allowed to include structured references
        final int rowIndex = -1;
        Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
        if (shifter.adjustFormula(ptgs, sheetIndex)) {
            String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
            name.setRefersToFormula(shiftedFmla);
        }
    }
}

Example 62 with XSSFWorkbook

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

the class TestWorkbookFactory method testCreateWithPasswordFromStream.

/**
     * Check that the overloaded stream methods which take passwords work properly
     */
@Test
public void testCreateWithPasswordFromStream() throws Exception {
    Workbook wb;
    // Unprotected, no password given, opens normally
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xls), null);
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls, wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xlsx), null);
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx, wb);
    // Unprotected, wrong password, opens normally
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xls), "wrong");
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls, wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xlsx), "wrong");
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx, wb);
    // Protected, correct password, opens fine
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xls_prot[0]), xls_prot[1]);
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls_prot[0], wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xlsx_prot[0]), xlsx_prot[1]);
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx_prot[0], wb);
    // Protected, wrong password, throws Exception
    try {
        wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xls_prot[0]), "wrong");
        assertCloseDoesNotModifyFile(xls_prot[0], wb);
        fail("Shouldn't be able to open with the wrong password");
    } catch (EncryptedDocumentException e) {
    }
    try {
        wb = WorkbookFactory.create(HSSFTestDataSamples.openSampleFileStream(xlsx_prot[0]), "wrong");
        assertCloseDoesNotModifyFile(xlsx_prot[0], wb);
        fail("Shouldn't be able to open with the wrong password");
    } catch (EncryptedDocumentException e) {
    }
}

Example 63 with XSSFWorkbook

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

the class TestWorkbookFactory method testCreateReadOnly.

@Test
public void testCreateReadOnly() throws Exception {
    Workbook wb;
    // POIFS -> hssf
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xls), null, true);
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls, wb);
    // Package -> xssf
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xlsx), null, true);
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx, wb);
}

Example 64 with XSSFWorkbook

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

the class TestWorkbookFactory method testCreateWithPasswordFromFile.

/**
     * Check that the overloaded file methods which take passwords work properly
     */
@Test
public void testCreateWithPasswordFromFile() throws Exception {
    Workbook wb;
    // Unprotected, no password given, opens normally
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xls), null);
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls, wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xlsx), null);
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx, wb);
    // Unprotected, wrong password, opens normally
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xls), "wrong");
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls, wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xlsx), "wrong");
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertCloseDoesNotModifyFile(xlsx, wb);
    // Protected, correct password, opens fine
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xls_prot[0]), xls_prot[1]);
    assertNotNull(wb);
    assertTrue(wb instanceof HSSFWorkbook);
    assertCloseDoesNotModifyFile(xls_prot[0], wb);
    wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xlsx_prot[0]), xlsx_prot[1]);
    assertNotNull(wb);
    assertTrue(wb instanceof XSSFWorkbook);
    assertTrue(wb.getNumberOfSheets() > 0);
    assertNotNull(wb.getSheetAt(0));
    assertNotNull(wb.getSheetAt(0).getRow(0));
    assertCloseDoesNotModifyFile(xlsx_prot[0], wb);
    // Protected, wrong password, throws Exception
    try {
        wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xls_prot[0]), "wrong");
        assertCloseDoesNotModifyFile(xls_prot[0], wb);
        fail("Shouldn't be able to open with the wrong password");
    } catch (EncryptedDocumentException e) {
    // expected here
    }
    try {
        wb = WorkbookFactory.create(HSSFTestDataSamples.getSampleFile(xlsx_prot[0]), "wrong");
        assertCloseDoesNotModifyFile(xlsx_prot[0], wb);
        fail("Shouldn't be able to open with the wrong password");
    } catch (EncryptedDocumentException e) {
    // expected here
    }
}

Example 65 with XSSFWorkbook

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

the class TestXSSFImportFromXML method testImportFromXML.

@Test
public void testImportFromXML() throws IOException, XPathExpressionException, SAXException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMappings.xlsx");
    try {
        String name = "name";
        String teacher = "teacher";
        String tutor = "tutor";
        String cdl = "cdl";
        String duration = "duration";
        String topic = "topic";
        String project = "project";
        String credits = "credits";
        String testXML = "<CORSO>" + "<NOME>" + name + "</NOME>" + "<DOCENTE>" + teacher + "</DOCENTE>" + "<TUTOR>" + tutor + "</TUTOR>" + "<CDL>" + cdl + "</CDL>" + "<DURATA>" + duration + "</DURATA>" + "<ARGOMENTO>" + topic + "</ARGOMENTO>" + "<PROGETTO>" + project + "</PROGETTO>" + "<CREDITI>" + credits + "</CREDITI>" + "</CORSO>";
        XSSFMap map = wb.getMapInfo().getXSSFMapByName("CORSO_mapping");
        assertNotNull(map);
        XSSFImportFromXML importer = new XSSFImportFromXML(map);
        importer.importFromXML(testXML);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(0);
        assertTrue(row.getCell(0).getStringCellValue().equals(name));
        assertTrue(row.getCell(1).getStringCellValue().equals(teacher));
        assertTrue(row.getCell(2).getStringCellValue().equals(tutor));
        assertTrue(row.getCell(3).getStringCellValue().equals(cdl));
        assertTrue(row.getCell(4).getStringCellValue().equals(duration));
        assertTrue(row.getCell(5).getStringCellValue().equals(topic));
        assertTrue(row.getCell(6).getStringCellValue().equals(project));
        assertTrue(row.getCell(7).getStringCellValue().equals(credits));
    } finally {
        wb.close();
    }
}