Using custom colors with SXSSF (Apache POI)

Question

I am trying to write a huge excel file, my requirement allows me to write the row and forget, so i am using SXSSF which allows to keep only a few number of rows in memory and rest all are written to the document. this helps in overcoming outofmemory exception for large files.

but I also need to set styles to cells using sxssf workbook. i am not able to find a way to define and use custom colors in SXSSF (like in XSSF, we can define a custom color directly, and in HSSF we can replace an entry in the palette with a custom color)

i can find no way to access a palette from SXSSF workbook.

I can not create a new HSSF palette as the constructor is protected.

the only way that seems feasible right now is to somehow find a similar color from a list of all predefined colors and use it, instead of the original (but that would require having a rgb color matching algo, which would be another task)

Can someone suggest a workaround (or maybe suggest a primitive rgb color matching algorithm)

Stannite

Answer 1

So, after a bit of searching through the web and reading the docs, i got a hint that SXSSF is actually a wrapper around XSSF, so i typecasted the CellStyle returned by SXSSF workbook to XSSF and was able to use XSSFColor directly for generating colors.

SXSSFWorkbook workbook = new SXSSFWorkbook(50); 
Sheet sheet = workbook.createSheet("Image Data"); 
....
Cell cell = row.createCell(j);
cell.setCellValue(j);
XSSFCellStyle cs1 = (XSSFCellStyle) workbook.createCellStyle();
cs1.setFillForegroundColor(new XSSFColor(new java.awt.Color(red,green,blue)));          
cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(cs1);

Stannite

otherwise i had tried converting RGB to Lab format and then using euclidean distance to find similar colors, but that did not work in this case because a similar color had to be found from only 48 predefined available HSSFColors. so in all 256*256*256 colors were being mapped to only 48. – Stannite

Answer 2

To avoid the need of typecast for the cellStyles, create first a XSSFWorkbook with cellStyles (XSSFCellStyle) applying the custom colors and then wrap it with a SXSSFWorkbook constructor like the sample below:

/**
 * Sample based on POI <a href="http://poi.apache.org/spreadsheet/how-to.html#sxssf">Spreadsheet How-To</a>.
 * 
 * @see <a href="https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html">SXSSFWorkbook</a>
 */
public static void main(String[] args) throws Throwable {

    XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

    XSSFColor colorGrey = new XSSFColor(new Color(210, 210, 210));
    XSSFCellStyle cellStyleGrey = xssfWorkbook.createCellStyle();
    cellStyleGrey.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyleGrey.setFillForegroundColor(colorGrey);

    // keep 100 rows in memory, exceeding rows will be flushed to disk
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, 100);
    Sheet sheet = sxssfWorkbook.createSheet();

    for (int rownum = 0; rownum < 1000; rownum++) {
        Row row = sheet.createRow(rownum);
        for (int cellnum = 0; cellnum < 10; cellnum++) {
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);

            // for even rows apply the grey cellStyle
            if (rownum % 2 == 0) {
                cell.setCellStyle(cellStyleGrey);
            }
        }

    }

    // Omitted asserts block from original sample...

    FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
    sxssfWorkbook.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    sxssfWorkbook.dispose();
}

Scavenge

Answer 3

CellStyle style = workbook.createCellStyle(); // workbook is of SXSSF type
byte orange[] = new byte[] { (byte) 248, (byte) 203, (byte) 173 };
byte thick_shade_blue[] = new byte[] { (byte) 142, (byte) 169, (byte) 219 };
byte blue[] = new byte[] { (byte) 180, (byte) 198, (byte) 231 };

((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(thick_shade_blue, null));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

Osiris

Answer 4

Small addition to @Jerome Edward's solution:

1. since poi 5.2.3 there is no necessity to cast style to (XSSFCellStyle), CellStyle also has the .setFillForegroundColor() method too;
2. since poi 5.2.0 there is no necessity to pass any IndexedColorMap, because there is new XSSFColor constructor with only byte[] parameter.

So, with this poi versions appropriate solution would be, for example:

    CellStyle parentStyle = wb.createCellStyle(); // wb is of SXSSF type
    byte[] lavender = new byte[] { (byte) 221, (byte) 235, (byte) 247 };
    parentStyle.setFillForegroundColor(new XSSFColor(lavender));

Cymograph