I'm exporting data from a 'Grouped by' grid into Excel. When I open Excel, the GroupedBy columns are shown as in the grid, with the '+' . But I would like to know if it is possible to show the data in Excel fully expanded automatically.
In the grid this can be done with the ExpandAll command, but how can I do this in the Excelexport?
It may also be necessary to remove/alter the grouping when exporting. Is there a way to achieve this?
You can expand all groups by iterating over all Rows and setting their Hidden properties to False.
It does not seem to work. When I open the exported file in Excel, it is shown with the grouped fields still collapsed. I used the InitializeRow method of the Excelexporter to set the Hidden property to false.
Did I use the correct method?
protected void UltraWebGridExcelExporter1_InitializeRow(object sender, ExcelExportInitializeRowEventArgs e) { e.Row.Hidden = false; }
You were correct. I did need to set the Hidden property to false after the row was exported.But I was also trying to affect the wrong row.
'e.Row.Hidden = false' would affect the Gridrow itself, and I needed the row in the Excel worksheet.The correct method is:
void UltraWebGridExcelExporter1_RowExported(object sender, RowExportedEventArgs e) { e.CurrentWorksheet.Rows[e.CurrentRowIndex].Hidden = false; }
I am not familiar with the UltraWebGridExporter component, so I can't comment on this event. But from the name, it looks like this is called before the export of each row. If you set Hidden to False there, chances are the subsequent export process will then set it back to True. You probably need to handle an event after the row is exported. Or you can loop through all rows after the export of the grid and before you save the workbook to a file or stream.