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

Example 36 with XSSFWorkbook

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

the class TestFormulaParser method testParserErrors.

@Test
public void testParserErrors() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("testNames.xlsm");
    try {
        XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb);
        parseExpectedException("(");
        parseExpectedException(")");
        parseExpectedException("+");
        parseExpectedException("42+");
        parseExpectedException("IF()");
        //no closing paren
        parseExpectedException("IF(");
        //no closing paren
        parseExpectedException("myFunc(", workbook);
    } finally {
        wb.close();
    }
}

Example 37 with XSSFWorkbook

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

the class TestSharedStringsTable method testReadWrite.

public void testReadWrite() throws IOException {
    XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("sample.xlsx");
    SharedStringsTable sst1 = wb1.getSharedStringSource();
    //serialize, read back and compare with the original
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    SharedStringsTable sst2 = wb2.getSharedStringSource();
    assertEquals(sst1.getCount(), sst2.getCount());
    assertEquals(sst1.getUniqueCount(), sst2.getUniqueCount());
    List<CTRst> items1 = sst1.getItems();
    List<CTRst> items2 = sst2.getItems();
    assertEquals(items1.size(), items2.size());
    for (int i = 0; i < items1.size(); i++) {
        CTRst st1 = items1.get(i);
        CTRst st2 = items2.get(i);
        assertEquals(st1.toString(), st2.toString());
    }
    XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
    assertNotNull(wb3);
    wb3.close();
    wb2.close();
    wb1.close();
}

Example 38 with XSSFWorkbook

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

the class TestStylesTable method populateNew.

@Test
public void populateNew() {
    XSSFWorkbook wb = new XSSFWorkbook();
    StylesTable st = wb.getStylesSource();
    assertNotNull(st.getCTStylesheet());
    assertEquals(1, st._getXfsSize());
    assertEquals(1, st._getStyleXfsSize());
    assertEquals(0, st.getNumDataFormats());
    int nf1 = st.putNumberFormat("yyyy-mm-dd");
    int nf2 = st.putNumberFormat("yyyy-mm-DD");
    assertEquals(nf1, st.putNumberFormat("yyyy-mm-dd"));
    st.putStyle(new XSSFCellStyle(st));
    // Save and re-load
    st = XSSFTestDataSamples.writeOutAndReadBack(wb).getStylesSource();
    assertNotNull(st.getCTStylesheet());
    assertEquals(2, st._getXfsSize());
    assertEquals(1, st._getStyleXfsSize());
    assertEquals(2, st.getNumDataFormats());
    assertEquals("yyyy-mm-dd", st.getNumberFormatAt((short) nf1));
    assertEquals(nf1, st.putNumberFormat("yyyy-mm-dd"));
    assertEquals(nf2, st.putNumberFormat("yyyy-mm-DD"));
    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
}

Example 39 with XSSFWorkbook

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

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