C#Excel如何以编程方式复制/粘贴非连续范围 [英] C# Excel How to copy/paste non contiguous ranges programatically

问题描述

如何使用Excel COM C#复制非连续范围并将这些范围粘贴到另一个WorkBook?
工作表不能影响其他超出定义范围的单元格.我的某些目标范围"将与源范围"不同.
这是我的一些代码.它给出了编译器错误该命令无法在多个选择上使用...."我什至尝试使用application.union执行复制和粘贴无效.
拜托，有人可以帮我吗?我发现的大多数涉及整张纸或连续范围...

How can I use Excel COM C# to copy non contiguous ranges and paste those ranges to another WorkBook?
The Sheets cannot have other cells outside of the defined ranges affected. Some of my Destination Ranges will be different than the Source Ranges.
Here is some of my code. It gives compiler error "That command cannot be used on multiple selections.... " I have even tried to perform the copy and paste using application.union to no effect.
Please, can any one help me with this? Most of what I have found involved full sheets or contiguous ranges...

Excel.Range original1 = worksheetSource.get_Range("A1:A9, A15:A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1:A9, A15:A35", Type.Missing);

original1.Copy(Type.Missing);
                Destination1.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

推荐答案

我建​​议进行首次调用，并从表单的第一张表格中获取数据数据表.然后，设置目标工作表的值，并通过以前的方法传入数据表.您可以将其分为两个呼叫吗?并使用get_Range方法通过定义startCell和endCell来获取一定范围的单元格.

I would suggest making the first call and grabbing the data from the first sheet in the form of a DataTable. Then set the values of the destination sheet and pass in the data table from the previous method. Can you split this up into two calls? and use the get_Range method to get a range of cells by defining the startCell, and endCell.

Excel.Range original1 = worksheetSource.get_Range("A1", "A9" , Type.Missing);

Excel.Range original2 = worksheetSource.get_Range("A15" "A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1", "A9" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A15" "A35" , Type.Missing);

当然，除了将其分成两部分，您还可以执行以下操作(假设从A10到A14没有数据):

Of course, instead of splitting this into two pieces you, could also do the following (Assuming there is no data from A10 to A14):

Excel.Range original2 = worksheetSource.get_Range("A1" "A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1", "A35" , Type.Missing);

完整代码:

Full Code:

string fileName1 = @"C:\testDir\Book1.xls";
string fileName2 = @"C:\testDir\Book2.xls";

System.Data.DataTable test1 = new System.Data.DataTable();

test1 = getCellRangeValueAsDataTable(fileName1, "A1", "A9");

setCellRangeValueWithDataTable(fileName2, "A1", "A9", test1);

public System.Data.DataTable getCellRangeValueAsDataTable(string filename, string startCell, string endCell)
   {
       System.Data.DataTable newTable = new System.Data.DataTable();

       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
       string result = "";
       if (xlApp == null)
       {
           MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
           return newTable;
       }
       //xlApp.Visible = true;

       Workbook wb = xlApp.Workbooks.Open(fileName,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing);
       Worksheet ws = (Worksheet)wb.Worksheets[1];

       if (ws == null)
       {
           MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
           return newTable;
       }

       // Select the Excel cells, in the range startcell to endcell in the worksheet.
       Range aRange = ws.get_Range(startCell, endCell);

       if (aRange == null)
       {
           MessageBox.Show("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
           return newTable;
       }

       for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
       {
           newTable.Columns.Add("String"+cCnt, typeof(string));
       }

       for (int rCnt = 1; rCnt <= aRange.Rows.Count; rCnt++)
       {
           object[] objArr = new object[aRange.Columns.Count];

           for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
           {
               objArr[cCnt - 1] = (string)(aRange.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
           }
           newTable.Rows.Add(objArr);
       }


       var val = aRange.Value2;

       wb.Close(true, Type.Missing, Type.Missing);

       xlApp.Quit();

       releaseObject(ws);
       releaseObject(wb);
       releaseObject(xlApp);

       return newTable;
   }

   /// <summary>
   /// sets the values for a given cell range by utilizing values from a specified datatable
   /// </summary>
   /// <param name="filename"></param>
   /// <param name="startCell"></param>
   /// <param name="endCell"></param>
   /// <param name="values"></param>
   public void setCellRangeValueWithDataTable(string filename, string startCell, string endCell, System.Data.DataTable values)
   {
       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

       if (xlApp == null)
       {
           MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
           return;
       }
       //xlApp.Visible = true;

       Workbook wb = xlApp.Workbooks.Open(fileName,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing);
       Worksheet ws = (Worksheet)wb.Worksheets[1];

       if (ws == null)
       {
           MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
       }
       Range aRange = ws.get_Range(startCell, endCell);

       for (int i = 0; i <= values.Rows.Count - 1; i++)
       {
           for (int j = 0; j <= values.Rows[i].ItemArray.Count() - 1; j++)
           {
               aRange.Cells[i + 1, j + 1] = values.Rows[i].ItemArray[j];
           }
       }

       wb.Close(true, Type.Missing, Type.Missing);

       xlApp.Quit();

       releaseObject(ws);
       releaseObject(wb);
       releaseObject(xlApp);
   }

private void releaseObject(object obj)
   {
       try
       {
           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
           obj = null;
       }
       catch (Exception ex)
       {
           obj = null;
           MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
       }
       finally
       {
           GC.Collect();
       }
   }

这篇关于C#Excel如何以编程方式复制/粘贴非连续范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！