Excel中插入行（不添加） [英] Excel insert rows (not Add)

问题描述

我有一个采购订单的Excel '07模板文件。在模板中，有3行价值的项目唯一的房间，然后在模板显示总。

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.

因此​​，基本上，在模板它具有： 行19 - 项目 行20 - 项目 行21 - 项目 行22 - 总的项目

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

显然，大多数的购买将有超过3项，虽然。所以，我怎么会的插入的21和22之间的一排，打印3项出来后？

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（编辑）所以，这里是我的：

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());

不过，我将被插入到了错误的工作，哈。而不是在那里我会想象它得到inserted-行2列19。

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.

推荐答案

首先，我没有看到你在哪里设置你的 xlWorksheet ，但是这将是首先我会检查，看看为什么你的细胞被插在错误的表。

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.

其次，我不认为你的 Excel.Range 对象被设置正确。你可能会遇到了麻烦，因为你只在 WorkSheet.Cells 属性指定的行号，而不是列名。当我想，我是越来越细胞细胞的使用范围后插入，不是我想要的。我会倾向于使用工作表对象的 get_Range（）方法，因为这通常工作在一个更predictable方式。

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);

这篇关于Excel中插入行（不添加）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！