SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to export excel sheet with SPECIAL formats


Need to export excel sheet with SPECIAL formats

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39368 Visits: 14411
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
manibad
manibad
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 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..
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39368 Visits: 14411
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
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10557 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

    Group: General Forum Members
    Points: 51290 Visits: 21156
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10557 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Orlando Colamatteo
    Orlando Colamatteo
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39368 Visits: 14411
    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
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10557 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

    Group: General Forum Members
    Points: 51290 Visits: 21156
    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 ... :-D


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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10557 Visits: 7891
    Phil Parkin (1/10/2013)
    If people pledge enough beer money, I might even be tempted to investigate ... :-D


    :-D Like your motivation :-D

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search