CODEWITHSUNDEEP
c#excelexcel-interop
Tripati Subudhi
Tripati Subudhi

Reputation: 1691

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

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.

Upvotes: 4

Views: 17793

Answers (2)

Ashu
Ashu

Reputation: 19

Error Free and Improve Answer

create result2.xlsx file as same location and you find final excel sheet as you want

 class Program
     {        
      static void Main(string[] args)
        {
        Application app = new Application();
        app.Visible = true;
        Workbook w1 = app.Workbooks.Add(@"D:\MyDownload\result2.xlsx");
        Workbook w2 = app.Workbooks.Add(@"D:\MyDownload\merge1.xlsx");
        Workbook w3 = app.Workbooks.Add(@"D:\MyDownload\merge2.xlsx");
        for (int i = 2; i <= app.Workbooks.Count; i++)
        {
            for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
            {
                Worksheet ws = (Worksheet)app.Workbooks[i].Worksheets[j];
                ws.Copy(app.Workbooks[1].Worksheets[1]);
            }
        }
        app.Workbooks[1].SaveCopyAs(@"D:\MyDownload\result2.xlsx");
        w1.Close(0);
        w2.Close(0);
        w3.Close(0);
        app.Quit();
    }
}

Upvotes: 0

BugFinder
BugFinder

Reputation: 17868

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

Upvotes: 8

Related Questions