How to export data to Excel?

Question

Helloeveryone. I have Lots of datas in my App. So I want to export those datas to Excel. So I can edit those data in the Excel rather than in my App? How to do this? I use C#2008 and Excel 2007. Thanks.

Satyapriya Nayak · Answer

Hi Jack 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. 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 student; com = new OleDbCommand(str con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds student); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = student; } 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

Jack Smith · Answer

Hi Satyapriya.Thanks.That helps.Haha. But can you help me again. I want to set the a lignment to be centre in Excel. How to do this?

Satyapriya Nayak · Answer

Hi Jack This is a web application.I think you need windows version. Thanks

Jack Smith · Answer

Hi SatyapriyaThanks very much for helping me. And I like VB much better than C#haha.Your are very kind for offering me VB codes. But I am a very beginner.I don't know what is a .aspx file and the usage of it. What is your _default class a Form Class? Ifyes. I guss the form's name is Page. And there is a button named ImageButton1 a GridView control named g1. Am I right? But I did't find any control named GridView? I have only DataGridView control listed on my toolbox.Can you explain a little more about your circumstance.

Satyapriya Nayak · Answer

Hi Jack Normal 0 Default.aspx code @ Page Language =VB AutoEventWireup =false CodeFile =Default.aspx.vb Inherits =_Default %> DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd> html xmlns =http://www.w3.org/1999/xhtml> head runat =server> title > Untitled Page title > head > body > form id =form1 runat =server> div > div > asp : GridView ID =g1 runat =server> asp : GridView > br /> br /> asp : Label ID =Label1 runat =server Text =Export data to Excel BackColor =Yellow Font-Bold =True ForeColor =#FF3300> asp : Label > br /> asp : ImageButton ID =ImageButton1 runat =server ImageUrl = ~/images/Excel2.JPG /> form > body > html > Default.aspx.vb code Imports System.Data Imports System.Data.SqlClient Imports System.IO Partial Class _Default Inherits System.Web.UI.Page Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item( ConnectionString ).ToString() Dim con As New SqlConnection(strConnString) Dim str As String Dim com As SqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Protected Sub Page_Load( ByVal sender As Object  ByVal e As System.EventArgs) Handles Me .Load bindgrid() g1.Visible = False End Sub Sub bindgrid() con.Open() str = select * from SampleCustomer com = New SqlCommand(str con) sqlda = New SqlDataAdapter(com) ds = New DataSet sqlda.Fill(ds SampleCustomer ) g1.DataSource = ds g1.DataMember = SampleCustomer g1.DataBind() con.Close() End Sub Private Sub ExportToExcel( ByVal strFileName As String  ByVal dg As GridView) Response.Clear() Response.Buffer = True Response.ContentType = application/vnd.ms-excel Response.Charset =  Me .EnableViewState = False Dim oStringWriter As New System.IO.StringWriter Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter) g1.RenderControl(oHtmlTextWriter) Response.Write(oStringWriter.ToString()) Response.[End]() End Sub Protected Sub ImageButton1_Click( ByVal sender As Object  ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click g1.Visible = True ExportToExcel( Report.xls  g1) End Sub Public Overloads Overrides Sub VerifyRenderingInServerForm( ByVal control As Control) End Sub End Class Thanks