Cell Color in Excel

[Ram]

Hello - I am in dire straits trying to figure out the following. I am importing a bunch of excel files that have color coded cells. Specifically it has 3 types of cell background colors (Yellow, Red and White). Based on the cell color I have to perform a manipulation in SAS. These cells can be in any column in the excel sheet. Please help! Its a weird problem that I need to figure out. Please help! Sample code will be much appreciated.

[jakarman]

Never seen the color value alone of a cell as something that could be read by SAS.

Being created from SAS yes, reading no. You will need an additional step/analyses  

[Reeza]

I've tried using DDE to obtain the style/color of a cell before to no luck .

You could create a VBA macro that calculated the color of each cell as a number and then use DDE to run that macro and then import the data. It depends on how much pre-work you can do in the Excel workbook mostly and how consistent the format of the workbook is.

[esjackso]

This is interesting Reeza ... if I understand you correctly you basically have a matrix that matches with the actually data that pinpoints the cells color. In my head you then parse that data (which variable is colored and what color it is) and create flags that you can merge back to the original data and use for the further requirements.

Is this VBA code something you have used before?

EJ

[jakarman]

Some weird idea, Possible to try.

If you save the spreadsheets in xml format is the color encoding still available?

Simple check by storing a small spreadsheet and viewing it in notepad.

If yes then the attribute is stored in the xml-file and must be able to be read somehow.

If no then you have an additional argument not to work with color attributes in that way.

[esjackso]

I run into this alot as well with not a lot of success being able to pull in and use the cell coloring in SAS. I usually just have to duplicate the excel algorithm that lead to the colors in SAS before even doing the next step (and this assumes there is logic that is repeatable behind the colorings).

I would be interested to see others solutions for this as well!

EJ

[jakarman]

The new excel interface xlsx are xml. You can rename a file like to the common zip extension for viewing.

Looks promising the submap xl\worksheets is containing the xml of the several sheets with the s indications of the colors. Next question: has someone ever tried this? is there a standard approach or must xmlmapper be used.

[Reeza]

In the XML approach you'll need to process multiple XML files. 

Though it does look like it will contain the rule for conditional formatting and then the style would be in the styles.xml sheet, so if its a conditionally formatted sheet then the XML approach is the easiest to work with.

But examining the rules once and implementing in SAS is probably more efficient for consistently formatted files.

I have used VBA code embedded and called from SAS before, not in this particular context. My issue is that I usually embed my macros into the sheets ahead of time. I *think* there's a way to embed a macro in a sheet using DDE but never tried that route. I don't see anything wrong with the theory...but so far just theory

[jakarman]

Reeza, The stylesheets are not really needed. the xl/worksheets is containing all needed information.

The stylesheets are defining the way it is presented logic like a CSS stylesheet, Changing a style can change all the way it look like wihtout having touched the real infromation.behind. Therefore the colors by it self are not trustwothy information but the different indicators to colors is. 

[Reeza]

In the one I tested the sheet1.xml showed that the cells were colored differently, but not why.
For example it gave B3/B5/B6 as one color and B8/B9/B10/B12 as a second color, however it doesn't state the color. To get the colour you need to go to the stylesheets, from what I can see.

From when I've had to do things like this, the color was the important indicator...though I suppose the style could be used, either way you need to open the excel file at least once to verify logic.

Jaap Karman wrote:
Changing a style can change all the way it look like wihtout having touched the real infromation.behind. Therefore the colors by it self are not trustwothy information but the different indicators to colors is.  

I agree, however usually people who color code things don't seem to think the same way

[jakarman]

And than there are many man being colorblind. Not me, but I know some of them.

We are discriminated by nature as at women this is a more rare situation. 

[jakarman]

http://support.sas.com/resources/papers/proceedings13/315-2013.pdf

just a pity the xlsx reading is not done.

[Reeza]

Here's an example using DDE, with the get.cell(63, Reference) function:

http://www.pharmasug.org/proceedings/2012/AD/PharmaSUG-2012-AD23.pdf

[jakarman]

Reeza, I did some more checking on the backgrounds on the xlslx.

The XML words are telling something different. .docx .xlsx are open standards. It is freely available. Publicly Available Standards

See  ISO-IEC_29500 "OPEN Office XML file formats".

They are implemented with Microsoft and SAS is starting to adapt them.xlslx is basically a Zip file containing map/folders and xml files. Zip is coming into SAS 9.4. The xlsx access will run at all SAS machines as (unix included) DBMS=XLSX (9.3m1).

There is NO Access/PC files needed for that. There is no dependency on Windows so this will be the future for more secured environments (as in health area) running on Unix machines/servers. 

SAS did not provide the attributes in the xlsx interface (yes). could be a good enhancement request.

Open source is hitting that approach waht is happening there. R is a buzzing hype. Guess what:

http://www.r-bloggers.com/quickly-read-excel-xlsx-worksheets-into-r-on-any-platform/

They are starting also that xml approach, as they have to R is far more open source to be read as Unix based. 

[Ram]

Thanks for all your comments/help.