Data Copying from one excel workbook to another through C#

Please help m开发者_运维百科e in below code. I want to copy all my data from "D:\Data Validation_Source Code\TestExcel\DataValidationTest.xlsx" excel sheet to "D:\Data Validation_SourceCode\TestExcel\DataValidationTest - updated.xlsx" excel sheet, but instead of its copying in a new work book. Please help me.

try 
{ 
    string startPath = System.IO.Path.GetDirectoryName( 
        System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName); 
    string filePath = System.IO.Path.Combine(startPath,  
        "D:\\Data Validation_Source Code\\TestExcel\\DataValidationTest.xlsx"); 

    xlApp = new Excel.Application(); 
    xlWorkBook = xlApp.Workbooks.Open(filePath); 
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); 
    xlWorkSheet.Copy(Type.Missing, Type.Missing); 
    xlWorkSheet = xlApp.Workbooks[2].Sheets[1]; 

} 
finally 
{ 
    if (xlWorkBook != null) 
    { 
         xlWorkBook.Close(); 
    } 
    if (xlApp != null) 
    { 
        xlWorkBook = xlApp.Workbooks.Open( 
            "D:\\Data Validation_SourceCode\\TestExcel\\DataValidationTest - updated.xlsx"); 
        xlApp.Quit(); 
    } 
    this.releaseObject(xlWorkSheet); 
    this.releaseObject(xlWorkBook); 
    this.releaseObject(xlApp); 
}  

One problem is that your second argument to Path.Combine is a fully-qualified path. In that case, the method effectively ignores the first argument and returns the second. So the call to GetCurrentProcess().MainModule.FileName doesn't accomplish anything.

Your main problem, though, is that you are not telling Excel where to put the new worksheet. How can it know where to put it, if the file name "D:\Data Validation_SourceCode\TestExcel\DataValidationTest - updated.xlsx" does not appear in the program until after you have called the Copy method?

Here's the code sample again for formatting, with comments added:

try
{
    string startPath = System.IO.Path.GetDirectoryName( System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
    string filePath = System.IO.Path.Combine(startPath, "D:\Data Validation_Source Code\TestExcel\DataValidationTest.xlsx");
    xlApp = new Excel.Application();  
    xlWorkBook = xlApp.Workbooks.Open(filePath);  
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
    xlWorkSheet.Copy(Type.Missing, Type.Missing); //Footnote 1
    xlWorkSheet = xlApp.Workbooks[2].Sheets[1];   //Footnote 2
}
finally
{
    if (xlWorkBook != null)
    {
        xlWorkBook.Close();
    }
    if (xlApp != null)
    {
        xlWorkBook = xlApp.Workbooks.Open( "D:\Data Validation_SourceCode\TestExcel\DataValidationTest - updated.xlsx");
        xlApp.Quit();
    }
    this.releaseObject(xlWorkSheet);
    this.releaseObject(xlWorkBook);
    this.releaseObject(xlApp);
}

Footnote 1: Read the help file for the Copy method. It says "If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet." That explains the behavior you're seeing.

Footnote 2: This assignment accomplishes basically nothing, so it's not clear what you're hoping to achieve here.

Advice: If the workbook "D:\Data Validation_SourceCode\TestExcel\DataValidationTest - updated.xlsx" already exists, you need to open it before you call the copy method, and then pass one of its sheets to the Copy method as either the "before" or "after" argument. If the workbook doesn't already exist, you can call Copy as you are, specifying neither before or after, and then save the new workbook with the proper name.