I have a WebDataGrid with some numerical columns where I summarize them with either totals or a custom summary of weighted averages. On the web page presentation, I format the columns with {1} so that the "Sum = " or whatever my custom summary is named (like "Blank = ") is hidden. I did a custom summary for a column with a blank to hide the " -- " that displays in the summary row.
When I export the grid to Excel (2003 version) using the WebExcelExporter, my column headers display the stylesheet formatting but my data rows are not formatted with the stylesheet formatting. However, most crucially, my summary row has lost the formatting (Format="{1}") and shows "Sum = " and "Blank = " which is not acceptable to my users.
Is there a setting or tip/trick to get the formatting from the grid to pass through to the exported Excel file?
Here is an excerpt of my code:
<ig:WebDataGrid ID="grdGrid" runat="server" AutoGenerateColumns="False" CssClass="ControlClass" ItemCssClass="RowDetail" AltItemCssClass="RowDetailAlt"> <Columns> <ig:TemplateDataField Key="FIELD1" Header-CssClass="HeaderCaptionClass" CssClass="Center" Width="75" > <ItemTemplate> <a href='#' onclick='editRow('<%# Eval("FIELD1") %>')'><%#Eval("FIELD1")%></a> </ItemTemplate> </ig:TemplateDataField> <ig:BoundDataField DataFieldName="FIELD2" Key="FIELD2" Header-CssClass="HeaderCaptionClass" CssClass="Right" DataFormatString="{0:###,###,##0.00}" Width="80" > <Header Text="Gross Quantity" /> </ig:BoundDataField> <ig:BoundDataField DataFieldName="FIELD3" Key="FIELD3" Header-CssClass="HeaderCaptionClass" CssClass="Right" DataFormatString="{0:###,###,##0.00}" Width="80" > <Header Text="Net Quantity" /> </ig:BoundDataField> <ig:BoundDataField DataFieldName="FIELD4" Key="FIELD4" Header-CssClass="HeaderCaptionClass" CssClass="Right" DataFormatString="{0:##0.00}" Width="30" > <Header Text="Factor" /> </ig:BoundDataField> </Columns> <Behaviors> <ig:Activation Enabled="true" /> <ig:Paging PagerAppearance="Bottom" PagerCssClass="Pager" QuickPages="8" PagerMode="NumericFirstLast" Enabled="true" PageSize="10"> </ig:Paging> <ig:Sorting SortingMode="Single" Enabled="true" /> <ig:ColumnMoving Enabled="true" /> <ig:ColumnResizing Enabled="true" /> <ig:SummaryRow Enabled="true" EnableSummariesFilter="False" FormatString="{1}" ShowSummariesButtons="False" SummariesCssClass="Summary"> <ColumnSummaries> <ig:ColumnSummaryInfo ColumnKey="FIELD1"> <Summaries> <ig:Summary SummaryType="Custom" CustomSummaryName="Blank" /> </Summaries> </ig:ColumnSummaryInfo> <ig:ColumnSummaryInfo ColumnKey="FIELD2"> <Summaries> <ig:Summary SummaryType="Custom" CustomSummaryName="SumGross" /> </Summaries> </ig:ColumnSummaryInfo> <ig:ColumnSummaryInfo ColumnKey="FIELD3"> <Summaries> <ig:Summary SummaryType="Custom" CustomSummaryName="SumNet" /> </Summaries> </ig:ColumnSummaryInfo> <ig:ColumnSummaryInfo ColumnKey="FIELD4"> <Summaries> <ig:Summary SummaryType="Custom" CustomSummaryName="NetAvg" /> </Summaries> </ig:ColumnSummaryInfo> </ColumnSummaries> </ig:SummaryRow> </Behaviors> </ig:WebDataGrid>
Here is the code-behind. Notice I hide the template column before exporting which works great.
Private Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click LoadGrid() Me.grdGrid.Columns(0).Hidden = True Me.WebExcelExporter1.DataExportMode = Infragistics.Web.UI.GridControls.DataExportMode.AllDataInDataSource Me.WebExcelExporter1.Export(Me.grdGrid) Me.grdGrid.Columns(0).Hidden = False End Sub
Private Function grdGrid_CalculateCustomSummary(ByVal sender As Object, ByVal e As Infragistics.Web.UI.GridControls.CustomSummaryEventArgs) As Object Handles grdGrid.CalculateCustomSummary If e.Summary.CustomSummaryName = "Blank" Then Return "" End If If e.Summary.CustomSummaryName = "SumGross" Then Dim sum As Double = 0.0 For Each gr As GridRecord In Me.grdGrid.Rows sum += Convert.ToDouble(gr.Items(1).Value) Next Return Math.Round(sum, 2) End If If e.Summary.CustomSummaryName = "SumNet" Then Dim sum As Double = 0.0 For Each gr As GridRecord In Me.grdGrid.Rows sum += Convert.ToDouble(gr.Items(2).Value) Next Return Math.Round(sum, 2) End If If e.Summary.CustomSummaryName = "NetAvg" Then Dim sumAvg As Double = 0.0 Dim net As Double = 0.0 Dim count As Integer = 0 For Each gr As GridRecord In Me.grdGrid.Rows If Convert.ToDouble(gr.Items(3).Value) > 0 Then sumAvg += Convert.ToDouble(gr.Items(3).Value) * Convert.ToDouble(gr.Items(2).Value) net += Convert.ToDouble(gr.Items(2).Value) End If Next Dim avg As Double = 0.0 If net > 0 Then avg = sumAvg / net Else avg = 0 End If Return Math.Round(avg, 2) End If Return Nothing End Function
Here is the stylesheet:
.ControlClass { border-style:solid; border-color:#999999; background-color:#F0F0F0; border-width:thin; margin-top:1px; margin-bottom:1px; margin-left:1px; margin-right:1px; } .ItemClass { background-color:white; } .AltItemClass { background-color:#C2C2A0; } .HeaderCaptionClass { text-align:center; background-color:#6E7645; border-style:Solid; border-width:thin; border-left-width:thin; border-top-width:thin; color:White; white-space:normal; font-family:Verdana; font-size:8pt; font-weight:bold; }
.hideMyColumn { display:none; } .RowLeft { text-align:left; } .Pager { text-align:left; } .Summary { text-align:right; white-space:normal; font-family:Verdana; font-size:8pt; font-weight:bold; } tbody > tr.RowDetail > td { background-color:white; white-space:normal; font-family:Verdana; font-size:8pt; } tbody > tr.RowDetailAlt > td { background-color:#C2C2A0; white-space:normal; font-family:Verdana; font-size:8pt; } tbody > tr > td.Left { text-align:left; white-space:normal; font-family:Verdana; font-size:8pt; } tbody > tr > td.Right { text-align:right; white-space:normal; font-family:Verdana; font-size:8pt; } tbody > tr > td.Center { text-align:center; white-space:normal; font-family:Verdana; font-size:8pt; }
Hi kgugler,
When you say the formatting is lost, do you mean the styling? Or do you mean the data itself. With our summaries, we should attempt to export an excel formula in the cell. Is that formula not there? As an aside, you can set EmptyFooterText on the summary row behavior to empty string so that '--' does not appear in empty cells; that is simply the default value.
regards,David Young
Hi David,
Thanks for the tip on the EmptyFooterText. That took care of the columns with the "--". My workaround using a custom summary of "Blank" left a "Blank = " in the summary row. Now those columns exported to excel show nothing (as I had hoped). But I still have problems with the columns for which I set a custom footer or some built-in summary function. In answer to your question, yes, I do mean styling (color, font, alignment, etc) with regards to the rows (they do not hold the style set in the website's grid that I assigned using a stylesheet for the item rows when exported to an Excel spreadsheet) but, curiously, the header is styled correctly in the exported Excel spreadsheet according to what I set in the stylesheet for the headers of the grid.
I do mean format in regards to the data for the summaries row. I am setting FormatString="{1}" so that the "Sum = " does not show in the grid summary row on the web page but when I export the grid to Excel, that "Sum = " appears in the summary row on the spreadsheet. Is there any way to get around this behavior?
Thanks again for the help!
Kurt