How to copy data from one excel sheet to another?

Question

2.14/5 (3 votes)

See more:

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

C#

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??

Posted 11-Dec-13 18:41pm

Member 10426182

Add a Solution

Comments

creepz03 12-Dec-13 22:55pm

Do you want to copy the sheet to another sheet of the same workbook? Or do you want to copy a sheet in to a sheet of another workbook?

2 solutions

Solution 1

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

Posted 12-Dec-13 3:13am

bowlturner

Comments

Member 10426182 16-Dec-13 1:57am

Thanks @bowlturner. That piece guided me towards a probable solution. My final code looks something like what I have mentioned below.

bowlturner 16-Dec-13 9:23am

Glad I could help.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10426182 · Accepted Answer · 2013-12-15T20:06:00

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-

C#

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