I wrote a code to export my data in gridview to excel

Question

Hi,
I wrote a code to export my data in gridview to excel. But at runtime it throws an exception saying "syntax error in INSERT TO" on execution of the command. I stepped through the code to see the output of the sql statement and I see nothing wrong with it. Can anyone assist me quickly on this please? Very many thanks in advance.

NB: The table creates perfectly, but the INSERT statement gives the exception.

I attached my source code here for your perusal.

Satyapriya Nayak · Answer

Hi kacey, Try this... Untitled Page onclick="btn_search_Click" /> Text="Import to Excel" onclick="btn_import_Click" /> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Text; namespace import_the_gridview_data_in_excel { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlCommand cmd; SqlDataAdapter sqlda; DataSet ds; string str; protected void btn_import_Click(object sender, EventArgs e) { ExportToExcel("Report.xls", GridView1); } private void ExportToExcel(string strFileName, GridView dg) { Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); GridView1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } protected void btn_search_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); str = "select * from employee where Empname like '" + TextBox1.Text + "%' or Empcity like '" + TextBox1.Text + "%' or Empstate like '" + TextBox1.Text + "%'"; cmd = new SqlCommand(str, conn); sqlda = new SqlDataAdapter(cmd); ds = new DataSet(); sqlda.Fill(ds, "employee"); conn.Close(); GridView1.DataSource = ds; GridView1.DataMember = "employee"; GridView1.DataBind(); } } } Thanks If this post helps you mark it as answer

Kacey Ezerioha · Answer

Hi Satyapriya, Thank you very much for your responses and help. I really appreciate it. I tried your code but it was giving me exactly how my page looks like on the browser, on the excel file. So I brainstormed a little bit and came up with something that helped me get what I wanted. I got some help from the code u posted. So I'll give u the credit for assisting me in developing my own solution. Very many thanks Pal. Here's the attached file of my solution.

Kacey Ezerioha · Answer

Thanks. But maybe I should explain my requirement a little more further. I have this page that is used to search for students records using some search criteria like surname, firstname, registration number etc. The search results are based on entry of this criteria mentioned. If they are not entered, all the records in the database will be displayed in gridview. So I would want to export the exact data that is displayed on the gridview to excel, which is of course based on the search criteria entered by the user. The solution you provided works, but it's for all records, which is not what I'm looking for. I hope u understand my requirement? Many thanks pal.

Satyapriya Nayak · Answer

Hi kacey, In Asp.net Try this... Untitled Page Text="Import to Excel" /> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Text; namespace import_the_gridview_data_in_excel { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from employee", conn); DataSet ds = new DataSet(); sqlda.Fill(ds, "employee"); GridView1.DataSource = ds; GridView1.DataMember = "employee"; GridView1.DataBind(); } protected void Button1_Click(object sender, EventArgs e) { ExportToExcel("Report.xls", GridView1); } private void ExportToExcel(string strFileName, GridView dg) { Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); GridView1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } } } Thanks

Kacey Ezerioha · Answer

Thanks Satyapriya for your quick response. But Actually, I'm trying to do this in asp.net. Will your solution still work for it? Thanks.

Satyapriya Nayak · Answer

Hi kacey, Try this... using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.IO; namespace Export_to_Excel_in_windows { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["dsn"]; OleDbCommand com; DataSet ds; OleDbDataAdapter oledbda; DataTable dt; string str; public Form1() { InitializeComponent(); } private void btndisplay_Click(object sender, EventArgs e) { try { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = "select * from emp"; com = new OleDbCommand(str, con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds, "emp"); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = "emp"; } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btn_export_Click(object sender, EventArgs e) { //We have to add a reference to the Microsoft Excel object library. //Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library. 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; try { worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; worksheet.Name = "Exported from DataGridView"; for (int i = 1; i { worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText; } for (int i = 0; i { for (int j = 0; j { worksheet.Cells[i + 2, j + 1] = DataGridView1.Rows[i].Cells[j].Value.ToString(); } } string fileName = String.Empty; saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*"; saveFileExcel.FilterIndex = 2; saveFileExcel.RestoreDirectory = true; if (saveFileExcel.ShowDialog() == DialogResult.OK) { fileName = saveFileExcel.FileName; workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else return; } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { app.Quit(); workbook = null; app = null; } } } } Thanks

I wrote a code to export my data in gridview to excel

Roei Bar

Ken Barrett