javaexcelapache-poi

Using custom colors with SXSSF (Apache POI)


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)


Solution

  • 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);