Search in sources :

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();
        }
    }
}
Also used : CellReference(org.apache.poi.ss.util.CellReference) CTColor(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor) LinkedHashMap(java.util.LinkedHashMap) ThemeElement(org.apache.poi.xssf.model.ThemesTable.ThemeElement) XSSFColor(org.apache.poi.xssf.usermodel.XSSFColor) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFCellStyle(org.apache.poi.xssf.usermodel.XSSFCellStyle) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) XSSFFont(org.apache.poi.xssf.usermodel.XSSFFont) FileOutputStream(java.io.FileOutputStream) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) XSSFCell(org.apache.poi.xssf.usermodel.XSSFCell) Test(org.junit.Test)

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
}
Also used : XSSFColor(org.apache.poi.xssf.usermodel.XSSFColor) ThemeElement(org.apache.poi.xssf.model.ThemesTable.ThemeElement) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFCellStyle(org.apache.poi.xssf.usermodel.XSSFCellStyle) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) XSSFCell(org.apache.poi.xssf.usermodel.XSSFCell) Test(org.junit.Test)

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();
}
Also used : EncryptedTempData(org.apache.poi.poifs.crypt.temp.EncryptedTempData) SXSSFWorkbookWithCustomZipEntrySource(org.apache.poi.poifs.crypt.temp.SXSSFWorkbookWithCustomZipEntrySource) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) XSSFCell(org.apache.poi.xssf.usermodel.XSSFCell) ZipEntrySource(org.apache.poi.openxml4j.util.ZipEntrySource) AesZipFileZipEntrySource(org.apache.poi.poifs.crypt.temp.AesZipFileZipEntrySource) SXSSFWorkbookWithCustomZipEntrySource(org.apache.poi.poifs.crypt.temp.SXSSFWorkbookWithCustomZipEntrySource) OPCPackage(org.apache.poi.openxml4j.opc.OPCPackage) Test(org.junit.Test)

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);
}
Also used : XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) POIXMLDocumentPart(org.apache.poi.POIXMLDocumentPart) CTMapInfo(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMapInfo) XSSFMap(org.apache.poi.xssf.usermodel.XSSFMap) Node(org.w3c.dom.Node) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) CTMapInfo(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMapInfo)

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;
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing)

Aggregations

XSSFSheet (org.apache.poi.xssf.usermodel.XSSFSheet)64 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)46 FileOutputStream (java.io.FileOutputStream)22 Test (org.junit.Test)22 File (java.io.File)19 XSSFRow (org.apache.poi.xssf.usermodel.XSSFRow)16 XSSFCell (org.apache.poi.xssf.usermodel.XSSFCell)11 Row (org.apache.poi.ss.usermodel.Row)8 XSSFCellStyle (org.apache.poi.xssf.usermodel.XSSFCellStyle)8 IOException (java.io.IOException)6 Sheet (org.apache.poi.ss.usermodel.Sheet)6 CellReference (org.apache.poi.ss.util.CellReference)6 XSSFMap (org.apache.poi.xssf.usermodel.XSSFMap)6 OutputStreamWriter (java.io.OutputStreamWriter)5 Cell (org.apache.poi.ss.usermodel.Cell)5 XlsxWriterHelper (com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper)4 OutputStream (java.io.OutputStream)4 ArrayList (java.util.ArrayList)4 BscStructTreeObj (com.netsteadfast.greenstep.bsc.model.BscStructTreeObj)3 VisionVO (com.netsteadfast.greenstep.vo.VisionVO)3