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

Example 46 with XSSFWorkbook

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

the class TestSXSSFWorkbook method addToExistingWorkbook.

@Test
public void addToExistingWorkbook() throws IOException {
    XSSFWorkbook xssfWb1 = new XSSFWorkbook();
    xssfWb1.createSheet("S1");
    Sheet sheet = xssfWb1.createSheet("S2");
    Row row = sheet.createRow(1);
    Cell cell = row.createCell(1);
    cell.setCellValue("value 2_1_1");
    SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1);
    XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb1);
    assertTrue(wb1.dispose());
    xssfWb1.close();
    SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2);
    // Add a row to the existing empty sheet
    Sheet sheet1 = wb2.getSheetAt(0);
    Row row1_1 = sheet1.createRow(1);
    Cell cell1_1_1 = row1_1.createCell(1);
    cell1_1_1.setCellValue("value 1_1_1");
    // Add a row to the existing non-empty sheet
    Sheet sheet2 = wb2.getSheetAt(1);
    Row row2_2 = sheet2.createRow(2);
    Cell cell2_2_1 = row2_2.createCell(1);
    cell2_2_1.setCellValue("value 2_2_1");
    // Add a sheet with one row
    Sheet sheet3 = wb2.createSheet("S3");
    Row row3_1 = sheet3.createRow(1);
    Cell cell3_1_1 = row3_1.createCell(1);
    cell3_1_1.setCellValue("value 3_1_1");
    XSSFWorkbook xssfWb3 = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb2);
    wb2.close();
    assertEquals(3, xssfWb3.getNumberOfSheets());
    // Verify sheet 1
    sheet1 = xssfWb3.getSheetAt(0);
    assertEquals("S1", sheet1.getSheetName());
    assertEquals(1, sheet1.getPhysicalNumberOfRows());
    row1_1 = sheet1.getRow(1);
    assertNotNull(row1_1);
    cell1_1_1 = row1_1.getCell(1);
    assertNotNull(cell1_1_1);
    assertEquals("value 1_1_1", cell1_1_1.getStringCellValue());
    // Verify sheet 2
    sheet2 = xssfWb3.getSheetAt(1);
    assertEquals("S2", sheet2.getSheetName());
    assertEquals(2, sheet2.getPhysicalNumberOfRows());
    Row row2_1 = sheet2.getRow(1);
    assertNotNull(row2_1);
    Cell cell2_1_1 = row2_1.getCell(1);
    assertNotNull(cell2_1_1);
    assertEquals("value 2_1_1", cell2_1_1.getStringCellValue());
    row2_2 = sheet2.getRow(2);
    assertNotNull(row2_2);
    cell2_2_1 = row2_2.getCell(1);
    assertNotNull(cell2_2_1);
    assertEquals("value 2_2_1", cell2_2_1.getStringCellValue());
    // Verify sheet 3
    sheet3 = xssfWb3.getSheetAt(2);
    assertEquals("S3", sheet3.getSheetName());
    assertEquals(1, sheet3.getPhysicalNumberOfRows());
    row3_1 = sheet3.getRow(1);
    assertNotNull(row3_1);
    cell3_1_1 = row3_1.getCell(1);
    assertNotNull(cell3_1_1);
    assertEquals("value 3_1_1", cell3_1_1.getStringCellValue());
    xssfWb2.close();
    xssfWb3.close();
    wb1.close();
}

Example 47 with XSSFWorkbook

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

the class TestSXSSFWorkbook method bug53515.

@Ignore("currently writing the same sheet multiple times is not supported...")
@Test
public void bug53515() throws Exception {
    Workbook wb1 = new SXSSFWorkbook(10);
    populateWorkbook(wb1);
    saveTwice(wb1);
    Workbook wb2 = new XSSFWorkbook();
    populateWorkbook(wb2);
    saveTwice(wb2);
    wb2.close();
    wb1.close();
}

Example 48 with XSSFWorkbook

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

the class TestSXSSFWorkbook method testTemplateFile.

@Ignore("Just a local test for http://stackoverflow.com/questions/33627329/apache-poi-streaming-api-using-xssf-template")
@Test
public void testTemplateFile() throws IOException {
    XSSFWorkbook workBook = XSSFTestDataSamples.openSampleWorkbook("sample.xlsx");
    SXSSFWorkbook streamingWorkBook = new SXSSFWorkbook(workBook, 10);
    Sheet sheet = streamingWorkBook.getSheet("Sheet1");
    for (int rowNum = 10; rowNum < 1000000; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 700; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue("somevalue");
        }
        if (rowNum % 100 == 0) {
            System.out.print(".");
            if (rowNum % 10000 == 0) {
                System.out.println(rowNum);
            }
        }
    }
    FileOutputStream fos = new FileOutputStream("C:\\temp\\streaming.xlsx");
    streamingWorkBook.write(fos);
    fos.close();
    streamingWorkBook.close();
    workBook.close();
}

Example 49 with XSSFWorkbook

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

the class TestSXSSFWorkbookWithCustomZipEntrySource method customZipEntrySourceForWriteAndRead.

// write an encrypted workbook to disk, and encrypt any temporary files as well
@Test
public void customZipEntrySourceForWriteAndRead() throws IOException, GeneralSecurityException, InvalidFormatException {
    SXSSFWorkbookWithCustomZipEntrySource workbook = new SXSSFWorkbookWithCustomZipEntrySource();
    SXSSFSheet sheet1 = workbook.createSheet(sheetName);
    SXSSFRow row1 = sheet1.createRow(1);
    SXSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue(cellValue);
    EncryptedTempData tempData = new EncryptedTempData();
    workbook.write(tempData.getOutputStream());
    workbook.close();
    workbook.dispose();
    ZipEntrySource zipEntrySource = AesZipFileZipEntrySource.createZipEntrySource(tempData.getInputStream());
    tempData.dispose();
    OPCPackage opc = OPCPackage.open(zipEntrySource);
    XSSFWorkbook xwb = new XSSFWorkbook(opc);
    zipEntrySource.close();
    XSSFSheet xs1 = xwb.getSheetAt(0);
    assertEquals(sheetName, xs1.getSheetName());
    XSSFRow xr1 = xs1.getRow(1);
    XSSFCell xc1 = xr1.getCell(1);
    assertEquals(cellValue, xc1.getStringCellValue());
    xwb.close();
    opc.close();
}

Example 50 with XSSFWorkbook

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

the class TestMapInfo method testMapInfoExists.

public void testMapInfoExists() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMappings.xlsx");
    MapInfo mapInfo = null;
    SingleXmlCells singleXMLCells = null;
    for (POIXMLDocumentPart p : wb.getRelations()) {
        if (p instanceof MapInfo) {
            mapInfo = (MapInfo) p;
            CTMapInfo ctMapInfo = mapInfo.getCTMapInfo();
            assertNotNull(ctMapInfo);
            assertEquals(1, ctMapInfo.sizeOfSchemaArray());
            for (XSSFMap map : mapInfo.getAllXSSFMaps()) {
                Node xmlSchema = map.getSchema();
                assertNotNull(xmlSchema);
            }
        }
    }
    XSSFSheet sheet1 = wb.getSheetAt(0);
    for (POIXMLDocumentPart p : sheet1.getRelations()) {
        if (p instanceof SingleXmlCells) {
            singleXMLCells = (SingleXmlCells) p;
        }
    }
    assertNotNull(mapInfo);
    assertNotNull(singleXMLCells);
}