Tech Off Thread

14 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

convert dataset to excel for download in asp.net

Back to Forum: Tech Off
  • User profile image
    trisonics

    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?

  • User profile image
    odujosh

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

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

  • User profile image
    Matthew van Eerde

    I'd suggest .csv format instead of Excel.

  • User profile image
    trisonics

    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.

  • User profile image
    Matthew van Eerde

    I don't know a javascript way to do it (doesn't mean there isn't one) but if you use the Content-Disposition header you can trigger the Save dialog and suggest a filename

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    trisonics

    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();

    }

    }






     

  • User profile image
    Rossj

    Trisonics, what happens if your data contains a comma?

  • User profile image
    littleguru

    Rossj wrote:
    Trisonics, what happens if your data contains a comma?


    Ye. Escape code handling is completely missing in the code.

  • User profile image
    Matthew van Eerde

    Adding a double-quote at the beginning and the end, and doubling-up internal double-quotes, should take care of the escape code handling.

    sb.Append("\"" + dt.Columns[i].Replace("\"", "\"\"") + "\"");

    What to do with internal line breaks is an interesting question.

  • User profile image
    figuerres

    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.

  • User profile image
    trisonics

    Hi Figuerres,

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

  • User profile image
    mayu_shuiic​hi

    Sad[C][C][C]

  • User profile image
    mayu_shuiic​hi

    Sad[C]Embarassed

    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....

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.