剪切和粘贴列在Excel范围与c＃ [英] Cut and Paste Columns in Excel Range with c#

问题描述

我正在Excel中将列B移动到Excel表中，作为我正在开展的报表的一部分。我有VBA的经验，但在c＃中相对较少，所以我花了最后一个小时在Google上找不到解决方案，我觉得这样做应该很简单，但我不太明白。

I'm trying to move column B in front of column Q in an excel sheet as part of a report I'm working on. I have experience in VBA but relatively little in c# so I've spent the last hour on Google and can't find a solution, I feel like this should be simple but I can't quite get it.

方法一，导致Range类失败的插入方法msg。

Method one, which results in a "Insert method of Range class failed" msg.

Excel.Range rngCut1 = JobLabourSheet.get_Range("B:B", Type.Missing);
Excel.Range rngPaste1 = JobLabourSheet.get_Range("Q:Q", Type.Missing);
            rngCut1.Columns.Cut(rngPaste1.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, rngCut1));

方法二的结果是无法获取Range类的Cut属性msg。 p>

Method two results in a "Unable to get a Cut property of the Range class" msg.

Excel.Range rngCut1 = JobLabourSheet.get_Range("B:B", Type.Missing);
Excel.Range rngPaste1 = JobLabourSheet.get_Range("Q:Q", Type.Missing);
            rngCut1.Columns.Cut(rngPaste1.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Missing.Value));

在第二种方法中，当我省略CopyOrigin时，我得到了msg，但它插入一个空白列在列Q前面。

In the second method when I omit the CopyOrigin I get the msg but it does insert a blank column in front of column Q.

在VBA中，我将使用以下内容：

In VBA I would use the following:

Columns("B:B").Cut
Columns("Q:Q").Insert Shift:=xlToRight

但是像我说的那样，我的c＃体验目前是有限的，所以我不知道如何去翻译它到c＃

But like I said, my c# experience is limited at the moment so I have no idea how to go about translating it to c#

推荐答案

这不是很直观，但这是我如何工作。我采取了一个插入范围，并使用了Insert（）方法，并传递了一个range.Cut（）方法作为复制原始参数。

This isn't very intuitive, but this is how I got it to work. I took an "insert" range and used the Insert() method and passed a "range.Cut()" method as the "Copy Origin" parameter.

参考文档：

• http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.insert.aspx


• http：/ /msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlinsertshiftdirection.aspx


• http://msdn.microsoft.com/en-us/library/microsoft.office。 interop.excel.range.cut.aspx

• http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.insert.aspx
• http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlinsertshiftdirection.aspx
• http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.cut.aspx

这是一个示例应用程序（请务必添加对Microsoft的引用.Office.Interop.Excel）：

Here is a sample app (be sure to add a reference to Microsoft.Office.Interop.Excel):

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelCutAndInsertColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx");
            Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1

            xlApp.Visible = true;

            // cut column B and insert into A, shifting columns right
            Excel.Range copyRange = xlWs.Range["B:B"];
            Excel.Range insertRange = xlWs.Range["A:A"];

            insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, copyRange.Cut());
        }
    }
}

这篇关于剪切和粘贴列在Excel范围与c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！