can background color of a cell on an excel sheet be read using coldfusion

Question

I have an excel sheet where additions have a red background, changes have a yellow background, and deletions are grey. What I am hoping to do is read through the sheet, and based on the cell background color, perform the relevant database action.

Normally I would make each type of action in its own column, or add another column to determine action.

What options do I have for getting at the "format" that comes back in the spreadsheet object?

Thanks

Answer 1

Relying on cell color sounds brittle IMO. Assigning an explicit action column would be a better approach IMO.

That said, it is possible to access the color. However, there are no built in CF methods. You must dip into the underlying POI. First iterate through the cells in the spreadsheet:

<cfscript>
   // get the sheet you want to read
   cfSheet = SpreadSheetRead("c:/path/to/somefile.xlsx"); 
   workbook = cfSheet.getWorkBook();
   sheetIndex = workbook.getActiveSheetIndex();
   sheet = workbook.getSheetAt( sheetIndex );


   // process the rows and columns
   rows = sheet.rowIterator();
   while (rows.hasNext()) {
       currentRow = rows.next();

       // loop through populated cells in this row
       cells = currentRow.cellIterator();
       while (cells.hasNext()) { 
           currentCell = cells.next();

           // .... get color
       }
    }
</cfscript>

Then for each cell, extract the style color. Not tested, but something like this should work. (See XSSFColor)

   cellColor = currentCell.getCellStyle().getFillForegroundColorColor();
   colorValue = cellColor.getARGBHex(); 

Update:

As @Sean mentioned in the comments, CF9 does not have the method above. Unfortunately getFillForegroundColorColor() and getARGBHex() were introduced sometime around 3.7, but CF is bundled with an earlier version: 3.5 (I think). So you must use the indexed color method instead (or upgrade the POI jars).

    // only create once
    colors = createObject("java", "org.apache.poi.ss.usermodel.IndexedColors");

    //....
    cellColor = currentCell.getCellStyle().getFillForegroundColor();
    if (cellColor == colors.RED.getIndex()) {
       WriteDump("This cell is RED. Do something...");          
    }