3

how to move entire existing excel range to one row down using interop in C#?

I need to add a header in 1st row of existing excel which has only data.I want to move the entire rangel one setup down programatically in C#.

I saw moving a cell stuff,but moving the entrire range was not clear. could you please help me with it.

Carlos Landeras
  • 11,025
  • 11
  • 56
  • 82
MahaSwetha
  • 1,058
  • 1
  • 12
  • 21
  • 1
    Is C# interop with Excel a must? Otherwise there is a _really_ good open source library called ClosedXML (http://closedxml.codeplex.com/) that works like a charm especially in server (headless) environments. – Philipp Aumayr Dec 03 '12 at 07:23

1 Answers1

3

I agree if you are running on a server, CloseXML is a better solution, but if you want to go the interop way:

Here is the code to add a row and shift the existing data down by one row.

// Inserts a new row at the beginning of the sheet
Microsoft.Office.Interop.Excel.Range a1 = sheet.get_Range( "A1", Type.Missing );
a1.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, 
                    Type.Missing );

After you have inserted your new row at the top, you can add the headers to the cells.
I assume you know how to open and access the sheets.

nunespascal
  • 17,584
  • 2
  • 43
  • 46
  • @nune : hey, if i have 2 header columns in the excel at row 1 and at row 3..then if i start inserting data from 2nd row, how can i make the 3rd row, which has the headercolumns get pushed down for each insertion at the top... Thanks... – Sandeep Jan 20 '13 at 04:57