Excel data export fixing number errors/removing green triangles
After exporting data using a third party component the data in the excel sheet isn't type correctly. Excel thinks that some values are string while they are numbers and a lit开发者_JAVA技巧tle green triangle shows up.
We've coded the following to fix this:
For Each objCell As Microsoft.Office.Interop.Excel.Range In objWorkSheetReport.Range(objWorkSheetReport.Cells(1, 1), objWorkSheetReport.Cells(Me.RowCount + 10, Columns.Count + 10)).Cells
If IsNumeric(objCell.Value) Then
objCell.Value = CDbl(objCell.Value)
End If
Next
This removes all those little green triangles but is really slow.
The question
Is there a faster way to convert a Range of data quickly so the green triangles don't show up?
Use the .SpecialCells()
method of the range to narrow it down to only those cells that need to be changed.
Assuming a range NarrowedRange
and a worksheet Sheet
(substitute your own range for A1:A8
, and objWorksheetReport
for your sheet)
NarrowedRange =
Sheet.Range("A1:A8").SpecialCells(Excel.XlCellType.xlCellTypeConstants,
Excel.XlSpecialCellsValue.xlTextValues)
will get you only the text value elements of your original range, so just change those accordingly.
精彩评论