I am exporting a WHDG control to excel using WebExcelExporter. Everything works great (I see all data, I see correct column groupings, I see proper column reordering, etc) EXCEPT for an empty footer value.
All binding is done in code-behind as the data source is determined in runtime. I manually create the columns on the WHDG.GridView property and bind a DataSet (containing just one DataTable) to the grid control. If a column is of a specific data type, I manually create the summary row setting via:
whdgReport.GridView.Behaviors.SummaryRow.Enabled = true; var sumRowSetting = new SummaryRowSetting() { ColumnKey = rc.ColumnName, ShowSummaryButton = false, FormatString = "{0:c}" }; sumRowSetting.SummarySettings.Add(new SummarySetting(SummaryType.Sum) { FormatString = "{1}" }); whdgReport.GridView.Behaviors.SummaryRow.ColumnSettings.Add(sumRowSetting); var colSummaryInfo = new ColumnSummaryInfo() { ColumnKey = rc.ColumnName }; colSummaryInfo.Summaries.Add(new Summary() { SummaryType = SummaryType.Sum }); whdgReport.GridView.Behaviors.SummaryRow.ColumnSummaries.Add(colSummaryInfo);
When I export to excel, I correctly see a summary row and a value in the correct column, it just has a value of 0 instead of the sum of the columns.
Am I missing anything obvious? After seeing all the data appear in the grid (along with the correct user edits -- column groupings and reordering), I was surprised to see the footer not get calculated correctly.
Thanks!
Hello Kfiduk,
When you export the grid the summary row will apply the formula to the excel which means that if you have your columns from type string it will not represent the final result and 0 will appear there. Set the column types to be numerable in order for this to calculate them correctly.
I hope this helps.
For any further questions do not hesitate to contact me.
Sincerely,
Georgi Sashev
Developer Support Engineer
Infragistics, Inc.
http://ko.infragistics.com/support
I am. The DataType of the BoundDataField object is set to "System.Decimal". I'm using a DataFormatString of "{0:c}" so the field is formatted as currency/money, but the underlying data driving the column is correctly set to a numeric data type (System.Decimal).
The sum footer works in the excel spreadsheet if I don't use a DataFormatString. However, now, on the website, the grid's money column looks ridiculous and is pretty much unusable when unformatted.
So the simple matter of providing a DataFormatString for the grid's display trips up the excel exporting? Is there anything I can do? Even if I can turn off the DataFormatString JUST for the excel export, the resulting spreadsheet would have the same unformatted and ugly money columns.
Hi kfiduk,
You could turn off the DataFormatString on the grid column, but then set a format string on the excel spreadsheet column.
regards,David Young
AgentD5 said: Hi kfiduk, You could turn off the DataFormatString on the grid column, but then set a format string on the excel spreadsheet column. regards,David Young
Is there a best practice way of going about doing this?
Currently, I'm clearing out all DataFormatString properties of my columns like so before exporting it:
whdgReport.GridView.Columns.OfType<FormattedGridField>().ToList().ForEach(col => col.DataFormatString = string.Empty);
This is working correctly and gets the correct sum value in the footer of the exported spreadsheet. There may be a more "correct" way of doing this, tho.
I'm lost on the second part though. After I've exported, how do I reformat the excel spreadsheet column back to currency/money? Hook into an event of WebExcelExporter? I see a couple possible candidates in CellExported and SummaryCellExported.
Thanks for the guidance!
Um, it's kind of however you want. You could handle exporting or exported events, go through grid columns and then set appropriate formats on the excel columns. Rather than handling a cell exporting and doing it multiple times. But those events you mention would work too.
Dave
Hi,
You may found this link helpful.
http://help.infragistics.com/NetAdvantage/ASPNET/2011.1/CLR4.0/?page=WebExcelExporter_Exporting_Custom_Summary.html
Review it let me know if you need further assistance.
I'm likely missing something obvious -- how do I get a reference to the resulting excel spreadsheet to change its formatting?
Looking at the Exporting and Exported events (and they're accompanying EventArgs objects), I can't find access to columns, either the grid's or the spreadsheet's.