开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜