How to insert a new row in the specified cell only of Excel sheet using c

Question

So I have this code:

        using Excel = Microsoft.Office.Interop.Excel;
        ...

        public static void AppendColumnValuesGivenStart(Excel.Worksheet wks, int column, int columnStarts, int totalColumnsInTable, List<ColumnValues> columnValues)
        {
            for (int x = 0; x < columnValues.Count; x++)
            {
                for (int y = 0; y < totalColumnsInTable; y++)
                {
                    wks.Cells[x + columnStarts, column + y].Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
                }
            }
        }

It is supposed to insert a new row at the specified position of the cell, but in the output, it makes a new row that spans all the way to the right and interferes with other tables.

What am I doing wrong and what would be the correct way of inserting a new row in the specified cell?

Answer 1

I have this worksheet with a matrix of data ...

I then want to insert 5 rows between columns E and J to give me a result that looks like this ...

To achieve this, there are a few ways to do it.

You can do it by selecting the range specifically and running the insert method ...

xlWorksheet.Range["E6:J10"].Insert(XlInsertShiftDirection.xlShiftDown);

You can obviously pass in parameters to make it happen as well ...

int rowFrom = 6;
int rowTo = 10;

xlWorksheet.Range[$"E{rowFrom}:J{rowTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

Or you can do it using cell references ...

var cellAddressFrom = ((Range)xlWorksheet.Cells[6, 5]).Address;
var cellAddressTo = ((Range)xlWorksheet.Cells[10, 10]).Address;

xlWorksheet.Range[$"{cellAddressFrom}:{cellAddressTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

Or more again, cell references using column letters, not numbers ...

var cellAddressFrom = ((Range)xlWorksheet.Cells[6, "E"]).Address;
var cellAddressTo = ((Range)xlWorksheet.Cells[10, "J"]).Address;

xlWorksheet.Range[$"{cellAddressFrom}:{cellAddressTo}"].Insert(XlInsertShiftDirection.xlShiftDown);

The bottom line, you need to get your parameters right and you need to make up a matrix (unless it's a single cell you want to shift down) in order to shift cells down or right.