Need to export excel sheet with SPECIAL formats

Question

Need to export excel sheet with SPECIAL formats

manibad

SSCommitted

Points: 1556
More actions
January 2, 2013 at 9:35 pm

#268162

Hi,
I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it using Scripts via script component in SSIS but my question is there any other way in doing it so.
Please explain it if there is a way in doing it so.
Thanks in advance:)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Henrik Schütze Ten Centuries Points: 1004 More actions · Answer 1

manibad (1/2/2013)
Hi,
I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it using Scripts via script component in SSIS but my question is there any other way in doing it so.
Please explain it if there is a way in doing it so.
Thanks in advance:)

What do you mean special Formats?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.

One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

manibad SSCommitted Points: 1556 More actions · Answer 3

Special formats for example...in excel sheet if you choose "Special format" under that "Social security number" the data specified in such a column would be always in SSN format..

I think i am clear...shld i explain more...

🙂

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

opc.three (1/3/2013)
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.
One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.

+1 to this second idea.

Or, in a similar vein, have an Excel template which contains a 'Format' button & associated VBA code to allow the formatting to be done by whoever opens the Excel file after the export has completed

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manibad SSCommitted Points: 1556 More actions · Answer 5

opc.three (1/3/2013)
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.
One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.

Using file system task i was able to copy the file template from one location to other.Using data flow task i manipulated the source using source and then i coded to execute the file system task.

In dataflow task,

I sorted the data and then i connected with excel destination.Where the destination is the template.

I am getting the output without special formats.

Data flow task and file system task was completed successfully.

What to do for rectifying the above and obtaining the desired output.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

The formatting will be pre-set in the "template" file. Open your "template" in Excel, adjust the formatting so it appears as you would like (e.g. bold column headers, background colors, etc.), save the "template" xlsx file and then close Excel. Then re-run your SSIS package. The outcome should be that the output file, the one you loaded data to, should also contain the formatting taken from the "template".

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

manibad SSCommitted Points: 1556 More actions · Answer 7

I will list out the things what i have done.

1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))

2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.

3.Package will be executed in this order Dataflow task->File system task

Note:Format.xlsx was placed after assigning the special formats to the worksheet.

but still i am getting the exported file with general format.I dint get it with special formats.

Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

manibad (1/7/2013)
I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task
Note:Format.xlsx was placed after assigning the special formats to the worksheet.
but still i am getting the exported file with general format.I dint get it with special formats.
Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.

Pre-requisite: open Format.xlsx (template file) in Excel and apply formatting as needed. Save to disk in original location as original name Format.xlsx.

SSIS Workflow:

1. In file system task, set to Copy source path Format.xlsx (template file) to destination file name is destiny.xlsx (copy of template file). Here the destination file is the copy of template file.

2. In data flow task,OLE DB source->Sort->Data conversion->Excel destination (target is destiny.xlsx (copy of template file))

3. Package will be executed in this order File system task->Dataflow task

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

manibad SSCommitted Points: 1556 More actions · Answer 9

opc.three (1/7/2013)
manibad (1/7/2013)
I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task
Note:Format.xlsx was placed after assigning the special formats to the worksheet.
but still i am getting the exported file with general format.I dint get it with special formats.
Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.
Pre-requisite: open Format.xlsx (template file) in Excel and apply formatting as needed. Save to disk in original location as original name Format.xlsx.
SSIS Workflow:
1. In file system task, set to Copy source path Format.xlsx (template file) to destination file name is destiny.xlsx (copy of template file). Here the destination file is the copy of template file.
2. In data flow task,OLE DB source->Sort->Data conversion->Excel destination (target is destiny.xlsx (copy of template file))
3. Package will be executed in this order File system task->Dataflow task

after performing the above steps too i am unable to achieve my goal.

I am unable to find where is the error.

actually special format is nothing but the formats such as Zipcode,zipcode+4,Home phone number and social security number...

this is what i have to achieve...

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

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 SSCommitted Points: 1556 More actions · Answer 11

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 SSC Guru Points: 182279 More actions · Answer 12

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 SSC-Forever Points: 47174 More actions · Answer 13

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);

Phil Parkin SSC Guru Points: 246961 More actions · Answer 14

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin