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("");
}
}
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);
Member
5 Points
29 Posts
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();
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
Regards!
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:
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
None
0 Points
1 Post
Re: Downloading DataSet to Excel
Apr 29, 2020 10:48 AM|fred mangel|LINK
you can try https://zetexcel.com/. it has the ability to generate, modify, convert, render and print spreadsheets without using Microsoft Excel.