convert dataset to excel for download in asp.net

I am having asp.net page where I have data. On the click of a
button the data in the dataset need to be downloaded on the client in excel format.
I donot want to have a postback for doing this as the data is already on the page.

can anyone help?

http://www.eggheadcafe.com/articles/20050404.asp

Then just use Ajax to past requests back and forth to the server.

Yes for compatibility CSV is better.

But can you suggest a way output the CSV string generated from the dataset to the browser so that the save open dialogue box is thrown by the browser.

The best option will be by javascript. can you give sample code.

Here is some code I used to output a CSV file for my db. It’s not commented and you’ll obviously have to alter it for your database. Please reply / email with any questions.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class Stat_GetCDF : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.ContentType = "text/csv";

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT [Name], [Question], [Choices] FROM [STAT_Fields] WHERE ([SID] = '" + Request.QueryString["SID"] + "') ORDER BY [SortOrder]", con);

System.Data.SqlClient.SqlDataReader read;

con.Open();

read = cmd.ExecuteReader();

System.Collections.Generic.Dictionary<string, string> ColChoices = new System.Collections.Generic.Dictionary<string, string>();

System.Collections.Generic.Dictionary<Int32, System.Collections.Generic.Dictionary<Int32, string>> LookupValues = new System.Collections.Generic.Dictionary<int, System.Collections.Generic.Dictionary<int, string>>();

while (read.Read())

{

ColChoices.Add( "C" + read.GetString(0), read.GetString(2));

}

read.Close();

cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM [STAT_tbls_" + Request.QueryString["SID"] + "]", con);

read = cmd.ExecuteReader();

read.Read();

for (int i = 0; i < read.FieldCount; i++)

{

if (read.GetName(i) != "CID")

{

string choicesText = ColChoices[read.GetName(i)];

string[] choices = choicesText.Split(';');

System.Collections.Generic.Dictionary<Int32, string> ChoiceValues = new System.Collections.Generic.Dictionary<int, string>();

int c = 0;

foreach (string choice in choices)

{

ChoiceValues.Add(c, choice);

c++;

}

LookupValues.Add(i, ChoiceValues);

}

}

System.Text.StringBuilder sb = new System.Text.StringBuilder();

for (int i = 0; i < read.FieldCount; i++)

{

if (read.GetName(i) != "CID")

{

int val = read.GetInt32(i);

if (val != -1)

{

sb.Append(read.GetName(i).Substring(1));

}

if (i < (read.FieldCount - 1))

{

sb.Append(",");

}

}

}

sb.AppendLine();

while (read.Read())

{

for (int i=0; i < read.FieldCount;i++)

{

if (read.GetName(i) != "CID")

{

int val = read.GetInt32(i);

if (val != -1)

{

sb.Append(LookupValues[i][val]);

}

if (i < (read.FieldCount - 1))

{

sb.Append(",");

}

}

}

sb.AppendLine();

}

Response.Write(sb.ToString());

con.Close();

Response.End();

}

}

Thanks both of you.

I have created a class that generates the csv string and prompts for open/save dialogue.  I am posting the code for ref. for anyone who would like to use.
 
The only backdraw is no column formatting can be done.

public class ExcelUtil

{

public void export2CSV(System.Web.UI.Page page, DataSet ds, string strFname)

{

string strCSV = createCSV(ds);

saveCSV(page,strCSV, strFname);

}

public void export2CSV(System.Web.UI.Page page, DataSet ds)

{

string strCSV = createCSV(ds);

saveCSV(page,strCSV, "report.csv");

}

private void saveCSV(System.Web.UI.Page page, string strCSV, string strFname)

{

page.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFname);

page.Response.AddHeader("Content-Length", strCSV.Length.ToString());

page.Response.Charset = "";

page.Response.Write(strCSV);

page.Response.Flush();

page.Response.End();

}

private string createCSV(DataSet ds)

{

// Create the CSV file to which grid data will be exported.

// First we will write the headers.

StringBuilder sb = new StringBuilder();

DataTable dt;

int intHdrSp=2, intTabSp=4;

for (int tabCnt = 0; tabCnt < ds.Tables.Count; tabCnt++)

{

dt = ds.Tables[tabCnt];

int iColCount = dt.Columns.Count;

for (int i = 0; i < iColCount; i++)

{

sb.Append(dt.Columns[i]);

if (i < iColCount - 1)

{

sb.Append(",");

}

}

//seperate the header and the data

for (int i=0; i < intHdrSp; i++)

{

sb.AppendLine();

}

// Now write all the rows.

foreach (DataRow dr in dt.Rows)

{

for (int i = 0; i < iColCount; i++)

{

if (!System.Convert.IsDBNull(dr[i]))

{

sb.Append(dr[i].ToString());

}

if (i < iColCount - 1)

{

sb.Append(",");

}

}

sb.AppendLine();

}

//seperate one table with another

for (int i = 0; i < intTabSp; i++)

{

sb.AppendLine();

}

}

return sb.ToString();

}

}

Trisonics, what happens if your data contains a comma?

As I recall Excel 2003 will read an XML file.

so that may be of use also....

I think I used that with table data.....

Oh yea that's right ...  MSDN has an option to download an xml file of your product keys.  that file reads into excel very nice.

just in case you wanted to try say a datatable in xml format.

Hi Figuerres,

Can you pl. post the deatils/code for everyone reference.

[C][C][C]

[C]

ei.....with regards to the topics mentioned here.....I also tried to export all my data from datagrid to excel using this code....

protected void ImageButton1_Click(object sender, ImageClickEventArgs e)

{

if (DataGrid1.Items.Count + 2 < 65536)

{

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment;filename=employee list.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.xls";

StringWriter stringWriter = new StringWriter();

HtmlTextWriter htmlwriter = new Html32TextWriter(stringWriter);

DataGrid1.HeaderStyle.BackColor = System.Drawing.Color.Yellow;

DataGrid1.AlternatingItemStyle.BackColor = System.Drawing.Color.White;

DataGrid1.HeaderStyle.Font.Size = 6;

DataGrid1.AlternatingItemStyle.Font.Size = 6;

DataGrid1.ItemStyle.Font.Size = 6;

DataGrid1.BorderStyle = BorderStyle.Inset;

DataGrid1.RenderControl(htmlwriter);

Response.Write(stringWriter.ToString());

Response.End();

}

else

{

this.Label4.Text = "Too many rows to export in excel file.";

this.Label4.Visible = true;

this.ImageButton1.Attributes.Add("onclick", "return alert('Excel file not created!')");

}

the problem is I wanted to have a link from my excel to my system for me to update or edit data....how can i do that? can anyone help me:(

I need urgent reply so pls.....help........[C]......

tnx...tnx.....tnx....