Use COM Interop to Create an Excel in MVC
This tip will provide one of the ways of generating Excel files using Microsoft COM interop in MVC project.
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);
}
//