Excel insert rows (not Add)

Question

I have an Excel '07 Template file for a purchase order. On the template, there's only room for 3 rows worth of items, then the template shows the Total.

So, basically, in the Template it has: Row 19 - item Row 20 - item Row 21 - item Row 22 - total of the items

Obviously, most purchases will have more than 3 items, though. So how would I insert a row between 21 and 22, after printing 3 items out?

Edit; So here's what I have:

            xlApp.Workbooks.Open(template, misValue, misValue, misValue, 
                misValue, misValue, misValue, misValue, misValue, misValue, 
                misValue, misValue, misValue, misValue, misValue); 

int row = 19;
if (poDetailBO1.MoveFirst())
            {
                do
                {
                    itemsBO3.FillByPK(poDetailBO1.Style);
                    if (row < 22)
                    {


                        xlApp.Cells[row, 1] = poDetailBO1.LineNo;
                        xlApp.Cells[row, 2] = itemsBO3.Factory;
                        xlApp.Cells[row, 3] = poDetailBO1.Style;
                        xlApp.Cells[row, 4] = itemsBO3.UPC_Code;
                        xlApp.Cells[row, 5] = itemsBO3.Item_Description;
                        xlApp.Cells[row, 6] = "TARRIFF"; //To be replaced later
                        xlApp.Cells[row, 7] = itemsBO3.Plate_Color;
                        xlApp.Cells[row, 8] = itemsBO3.Color;
                        xlApp.Cells[row, 9] = poDetailBO1.PrePack;
                        xlApp.Cells[row, 10] = itemsBO3.Cost;
                        xlApp.Cells[row, 11] = poDetailBO1.Qty;
                        xlApp.Cells[row, 12] = poDetailBO1.Qty * itemsBO3.Cost;

                        row++;
                    }
                    else if (row >= 22)
                    {
                        Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[row, misValue], xlWorkSheet.Cells[row, misValue]];
                        r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue);
                        r.Value2 = "GOBBLYDEEGOOK";

                        row++;
                    }
                } while (poDetailBO1.MoveNext());

However, my Insert gets inserted into the wrong worksheet, hah. And not where I'd even imagine it to get inserted- Row 2, column 19.

Answer 1

First of all, I don't see where you are setting your xlWorksheet but that would be the first place I'd check to see why your cells are being inserted on the wrong sheet.

Secondly, I don't think your Excel.Range object is being set up properly. You could be running into trouble because you're only specifying row numbers in the WorkSheet.Cells property and not column names. When I tried that I was getting cells inserted after the used range of cells, not where I wanted. I would be inclined to use the get_Range() method of the Worksheet object since that usually works in a more predictable manner.

Given all that, depending on whether you want specific cells shifted down, or the entire row, you can use one of the following:

// To shift down a set of cells from columns A to F

Excel.Range r = xlWorkSheet.get_Range("A" + row.ToString(), "F" + row.ToString());
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown);

// To shift down all of a row

Excel.Range r = xlWorkSheet.get_Range("A" + row.ToString(), "A" + row.ToString()).EntireRow;
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown);

Answer 2

I didn't see Sid Holland's post until after my lunch break, where a co-worker sent me this code that does basically the same thing as his...

    private void CopyRowsDown(int startrow, int count, Excel.Range oRange, Excel.Worksheet oSheet)
    {
        oRange = oSheet.get_Range(String.Format("{0}:{0}", startrow), System.Type.Missing);
        oRange.Select();
        oRange.Copy();
        //oApp.Selection.Copy();

        oRange = oSheet.get_Range(String.Format("{0}:{1}", startrow + 1, startrow + count - 1), System.Type.Missing);
        oRange.Select();
        oRange.Insert(-4121);
        //oApp.Selection.Insert(-4121);

    }

Worked perfectly, even when count is 1.