How to merge two excel files into one with their sheet names?

16,538

The following worked fine for me, including copying the name and where the name clashed it kindly even handled the Sheet1(2) etc.

Excel.Application app = new Excel.Application();
app.Visible = true;
app.Workbooks.Add("");
app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
  app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
for (int i = 2; i <= app.Workbooks.Count; i++)
{
    for (int j = 1; j <= app.Workbooks[i].Worksheets.Count;j++ )
    {
        Excel.Worksheet ws = app.Workbooks[i].Worksheets[j];
        ws.Copy(app.Workbooks[1].Worksheets[1]);
    }
}
Share:
16,538

Related videos on Youtube

Tripati Subudhi
Author by

Tripati Subudhi

Updated on June 04, 2022

Comments

  • Tripati Subudhi
    Tripati Subudhi about 2 years

    For merging of two excel sheet, I am using below code.

    using System;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Reflection; 
    
    namespace MergeWorkBooks
    {
        class Program
        {
            static void Main(string[] args)
            {
                Excel.Application app = new Excel.Application();
    
                app.Visible = true;
                app.Workbooks.Add("");
                app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
                app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
    
    
                for (int i = 2; i <= app.Workbooks.Count; i++)
                {
                    int count = app.Workbooks[i].Worksheets.Count;
    
                    app.Workbooks[i].Activate();
                    for (int j=1; j <= count; j++)
                    {
                        Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
                        ws.Select(Type.Missing);
                        ws.Cells.Select();
    
                        Excel.Range sel = (Excel.Range)app.Selection;
                        sel.Copy(Type.Missing);
    
                        Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        );
    
                        sheet.Paste(Type.Missing, Type.Missing);
    
                    }
                }
            }
        }
    }
    

    This code is working good for me for merging excel workbook. But at the time of merging I am not getting the excel sheet names. Here I need that when the excel is merging at the same time the sheet names should also go to the merged excel sheet.

  • Tripati Subudhi
    Tripati Subudhi over 12 years
    When I am going to run this then I am getting below error. Compiler Error Message: CS0266: Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?) And also it is saying "No overload for method 'Copy' takes '1' arguments"
  • BugFinder
    BugFinder over 12 years
    Well, other than the name of the 2 workbooks to merge, that was my entire app. Im guessing they changed it in Office2010 then.
  • William Humphries
    William Humphries almost 4 years
    @TripatiSubudhi Did you end up finding a solution for this? Just using Excel.Worksheet ws = (Excel.Worksheet) app.Workbooks[i].Worksheets[j]; ?