Search code examples
c#excelexcel-dnanetoffice

Insert Excel Rows/Columns with ExcelDNA or NetOffice

I am using ExcelDNA to set and get cell values. I can get and set cell values

var ref = ExcelReference(2, 2);
var val = ref.GetValue();
ref.SetValue(42);

is there a way to insert an entire row or column by moving entries to the right or down? I want the same behavior as when the user right clicks the column and all the entries are shifted to the right. Solution can use NetOffice if necessary.

Solution

  • I'd recommend using the COM object model for this, and the code will be similar to VBA for the same task.

    You get hold of the root Application object with a call to ExcelDnaUtil.Application. The resulting object will be of type Microsoft.Office.Interop.Excel.Application and can be used to select the row of column, then call app.Selection.Insert() to insert the new row or column.

    It should also be possible using the C API, but that is unlikely to be easier or faster.