Hello,
I am having to manually export an Infragistics WebGrid to excel (using the .Export method does not work in my case as there are some additional things that must be added/removed from the export).
Everything is exporting fine, except for the format... I would like to format the numbers in the grid cells to 2 decimal places if it already contains a decimal place, otherwise I would like to use no decimal places.
I have the following and the output is always the same, if there are no decimal places, none appear (which is great), but otherwise it uses the same amount of decimal places that are in the grid (so, up to 16 decimal places in some cases!).
Dim excelWorkbook As WorkbookDim exportRowNumber As IntegerDim exportCellIndex As Integer
...
excelWorkbook.WindowOptions.SelectedWorksheet.Rows(exportRowNumber).Cells(exportCellIndex).Value = <cell from the grid>.TextexcelWorkbook.WindowOptions.SelectedWorksheet.Rows(exportRowNumber).Cells(exportCellIndex).CellFormat.FormatString = "##.##"
Why is the format string that I'm setting not being applied to the cell in Excel?
An UltraGridCell's Text property is always going to be of type String. To apply that format string, you want to store a number (Decimal, Double or Single data types) in the WorksheetCell's Value property.
Use the UltraGridCell's Value property. Make sure that the DataType of the UltraGridColumn that cell resides in is one of the numeric data types, and then make sure that the data you are binding against is strongly-typed to be numeric. (If either of these are set to type String, then the Value you get back will be a String).
If you can't make the UltraGridCell's Value a number, then you can try parsing UltraGridCell's Text property as a number, when you are assigning it to the WorksheetCell's Value.
I have set the DataType of the UltraGridColumn to Double and have applied the format string of "#0.##%" to it... the values in that column in the grid are showing up fine. ie. 0% and 100%
However, when I manually export it I'm still seeing something different. Instead of seeing "0%" in the cell in excel, I'm seeing "0.%", and "100.%". Despite the fact that the same format string is being used in both the grid and the exported document, the contents of the cells are not appearing as they should. Here is a snippet of the export code:
.Rows(exportRowNumber).Cells(exportCellIndex).Value = row.Cells(cellIndex).Value .Rows(exportRowNumber).Cells(exportCellIndex).CellFormat.FormatString = row.Cells(cellIndex).Column.Format