Reputation: 95
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);
}
Upvotes: 3
Views: 5109
Reputation: 101
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);
}
}
Upvotes: 0
Reputation: 12505
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\")";
Upvotes: 3