none
Export Data to One Worksheet and Create Pivot Table in Another sheet Based on the Data in MVC RRS feed

  • Question

  • I am able to get the data to excell sheet1 from sqlserver database, now I need to create Pivot tables in sheet2 based on the excell sheet1 data.
    I am using MVC with entityframework with c# for my requirement.

    Here is sample code which used for getting data from datatable to excel. Now i need to create pivot tables based on the gettting excel data.

         using (XLWorkbook wb = new XLWorkbook())
                       {
                           wb.Worksheets.Add(dt); //Here dt is DataTable object
                           wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                           wb.Style.Font.Bold = true;                      
                           Response.Clear();
                           Response.Buffer = true;
                           Response.Charset = "";
                           Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                           Response.AddHeader("content-disposition", "attachment;filename= EmployeeReport.xlsx");

                           using (MemoryStream MyMemoryStream = new MemoryStream())
                           {
                               wb.SaveAs(MyMemoryStream);
                               MyMemoryStream.WriteTo(Response.OutputStream);
                               Response.Flush();
                               Response.End();
                               return Redirect("Analyse");
                           }
                       }  
    • Moved by CoolDadTx Friday, May 6, 2016 3:41 PM Office related
    Friday, May 6, 2016 6:29 AM

Answers

  • Hi Srinivas,

    The Office automation is not recommend on the server side. I suggest that you using the Open XML as a workaround.

    To get started creating a demo using Open XML, we can create a new workbook contained the data to create the pivot table. Then copy this workbook and create the pivot table using the new workbook.

    At last we can reflect the difference between the two workbooks using the Open XML SDK 2.5 Productivity Tool. Here is an screenshot for your reference:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 11, 2016 7:46 AM
    Moderator

All replies

  • To create Pivot table in another worksheet with C#, this article might help: Create Pivot Table in Excel in C#, VB.NET. Besides, you can also post your questions to asp.net to get help from there.

    Friday, May 6, 2016 9:47 AM
  • Hi Srinivas,

    The Office automation is not recommend on the server side. I suggest that you using the Open XML as a workaround.

    To get started creating a demo using Open XML, we can create a new workbook contained the data to create the pivot table. Then copy this workbook and create the pivot table using the new workbook.

    At last we can reflect the difference between the two workbooks using the Open XML SDK 2.5 Productivity Tool. Here is an screenshot for your reference:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 11, 2016 7:46 AM
    Moderator