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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2694

    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;
    }
  • doug.brown

    SSCertifiable

    Points: 5558

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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • sterling3721

    SSC-Addicted

    Points: 445

    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.

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

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