i need to export my gridview to my excel, but have some minor problem..
when i open in excel 2007, error message:
"The file you are trying to open, [filename.xls], is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
and yes, it did open the excel after i press "Yes" button.
but when i try to open in excel 2000, error message with "Unable to read file."
You are generating an HTML file and attempting to persuade Excel to accept it by adding a .xls extension. From memory, Excel 2000 did not natively support HTML, so it will reject the output. If you really have to work with Excel 2000 (which reached the end
of its life more than 3 years ago), you can use JET to populate a worksheet through ADO.NET.
if using ADO.net, i need to insert the data to insert row by row. but under the same page, i have a lot of gridviews and contain many differents type of information. if there any shortcut way of doing this?
As I said in my first reply, you are generating HTML when you render a GridView. And as I also said, Excel 2000 cannot cope with HTML.
Here's some sample code that takes the data from a SqlDataSource called CustomerDataSource and inserts it into an Excel spreadsheet that is in the App_Data folder. You should be able to adapt it to your needs:
protected void btnExport_Click(object sender, EventArgs e) {
var connString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Customers.xls;Extended Properties='Excel 8.0;HDR=Yes;'";
using (var conn = new OleDbConnection(connString)) {
var sql = "INSERT INTO [Sheet1$] (CustomerID, CompanyName, ContactName, ContactTitle) VALUES (?,?,?,?)";
var cmd = new OleDbCommand(sql, conn);
var dataView = (DataView)CustomerDataSource.Select(DataSourceSelectArguments.Empty);
var table = dataView.Table;
conn.Open();
foreach (DataRow row in table.Rows) {
cmd.Parameters.AddWithValue("", row["CustomerID"]);
cmd.Parameters.AddWithValue("", row["CompanyName"]);
cmd.Parameters.AddWithValue("", row["ContactName"]);
cmd.Parameters.AddWithValue("", row["ContactTitle"]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
Response.AddHeader("Content-Disposition", "attachment; filename=Customers.xls");
Response.ContentType = "application/excel";
Response.WriteFile(Server.MapPath("~/App_Data/Customers.xls"));
Response.End();
}
You will need to add the following to the top of your code-behind:
melvintcs
Member
182 Points
238 Posts
export to excel, cant open excel in 2000
Dec 18, 2012 07:41 AM|LINK
i need to export my gridview to my excel, but have some minor problem..
when i open in excel 2007, error message:
"The file you are trying to open, [filename.xls], is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
and yes, it did open the excel after i press "Yes" button.
but when i try to open in excel 2000, error message with "Unable to read file."
please advice me? below is the code i used:
protected void button1_Click(object sender, EventArgs e) { Response.ContentType = "application/ms-excel"; Response.AddHeader("Content-Disposition", "inline;filename=report.xls"); }Mikesdotnett...
All-Star
154901 Points
19864 Posts
Moderator
MVP
Re: export to excel, cant open excel in 2000
Dec 18, 2012 09:45 AM|LINK
You are generating an HTML file and attempting to persuade Excel to accept it by adding a .xls extension. From memory, Excel 2000 did not natively support HTML, so it will reject the output. If you really have to work with Excel 2000 (which reached the end of its life more than 3 years ago), you can use JET to populate a worksheet through ADO.NET.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
melvintcs
Member
182 Points
238 Posts
Re: export to excel, cant open excel in 2000
Dec 19, 2012 11:52 PM|LINK
hi, thx for the reply, i did my research and i found this article:
http://mudassarkhan.wordpress.com/2009/01/12/export-data-to-excel-adonet/
if using ADO.net, i need to insert the data to insert row by row. but under the same page, i have a lot of gridviews and contain many differents type of information. if there any shortcut way of doing this?
Mikesdotnett...
All-Star
154901 Points
19864 Posts
Moderator
MVP
Re: export to excel, cant open excel in 2000
Dec 20, 2012 04:41 AM|LINK
How are you populating your GridViews? SqlDataSource? Linq? DataSet?
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
melvintcs
Member
182 Points
238 Posts
Re: export to excel, cant open excel in 2000
Dec 20, 2012 05:07 AM|LINK
there are 20 gridviews and 20 sqldatasources
I believe Excel 2000 cant open because Excel 2000 cannot open xml?
if i open my .xls with notepad, it's a html form.
Mikesdotnett...
All-Star
154901 Points
19864 Posts
Moderator
MVP
Re: export to excel, cant open excel in 2000
Dec 20, 2012 06:15 AM|LINK
As I said in my first reply, you are generating HTML when you render a GridView. And as I also said, Excel 2000 cannot cope with HTML.
Here's some sample code that takes the data from a SqlDataSource called CustomerDataSource and inserts it into an Excel spreadsheet that is in the App_Data folder. You should be able to adapt it to your needs:
protected void btnExport_Click(object sender, EventArgs e) { var connString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Customers.xls;Extended Properties='Excel 8.0;HDR=Yes;'"; using (var conn = new OleDbConnection(connString)) { var sql = "INSERT INTO [Sheet1$] (CustomerID, CompanyName, ContactName, ContactTitle) VALUES (?,?,?,?)"; var cmd = new OleDbCommand(sql, conn); var dataView = (DataView)CustomerDataSource.Select(DataSourceSelectArguments.Empty); var table = dataView.Table; conn.Open(); foreach (DataRow row in table.Rows) { cmd.Parameters.AddWithValue("", row["CustomerID"]); cmd.Parameters.AddWithValue("", row["CompanyName"]); cmd.Parameters.AddWithValue("", row["ContactName"]); cmd.Parameters.AddWithValue("", row["ContactTitle"]); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } } Response.AddHeader("Content-Disposition", "attachment; filename=Customers.xls"); Response.ContentType = "application/excel"; Response.WriteFile(Server.MapPath("~/App_Data/Customers.xls")); Response.End(); }You will need to add the following to the top of your code-behind:
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter