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

Example 11 with XSSFSheet

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

the class TestThemesTable method testThemesTableColors.

@Test
public void testThemesTableColors() throws Exception {
    // Load our two test workbooks
    XSSFWorkbook simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple);
    XSSFWorkbook complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
    // Save and re-load them, to check for stability across that
    XSSFWorkbook simpleRS = XSSFTestDataSamples.writeOutAndReadBack(simple);
    XSSFWorkbook complexRS = XSSFTestDataSamples.writeOutAndReadBack(complex);
    // Fetch fresh copies to test with
    simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple);
    complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
    // Files and descriptions
    Map<String, XSSFWorkbook> workbooks = new LinkedHashMap<String, XSSFWorkbook>();
    workbooks.put(testFileSimple, simple);
    workbooks.put("Re-Saved_" + testFileSimple, simpleRS);
    workbooks.put(testFileComplex, complex);
    workbooks.put("Re-Saved_" + testFileComplex, complexRS);
    // Sanity check
    assertEquals(rgbExpected.length, rgbExpected.length);
    // For offline testing
    boolean createFiles = false;
    //  for the theme-applied cells in Column A are correct
    for (String whatWorkbook : workbooks.keySet()) {
        XSSFWorkbook workbook = workbooks.get(whatWorkbook);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int startRN = 0;
        if (whatWorkbook.endsWith(testFileComplex))
            startRN++;
        for (int rn = startRN; rn < rgbExpected.length + startRN; rn++) {
            XSSFRow row = sheet.getRow(rn);
            assertNotNull("Missing row " + rn + " in " + whatWorkbook, row);
            String ref = (new CellReference(rn, 0)).formatAsString();
            XSSFCell cell = row.getCell(0);
            assertNotNull("Missing cell " + ref + " in " + whatWorkbook, cell);
            int expectedThemeIdx = rn - startRN;
            ThemeElement themeElem = ThemeElement.byId(expectedThemeIdx);
            assertEquals("Wrong theme at " + ref + " in " + whatWorkbook, themeElem.name.toLowerCase(Locale.ROOT), cell.getStringCellValue());
            // Fonts are theme-based in their colours
            XSSFFont font = cell.getCellStyle().getFont();
            CTColor ctColor = font.getCTFont().getColorArray(0);
            assertNotNull(ctColor);
            assertEquals(true, ctColor.isSetTheme());
            assertEquals(themeElem.idx, ctColor.getTheme());
            // Get the colour, via the theme
            XSSFColor color = font.getXSSFColor();
            // Theme colours aren't tinted
            assertEquals(false, color.hasTint());
            // Check the RGB part (no tint)
            assertEquals("Wrong theme colour " + themeElem.name + " on " + whatWorkbook, rgbExpected[expectedThemeIdx], Hex.encodeHexString(color.getRGB()));
            long themeIdx = font.getCTFont().getColorArray(0).getTheme();
            assertEquals("Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook, expectedThemeIdx, themeIdx);
            if (createFiles) {
                XSSFCellStyle cs = row.getSheet().getWorkbook().createCellStyle();
                cs.setFillForegroundColor(color);
                cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
                row.createCell(1).setCellStyle(cs);
            }
        }
        if (createFiles) {
            FileOutputStream fos = new FileOutputStream("Generated_" + whatWorkbook);
            workbook.write(fos);
            fos.close();
        }
    }
}

Example 12 with XSSFSheet

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

the class TestThemesTable method themedAndNonThemedColours.

/**
     * Ensure that, for a file with themes, we can correctly
     *  read both the themed and non-themed colours back.
     * Column A = Theme Foreground
     * Column B = Theme Foreground
     * Column C = Explicit Colour Foreground
     * Column E = Explicit Colour Background, Black Foreground
     * Column G = Conditional Formatting Backgrounds
     * 
     * Note - Grey Row has an odd way of doing the styling... 
     */
@Test
public void themedAndNonThemedColours() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFCellStyle style;
    XSSFColor color;
    XSSFCell cell;
    String[] names = { "White", "Black", "Grey", "Dark Blue", "Blue", "Red", "Green" };
    String[] explicitFHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0070C0", "FFFF0000", "FF00B050" };
    String[] explicitBHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0000FF", "FFFF0000", "FF00FF00" };
    assertEquals(7, names.length);
    // Check the non-CF colours in Columns A, B, C and E
    for (int rn = 1; rn < 8; rn++) {
        int idx = rn - 1;
        XSSFRow row = sheet.getRow(rn);
        assertNotNull("Missing row " + rn, row);
        // Theme cells come first
        XSSFCell themeCell = row.getCell(0);
        ThemeElement themeElem = ThemeElement.byId(idx);
        assertCellContents(themeElem.name, themeCell);
        // Sanity check names
        assertCellContents(names[idx], row.getCell(1));
        assertCellContents(names[idx], row.getCell(2));
        assertCellContents(names[idx], row.getCell(4));
        // Check the colours
        //  A: Theme Based, Foreground
        style = themeCell.getCellStyle();
        color = style.getFont().getXSSFColor();
        assertEquals(true, color.isThemed());
        assertEquals(idx, color.getTheme());
        assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB()));
        //  B: Theme Based, Foreground
        cell = row.getCell(1);
        style = cell.getCellStyle();
        color = style.getFont().getXSSFColor();
        assertEquals(true, color.isThemed());
        if (idx != 2) {
            assertEquals(idx, color.getTheme());
            assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB()));
        } else {
            assertEquals(1, color.getTheme());
            assertEquals(0.50, color.getTint(), 0.001);
        }
        //  C: Explicit, Foreground
        cell = row.getCell(2);
        style = cell.getCellStyle();
        color = style.getFont().getXSSFColor();
        assertEquals(false, color.isThemed());
        assertEquals(explicitFHexes[idx], color.getARGBHex());
        // E: Explicit Background, Foreground all Black
        cell = row.getCell(4);
        style = cell.getCellStyle();
        color = style.getFont().getXSSFColor();
        assertEquals(true, color.isThemed());
        assertEquals("FF000000", color.getARGBHex());
        color = style.getFillForegroundXSSFColor();
        assertEquals(false, color.isThemed());
        assertEquals(explicitBHexes[idx], color.getARGBHex());
        color = style.getFillBackgroundColorColor();
        assertEquals(false, color.isThemed());
        assertEquals(null, color.getARGBHex());
    }
// Check the CF colours
// TODO
}

Example 13 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet 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 14 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet 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);
}

Example 15 with XSSFSheet

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

the class TestXSSFChartTitle method getChartFromWorkbook.

/**
     * Gets the first chart from the named sheet in the workbook.
     */
private XSSFChart getChartFromWorkbook(Workbook wb, String sheetName) {
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet instanceof XSSFSheet) {
        XSSFSheet xsheet = (XSSFSheet) sheet;
        XSSFDrawing drawing = xsheet.getDrawingPatriarch();
        if (drawing != null) {
            List<XSSFChart> charts = drawing.getCharts();
            if (charts != null && charts.size() > 0) {
                return charts.get(0);
            }
        }
    }
    return null;
}