C# Excel 插入行（不是添加）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13418776/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel insert rows (not Add)
提问by user1096207
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.
我有一个用于采购订单的 Excel '07 模板文件。在模板上，只有 3 行的项目空间，然后模板显示总计。
So, basically, in the Template it has: Row 19 - item Row 20 - item Row 21 - item Row 22 - total of the items
所以，基本上，在模板中，它有：第 19 行 - 项目第 20 行 - 项目第 21 行 - 项目第 22 行 - 项目总数
Obviously, most purchases will have more than 3 items, though. So how would I inserta row between 21 and 22, after printing 3 items out?
显然，大多数购买都会有超过 3 件商品。那么，在打印 3 个项目后，如何在 21 和 22 之间插入一行？
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.
但是，我的 Insert 被插入到错误的工作表中，哈哈。而不是我什至想象它被插入的地方 - 第 2 行，第 19 列。
回答by Sid Holland
First of all, I don't see where you are setting your xlWorksheetbut that would be the first place I'd check to see why your cells are being inserted on the wrong sheet.
首先，我没有看到你在哪里设置你的，xlWorksheet但这将是我检查的第一个地方，看看为什么你的单元格被插入了错误的工作表上。
Secondly, I don't think your Excel.Rangeobject is being set up properly. You could be running into trouble because you're only specifying row numbers in the WorkSheet.Cellsproperty 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 Worksheetobject since that usually works in a more predictable manner.
其次，我认为您的Excel.Range对象没有正确设置。您可能会遇到麻烦，因为您只在WorkSheet.Cells属性中指定行号而不是列名。当我尝试在使用的单元格范围之后插入单元格时，而不是我想要的位置。我倾向于使用对象的get_Range()方法，Worksheet因为它通常以更可预测的方式工作。
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);
回答by user1096207
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...
直到午休后我才看到 Sid Holland 的帖子，一位同事给我发送了这段代码，该代码与他的工作基本相同......
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.
即使计数为 1，也能完美运行。
回答by Veronika Krytskaya
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 count
适用于任何计数
