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#.

The resource has not been reviewed by Editors yet. Readers are advised to use their best judgement before accessing this resource.
This resource will be reviewed shortly.
If you think this resource contain inappropriate content, please report to webmaster.
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: