I have a text file that I'm importing into the grid, and then out to excel, running on a Windows Server machine that doesn't have Office installed.
Sometimes, a single cell, say in a Quantity on Hand field, which is a number, is not a number, but a string like 'Obsolete' or 'No Longer Available'. If I import this data into Excel directly on my personal machine, I use the import wizard (it's a tab-delimited text file), and set the column to numeric, and numbers appear as number, right justified, and any text in a cell in the column appears as text, left justified.
I'm trying to duplicate this behavior in exporting the grid to Excel, but since I've mapped the grid to a data table, and all of the table's columns are typed as string, since putting 'Obsolete' in an Int16 column would blow it up, I don't know how to format all my numbers as numbers and text as text. Instead, everything exports to Excel as text, and I get those lovely diamonds telling me that all these numbers are bing treated as text.
So, I need a way to test the value, see if it's a number, a data, text, what have you, and format that individual cell, rather than all cells in the column.
Can this be accomplished with the Grid?
Thanks.
I whipped up a quick sample app in case anyone else reads this thread.
Run the sample and there's a grid with a single column whose DataType is string.
Click Export and the Excel sheet comes up but all of the numeric cells show a little chevron indicating a numeric value stored as a string.
Close the Excel sheet and then check the checkbox on the form to work around this.
Click Export again and this time it works fine with numeric values in the cells where they are appropriate.
Yep, that works, but it's SLOW!
I'd spoken to tech support about it and came up with a case statement to handle it. But, wow: a 8000 line spreadsheet takes something like 10 minutes.
Still: it works!
Private Sub UltraGridExcelExporter1_CellExporting(sender As Object, e As ExcelExport.CellExportingEventArgs) Handles UltraGridExcelExporter1.CellExporting
Dim colptr As Integer = e.GridColumn.Index Dim datatype As String datatype = datatypeline.Split(Param_Delim)(colptr + 1) Try Select Case datatype Case "Int16" Dim exportvalue As Int16 If Int16.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "Int32" Dim exportvalue As Int32 If Int32.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "Int64" Dim exportvalue As Int64 If Int64.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "Decimal" Dim exportvalue As Decimal If Decimal.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "Double" Dim exportvalue As Double If Double.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "Date" Dim exportvalue As Date If Date.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case "DateTime" Dim exportvalue As DateTime If DateTime.TryParse(e.GridValue.ToString, exportvalue) Then e.ExportValue = exportvalue End If Case Else End Select Catch ex As Exception error_code = -8 Error_Flag = True End Try
Hi Samuel,
How long does it take without this code in the CellExporting event?
There's nothing particular performance-intensive here that I can see. The biggest performance hit I can see here is your call to the Split function. You might be able to make that more efficient by using IndexOf to find the two positions of the separators before and after the data type and then just getting the string you need instead of splitting the entire string into multiple parts.
Other than that, you seem to just be calling TryParse once for each cell, so I don't see any way to speed that up.