C# excel interop formula dragging

I'm using the C# excel interop framework to automating generate a bunch of reports for my company and I'm wondering if anyone knows how to do equation dragging开发者_JS百科 with this.

If I could do this with a named range this would be even better, but even if I have to hard code the rows so it's along the lines of drag A10:J10 down to A20:J20 this would probably be ok.

Even any pointers to a webpage explaining how to do this would be much appreciated as I'm kind of stuck on this one.

Thanks!

Range object has a method called AutoFill, you should try that.

range.AutoFill(destinationRange);

You can use the next snippet. Hope be helpful

Excel.Range r1 = xlWorksheet.Range[xlWorksheet.Cells[offset , 1], xlWorksheet.Cells[offset, 1]];
Excel.Range r2 = xlWorksheet.Range[xlWorksheet.Cells[offset, 1], xlWorksheet.Cells[offset + 10, 1]];
r1.AutoFill(r2, Excel.XlAutoFillType.xlFillCopy);

I did something like this not that long ago. Here's an example of my code:

Excel.Range rng, rngResize;
rng = (Excel.Range)oSheet.get_Range("A10", "J10");
rngResize = rng.get_Resize(10, Missing.Value);
rngResize.Formula = "=B9+I8";

The formula is applied relatively. And I used separate variables for each step so they can be Disposed. Let me know if this helps.

You can create an Excel table (ListObject) by invoking the Add method of the ListObjects collection of the Worksheet object. This is the equivalent of using the "Format as table" command in Excel. Any equation entered in a table row will auto-fill to all rows of the table using relative references to table columns. This behavior is on by default but can be controlled by the setting at

Globals.ThisWorkbook.Application.AutoCorrect.AutoFillFormulasInLists