Ouch... I just blew most of a night trying to do something that should have been pretty simple.
I have currency columns in my WebDataGrid. (using dataformat {0:c}). I just wanted to export the grid to Excel with totals at the bottom. Not an exotic thing to want to do at all.
Bug after bug after bug...
1) The values went into Excel with the $ as part of the value, converting the value from a number to text, and blocking any aggregation on the column. The WebExcelExporter should instead use Excel FormatStrings, to preserve the value as a number to allow calculations.
2) Once I figured out how to force the numbers to go into Excel as numbers, I had difficulty getting the summary formatted as currency
3) The "Sum = " text was exported to Excel as part of each summary despite it being removed from the Summary behavior.
Finally got it working... In case anyone else needs to do something similar, here is the magic code:
Protected Sub WebExcelExporter1_CellExported(sender As Object, e As Infragistics.Web.UI.GridControls.ExcelCellExportedEventArgs) Handles WebExcelExporter1.CellExported If Not e.IsFooterCell And Not e.IsHeaderCell And Not e.IsSummaryCell Then '{0:c} outputs the $ as part of the value instead of using Excel formatting... causing SUM not to work. 'Strip out the $ and replace it with an Excel format string Select Case CType(Session(sSessionVar), DataTable).Columns(e.CurrentColumnIndex).ColumnName Case "Base", "Discount", "Total", "TX Sales Tax", "Total with Tax" 'only apply this on my currency fields If nz(e.WorksheetCell.Value).StartsWith("$") Then e.WorksheetCell.Value = nzDbl(e.WorksheetCell.Value.ToString.Replace("$", "")) e.WorksheetCell.CellFormat.FormatString = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" End If End Select End If If e.IsSummaryCell Then 'WebExcelExporter has two bugs. '1) It does not format the amount in the summary as currency, and '2) it includes the "Sum = " text, regardless of how we have the Summary behavior configured. 'Must rebuild the SUM function. If Not IsNothing(e.WorksheetCell.Formula) Then Dim sformula As String = e.WorksheetCell.Formula.ToString(Infragistics.Excel.CellReferenceMode.R1C1) sformula = sformula.Replace("C", "C[0]") sformula = sformula.Replace("SUM(", "SUM(INDIRECT(""").Replace(")", """,FALSE))") sformula = sformula.Replace("""Sum = "" & ", "") e.WorksheetCell.ApplyFormula(sformula) Select Case CType(Session(sSessionVar), DataTable).Columns(e.CurrentColumnIndex).ColumnName Case "Base", "Discount", "Total", "TX Sales Tax", "Total with Tax" 'only apply this on my currency fields e.WorksheetCell.CellFormat.FormatString = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" End Select End If End If End Sub
A life-long fan of Infragistics, but pretty-please have a look at fixing this ;)
Hi Rob Hudson,
I'm sorry you ran into such trouble. When the formatting of the values happens, we do string.format in our code before assigning it to the excel cell's text. So this is sort of by design. You make a good point, however. I would suggest submitting a feature request on this. I will also pass it along to our product manager.
As for your third point, the labels in the summary row should follow whatever was in the actual grid. I do remember fixing a bug on this. Did you have the latest Service Release? If you did and it is still broken, could you attach a sample or some code you were trying so I could try to reproduce it and fix it?
regards,David Young
David,
On the third point, I am currently on build 2120. If there has been a service release since then that fixes this issue, I don't have it. Not a big deal... I don't mind waiting for the next release now that I have a workaround.
On the currency issue.... working with currency values is such a fundamental function of a spreadsheet that it's hard to imagine a mature product without support for it. I've got to believe that better currency support is in the roadmap somewhere. I was a little surprised to discover the only way to format for currency was to use the string format codes, which remove any opportunity for you to improve on the logic (like Excel exporting?)
As for submitting a feature request, I'll let you submit it and take the credit for the inspired idea ;-)
Cheers
-Rob
Hello Rob,
As David mentioned there was a development issue regarding this.
Issue 49412:When Grid field is formatted using format string the Exporter exports string
The issue was fixed by adding a new property – DisableCellValueFormatting
This property should be available in 2120 build
You should set it to true in order to always export the dataItem value and ignore the DataFormatString.
It disables formatting of the cell value when grid BoundDataField has DataFormatString and the value is exported as numeric
Inside OnExported handler you should set the FormatString :
protected void WebExcelExporter1_Exported(object sender, Infragistics.Web.UI.GridControls.ExcelExportedEventArgs e)
{
e.Worksheet.Columns[1].CellFormat.FormatString = "$ 0.00";
}
Please refer to the sample attached and let me know if you need further assistance regarding this.
Tsvetelina, that worked brilliantly!
As I was working through this, I went to the Samples Browser for the excel exporter, and tried to find an example of how to do this... Not one of the samples included a currency column.
It might save someone else a lot of time if you guys modified one of the samples to include this technique.
Thank you very much!
I have forwarded your suggestion to the team responsible for the sample browser.
Let us know if we can assist you further.
Hi Aaron,
These are two separate controls, in different products. I would suggest asking you question on the Windows Forms forums. I do not know about the API for those controls.
-Dave
Hello,
I'm using v7.3 of the UltraWinGrid and am having a similar problem as above. My summary row data is exporting to excel as text, even though all the other data in the grid is correctly exported as a number.
How do I make use of the fix that Tsvetelina mentioned above?
Thanks,
Aaron