Excel file create

Question

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

Sachin Singh · 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 { worksheet.Cells[1, i + 1] = transcationTableDataGridView.Columns[i].HeaderText; } for ( int i = 0; i { for ( int j = 0; 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()); } }

Sachin Singh · Answer

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 = @ " .textmode { } " ; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); } } public override void VerifyRenderingInServerForm(Control control) { /* Verifies that the control is rendered */ }

Sachin Singh · Answer

You just need to reference Microsoft.Office.Interop.Excel and replace transcationTableDataGridView with your dataGridview and thats it.

Rajanikant Hawaldar · Answer

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

alice yang · Answer

Check this: https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

Gangadhar Jagtap · Answer

Hi I have already added reference and change name for gridview but error is not resolve below added reference

Gangadhar Jagtap · Answer

Hi I have added reference but still getting below error please let me know is there any mistake happen or add any reference thanks

Rajanikant Hawaldar · Answer

@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.

Gangadhar Jagtap · Answer

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

Rajanikant Hawaldar · Answer

@Gangadhar, No problem. if you want have mutiple sheets in excell for each table that is also possible.

Gangadhar Jagtap · Answer

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

Excel file create

Answers (11)