Post reply

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

  • 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,

  • 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!

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply