Insert Excel Rows (Not Adding)

Question

I have an Excel '07 template file for a purchase order. The template has a place for 3 rows of items, then Total is displayed in the template.

So basically, in the Template there is: Row 19 - item Line 20 - position Line 21 - position Line 22 - total number of items

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

Change So here is 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 is inserted in the wrong worksheet, hah. And not where I could even suggest that it is inserted - row 2, column 19.

Answer 1

First of all, I don’t see where you are installing your xlWorksheet , but this will be the first thing I would check to understand why your cells are pasted on the wrong sheet.

Secondly, I don’t think your Excel.Range object Excel.Range configured correctly. You may have problems because you specify line numbers in the WorkSheet.Cells property, not in the column names. When I tried, I was getting cells pasted after the used cell range, not where I wanted. I would be inclined to use the get_Range() method of the Worksheet object since it usually works in a more predictable way.

Given all this, depending on whether you want certain cells to be shifted down, or the entire row, you can use one of the following values:

 // 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 did not see Sid Holland's message until my lunch break, where an employee sent me this code, which does basically the same thing as it ...

  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 fine even if the counter is 1.

Answer 3

 public static void CopyRowsDown(_Worksheet worksheet, int startRowIndex, int countToCopy) { for (int i = 1; i < countToCopy; i++) { var range = worksheet.get_Range(string.Format("{0}:{0}", startRowIndex, Type.Missing)); range.Select(); range.Copy(); range = worksheet.get_Range(string.Format("{0}:{1}", startRowIndex + i, startRowIndex + i, Type.Missing)); range.Select(); range.Insert(-4121); } } 

works for any account