Create Excel (XLSX) file from Stored Procedure

Question

Create Excel (XLSX) file from Stored Procedure

sackmeister

SSChasing Mays

Points: 607
More actions
March 25, 2020 at 1:13 pm

#3736890

Hi,
I've been searching for current code that will allow me to create an Excel file from the contents of a SQL Server table. I'm just looking for the simplest approach. Does anyone have working code they would be willing to share with me? We are on SQL Server 2017. Also, I want to be able to do this from T-SQL, not export via SSMS, and not SSIS. Just looking for a working code example.
Thanks in advance!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

You'll need to enable 'Ad Hoc Distributed Queries' as a server configuration if it is not already enabled to perform this task using T-SQL.

EXEC sp_reconfigure 'show advanced options', 1';
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Once enabled, you'll need to have the Excel sheet already created with the column names from the table you're wishing to export. This is one of the downsides it seems from trying to perform this task, as we'd typically like to create the Excel file dynamically. You should then be able to use the following code to export the data to your created Excel file with the appropriate provider. In this case, the ACE.OLEDB provider.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 8.0;Database=C:\Path\To\Your\File.xlsx;', 
                       'SELECT col1, col2, col3 FROM [Sheet1$]')
SELECT col1, col2, col3
FROM dbo.MyTable;

I found a few resources along the way that might explain this with some additional context:

https://midnightprogrammer.net/post/t-sql-export-table-to-excel/

https://stackoverflow.com/questions/13888082/ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null-returned-m

sackmeister SSChasing Mays Points: 607 More actions · Answer 2

Thank you so much for the fast response!! This is almost exactly what I was looking for, but missing a few bits.

I need the code to create the Excel file, the code you provided expects the Excel file to be already created with matching column headers, can you provide code to create the Excel file.
Once I create the Excel file, and I do any minor cosmetic changes? I'd like to Auto-Fit the column widths and bold the column headers in the first row.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

One thought on how to create the excel file dynamically would be to have a "template" excel file that has the table headers already entered, but no data and then an xp_cmdshell call to copy the template file you created to the new location. Downside to this is first, you need to have 1 excel template per table you want to export and second, you need to turn on xp_cmdshell.

To me, this task sounds like it would be a LOT easier to do in C# (or any .NET language) than from SQL Server. Using C# you can pull the data from SQL Server and push it to an excel file, including one that doesn't exist yet by creating it in C#. And you can do all the formatting from C# that you can do in Excel -set the widths, set the height, set the font, color, background etc. Your task sounds like a task that is better suited for .NET than SQL Server.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

As far as I know (and I will happily be proved wrong), programmatic formatting of Excel sheets requires the installation of the Excel application. Without that, you do not have access to the Excel interop, which is what is required here.

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

you can install OpenXML SDK and use that to create Excel Files without the need for Office to be installed.

But yes it does require coding in C# to do the task

This reply was modified 4 years, 8 months ago by frederico_fonseca.
This reply was modified 4 years, 8 months ago by frederico_fonseca.

sackmeister SSChasing Mays Points: 607 More actions · Answer 6

I just want to thank everyone who responded so quickly. You all make this a great community. I got enough here to get this to work for my needs, so thank you to all of you who took the time to help!!!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

frederico_fonseca wrote:

you can install OpenXML SDK and use that to create Excel Files without the need for Office to be installed.
But yes it does require coding in C# to do the task

For some reason, I always seem to forget that this thing exists. Thanks for jumping in and posting this!

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