将特定范围的excel单元格从一个工作表复制到另一个工作表 [英] copying of specific range of excel cells from one worksheet to another worksheet

问题描述

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

I am writing a C# program which copies a range of cells from a worksheet of one workbook to a worksheet of an other workbook. But the problem I am facing is I am only able to copy and paste the whole worksheet of first workbook. I want to know how to select only a specific range(from row 5 [column 1 to column 10] to row 100 [column 1 to column 10]) and paste it in second workbook worksheet starting from row 2 column 8.

我还想知道如何直接用C1到C100填充具有某些值的列，而不是使用下面的循环

Also i want to know how a fill a column say from C1 to C100 with some value in a direct way instead of using the loop like below

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

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

Here is the code that i have written so far

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

        }
    }
}

推荐答案

在第一部分中，为整个范围设置相同的值，而不是进行循环跟踪

For the First part of setting the same value for the entire range, instead of looping following will work out

range1 = workSheet.get_Range（ A1：B100）;
range1.Value = Fixed;

range1 = workSheet.get_Range("A1:B100"); range1.Value = "Fixed";

对于复制，您可以尝试@mrtig的建议。

And for copying you can try what @mrtig has suggested.

这篇关于将特定范围的excel单元格从一个工作表复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！