Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datagrid export format problem in Excel 1

Status
Not open for further replies.
mcowen

mcowen

Programmer
Oct 21, 2001
134
0
0
GB
When I export a datagrid that contains currency values (with the pound sign, £) to Excel I get £0.00. I have seen this before when transforming XSL with UTF-8 encoding so I tried iso-8859-1 but it made no difference. Does anyone know how to solve this please? Here is a snippet of my code

Code: 
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "iso-8859-1";
		Response.Cache.SetCacheability(HttpCacheability.NoCache);
this.EnableViewState = false;
Response.Write("\r\n");
Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("\r\n");
Response.Write("<style> .mystyle1 "+"\r\n"+"{mso-number-format:\""+@"\@"+"\""+";} "+"\r\n"+"</style>");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter( oStringWriter );
this.ClearControls(MySQLDataGrid);
MySQLDataGrid.RenderControl( oHtmlTextWriter );

//Response.AppendHeader("content-disposition","attachment;filename=x.xls");
			Response.Write(oStringWriter.ToString());
Response.End();

Which outputs..

Department Nov
Finance £0.00
Internal Systems £0.00
Legal £0.00
Total £0.00

Thanks


Matt
 
Sort by date Sort by votes
I can't test this at the minute but does adding "Response.ContentEncoding = System.Text.Encoding.Unicode" do anything?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 
Upvote 0 Downvote
Response.ContentType = "application/vnd.ms-excel"

Response.Charset = ""

Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

YourGrid.RenderControl(hw)

Response.Write(tw.ToString())

Response.AddHeader("Content-Disposition", "attachment;filename=" & Session.SessionID & ".xls;")

Response.End()

I have no problems using currency with my code. as long as your grid is formatted correctly you should have no problems

 
Upvote 0 Downvote
Thanks guys but still getting the same. I tried ca8msm's and it came back with a dialog saying it wasnt an excel file.
I tried dvannoy's but no change. When you say the grid must be formatted correctly is this ok? The dataformatstring sets the currency for the locale.

Code: 
BoundColumn col = new BoundColumn();
col.HeaderText = "Nov";
col.DataField = "Nov";
col.DataFormatString = "{0:C}";
col.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
this.MySQLDataGrid.Columns.Add(col);

If I use the contectencoding the file returned contains a HTML table. That table contains values with the £ as it should. Not sure whats going on here. Any other ideas?

Thanks

Matt
 
Upvote 0 Downvote
forget excel for a minute...when you bind your grid does the data display correctly? all my code does is take the grid and export it to excel with the exact look as the grid.

 
Upvote 0 Downvote
Yes, the grid displays correctly. Each cell contains a £ as I expect e.g. £100,430.00

Matt
 
Upvote 0 Downvote
Thanks for the article but cant see anything on there that indicates a difference. I run your code on clicking a button. Can't seem to find anything about this on the internet - can't believe I am the only person to get this! I am using MS Excel 2003. Dvannoy, do you have the grid cell layout set to currency?

In the meantime have a star for your efforts. Much appreciated.

Matt
 
Upvote 0 Downvote
Can you post the HTML section of your DataGrid (i.e. from the View Source of your browser).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 
Upvote 0 Downvote
no, my grid has no currency formatting in my html. my DB fields are set to money. when exporting my grid to excel the field type must get passed to excel. I did have problems with dates so my date fields are formatted in my html but not my currency.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JohnB33
  • Locked
  • Question
int type causes error
Replies
0
Views
118
JohnB33
JohnB33
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
163
a6green
a6green
WIREMESH
  • Locked
  • Question
Play mp4 files in asp.net 2.0 webforms application
Replies
0
Views
44
WIREMESH
WIREMESH
sugu1
  • Locked
  • Question
sound editing in web site to record, cut/trim, paste and save as wav file
Replies
0
Views
127
sugu1
sugu1
G12Consult
  • Locked
  • Question
Importing Excel to SQL Server using EPPlus
Replies
3
Views
120
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top