Excel file create

Gangadhar Jagtap

5y984.NET

Hi all I want export data from datagridview in excel using asp.net. please suggest Thanks

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
private void exportToExcel_Click(object sender, EventArgs e)
   {
       try
       {
           Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
           Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
           Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
           app.Visible = true;
           worksheet = workbook.Sheets["Sheet1"];
           worksheet = workbook.ActiveSheet;
           worksheet.Name = "Records";

           try
           {
               for (int i = 0; i < transcationTableDataGridView.Columns.Count; i++)
               {
                   worksheet.Cells[1, i + 1] = transcationTableDataGridView.Columns[i].HeaderText;
               }
               for (int i = 0; i < transcationTableDataGridView.Rows.Count; i++)
               {
                   for (int j = 0; j < transcationTableDataGridView.Columns.Count; j++)
                   {
                       if (transcationTableDataGridView.Rows[i].Cells[j].Value != null)
                       {
                           worksheet.Cells[i + 2, j + 1] = transcationTableDataGridView.Rows[i].Cells[j].Value.ToString();
                       }
                       else
                       {
                           worksheet.Cells[i + 2, j + 1] = "";
                       }
                   }
               }

               //Getting the location and file name of the excel to save from user.
               SaveFileDialog saveDialog = new SaveFileDialog();
               saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
               saveDialog.FilterIndex = 2;

               if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
               {
                   workbook.SaveAs(saveDialog.FileName);
                   MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
               }
           }
           catch (System.Exception ex)
           {
               MessageBox.Show(ex.Message);
           }

           finally
           {
               app.Quit();
               workbook = null;
               worksheet = null;
           }
       }
       catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
   }
+3
alice yang5y ago
Check this:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
+1
Sachin Singh5y ago
please use below code for web application
protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();

        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }

        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}
+4
Gangadhar Jagtap5y ago
Hi
I have already added reference and change name for gridview
but error is not resolve
below added reference

+1
Sachin Singh5y ago
You just need to reference Microsoft.Office.Interop.Excel and replace transcationTableDataGridView with your dataGridview and thats it.
+1
Gangadhar Jagtap5y ago
Hi
I have added reference but still getting below error

please let me know is there any mistake happen or add any reference
thanks
+1
Rajanikant Hawaldar5y ago
@Gangadhar, open the solution explorer window expand your project folder accordion. right click on the references element select "add reference" from the dropdown select the .NET tab and look for the object library called Microsoft.Office.Interop.Excel click ok. transcationTableDataGridView is grid control name given in @Sachin singh example. i think you have just copy pasted so you need add required references and use.
+1
Gangadhar Jagtap5y ago
Hi

i am getting bellow 23 types of errors

Severity Code Description Project File Line Suppression State
Error CS0426 The type name 'Application' does not exist in the type 'Office.Interop.Excel' WebApp C:\Users\hp\source\repos\WebApp\WebApp\Data.aspx.cs 34 Active
Severity Code Description Project File Line Suppression State
Error CS1061 'Office.Interop.Excel._Application' does not contain a definition for 'Workbooks' and no accessible extension method 'Workbooks' accepting a first argument of type 'Office.Interop.Excel._Application' could be found (are you missing a using directive or an assembly reference?) WebApp C:\Users\hp\source\repos\WebApp\WebApp\Data.aspx.cs 35 Active

Severity Code Description Project File Line Suppression State
Error CS1061 'Office.Interop.Excel._Worksheet' does not contain a definition for 'Name' and no accessible extension method 'Name' accepting a first argument of type 'Office.Interop.Excel._Worksheet' could be found (are you missing a using directive or an assembly reference?) WebApp C:\Users\hp\source\repos\WebApp\WebApp\Data.aspx.cs 40 Active
Severity Code Description Project File Line Suppression State
Error CS0103 The name 'transcationTableDataGridView' does not exist in the current context WebApp C:\Users\hp\source\repos\WebApp\WebApp\Data.aspx.cs 44 Active

some of few errors
+1
Rajanikant Hawaldar5y ago
@Gangadhar, No problem. if you want have mutiple sheets in excell for each table that is also possible.
+1
Gangadhar Jagtap5y ago
Hi Thanks for reply In my database there is an two tables Registration and Admission so is there any difficulty to export different tables in excel. Thanks
+1
Rajanikant Hawaldar5y ago
https://www.c-sharpcorner.com/article/export-Asp-Net-datagrid-to-excel/
https://www.c-sharpcorner.com/blogs/export-to-excel-from-datagridview-in-asp-net1
+2