Drag formulas in excel from C#

Question

I have a feeling that this question is very simple, but I just can't find the answer.

I want to apply a column of formulas in column "C" based on information in col "A" and "B". I want the formula to work as it would in excel when you write a formula, and then drag, creating row-relative formulas all the way down.

The method below works, but it's very slow, since it writes each formula separately. I'm sure there's a more efficient method out there somewhere.

Thanks

        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

I am posting this code for Drag formulas in excel from C# using Microsoft.Office.Interop.Excel Library

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

    }
}

Answer 2

Use R1C1 formula, and replace :

    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\")";
    }

with

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