March 3, 2011 at 6:40 am
Does anyone have any examples of an SSIS script task to color code an excel cell based on certain value?
For instance if the cell value = 1 (color it green) ; if = 2 (color it red)
Thanks,
March 3, 2011 at 7:26 am
via SSIS, it's not possible; you'd have to automate excel in order to do that: formatting codes for excel cells are not exposed.
see this thread for Koen's more complete answer:
http://www.sqlservercentral.com/Forums/Topic1069138-148-1.aspx
a cheesy way to do it is to write the docuemtn as a table in html, rename the file to .xls extension, and let excel auto convert the document; in 2007 and above, for whatever reason files that convert like that seem to convert really slowly for me..
Lowell
March 3, 2011 at 9:22 am
Lowell (3/3/2011)
via SSIS, it's not possible; you'd have to automate excel in order to do that: formatting codes for excel cells are not exposed.see this thread for Koen's more complete answer:
http://www.sqlservercentral.com/Forums/Topic1069138-148-1.aspx
a cheesy way to do it is to write the docuemtn as a table in html, rename the file to .xls extension, and let excel auto convert the document; in 2007 and above, for whatever reason files that convert like that seem to convert really slowly for me..
If you read my answer (which I forgot I wrote. Must've been some time ago :-D), you'll see I suggested a script task to color code a cell as one of the alternatives.
I've never done it before myself, but I know you can do it in VBA. If you can do it in VBA, then you can sure as hell do it in a full fledged .NET script task
I think it is just a matter of referencing the correct assemblies and using the correct methods/functions.
This is an example of a script task retrieving the value of one cell:
You can modify it to get a bigger range where you loop over all the cells.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy