SSIS Script Task - Need to color code excel cell based on certain value

• tan110

  Hall of Fame

  Points: 3855

  More actions

  March 3, 2011 at 6:40 am

  #232929

  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,

• Lowell

  SSC Guru

  Points: 323524

  More actions

  March 3, 2011 at 7:26 am

  #1294007

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  March 3, 2011 at 9:22 am

  #1294090

  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:

  http://www.visualbasicask.com/visual-basic-general/ssis-script-task--get-only-1-cells-data-from-excel-source-file.shtml

  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