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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.