Use COM Interop to Create an Excel in MVC

Introduction

In this tip, you will see how to create Excel files using Microsoft COM Interop in MVC project.

Using the Code

Add reference of COM Interop in project.

Include reference to class file.

//Including reference to class file
  using Microsoft.Office.Interop.Excel;

//Function to export excel file
 public ActionResult ExportToExcel()
 {
     string reportPath="your file path for excel";
     string reportName="YourReport.xlsb";

     System.Data.DataTable table = 
     GetDatatable();//-your code to create datatable and return it
     table.TableName = "Your Report Name"; 
     
     Microsoft.Office.Interop.Excel.Application excelApp = 
     new Microsoft.Office.Interop.Excel.Application();

     //Create an Excel workbook instance 
     Microsoft.Office.Interop.Excel.Workbook excelWorkBook = 
     excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

     Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
     excelWorkSheet.Name = Convert.ToString(table.TableName);
     excelWorkSheet.Columns.AutoFit();
               
     for (int i = 1; i < table.Columns.Count + 1; i++)
     {
        excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;                
     }

     for (int j = 0; j < table.Rows.Count; j++)
     {
        for (int k = 0; k < table.Columns.Count; k++)
        {
           excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
        }
     }
     
    //-- check file directory is present or not/if note create new
    bool exists = System.IO.Directory.Exists(reportPath);
    if (!exists)
    {
      System.IO.Directory.CreateDirectory(reportPath);
    }
     
     excelWorkBook.SaveAs(reportPath+reportName, 
     XlFileFormat.xlExcel12, Type.Missing, Type.Missing, 
     Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 
     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
     excelWorkBook.Close();
     excelApp.Quit();
     
     return File(reportPath+reportName, "application/vnd.ms-excel", reportName);      
    }
//