Downloading DataSet to Excel

Reply

Member

5 Points

Downloading DataSet to Excel

Sep 19, 2014 01:04 PM|usftubaz|LINK

Hello everyone. I am having a problem with how Excel reads a file once it is saved. In a number of places in my ASP.NET/C# app I need to export data to Excel files. I have come up with a way to get the data in Excel fine, but I would like to fix the bugs along the way. The code will follow.

The first bug is the application used in the Response. The file download window shows the file type as Microsoft Excel 97-2003 Worksheet. I would like to have at least Excel 2007.

The second bug is when I open the file in Excel I get an error 'The file format and extension of '**filename**' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?'. When I open the file everything looks fine, all the data that is supposed to be there is there.

If anyone has a solution to these it would be greatly appreciated. Thanks.

Code:

        string path = "E:\\Temp\\" + ru.UserLastName + "-download.xls";
        if (!File.Exists(path))
        {
            using (StreamWriter sw = File.CreateText(path))
            {
                sw.Write("");
            }
        }

DataTable dt = new DataTable();

//Filling DataTable

        DataSet ds = new DataSet("Crack Data");
        ds.Tables.Add(dt);
        Save(path, ds);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment;filename=\"" + path + "\"");
        Response.TransmitFile(path);
        Response.End();

Reply

csharpgreg

Member

670 Points

188 Posts

Re: Downloading DataSet to Excel

Sep 19, 2014 02:31 PM|csharpgreg|LINK

Hello Usftubaz! Thanks for your post!

Please take a look at this post and see the correct answer by "bullpit": http://forums.asp.net/t/1221467.aspx?Export+GridView+to+Excel+xlsx+Excel+2007+Format

protected void bttnExportXL_Click(object sender, EventArgs e)
    {
       gridViewMaster.AllowPaging = false;
       gridViewMaster.AllowSorting = false;
        Response.Clear();

        //Response.AddHeader("content-disposition", "attachment;filename=Report_"+ DateTime.Now.ToShortDateString() +".xlsx");
        Response.AddHeader("content-disposition", "attachment;filename=Report.xlsx");
        Response.Charset = "";

        // If you want the option to open the Excel file without saving than
        // comment out the line below
        // Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        //Response.ContentType = "application/vnd.xls";
        
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        foreach (GridViewRow r in gridViewMaster.Rows)
        {
            if (r.RowType == DataControlRowType.DataRow)
            {
                for (int columnIndex = 0; columnIndex < r.Cells.Count; columnIndex++)
                {
                    r.Cells[columnIndex].Attributes.Add("class", "text");
                }
            }
        }
        
        gridViewMaster.RenderControl(htmlWrite);
        
        string style = @"&lt;style> .text { mso-number-format:\@; } </style> ";
        Response.Write(style);
      
        Response.Write(stringWrite.ToString());
        Response.End();
        
    }

Regards!

Reply

Eileen ni -...

All-Star

19919 Points

2016 Posts

Re: Downloading DataSet to Excel

Sep 28, 2014 01:05 AM|Eileen ni - MSFT|LINK

Hi usftubaz,

Thanks for your post.

To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:

var ds = new DataSet();
            var dt = new DataTable("TableName For Sheet1");
            dt.Columns.Add("col1");
            dt.Columns.Add("col2");
            dt.Rows.Add("Value1", "Value2");

            var dt2 = new DataTable("TableName For Sheet2");
            dt2.Columns.Add("col1");
            dt2.Columns.Add("col2");
            dt2.Rows.Add("Value1", "Value2");
            ds.Tables.Add(dt);
            ds.Tables.Add(dt2);
            ExcelHelper.ToExcel(ds, "test.xls", Page.Response);

More information,you can check Source link:

#Export DataSet to Multiple Excel Sheets

http://www.codeproject.com/Articles/31516/Export-DataSet-to-Multiple-Excel-Sheets

Hope this can be helpful.

Best Regards,

Eileen

We are trying to better understand customer views on social support experience. Click HERE to participate the survey. Thanks!