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

Question

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

有现有的方法可以做到吗？提前致谢！

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

Answer 1

在 VBA（可轻松修改为 vbscript）中，您可以使用高效的变体数组快速将所有工作表中的所有公式转储到平面 txt 文件（更改您的路径以适应）。 代码源自我的文章

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

Answer 2

这是我用来获取工作表上包含公式的单元格列表的一些代码。看起来相当快。

try
{
    Excel.Worksheet excelWorksheet = workbook.ActiveSheet as Excel.Worksheet;
    Excel.Range formulaCell = excelWorksheet.Cells.SpecialCells(
        Excel.XlCellType.xlCellTypeFormulas, Type.Missing);

    Excel.Range cell;
    foreach (var fc in formulaCell)
    {
        cell = fc as Excel.Range;
        string s1 = cell.Formula as string;
        int c = cell.Column;
        int r = cell.Row;

        // Gives formula text and location of formula.
    }
}
catch (Exception)
{
    ; // Throws an exception if there are no results.
      // Probably should ignore that exception only
}

Answer 3

组合键 ctrl+`（反勾）在查看值和公式之间切换，它不是一个平面列表，但很有用。

Answer 4

在 brettdj 的帮助下，我现在成功地进行了四叉树搜索

private static void FindFormula(Excel excel, TextWriter writer, int rowstart, int rowend, int colstart, int colend)
{
    // Select the range
    excel.Range(rowstart, rowend, colstart, colend);

    // Check whether this range has formulas
    if (!excel.RangeHasFormula())
        return;

    // Check if we only have a single cell
    if (excel.RangeCellCount() == 1)
    {
        Console.WriteLine(excel.CellFormula(rowstart, colstart));
        return;
    }

    int r1, r2, r3, r4;
    int c1, c2, c3, c4;

    r1 = rowstart;
    r2 = rowstart + (rowend - rowstart + 1) / 2 - 1;
    r3 = r2 + 1;
    r4 = rowend;

    if (colstart == colend)
    {
        c1 = c2 = c3 = c4 = colstart;

        FindFormula(excel, writer, r1, r2, c1, c2);
        FindFormula(excel, writer, r3, r4, c1, c2);
    }
    else
    {
        c1 = colstart;
        c2 = colstart + (colend - colstart + 1) / 2 - 1;
        c3 = c2 + 1;
        c4 = colend;

        FindFormula(excel, writer, r1, r2, c1, c2);
        FindFormula(excel, writer, r1, r2, c3, c4);
        FindFormula(excel, writer, r3, r4, c1, c2);
        FindFormula(excel, writer, r3, r4, c3, c4);
    }
}

Answer 5

我找到了这个答案，并尝试使用@Jake 的 C# 代码，但发现它很慢。

这是一个更快（且完整）的版本：

using System;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace ExportExcelFormulas
{
    static class ExcelAccess
    {
        public static void ExportFormulasSimple(string filePath)
        {
            var app = new Application();
            var workbook = app.Workbooks.Open(filePath);
            var sCount = workbook.Sheets.Count;

            var sb = new StringBuilder();

            for (int s = 1; s <= sCount; s++)
            {
                var sheet = workbook.Sheets[s];
                var range = sheet.UsedRange;
                var f = range.Formula;

                var cCount = range.Columns.Count;
                var rCount = range.Rows.Count;

                for (int r = 1; r <= rCount; r++)
                {
                    for (int c = 1; c <= cCount; c++)
                    {
                        var id = ColumnIndexToColumnLetter(c) + "" + r + ": ";
                        var val = f[r, c];

                        if (!string.IsNullOrEmpty(val))
                        {
                            sb.AppendLine(id + val);
                            Console.WriteLine(id + val);
                        }

                    }
                }

            }

            var text = sb.ToString();
        }

        // Based on https://www.add-in-express.com/creating-addins-blog/2013/11/13/convert-excel-column-number-to-name/
        public static string ColumnIndexToColumnLetter(int i)
        {
            var l = "";
            var mod = 0;

            while (i > 0)
            {
                mod = (i - 1) % 26;
                l = (char)(65 + mod) + l;
                i = (int)((i - mod) / 26);
            }

            return l;
        }
    }
}