c# - 生成所有 Excel 单元格公式的平面列表

Question

我有一个用 VBA 和单元格公式编写的大型程序。我的任务是将其逆向工程为 C# winforms。我想到了一个开始，我需要在一个平面列表中查看所有单元格公式。

有什么现成的方法吗？提前致谢!

编辑:为了分享，在回答者的帮助下，我设法想出了这个:

Answer 1

在 VBA 中(可轻松修改为 vbscript)，您可以使用高效的变体数组快速将所有工作表中的所有公式转储到一个平面 txt 文件(更改路径以适应)。 code sourced from my article here

Const sFilePath = "C:\test\myfile.txt"    

Sub CreateTxt_Output()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim X
    Dim lRow As Long
    Dim lCol As Long
    Dim strTmp As String
    Dim lFnum As Long

    lFnum = FreeFile
    Open sFilePath For Output As lFnum

    For Each ws In ActiveWorkbook.Worksheets
    Print #lFnum, "*****" & ws.Name & "*****"
        'test that sheet has been used
        Set rng1 = ws.UsedRange
        If Not rng1 Is Nothing Then
            'only multi-cell ranges can be written to a 2D array
            If rng1.Cells.Count > 1 Then
                X = ws.UsedRange.Formula
                For lRow = 1 To UBound(X, 1)
                    For lCol = 1 To UBound(X, 2)
                        'write each line to txt file
                        Print #lFnum, X(lRow, lCol)
                    Next lCol
                Next lRow
            Else
                Print #lFnum, rng1.Formula
            End If
        End If
    Next ws

    Close lFnum
    MsgBox "Done!", vbOKOnly
End Sub

[更新部分 - 您可以使用 SpecialCells 在 VBA 中快速隔离公式。如果工作表上没有公式，则需要进行错误处理，请参阅下面的 GetFormula

Sub GetFormula()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    For Each ws In ActiveWorkbook.Sheets
    Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rng1 Is Nothing Then
            For Each rng2 In rng1.Areas
            'dump cells here
            Next rng2
        End If
    Next ws
End Sub