Creating pivot table in excel using Microsoft.Office.Interop.Excel MVC 5 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.






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