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

Example 6 with XSSFCellStyle

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

the class AligningCells method centerAcrossSelection.

/**
     * Center a text over multiple columns using ALIGN_CENTER_SELECTION
     *
     * @param wb the workbook
     * @param row the row to create the cell in
     * @param start_column  the column number to create the cell in and where the selection starts
     * @param end_column    the column number where the selection ends
     * @param valign the horizontal alignment for the cell.
     */
private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, int start_column, int end_column, VerticalAlignment valign) {
    CreationHelper ch = wb.getCreationHelper();
    // Create cell style with ALIGN_CENTER_SELECTION
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
    cellStyle.setVerticalAlignment(valign);
    // Create cells over the selected area
    for (int i = start_column; i <= end_column; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
    }
    // Set value to the first cell
    XSSFCell cell = row.getCell(start_column);
    cell.setCellValue(ch.createRichTextString("Align It"));
    // Make the selection
    CTRowImpl ctRow = (CTRowImpl) row.getCTRow();
    // Add object with format start_coll:end_coll. For example 1:3 will span from
    // cell 1 to cell 3, where the column index starts with 0
    //
    // You can add multiple spans for one row
    Object span = start_column + ":" + end_column;
    List<Object> spanList = new ArrayList<Object>();
    spanList.add(span);
    //add spns to the row
    ctRow.setSpans(spanList);
}

Example 7 with XSSFCellStyle

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

the class BigGridDemo method main.

public static void main(String[] args) throws Exception {
    // Step 1. Create a template file. Setup sheets and workbook-level objects such as
    // cell styles, number formats, etc.
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("Big Grid");
    Map<String, XSSFCellStyle> styles = createStyles(wb);
    //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
    String sheetRef = sheet.getPackagePart().getPartName().getName();
    //save the template
    FileOutputStream os = new FileOutputStream("template.xlsx");
    wb.write(os);
    os.close();
    //Step 2. Generate XML file.
    File tmp = File.createTempFile("sheet", ".xml");
    Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
    generate(fw, styles);
    fw.close();
    //Step 3. Substitute the template entry with the generated data
    FileOutputStream out = new FileOutputStream("big-grid.xlsx");
    substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
    out.close();
    wb.close();
}

Example 8 with XSSFCellStyle

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

the class BigGridDemo method createStyles.

/**
     * Create a library of cell styles.
     */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();
    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);
    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);
    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(HorizontalAlignment.RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);
    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(HorizontalAlignment.RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);
    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);
    return styles;
}

Example 9 with XSSFCellStyle

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

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