I've got data in SQL Server 2000 and have a HyperLink that goes to a pass-through form whose code-behind will output the data to an Excel file. I've been following this tutorial:
http://www.dzone.com/links/r/export_gridview_to_excelcsv_in_net_using_c.html
I have succeeded in outputting some sample values from the DataReader. First problem I'm encountering is that there is no DataTable Load method in 1.1. I have data coming back via the DataReader but what I need help with is how to create the headers and output them, along with the rows of data, to the Excel file...
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
string attachment
= "attachment;filename=Report_" + DateTime.Now.ToString() + ".xls";
Response.AddHeader("content-disposition", attachment);
Response.Charset = string.Empty;
Response.Cache.SetCacheability(System.Web.HttpCacheability.Public);
Response.ContentType = "application/ms-excel";
DataTable dt = new DataTable();
dt.Columns.Add("Company");
dt.Columns.Add("Address1");
dt.Columns.Add("Address2");
dt.Columns.Add("City");
dt.Columns.Add("State");
dt.Columns.Add("ZipCode");
SqlConnection con = new SqlConnection();
SqlCommand com = new SqlCommand();
con.ConnectionString = "myconnstring";
com.Connection = con;
com.CommandText
= "SELECT DISTINCT Company, Address1, Address2, City, State, ZipCode" +
" FROM Vendor_View";
con.Open();
SqlDataReader dr = com.ExecuteReader();
while(dr.Read())
{
// how to grab and output data to Excel?
}
If it's simple data, then just emit a CSV file. Excel can be configured to open those pretty easily.
Something like the following would get you started:
I myself wrote a blog post about this. Basically there are 3 alternatives. But I recommend this one: