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