从 C# 中拖动 Excel 中的公式

Question

我感觉这个问题很简单，但我就是找不到答案。

我想根据“A”列和“B”列中的信息应用“C”列中的一列公式。我希望公式像在 Excel 中一样工作，编写公式，然后拖动，一直向下创建行相对公式。

下面的方法可行，但速度非常慢，因为它单独编写每个公式。我确信某个地方有更有效的方法。

谢谢

        using Excel = Microsoft.Office.Interop.Excel;

        ...


        object oOpt = System.Reflection.Missing.Value; //for optional arguments
        Excel.Application oXL = null;
        Excel.Workbook oWB = null;
        Excel.Worksheet oSheet = null;
        Excel.Range oRng = null;

        try
        {
            //Start Excel and get Application object.
            oXL = new Excel.Application();
            oXL.Visible = true;

            //Get a new workbook.
            oWB = (Excel.Workbook)(oXL.Workbooks.Add(Missing.Value));
            oSheet = (Excel.Worksheet)oWB.ActiveSheet;

            ...
            //Set numberOfRows
            //Load information to column A and B
            ...


            //Write the column of formulas
            for (int r = 2; r < numberOfRows + 2; r++)
            {
                oRng = oSheet.get_Range("C" + r, "C" + r);
                oRng.Formula = "= IF(AND(A" + r + "<> 0,B" + r + "<>2),\"YES\",\"NO\")";
            }


        }
        catch (Exception theException)
        {
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat(errorMessage, theException.Message);
            errorMessage = String.Concat(errorMessage, " Line: ");
            errorMessage = String.Concat(errorMessage, theException.Source);

            MessageBox.Show(errorMessage, "Error");
        }
        finally
        {

            // Cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.FinalReleaseComObject(oRng);
            Marshal.FinalReleaseComObject(oSheet);

            oWB.Close(Type.Missing, Type.Missing, Type.Missing);
            Marshal.FinalReleaseComObject(oWB);

            oXL.Quit();
            Marshal.FinalReleaseComObject(oXL);
       }

Answer 1

使用 R1C1 公式，并将 : 替换

    for (int r = 2; r < numberOfRows + 2; r++)
    {
        oRng = oSheet.get_Range("C" + r, "C" + r);
        oRng.Formula = "= IF(AND(A" + r + "<> 0,B" + r + "<>2),\"YES\",\"NO\")";
    }

为

    oRng = oSheet.get_Range("C2").get_Resize(100, 1);
    oRng.FormulaR1C1 = "=IF(AND(RC[-2]<> 0,RC[-1]<>2),\"YES\",\"NO\")";

Answer 2

我使用 Microsoft.Office.Interop.Excel 库从 C# 发布了在 excel 中拖动公式的代码

class Program
{
    static void Main(string[] args)
    {
        Program p = new Program();
        p.Excel();
    }

    public void Excel()
    {
        Application xlApp = new Application();
        Workbook xlWorkBook;
        Worksheet xlWorkSheet;
        object misValue = Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        for (int r = 1; r < 5; r++) //r stands for ExcelRow and c for ExcelColumn
        {
            // Its a my sample example: Excel row and column start positions for writing Row=1 and Col=1
            for (int c = 1; c < 3; c++)
            {
                if (c == 2)
                {
                    if (r == 1)
                    {
                        xlWorkSheet.Cells[r, c].Formula = "=SUM(A1+200)";
                    }
                    continue;
                }
                xlWorkSheet.Cells[r, c] = r;
            }
        }
        Range rng = xlWorkSheet.get_Range("B1");

        //  This is the main code we can Drag our excel sheet formulas in range
        rng.AutoFill(xlWorkSheet.get_Range("B1", "B4"), XlAutoFillType.xlLinearTrend);

        xlWorkBook.Worksheets[1].Name = "MySheetData";//Renaming the Sheet1 to MySheet

        xlWorkBook.SaveAs(@"E:\test.xlsx");

        xlWorkBook.Close();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

    }
}