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