How to generating Excel File with multi sheet by using SQL server 2012?

Question

How to generating Excel File with multi sheet by using SQL server 2012?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 6, 2020 at 3:48 am

#3761003
I need to create stored procedure or query or any thing on SQL server 2012 take data table from c# code
and create Excel file with multi sheet based on data Exist on data table .
From data below as Example I have Function GetDate return data table .
Create File ABC.xlsx with two sheet first sheet name source and second sheet name types
and load data related to every sheet based on data .
so Result will be Create file Abc.xlsx with two sheets source and Types and every sheet have one record
Are SQL server 2012 can create Excel File with multi sheet or not ?
I can do that by c# but from SQL cannot
so How can I achieve that from SQL server 2012 by any way ?
```
public static DataTable GetData() 
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("PartId", typeof(int));
            dataTable.Columns.Add("Company", typeof(string));
            dataTable.Columns.Add("Files", typeof(string));
            dataTable.Columns.Add("Tab", typeof(string));


            dataTable.Rows.Add(1222,"micro","Abc","source");
            dataTable.Rows.Add(1321, "silicon", "Abc", "Types");
            return dataTable;
        }
```

Viewing 4 posts - 1 through 3 (of 3 total)

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

doug.brown SSCertifiable Points: 5607 More actions · Answer 1

I don't know a way to do it directly with SQL, but you could do it using Visual Studio/SSRS to make a report. When you make a report with page breaks, those translate to different sheets when imported to Excel (and the Page Name property will label the Excel sheets). I'm sure one of our more informed community members will know a way to leverage SQL itself to make multi-sheet Excel output, but that's the only way I know how to do it.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

Thee are several methods for doing this but they're all too long to post here with any understanding. My recommendation is that you do a search for it and pick the method that suits you the best.

As an alternative, consider building a refreshable spreadsheet that gets data from an external source like a stored procedure, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sterling3721 SSChasing Mays Points: 652 More actions · Answer 3

I recommend sticking with your C#. This is probably the easiest and most flexible. If you want to explore options,

1. Telerik reporting, this can be easily put into front-end web application, together with some C# code.

2. SSRS, access via windows account is easy; could be very challenging to put into web application to access from outside the active directory; that's why I put Telerik reporting first.

3. use R. available in SQL 2016 or later, need to fetch data into SQL 2016 first from your SQL2012 via linked server, there is a learning curve to use R.

I use #1 and #2. For #3, I saw articles using R to generate multiple tabbed excel, so, it's doable.