How to add new column at the beginning of worksheet to an existing excel using microsoft interop

Question

Hi

I have an excel with 8 columns. I'm trying to add a new column at the beginning of the worksheet. The existing columns should shift.

Below is the code which I tried:

            OpenExcelWorkbook(@"d:\TLC\TLC3.xlsx");
            _sheet = (Excel.Worksheet)_sheets[1];

            _sheet.Select(Type.Missing);

            _sheet.Columns.Insert(1, 1);

But I'm getting the following error:

To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet. If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save.

Answer 1

Change your insert statement to

((Range) workSheet.Columns[1]).Select();
((Range) workSheet.Columns[1]).Insert(XlInsertShiftDirection.xlShiftToRight,
                                            XlInsertFormatOrigin.xlFormatFromLeftOrAbove);