Create Excel (XLSX) file from Stored Procedure

  • sackmeister

    SSChasing Mays

    Points: 607

    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!

  • BTylerWhite

    Right there with Babe

    Points: 770

    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

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

    1. 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.
    2. 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: 22561

    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.

  • Phil Parkin

    SSC Guru

    Points: 244134

    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.

    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.

  • frederico_fonseca

    SSChampion

    Points: 14475

    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

  • sackmeister

    SSChasing Mays

    Points: 607

    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: 244134

    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!

    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.

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

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