1

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.

Matthew Molloy
  • 1,166
  • 1
  • 10
  • 22

2 Answers2

1

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.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • ExcelDnaUtil.Application returns an instance of System.__ComObject. Which version of ExcelDna are you on? – Matthew Molloy Mar 15 '15 at 05:36
  • Yes - it's a COM object that is really the Excel Application object. If you reference Microsoft.Office.Interop.Excel you can cast it to Application. – Govert Mar 15 '15 at 06:58
  • I had to add an extra dll reference to get this working. Instructions http://stackoverflow.com/questions/15285880/how-to-reference-microsoft-office-interop-excel-dll – Matthew Molloy Mar 15 '15 at 09:33
1

I would like to add that NetOffice does not support the EntireRow and EntireColumn methods of the Range object, which would be useful for inserting or deleting full rows. As a workaround, one can replace this for rows by addressing full rows by Range(rowNoStart + ":" + rowNoEnd).

For columns, one can write Range(GetExcelColumnName(colStart) + ":" + GetExcelColumnName(colEnd)), where GetExcelColumnName is a function from this former SO post.

Community
  • 1
  • 1
Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • I was wondering about the differences between NetOffice and Excel-DNA, in case you know about this. I can open a question if you want me to. Thanks much! – NoChance Aug 12 '19 at 15:37
  • 1
    @NoChance: ExcelDNA and NetOffice fulfill different purposes, and they are orthogonal to each other, so the question about their differences makes no sense. NetOffice is a capsule around the COM object model of main MS Office programs, which allows writing programs which don't bind themselves to a specific Excel or Word version. So you could ask which parts of the full COM object model are still not supported by NetOffice, that would be a sensible question. – Doc Brown Aug 12 '19 at 19:04
  • Thank you for clearing this out, I have only knew about the products recently and did not have a chance to go deep. Thank you for your answer. – NoChance Aug 12 '19 at 19:14