Excel exporter does not export $ signs or commas in grid cell to Excel.

165

Arun posted over 15 years ago

Hi,

cell, and footers show COST in my grid with dollar sgn ($) before the value; well i set the format on grid.

but once the grid is exported to excel all formating is gone and values in excel cell are just strings??

any help is greatly appreciated.

thank you,

4960

Derek Harmon posted over 15 years ago

Is your UltraGridColumn's DataType = "System.Decimal" or some other numberic type? If it is String (the default) then it will get exported to Excel that way.

Is your UltraGridColumn using an embeddable editor like the WebCurrencyEditor? There's some special intelligence in the WebGrid Excel Exporter to work with embedded WebEditors to get at the true value of the cell without the formatting.

What type of format string are you using on the column? Some format specifiers are .NET-specific (like "N") whereas PICTURE-like format specifiers (i.e., "$0.00") are more compatible with Excel. If you are using a .NET-specific format string, you must handle Initialize Column to translate it into an Excel-compatible format string.

Have you tried handling the Initialize Column event on the WebGrid Excel Exporter? This event will pass you the .NET format string you've set-up on the UltraGridColumn, so that you can translate it into your desired Excel-compatible format string. The Custom Number Formats section in the Microsoft KB article, http://support.microsoft.com/kb/264372/en-us explains the acceptable makeup of an Excel format string although something like "$###,##0.00" would probably be a good starting point to work off of.
Cancel
Reply

Verify Answer

Cancel
165

Arun posted over 15 years ago in reply to Derek Harmon

Hi Derek ,

Thank you, for your reply.

1. My grid columns are of System.Decimal

2. embeddable editors on grid no i don't have any

3.Currency format "$ ###,###,##0.00"

4. I first tried not formating coulmn on _InitializeColumn event of exporter, it did not work. Then I set ExcelFormatString property of Initialiaze column it worked great. I can see commas and $ signs now, But i have an issue, i have footers and my grid is hierarchical type and users can also group it by column; Outlookgroupby.

Issue (on both GRID and EXCEL):

I can only see my format on footers of BAND 0 and 1 but not within the cells of either BANDs.

UltraWebGrid1.Bands(0).Columns.FromKey("Cost").DataType = "System.Decimal"

UltraWebGrid1.Bands(0).Columns.FromKey("Cost").Format = "$ ###,###,##0.00"

UltraWebGrid1.Bands(0).Columns.FromKey("Weight").DataType = "System.Decimal"

UltraWebGrid1.Bands(0).Columns.FromKey("Weight").Format = "###,###,###.##"

UltraWebGrid1.Bands(1).Columns.FromKey("Cost").DataType = "System.Decimal"

UltraWebGrid1.Bands(1).Columns.FromKey("Cost").Format = "$ ###,###,##0.00"

UltraWebGrid1.Bands(1).Columns.FromKey("Weight").DataType = "System.Decimal"

UltraWebGrid1.Bands(1).Columns.FromKey("Weight").Format = "###,###,###.##"
Cancel
Reply

Verify Answer

Cancel
420

Manoj Kumar posted over 15 years ago in reply to Arun

Try with following code instead of xlExporter. /// /// Exports the Excel /// public void btnExport1() { if (Page.Request.Params.Get("__EVENTTARGET") == "Button1") { Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); grdTaskPlanning.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } }
Cancel
Reply

Verify Answer

Cancel
10

jason posted over 13 years ago

You can try to use a c# excel component Spire.XLS,it supports importing/exporting to XLS, XLSX, CSV and HTML files with the format.
Cancel
Reply

Verify Answer

Cancel