I'm using 20.1. I have a report which requires different numeric formats in the same column. For example, one row is "percent of sales", which obviously needs to be formatted as percent, while other rows are formatted as currency.
After a lengthy search, I found that the only way to do this was to leave the columns unformatted, and apply an EditorWithText to the specific cells in InitializeRow, despite the fact that this is a read-only report.
(As an aside, I can't understand why it's possible to apply different CellAppearances in a single column, but not different numeric formats.)In any case, the EditorWithText workaround worked. But when I export the grid to Excel, there's no numeric formatting at all, because the export works on a column-by-column basis.
I'm already using ultraGridExcelExporter1_InitializeColumn to convert Ultragrid formats to Excel formats, and I'm using ultraGridExcelExporter1_ExportEnding to go into exported reports and autofit columns, since the exporter doesn't do that natively. I'm happy to go into the the reports and apply formatting myself on a cell-by-cell basis, since the reports aren't very big. But I can't do that, because I have no way to loop through grid columns in their display order. The columns collection is in datasource order, which in my case *can't* be the same as display order.
Is there any way for me to loop through columns in the order in which they appear, visually, in the report? Preferably non-hidden columns only, but I can allow for that myself if need be.
Hello Lisa,
If you need to loop through only the visible columns, you can use the GetFirstVisibleCol method and provide the ActiveColScrollRegion, which would provide the first visible column. After that a while loop could be used to apply the logic needed to format that column and lastly the column should be assigned to the next visible column by using the GetRelatedVisibleColumn by providing VisibleRelation.Next. The code for this should be similar to the code below:
UltraGrid grid = this.ultraGrid1; UltraGridBand band = grid.DisplayLayout.Bands[0]; UltraGridColumn column = band.GetFirstVisibleCol(grid.ActiveColScrollRegion, true); while (column != null) { //apply your logic here column = column.GetRelatedVisibleColumn(VisibleRelation.Next); }
Please let me know if you have any questions.
Regards, Ivan Kitanov
Wow. In a billion years, I would never have come up with that on my own. Thank you so much.
I'm glad that I was able to help.
Thank you for choosing Infragistics!
Regards,Ivan Kitanov
Also... I'm just now noticing that e.Workbook.WindowOptions.SelectedWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat is read-only.
Is there any way around that? Or am I going to have to open the Excel file with something like OpenOfficeXML and make the modifications that way?
Thanks again, Ivan. CellFormat.FormatString is exactly what I needed.
In case you are you are using grouped header via the RowLayoutStyle as Grouped layout, I recommend you exporting with the UltraGridExcelExporter, since it would create a copy of the layout of the grid and would export the grouped headers without the need of any additional code, then you would have to simply load the exported files using the Infragistics Excel Engine and apply the CellFormat.FormatString property to those cells that need number and percentage formats.
The other alternative would be to loop through the columns and find their ParentGroups, determinating which groups hold which columns and if the groups have a parent group as well and thus finding how many cells a group would need. This approach may involve some complex logic depending on the depth of the groups and since you mentioned that the reports are small, I strongly recommend you following the first approach and exporting the grid and then loading the exported file inside the Excel Engine.
For your other questions regarding the CellFormat object, you are correct it is Read-Only, however its properties could be accessed and changed. For setting the format of the cells the FormatString property is needed it could be set such as for currency formats:
worksheet.Rows[i + 1].Cells[2].CellFormat.FormatString = "$#,##0.00" ;
For percentage formats it should be as below:
worksheet.Rows[i + 1].Cells[2].CellFormat.FormatString = "0.00%"
Also, please note that cell value presented as percentage is considered as follows if the number is 1 it corresponds to 100%, so in case your cells contain values such as 30 for 30%, their values could be divided by 100 in the exported file in order to retain the correct values.
One additional thing that I would like to mention is how to find those formats. If you right-click on a cell in Excel and select “Cell Formatting…”, if you then select the “Custom” category you would be present with multiple different formats that correspond to the different categories and those formats could be used as string values for the FormatString property.