3

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

illiquent
  • 171
  • 2
  • 11

1 Answers1

5

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...");          
    }
Community
  • 1
  • 1
Leigh
  • 28,765
  • 10
  • 55
  • 103
  • 1
    if you know which cell it is you can grab it without iterating like so: `ss.getWorkbook().getSheetAt(0).getRow(0).getCell(0).getCellStyle().getFillForegroundColor()` where `ss` is your spreadsheet object, and sheet 0 is the sheet you want, and, in this example you want cell A1 (0,0). Also, I found in my testing that `getFillForegroundColor()` is actually what you want, not background. YMMV. – Sean Coyne Jan 15 '14 at 23:17
  • 1
    (Edit) Oops, copy paste error. Good catch @Sean. Yep. If you are not familiar with the POI stuff just a) watch out for nulls b) take care with indexes which start at zero (0), rather than one (1) as in CF. – Leigh Jan 15 '14 at 23:20
  • Well, all he wants to do is figure out if it is green. So he doesnt need the RGB code. He just needs to match it to the green code. You can get the code for "green" by running `greenCode = createobject('java', 'org.apache.poi.hssf.util.HSSFColor$GREEN').getIndex();` which returns 17. You can then check the result of `getFillForegroundColor()` in my above comment to get the cell's color index. If they are match then the cell is green! You can get the various color codes from here: http://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html just replace GREEN with the color – Sean Coyne Jan 15 '14 at 23:47
  • @SeanCoyne - No, it was my mistake. Trying to do too many things at once. I forgot the desired method really *is* named xxxxColorColor ;-) – Leigh Jan 15 '14 at 23:53
  • Well, another way to do it would be something like this (just a different take on Leigh's). Match the color index to known indices. https://gist.github.com/seancoyne/8447155 – Sean Coyne Jan 15 '14 at 23:54
  • 1
    Hmm, my style object doesn't have a method called getXXXColorColor(). Throws an error there. You sure @Leigh? – Sean Coyne Jan 15 '14 at 23:57
  • @Sean - Yep. Which version of CF are you running? getFillForegroundColorColor() works w/CF9.0.1. Re: Using indexed colors, it depends on the file format. The docs mention it is deprecated for XSSF, but for the legacy HSSF format, it is fine. – Leigh Jan 16 '14 at 00:06
  • If you dump the style, what methods do you have? – Leigh Jan 16 '14 at 00:25
  • 1
    I have `getFillForegroundColor()` and `getFillBackgroundColor()` both of which return a short. But no ColorColor() versions of those methods. – Sean Coyne Jan 16 '14 at 00:28
  • Hmm.. weird. Let me check the jars and CF10. Make sure I am not running a newer version of POI on that machine. – Leigh Jan 16 '14 at 01:19
  • Ugh... turns out my test machine was using a newer version 3.7 than ships with CF9. The sad part is 3.7 is pretty old .. and CF10 ships with an even older version! Time to upgrade POI. – Leigh Jan 16 '14 at 04:14