Export Data From Database Table to Excel File in ASP.Net MVC

Now create another action method” ExportToExcel()” for exporting data from the data table. Here I am using Microsoft's “Microsoft.Office.Interop.Excel” library (for this your system must contain Microsoft Office 2007) for converting the data into Excel form. You can add this library by right-clicking on References. Now click on Add Reference, now click on Extension in the Assembly tab now select Microsoft.Office.Interop.Excel.

public ActionResult ExportToExcel()
{
    int i = 0;
    int j = 0;
    string sql = null;
    string data = null;
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
    
    xlApp = new Excel.Application();
    xlApp.Visible = false;
    xlWorkBook = (Excel.Workbook)(xlApp.Workbooks.Add(Missing.Value));
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
    
    string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
    SqlConnection con = new SqlConnection(conn);
    con.Open();
    
    var cmd = new SqlCommand("SELECT TOP 0 * FROM Person", con);
    var reader = cmd.ExecuteReader();
    
    int k = 0;
    for (i = 0; i < reader.FieldCount; i++)
    {
        data = reader.GetName(i);
        xlWorkSheet.Cells[1, k + 1] = data;
        k++;
    }
    
    char lastColumn = (char)(65 + reader.FieldCount - 1);
    xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
    xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
    
    reader.Close();
    
    sql = "SELECT * FROM Person";
    SqlDataAdapter dscmd = new SqlDataAdapter(sql, con);
    DataSet ds = new DataSet();
    dscmd.Fill(ds);
    
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        var newj = 0;
        for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
        {
            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
            xlWorkSheet.Cells[i + 2, newj + 1] = data;
            newj++;
        }
    }
    
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
    
    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
    
    return RedirectToAction("Index", "ExportToExcel");
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch
    {
        obj = null;
        //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Now, build and run your application.

This is your Index page with an Export to Excel link. Now click on the Export To Excel link and export the data as a .xls file. The Excel file will look like this.

If you have any issues or queries, then feel free to contact me.