如何将数据从一个Excel工作表复制到另一个？ [英] How to copy data from one excel sheet to another?

问题描述

我有一个代码，它基本上从excel文件中获取数据并将其复制到另一个文件。我一直在使用以下代码

I have a code which basically takes the data from an excel file and copies it to another. I have been using the below code

string pathFileSource = "C:\\Temp\\Output.xls";
                string pathFileDestination = "C:\\Temp\\Dest.xls";
                Excel.Application excel = new Excel.Application();
                Excel.Workbook wbSource = excel.Workbooks.Open(pathFileSource, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                Excel.Workbook wbDestination = excel.Workbooks.Open(pathFileDestination, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                Excel.Worksheet WorksheetSource = wbSource.Sheets[1];
                //Copy all range in this worksheet
                WorksheetSource.UsedRange.Copy(Missing.Value);
                Excel.Worksheet WorksheetDestination = wbDestination.Sheets[1];
                // Select used Range, paste value only
                //WorksheetDestination.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);
                WorksheetDestination.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);
                wbDestination.Save(); 
                wbSource.Close();
                wbDestination.Close();
                //Quit application
                excel.Quit();

虽然数据已从源表复制到剪贴板，但它不会复制到目标文件。任何人都可以指出一些其他解决方案或帮助指出上述代码中的任何差距??

Although the data is getting copied from the source sheet to Clipboard, it is not copied to the destination file. Can anyone point out to some other solution or help point out any gaps in the above code??

推荐答案

我在一个线程上找到了这个并且用户声称它有效。

I found this on a thread and the user claims it works.

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

namespace CodeCall
{
    public class ExcelTest
    {
        public static void Main()
        {
            ApplicationClass app = new ApplicationClass();
            Workbook curWorkBook = null;
            Workbook destWorkbook = null;
            Worksheet workSheet = null;
            Worksheet newWorksheet = null;
Object defaultArg = Type.Missing;
try
{
// Copy the source sheet
curWorkBook = app.Workbooks.Open("c:\\Book1.xlsx", defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
workSheet = (Worksheet)curWorkBook.Sheets[1]; 
workSheet.UsedRange.Copy(defaultArg);

// Paste on destination sheet
destWorkbook = app.Workbooks.Open("c:\\Book2.xlsx", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
finally
{
if (curWorkBook != null) {
curWorkBook.Save();
curWorkBook.Close(defaultArg, defaultArg, defaultArg);
}

if (destWorkbook != null)
{
destWorkbook.Save();
destWorkbook.Close(defaultArg, defaultArg, defaultArg);
}
}
            app.Quit();
        }
    }
}

我试图将所有数据从'Output.xls'文件复制到'dest.xls'的'sheet1'。这就是我的最终代码 -

I was trying to copy all the data from 'Output.xls' file to the 'sheet1' of 'Dest.xls'. This is what my final code looks like-

string pathFileDestination = "C:\\Dest.xls";
Excel.Application excel = new Excel.Application();
//excel.Visible = true;
Excel.Workbook wbDest = excel.Workbooks.Open(pathFileDestination, 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetDest = wbDest.Sheets[1];
//Clear all contents in Destination workbook
WorksheetDest.UsedRange.ClearContents();
WorksheetDest.get_Range("A1").Value = "No Data";
wbDest.Save();
wbDest.Close();
//Open the Source file
Excel.Workbook wbSource = excel.Workbooks.Open("C:\\Output.xls", 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetSource = wbSource.Sheets[1];
//Copy all range in this worksheet
WorksheetSource.UsedRange.Copy(Missing.Value);
//Open destination workbook
Excel.Workbook wbDestination = excel.Workbooks.Open(pathFileDestination, 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetDestination = wbDestination.Sheets[1];
WorksheetDestination.UsedRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, misValue, misValue);
wbDestination.Save();
wbSource.Close();

这篇关于如何将数据从一个Excel工作表复制到另一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！