How to Export data to Excel in ASP.Net

Introduction:

In this article i will explain how to Create an Excel (or) Export data into Excel in ASP.Net.

Description:

In previous articles i explained about ListView. Now i will explain how to Create an Excel (or) Export data into Excel in ASP.Net.

Here in this article, i'm getting all employees data from database. After that excel will be created based on employees data.

1. Create a ASP.Net web site name it as 'ExcelDemo'.

2. Add New Folder to the solution and name it as 'ExcelFiles'.

3. Similarly add on more folder and name it as 'ExcelTemplate'.

4. Add one 'ExcelTemplate.xls' empty excel file to 'ExcelTemplate' folder. And also add one 'Notepad' to the same folder, name it as 'ReadMe.txt'.

In this article, this 'ExcelTemplate.xls' excel template is used to create the excels.

5. Now add Excel related References as shown below.

Following References added:

1. Microsoft.Office.Interop.Excel
2. Microsoft.Vbe.Interop
3. Office

Note:

In My system MS Office 2010 Professional 32-bit version is installed.

6. I added one Employee.aspx page to my solution. 

7. Add one Button control and one Label control.

8. In button click i'm getting employees data from database by using GetEmployees() method.

Add following NameSpaces:

using System.Data.SqlClient; using System.Data;

GetEmployees():

    #region GetEmployees()
    
    /// <summary>
    /// Get Employees data.
    /// </summary>
    /// <returns>DataTable</returns>

    private DataTable GetEmployees()
    {
        DataTable dtEmployees = null;
        try
        {
            string strConnection = @"Data Source = .; Initial Catalog = Test; Integrated Security= true;";
            SqlConnection con = new SqlConnection(strConnection);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select E.EmployeeID, E.EmployeeName, E.Email, D.DepartmentName from Employee E inner join Department D on E.DepartmentID = D.DepartmentID";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dtEmployees = ds.Tables[0];

            con.Close(); 

            return dtEmployees;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    #endregion

9. Now i'm exporting this data to excel by using GenerateExcel() method.

Add following NameSpace:

using Excel = Microsoft.Office.Interop.Excel;

GenerateExcel():

    #region GenerateExcel()

    /// <summary>
    /// Generate Excel.
    /// </summary>
    
    private object missing = System.Reflection.Missing.Value;
    public void GenerateExcel()
    {
            Microsoft.Office.Interop.Excel.Application oXL = null;
            Excel.Workbooks oWBs = null;
            Excel.Workbook oWB = null;
            Excel.Worksheet oSheet = null;
            Excel.Range oRng = null;
            GC.Collect();

            string strFile = string.Empty;

            // Excel Files Path.
            string strCurrentDir = @"G:\ProjectWork\ExcelDemo\ExcelFiles\";

            // Excel Template Path.
            string ExcelTemplatePath = @"G:\ProjectWork\ExcelDemo\ExcelTemplate\ExcelTemplate.xls";
            try
            {
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.DisplayAlerts = false;

                oWBs = oXL.Workbooks;
                oWB = (Excel.Workbook)(oWBs.Open(ExcelTemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing));
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;

                // GetEmployees.
                var dtEmployees = GetEmployees();

                // Check wether data exist or not.
                if (dtEmployees!= null && dtEmployees.Rows.Count > 0)
                {
                    // Heading
                    oSheet.Cells[1, 1] = "Employees Data";

                    # region Column Names

                    const int headStartRow = 3;
                    // Row Names.
                    oSheet.Cells[headStartRow, 1] = "Employee ID";
                    oSheet.Cells[headStartRow, 2] = "Employee Name";
                    oSheet.Cells[headStartRow, 3] = "Email";
                    oSheet.Cells[headStartRow, 4] = "Department";

                    #endregion

                    #region Employees Data

                    for(var i = 0; i<dtEmployees.Rows.Count; i++){
                        oSheet.Cells[headStartRow + 1 + i, 1] = dtEmployees.Rows[i]["EmployeeID"].ToString();
                        oSheet.Cells[headStartRow + 1 + i, 2] = dtEmployees.Rows[i]["EmployeeName"].ToString();
                        oSheet.Cells[headStartRow + 1 + i, 3] = dtEmployees.Rows[i]["Email"].ToString();
                        oSheet.Cells[headStartRow + 1 + i, 4] = dtEmployees.Rows[i]["DepartmentName"].ToString();
                    }

                    #endregion

                    #region Excel Styles

                    #region Set Excel sheet Font,Color, borders.

                    // Set Font Size.
                    oSheet.Cells.Font.Size = 11;
                    // Removing Gridlines i.e., making grid lines color white.
                    // oSheet.Cells.Borders.ColorIndex = 2;

                    #endregion

                    #endregion

                    #region Excel sheet Merge Cells

                    // Heading
                    oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, 4]).MergeCells = true;
                    // Row 2.
                    oSheet.get_Range(oSheet.Cells[2, 1], oSheet.Cells[2, 4]).MergeCells = true;

                    #endregion

                    //Format A1:Z1 as bold, vertical alignment = center.
                    oSheet.get_Range("A1", "Z3").Font.Bold = true;
                    oSheet.get_Range("A1", "Z3").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    //AutoFit columns A:Z.
                    oRng = oSheet.get_Range("A1", "Z1");
                    oRng.EntireColumn.AutoFit();

                    strFile = "Employee" + ".xls";

                    oWB.SaveAs(strCurrentDir + strFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
                    lblMessage.Text = "Success: Excel Generated.";
                }
                else

                {
                    throw new Exception("Warning! No data found.");
                }
            }
            catch (System.Threading.ThreadAbortException ex)
            {
            }
            catch (Exception ex)
            {
                String errorMessage = ex.Message;
            }
            finally
            {
                if (oWB != null) { oWB.Close(false, ExcelTemplatePath, missing); }
                if (oWBs != null) { oWBs.Close(); }
                if (oXL != null) { oXL.Quit(); }
                if (oRng != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng); }
                if (oSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet); }
                if (oWBs != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBs); }
                if (oWB != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB); }
                if (oXL != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); }
                oXL = null;
                oWBs = null;
                oWB = null;
                oRng = null;
                oSheet = null;
                GC.Collect();
            }
    }

    #endregion

Now in button click event call GenerateExcel() method.

protected void btnExport_Click(object sender, EventArgs e)
{
    GenerateExcel();
}

Output:

Excel will be create in ExcelFiles folder.

Excel Ouput: