Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Need to export excel sheet with SPECIAL formats Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
When I am talking about formatting I am talking about bolding the text in a column header, or setting the background color or a column of cells. What kind of formatting are you talking about?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405031
Posted Wednesday, January 9, 2013 3:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
I mean if i choose special format under that home phone number means the data will be like this (XXX) YYY-TTTT...or if i am going to choose social security number data present in such a column would be like this XXX-YY-TIOP...
Similarly date format too..
Post #1405049
Posted Wednesday, January 9, 2013 4:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
manibad (1/9/2013)
I mean if i choose special format under that home phone number means the data will be like this (XXX) YYY-TTTT...or if i am going to choose social security number data present in such a column would be like this XXX-YY-TIOP...
Similarly date format too..

I am not seeing a way to achieve it. The formatting will be lost when you overwrite the cell with data. The column header formatting will persist which is all I have used the technique I outlined for. You may be able to get what you want by writing your data to the spreadsheet in a Destination Script Component or Script Task by via the Excel Object Model, but that is quite another undertaking from using a simple Excel Destination.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405064
Posted Wednesday, January 9, 2013 5:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 1,787, Visits: 5,693
If you are using xlsx files, there is a way that is not too difficult if you are methodical...


  • Create your template file with formatting and some sample data.

  • Use your favourite zip tool (I like 7zip) to unzip the XLSX file (it is a zip archive that contains xml files)

  • Navigate to the xl\worksheets folder to find the xml file for your worksheet(s)

  • e.g. Sheet1.xml

  • Study the xml. You will see it contains a "sheetData" section which contains your sample data

  • 	<sheetData>
    <row r="1" spans="1:4" x14ac:dyDescent="0.25">
    <c r="A1" t="s">
    <v>0</v>
    </c>
    <c r="B1" t="s">
    <v>1</v>
    </c>
    <c r="C1" t="s">
    <v>2</v>
    </c>
    <c r="D1" t="s">
    <v>3</v>
    </c>
    </row>
    <row r="2" spans="1:4" x14ac:dyDescent="0.25">
    <c r="A2" s="4" t="s">
    <v>4</v>
    </c>
    <c r="B2" s="3" t="s">
    <v>5</v>
    </c>
    <c r="C2" s="2">
    <v>4441234</v>
    </c>
    <c r="D2" s="1">
    <v>24883</v>
    </c>
    </row>
    <row r="3" spans="1:4" x14ac:dyDescent="0.25">
    <c r="A3" s="4" t="s">
    <v>4</v>
    </c>
    <c r="B3" s="3" t="s">
    <v>5</v>
    </c>
    <c r="C3" s="2">
    <v>4441234</v>
    </c>
    <c r="D3" s="1">
    <v>24883</v>
    </c>
    </row>
    </sheetData>

  • Each "row" element represents a row in your sheet.

  • Each "c" element represents a cell in the row

  • The "r" attribute is the cell address

  • the t="s" attribute tells Excel the cell contains a "Shared String" - read up on these - if you have a lot of repeating values you can use these to save space in the file. For Shared Strings, the "v" node contains the Shared String number.

  • The "v" element is the cell value (or shared string number)

  • Now all you have to do is reproduce the "Sheet1.xml" file in SSIS / SQL Query and then zip your new file along with the other supporting files back up into a new XLSX file that has all your original formatting




MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1405086
    Posted Thursday, January 10, 2013 1:45 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 2:03 AM
    Points: 5,029, Visits: 11,759
    mister.magoo (1/9/2013)
    If you are using xlsx files, there is a way that is not too difficult if you are methodical...


    • Create your template file with formatting and some sample data.

    • Use your favourite zip tool (I like 7zip) to unzip the XLSX file (it is a zip archive that contains xml files)

    • Navigate to the xl\worksheets folder to find the xml file for your worksheet(s)

    • e.g. Sheet1.xml

    • Study the xml. You will see it contains a "sheetData" section which contains your sample data

    • 	<sheetData>
      <row r="1" spans="1:4" x14ac:dyDescent="0.25">
      <c r="A1" t="s">
      <v>0</v>
      </c>
      <c r="B1" t="s">
      <v>1</v>
      </c>
      <c r="C1" t="s">
      <v>2</v>
      </c>
      <c r="D1" t="s">
      <v>3</v>
      </c>
      </row>
      <row r="2" spans="1:4" x14ac:dyDescent="0.25">
      <c r="A2" s="4" t="s">
      <v>4</v>
      </c>
      <c r="B2" s="3" t="s">
      <v>5</v>
      </c>
      <c r="C2" s="2">
      <v>4441234</v>
      </c>
      <c r="D2" s="1">
      <v>24883</v>
      </c>
      </row>
      <row r="3" spans="1:4" x14ac:dyDescent="0.25">
      <c r="A3" s="4" t="s">
      <v>4</v>
      </c>
      <c r="B3" s="3" t="s">
      <v>5</v>
      </c>
      <c r="C3" s="2">
      <v>4441234</v>
      </c>
      <c r="D3" s="1">
      <v>24883</v>
      </c>
      </row>
      </sheetData>

    • Each "row" element represents a row in your sheet.

    • Each "c" element represents a cell in the row

    • The "r" attribute is the cell address

    • the t="s" attribute tells Excel the cell contains a "Shared String" - read up on these - if you have a lot of repeating values you can use these to save space in the file. For Shared Strings, the "v" node contains the Shared String number.

    • The "v" element is the cell value (or shared string number)

    • Now all you have to do is reproduce the "Sheet1.xml" file in SSIS / SQL Query and then zip your new file along with the other supporting files back up into a new XLSX file that has all your original formatting




    Wow - you've done this before? Nice.



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1405262
    Posted Thursday, January 10, 2013 2:39 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:29 PM
    Points: 1,787, Visits: 5,693
    Phil Parkin (1/10/2013)

    Wow - you've done this before? Nice.


    Not in SSIS, but I have manually - just to learn how to - no reason not to do it in SSIS though.

    I actually found out about it because I was struggling with reading XLSX files in some bespoke software and decided to use the OpenXML SDK, which then taught me a bit about the structure of the xlsx file.



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1405286
    Posted Thursday, January 10, 2013 4:16 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:38 PM
    Points: 7,097, Visits: 12,598
    Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1405332
    Posted Thursday, January 10, 2013 6:06 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:29 PM
    Points: 1,787, Visits: 5,693
    opc.three (1/10/2013)
    Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS


    Thanks for the nice words.

    Maybe it seems like a lot of steps, but it really is easy - just create the template, save it, unzip it and store it somewhere accessible.

    Then SSIS generates the Sheet.xml - easy enough - and zips it all up - easy enough.

    There are really only two steps to the SSIS part, unless you want to unzip it every time, but I can't see the point in that.

    It only gets more complicated if you also want to use the Shared Strings feature, but that is just another XML file to create in SSIS - one more step.

    As for an article...can I squeeze that in sometime in 2020 ... maybe?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1405373
    Posted Thursday, January 10, 2013 3:01 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 2:03 AM
    Points: 5,029, Visits: 11,759
    mister.magoo (1/10/2013)
    opc.three (1/10/2013)
    Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS


    Thanks for the nice words.

    Maybe it seems like a lot of steps, but it really is easy - just create the template, save it, unzip it and store it somewhere accessible.

    Then SSIS generates the Sheet.xml - easy enough - and zips it all up - easy enough.

    There are really only two steps to the SSIS part, unless you want to unzip it every time, but I can't see the point in that.

    It only gets more complicated if you also want to use the Shared Strings feature, but that is just another XML file to create in SSIS - one more step.

    As for an article...can I squeeze that in sometime in 2020 ... maybe?


    I'm wondering whether that <sheet> XML could be produced in pure T-SQL. If someone took the time to work this out, it would be a real service to the community. If people pledge enough beer money, I might even be tempted to investigate ...



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1405671
    Posted Friday, January 11, 2013 9:13 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:29 PM
    Points: 1,787, Visits: 5,693
    Phil Parkin (1/10/2013)
    If people pledge enough beer money, I might even be tempted to investigate ...


    Like your motivation


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1406123
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse