如何读取Excel中单元格的格式化文本表示 [英] How do I read the formatted textual representation of a cell in Excel

问题描述

我使用COM接口到Excel，我想获取格式化的文本表示形式，而不是真正的底层值。

例如，假设单元格包含数字 1.23456 ，并且用户已经指定了小数位数为1的数字格式。然后我想能够读取字符串1.2。我知道我可以使用 Range.Text 但是这失败了几个重要的方式。 Range.Text 返回用户在工作表视图中看到的内容，因此如果单元格被隐藏，则返回空字符串。如果单元格的宽度为低，则返回截断的字符串。 Range.Text 也会下降，因为它限制为1024个字符。

另一个用例是当单元格到错误，例如＃DIV / 0！， #NAME？， #REF！等等。我知道我可以读取 Range.Value 并测试变体是否是 varError （I' m使用Delphi，在VBA中会是 vbError ）。我不能弄清楚的是如何获取文本表示＃DIV / 0！等。 Range.Text

$ b> $ c> b $ b

我认为Range.Text的限制实际上是255个字符。

解决方案

请尝试此VBA代码。

由于排队需求，它不能正确处理会计格式，但它不清楚在这种情况下你想要做什么。

子测试（）
Dim oRng As Range
Dim var As Variant
设置oRng = Range（a3 ）
如果IsError（oRng）then
var = cstrError（oRng.Value）
Else
var = oRng.Value2
如果IsNumeric（var）Then var = Format （var，oRng.NumberFormatLocal）
End If
MsgBox Len（var）& & var
End Sub
函数cstrError（vError As Variant）as String
选择案例CLng（vError）
案例xlErrDiv0
cstrError =＃DIV / 0！
case xlErrNA
cstrError =＃N / A
案例xlErrName
cstrError =#NAME？
case xlErrNull
cstrError =#NULL！
case xlErrNum
cstrError =#NUM！
案例xlErrValue
cstrError =#VALUE！
case xlErrRef
cstrError =#REF！
Case Else
cstrError =＃N / A
结束选择
结束函数


I'm using the COM interface to Excel and I would like to get hold of the formatted textual representation of a cell rather than the true underlying value.

For example, suppose the cell contains the number 1.23456 and the user has specified the number format with 1 decimal place. Then I'd like to be able to read the string "1.2". I know that I can use Range.Text but this fails in a couple of significant ways. Range.Text returns what the user sees in the view of the sheet and so if the cell is hidden then the empty string is returned. If the cell's width is low then a truncated string is returned. Range.Text also falls down because it is limited to 1024 characters.

Another use case is when the cell evaluates to an error, e.g. #DIV/0!, #NAME?, #REF! etc. I know that I can read Range.Value and test if the variant is type varError (I'm using Delphi, in VBA it would be vbError). What I can't figure out is how to get hold of the text representation #DIV/0! etc. Again Range.Text returns this, but not if the cell is hidden or is too narrow.

EDIT

I think that the limit on Range.Text is actually 255 characters.

解决方案

Building on Steven's answer: try this VBA code.
It does not handle the Accounting format properly because of the lining-up requirement, but its not clear what you would want to do in that case anyway.

    Sub testing()
    Dim oRng As Range
    Dim var As Variant
    Set oRng = Range("a3")
    If IsError(oRng) Then
        var = cstrError(oRng.Value)
    Else
    var = oRng.Value2
    If IsNumeric(var) Then var = Format(var, oRng.NumberFormatLocal)
    End If
    MsgBox Len(var) & " " & var
End Sub
Function cstrError(vError As Variant) As String
    Select Case CLng(vError)
    Case xlErrDiv0
        cstrError = "#DIV/0!"
    Case xlErrNA
        cstrError = "#N/A"
    Case xlErrName
        cstrError = "#NAME?"
    Case xlErrNull
        cstrError = "#NULL!"
    Case xlErrNum
        cstrError = "#NUM!"
    Case xlErrValue
        cstrError = "#VALUE!"
    Case xlErrRef
        cstrError = "#REF!"
    Case Else
        cstrError = "#N/A"
    End Select
End Function

这篇关于如何读取Excel中单元格的格式化文本表示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！