Here's sample code..This was done directly on the form..but its easily modifiable to be used with Classes and Objects..(I've included both Windows form app and ASP.NET app)
1. For Windows Form Application...
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn ;
string connectionString = null;
string sql = null;
connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT * FROM Product";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
2. For ASP.NET application..
private void toExcel()
{
Response.Clear();
Response.Buffer = true;
Response.AppendHeader("Content-Disposition", "attachment;filename=schoolsinfo.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("EN-US", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
GridView1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
}
Besides, the important issue about this is needed to pay attention: the format of data which is from dataview to excel cell output. So if the data format output in Excel is incorrect, you can configure the DataGrid1_ItemDataBound Event to inder to define the data format:
private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
e.Item.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
If the above helps..Accept answer and Vote..:)