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 件.那么，在打印出 3 个项目后，我如何插入 21 到 22 之间的一行?

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

但是，我的 Insert 被插入到错误的工作表中，哈哈.而不是我什至想象它被插入的地方 - 第 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 属性中指定行号，而不是列名.当我尝试在使用的单元格范围之后插入单元格时，而不是我想要的位置.我倾向于使用 Worksheet 对象的 get_Range() 方法，因为它通常以更可预测的方式工作.

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屋！