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

Transact-SQL
,
1

I need to create stored procedure or query or any thing on SQL server 2012 take datatable from csharp and divide data to multi sheet inside Excel File xlsx created .

Create File ABC.xlsx with two sheet first sheet name source and second sheet name types based on module and load data related to every sheet based on data related to every sheet .

so if i have two modules meaning I have two sheet .

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.Columns.Add("Module", typeof(int));

            dataTable.Rows.Add(1222,"micro","Abc","source",1);
            dataTable.Rows.Add(1321, "silicon", "Abc", "Types", 3);
            dataTable.Rows.Add(1444, "cd2", "AutoD", "Rev", 10);
            dataTable.Rows.Add(1321, "cd3", "AutoD", "source", 11);
            dataTable.Rows.Add(1541,"mtvscro", "AutoD", "Rev", 12);
            dataTable.Rows.Add(9811, "tvs2", "Mog", "Dal", 6);
            dataTable.Rows.Add(1901, "tvs3", "Mog", "Mondo", 6);
            dataTable.Rows.Add(2111, "toyo", "Mog", "Pingo", 7);

            return dataTable;
        }

are SQL server 2012 can divide data on excel file to multi sheet or not ?

I can do that by csharp but from SQL cannot

so How can I achieve that from SQL server 2012 by any way ?

2

hope this helps .. :slight_smile:

3

thank you but this link give me solution by using csharp
what I need exactly solution by sql server 2012 take data as table and with these data
create excel file with multi sheet

4

hi

try this ..

try this query and remember first put your database name

Exec master..xp_cmdshell 'bcp "use dbname; Select DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,
Avg(Meter1) As Meter1,
Avg(Meter2) As Meter2,
Avg(Meter3) As Meter3
From TableName
Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)
" queryout "e:\test.xls" -c -CRAW'

your out put would be e:\test.xls

hope it will help you

5

but this not include any thing to divide data to multi sheet

6

Straight from Sql = no. Using ssis with c# code yes