生成所有excel单元格公式的平面列表-Generateaflatlistofallexcelcellformulas

2  

in VBA (easily modifiable to Vbscript) you could quickly dump all formulae in all sheets to a flat txt file (change your path to suit) with an efficient variant array. code sourced from my article here

在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

[Updated section - you can isolate formulae quickly in VBA by using SpecialCells. Error Handling is needed in case there are no formulae on a sheet, see GetFormula below

[更新部分 - 您可以使用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

1  

Here is some code that I used to get a list of cells on a worksheet with formulas in them. It seems pretty fast.

下面是一些代码,用于获取工作表中包含公式的单元格列表。看起来很快。

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
}

0  

The key combination ctrl+` (back tick) toggles between viewing values and formulas, it is not a flat list, but it is useful.

键组合ctrl +`(后退勾选)在查看值和公式之间切换,它不是平面列表,但它很有用。

0  

With help from brettdj, I managed to whip up a quad tree search at the moment

在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);
    }
}

0  

I found this answer, and tried to use the C# code by @Jake, but discovered it was slow.

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

Here is a faster (and complete) version:

这是一个更快(和完整)的版本:

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;
        }
    }
}