如何读取Excel中单元格的格式化文本表示形式

Question

我正在使用Excel的COM接口，并且希望获取单元格的格式化文本表示形式，而不是真正的基础值。

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

另一个用例是当单元格计算出错误时，例如， #DIV/0!,#NAME?, #REF!等。我知道我可以读取Range.Value并测试变体是否为varError（我正在使用Delphi，在VBA中它将是vbError）。我想不出的是如何获得文本表示#DIV/0!等等。Range.Text再次返回，但如果该单元格被隐藏或太窄，则不会。

编辑

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

Answer 1

建立在Steven的答案上：试试这个VBA代码。
由于排队要求，它没有正确处理会计格式，但不清楚你在这种情况下要做什么。

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 

Answer 2

为了得到一个隐藏的单元不包含一个错误的文本：

Application.WorksheetFunction.Text(the_cell.Value, the_cell.NumberFormat) 

如果它是一个错误，这将失败。所以，你可能想先检查：

Application.WorksheetFunction.IsError(the_cell) 

不幸的是，这是很难搞清楚你有什么样的错误为Error.Type功能不可用VBA中或COM对象。解决此问题的方法是将该公式写入同一工作表中的另一个单元格中并读取其值。

Answer 3

非常感谢Charles的回答和有用的意见。我现在将我需要的Delphi/COM版本拼凑在一起，如下所示：

function GetCell(const Sheet: ExcelWorksheet; const Row, Col: Integer): string; 

    function ErrorText(const Cell: ExcelRange; hr: HRESULT): string; 
    const 
    ErrorBase=HRESULT($800A0000); 
    var 
    i: Integer; 
    begin 
    Result := Cell.Text; 
    for i := 1 to Length(Result) do begin 
     if Result[i]<>'#' then begin 
     exit; 
     end; 
    end; 
    if hr=ErrorBase or xlErrDiv0 then begin 
     Result := '#DIV/0!'; 
    end else if hr=ErrorBase or xlErrNA then begin 
     Result := '#N/A'; 
    end else if hr=ErrorBase or xlErrName then begin 
     Result := '#NAME?'; 
    end else if hr=ErrorBase or xlErrNull then begin 
     Result := '#NULL!'; 
    end else if hr=ErrorBase or xlErrNum then begin 
     Result := '#NUM!'; 
    end else if hr=ErrorBase or xlErrRef then begin 
     Result := '#REF!'; 
    end else if hr=ErrorBase or xlErrValue then begin 
     Result := '#VALUE!'; 
    end else begin 
     Result := 'an error'; 
    end; 
    end; 

var 
    Cell: ExcelRange; 
    hr: HRESULT; 

begin 
    Cell := GetCellAsRange(Sheet, Row, Col); 
    if VarIsError(Cell.Value, hr) then begin 
    raise ECellValueError.CreateFmt(
     'Cell %s contains %s.', 
     [R1C1toA1(Row,Col), ErrorText(Cell, hr)] 
    ); 
    end else if VarIsNumeric(Cell.Value) then begin 
    Result := Sheet.Application.WorksheetFunction.Text(Cell.Value, Cell.NumberFormatLocal); 
    end else begin 
    Result := ConvertToString(Cell.Value); 
    end; 
end;