首页 文章

将特定范围的excel单元格从一个工作表复制到另一个工作表

提问于
浏览 次
9

我正在编写一个C＃程序，它将一系列单元格从一个工作簿的工作表复制到另一个工作簿的工作表 . 但我遇到的问题是我只能复制并粘贴第一个工作簿的整个工作表 . 我想知道如何仅选择特定范围（从第5行[第1列到第10列]到第100行[第1列到第10列]）并将其粘贴到从第2行第8列开始的第二个工作簿工作表中 .

另外我想知道填充列如何从C1到C100以直接的方式表示某些值，而不是像下面那样使用循环

for(i=1;i<2;i++)
{
for(j=1;j<101;i++)
{
worksheet.cells[i,j]="Fixed";
}
}

这是我到目前为止编写的代码

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

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application srcxlApp;
            Excel.Workbook srcworkBook;
            Excel.Worksheet srcworkSheet;
            Excel.Range srcrange;
            Excel.Application destxlApp;
            Excel.Workbook destworkBook;
            Excel.Worksheet destworkSheet;
            Excel.Range destrange;
            string srcPath;
            string destPath;
//Opening of first worksheet and copying
            srcPath="C:\\Documents and Settings\\HARRY\\Desktop\\incident.csv";
            srcxlApp = new Excel.Application();
            srcworkBook = srcxlApp.Workbooks.Open(srcPath);
            srcworkSheet = srcworkBook.Worksheets.get_Item(1);
            srcrange = srcworkSheet.UsedRange;
            srcrange.Copy(Type.Missing);

//opening of the second worksheet and pasting
            destPath = "C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report.xls";
            destxlApp = new Excel.Application();
            destworkBook = destxlApp.Workbooks.Open(destPath,0,false);
            destworkSheet = destworkBook.Worksheets.get_Item(1);
            destrange = destworkSheet.Cells[1, 1];
            destrange.Select();

            destworkSheet.Paste(Type.Missing, Type.Missing);
            destworkBook.SaveAs("C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report " + DateTime.Now.ToString("MM_dd_yyyy") + ".xls");
            srcxlApp.Application.DisplayAlerts = false;
            destxlApp.Application.DisplayAlerts = false;
            destworkBook.Close(true, null, null);
            destxlApp.Quit();
            srcworkBook.Close(false, null, null);
            srcxlApp.Quit();

        }
    }
}
c# excel

3 回答

  • 12

    对于为整个范围设置相同值的第一部分，而不是循环跟随将解决

    range1 = workSheet.get_Range（“A1：B100”）; range1.Value =“固定”;

    对于复制，您可以尝试@mrtig建议的内容 .

    回复于
  • 2

    你应该能够做到这一点：

    Excel.Range from = srcworkSheet.Range("C1:C100");
        Excel.Range to = destworkSheet.Range("C1:C100");

        from.Copy(to);
    回复于
  • 6

    mrtig有一个非常优雅的解决方案 . 但是如果您将工作簿放在excel的单独实例中，它将无法工作 . 所以，关键是只在一个实例中打开它们 . 我已修改您的示例以使用此方法显示：

    public void CopyRanges()
{
    // only one instance of excel
    Excel.Application excelApplication = new Excel.Application();

    srcPath="C:\\Documents and Settings\\HARRY\\Desktop\\incident.csv";
    Excel.Workbook srcworkBook = excelApplication.Workbooks.Open(srcPath);
    Excel.Worksheet srcworkSheet = srcworkBook.Worksheets.get_Item(1);

    destPath = "C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report.xls";
    Excel.Workbook destworkBook = excelApplication.Workbooks.Open(destPath,0,false);
    Excel.Worksheet destworkSheet = destworkBook.Worksheets.get_Item(1);

    Excel.Range from = srcworkSheet.Range("C1:C100");
    Excel.Range to = destworkSheet.Range("C1:C100");

    // if you use 2 instances of excel, this will not work
    from.Copy(to);

    destworkBook.SaveAs("C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report " + DateTime.Now.ToString("MM_dd_yyyy") + ".xls");
    srcxlApp.Application.DisplayAlerts = false;
    destxlApp.Application.DisplayAlerts = false;
    destworkBook.Close(true, null, null);
    srcworkBook.Close(false, null, null);
    excelApplication.Quit();
}
    回复于

相关问题