Forums Ultimate UI for ASP.NET Web Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Rob Hudson
5368
Summary on currency field
posted

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 ;)

Parents Reply Children