[DevExpress Support Team: CLONED FROM T248247: ASPxGridView - Time format of excel's cell in the exported document (DataAware mode)]
Hi Anthony,
i have second problem. Custom excel format not work for summaries. I use CustomizeCell for change summary type from custom to sum (it is necessary for time columns with datepart).
if (exportDateTimeAsTimeFieldNamesArray.Contains(e.ColumnFieldName, StringComparer.OrdinalIgnoreCase))
{
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea)
{
var sourceDate = Convert.ToDateTime(e.Value);
e.Value = new TimeSpan(sourceDate.Hour, sourceDate.Minute, sourceDate.Second);
}
else if (e.AreaType == DevExpress.Export.SheetAreaType.TotalFooter)
{
e.SummaryItem.SummaryType = DevExpress.Export.Xl.XlSummary.Sum;
}
else if (e.AreaType == DevExpress.Export.SheetAreaType.GroupFooter)
{
e.SummaryItem.SummaryType = DevExpress.Export.Xl.XlSummary.Sum;
}
e.Formatting.FormatType = DevExpress.Utils.FormatType.DateTime;
e.Formatting.NumberFormat = "[h]:mm:ss";
e.Handled = true;
}
Please check footer and group footer in attachment.
Thank you again.
ASPxGridViewExporter - e.Value is always null in the XlsxExportOptionsEx.CustomizeCell event
Answers approved by DevExpress Support
Hello,
I have modified the project. See the attachment. Please let me know if it meets your requirements.
UPDATED:
We have researched the issue in detail and found that this behavior is expected. When XlsExportOptionsEx.ExportType is DataAware, the ASPxGridViewExporter provides the possibility to change e.SummaryItem.SummaryType or define the CustomizeCellEventArgs.Value manually. In this case, it is unclear whether the cell should contains a formula or Value. That is why, the CustomizeCellEventArgs.Value is empty. However you can get the value of the summary cell as follows:
C#var summary = ASPxGridView1.TotalSummary[e.ColumnFieldName];
e.Value = ASPxGridView1.GetTotalSummaryValue(summary);
Hello,
Please accept our apologies for the delay. We have researched the issue in detail and found that this behavior is expected. When XlsExportOptionsEx.ExportType is DataAware, the ASPxGridViewExporter provides the possibility to change e.SummaryItem.SummaryType or define the CustomizeCellEventArgs.Value manually. In this case, it is unclear whether the cell should contains a formula or Value. That is why, the CustomizeCellEventArgs.Value is empty. However you can get the value of the summary cell as follows:
C#var summary = ASPxGridView1.TotalSummary[e.ColumnFieldName];
e.Value = ASPxGridView1.GetTotalSummaryValue(summary);
I have modified the project using this approach. See the attachment. Let me know if this solution meets your requirements.
Hello Nataly,
but your solution remove formula. For us it is the best solution to preserve formula.
but i don't understant why you add support for changing format in data area
https://www.devexpress.com/Support/Center/Question/Details/T253075
and omit the same option for formulas in summary area
Thank you for the clarification
Hello,
To process your recent post more efficiently, I created a separate ticket on your behalf: T283025: ASPxGridViewExporter - Custom formatting is not applied to summary cells in the XlsxExportOptionsEx.CustomizeCell event. This ticket is currently in our processing queue. Our team will address it as soon as we have any updates.
Hello,
I assume that the problem may be connected with the incorrect summary format. To be able to determine the exact reason for the issue, I need to reproduce and research it on my side. Please clarify how you calculate custom summaries for this column? A test project illustrating the issue would be very helpful. It will help me to better understand your scenario and find a solution more quickly. I am looking forward to your reply.
Hi Nattaly,
it is imposible to set normal summary over column which representing time. We must use custom summaries. Problem is not dependent which summary type is used (custom or normal). Please read carefully related problem.
This line
e.Formatting.NumberFormat = "[h]:mm:ss";
not working for that items
DevExpress.Export.SheetAreaType.TotalFooter
I created little demo.
Thank you for support.