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

Question

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

例如，假设单元格包含数字1.23456，并且用户已指定带有1个小数位的数字格式。那么我想能够读取字符串"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个字符。

- David Heffernan

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

- Charles Williams

---

当单元格包含超过255个字符时，此操作将失败。 - David Heffernan

是 Format 函数有 255 个字符的限制 - 尝试修订版本，仅在值为数字时使用格式。 - Charles Williams

这看起来很有前途，但Format是VBA函数。我正在使用COM（它是一个COM插件），并且我不确定是否有可用的等效函数。 - David Heffernan

如果 Delphi 没有与 VB6 的 Format 相当的函数，则您可以使用 Application.WorksheetFunction.Text(var, oRng.NumberFormatLocal) 代替 Format(var, oRng.NumberFormatLocal)。 这样做的效率较低，但应该能起到作用。 - Charles Williams

我对于语言环境（locales）、LCID等有一点了解。NumberFormalLocal的文档说这是用户所使用的语言的数字格式。这对我来说很晦涩！我猜想它等同于使用LOCALE_USER_DEFAULT。这听起来可信吗？ - David Heffernan

Answer 2

获取隐藏单元格中不包含错误的文本的方法如下：

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

如果出现错误，这个操作会失败。因此，您可能需要先检查以下内容：

Application.WorksheetFunction.IsError(the_cell)

很遗憾，由于VBA或COM对象中没有Error.Type函数，因此很难确定您遇到了什么类型的错误。解决方法是在同一工作簿的另一个单元格中编写该公式并读取其值。

- Steven Rumbalski

---

再次强调，当单元格包含超过255个字符时，此方法将失败。我越是尝试深入研究，就越怀疑这根本不可能实现。 - David Heffernan

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;

- David Heffernan