Creating pivot table in excel using Microsoft.Office.Interop.Excel MVC 5 C#


Hi friends today am going to show how to create pivot table in excel sheet using c#. I got the requirement but no clear code is found for the same in the internet. So I would like to post the article with clear c# code. Hope it will be useful for those who is looking for pivot table creation using c#.

Pivot table in excel can be created by using Microsoft.Office.Interop.Excel dll.

Using Microsoft.Office.Interop.Excel dll


    Step 1:
  • Take a new mvc 5 application add Microsoft.Office.Interop.Excel dll reference , In your project, right-click on "References" and select "Add Reference" Select "Extensions" on the left and look for Microsoft.Office.Interop.Excel.

  • Step 2:
  • Click on models folder add a class name it as ProductDTO. Add the properties as shown below


  • public class ProductDTO
    {
    public string categoery { get; set; }
    public string place { get; set; }
    public string name { get; set; }
    public string price { get; set; }
    public string units { get; set; }

    }

  • Click on modelsfolder add one more class name it as PivotDAO for data access. Add the following code as shown below


  • //This method is for getting data from database
    public List PivotData()
    {
    SqlDataReader dr = null;
    List _lstProductDTO = new List();
    if (con.State == ConnectionState.Open)
    {
    con.Close();
    }
    con.Open();
    SqlCommand cmd = new SqlCommand("select * from products", con);

    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
    ProductDTO _report = new ProductDTO();
    _report.categoery = !string.IsNullOrEmpty(dr["CATEGOERY"].ToString()) ? dr["CATEGOERY"].ToString() : "N/A";
    _report.place = !string.IsNullOrEmpty(dr["PLACE"].ToString()) ? dr["PLACE"].ToString() : "N/A";
    _report.name = !string.IsNullOrEmpty(dr["NAME"].ToString()) ? dr["NAME"].ToString() : "N/A";
    _report.price = dr["PRICE"].ToString() != null && dr["PRICE"].ToString() != "" ? dr["PRICE"].ToString() : "N/A";
    _report.units = !string.IsNullOrEmpty(dr["UNITS"].ToString()) ? dr["UNITS"].ToString() : "";

    _lstProductDTO.Add(_report);
    }
    return _lstProductDTO;
    }
    //this method will create pivot table in excel file
    public string OfficeDll()
    {
    string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Content/SbuReport.xlsx");
    int rows = 0;
    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(filepath);
    Excel.Worksheet excelworksheet = excelWorkBook.ActiveSheet;
    Excel.Worksheet sheet2 = excelWorkBook.Sheets.Add();
    try
    {
    sheet2.Name = "Pivot Table";
    excelApp.ActiveWindow.DisplayGridlines = false;
    Excel.Range oRange = excelworksheet.UsedRange;
    Excel.PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet
    Excel.PivotCaches pch = excelWorkBook.PivotCaches();
    pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[3, 3], "PivTbl_2", Type.Missing, Type.Missing);// Create Pivot table
    Excel.PivotTable pvt = sheet2.PivotTables("PivTbl_2");
    pvt.ShowDrillIndicators = true;
    pvt.InGridDropZones = false;
    Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("CATEGOERY"));
    fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    fld.set_Subtotals(1, false);

    fld = ((Excel.PivotField)pvt.PivotFields("PLACE"));
    fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    fld.set_Subtotals(1, false);

    fld = ((Excel.PivotField)pvt.PivotFields("NAME"));
    fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    fld.set_Subtotals(1, false);

    fld = ((Excel.PivotField)pvt.PivotFields("PRICE"));
    fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    fld.set_Subtotals(1, false);

    fld = ((Excel.PivotField)pvt.PivotFields("UNITS"));
    fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

    sheet2.UsedRange.Columns.AutoFit();
    pvt.ColumnGrand = true;
    pvt.RowGrand = true;
    excelApp.DisplayAlerts = false;
    excelworksheet.Delete();
    sheet2.Activate();
    sheet2.get_Range("B1", "B1").Select();
    excelWorkBook.SaveAs(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    excelApp.DisplayAlerts = false;
    excelWorkBook.Close(0);
    excelApp.Quit();
    Marshal.ReleaseComObject(excelWorkBook);
    Marshal.ReleaseComObject(excelApp);
    }
    catch (Exception ex)
    {
    excelWorkBook.Close(0);
    excelApp.Quit();
    Marshal.ReleaseComObject(excelWorkBook);
    Marshal.ReleaseComObject(excelApp);

    return ex.Message;
    }
    return filepath;
    }

    Step 3:
  • Click on controllers folder add new controller and add following code as shown below.



  • public ActionResult Index()
    {
    PivotDAO _piovtdao = new PivotDAO();
    List _list = new List();
    _list= _piovtdao.PivotData();
    _piovtdao.LoadExcelData(_list);
    return View();
    }

    public string PivotOffice()
    {
    string file = "";
    PivotDAO _office = new PivotDAO();
    file=_office.OfficeDll();
    string filename = Path.GetFileName(file);
    string mypath = System.Configuration.ConfigurationManager.AppSettings["root"];
    if (filename != null)
    {
    return mypath + filename;
    }
    else
    {
    return filename;
    }

    }

    Step 4:
  • Click on index action and add view and the view should contain the following code


  • @model IEnumerable<PivotExcelDownload.Models.PivotDTO>
    @{
    ViewBag.Title = "Index";
    Layout = null;
    }

    <h2>Index</h2>
    <script src="~/Content/jquery-1.11.0.min.js"></script>
    <script src="~/Content/jquery-ui-1.10.3.min.js"></script>
    <link href="~/Content/jquery-ui-1.9.2.custom.css" rel="stylesheet" />

    <script>
    function DownloadOfficeExcel()
    {
    debugger;
    var ActionUri = "@Url.Content("~/Home/PivotOffice")";

    $.post(ActionUri, null, function (data) {

    if (data == null || data=="")
    {
    debugger;
    alert("No matching records found");
    }
    else
    {
    debugger;
    window.location = data;
    }

    });
    }
    </script>

    @using (Html.BeginForm())
    {

    <input type="button" id="office" onclick="DownloadOfficeExcel();" value="OfficeExcel"/>


    }

    Step 5:
  • Finally the web.config should have your connection string and other root level settings as shown below.


  • <appSettings>
    <add key="webpages:Version" value="3.0.0.0"/>
    <add key="webpages:Enabled" value="false"/>
    <add key="ClientValidationEnabled" value="true"/>
    <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
    <add key="Connection" value="Data Source=DataSource;initial catalog=DatabaseName;user id=sa;password=123 ;persist security info=True;Pooling=False"></add>
    <add key="root" value="http://localhost/PivotExcelDownload/Content/" />
    </appSettings>

  • The out put of excel file will be as shown in the below screen shots.

  • offi

    4

  • Conclusion: Hope you have enjoyed the article. Thanks for reading it.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: