How to combine two Excel workbooks into one workbook in C #?

Question

Let's consider that I have two excel files (Workbooks) in local. Each Excel workbook has 3 sheets.

Suppose WorkBook1 has Sheet1, Sheet2, Sheet3

Workbook2 has Sheet1, Sheet2, Sheet3.

So, I need to combine these two excel books into one and a new excel workbook called Workbook3, which will have only 6 worksheets (a combination of workbook1 and workbook2).

I need code that will perform this operation in C # without using a third-party tool. If a third-party tool is a free version, then its fine.

Please try to help me. This is very relevant. For more information, please feel free to ask.

Thanks in advance...

Answer 1

Here's a working sample that combines two books into a new one, hope this gives you an idea:

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

Answer 2

A simpler solution is to copy the worksheets themselves , not their cells.

This method takes any number of paths to the excel file and copies them to a new file:

 private static void MergeWorkbooks(string destinationFilePath, params string[] sourceFilePaths) { var app = new Application(); app.DisplayAlerts = false; // No prompt when overriding // Create a new workbook (index=1) and open source workbooks (index=2,3,...) Workbook destinationWb = app.Workbooks.Add(); foreach (var sourceFilePath in sourceFilePaths) { app.Workbooks.Add(sourceFilePath); } // Copy all worksheets Worksheet after = destinationWb.Worksheets[1]; for (int wbIndex = app.Workbooks.Count; wbIndex >= 2; wbIndex--) { Workbook wb = app.Workbooks[wbIndex]; for (int wsIndex = wb.Worksheets.Count; wsIndex >= 1; wsIndex--) { Worksheet ws = wb.Worksheets[wsIndex]; ws.Copy(After: after); } } // Close source documents before saving destination. Otherwise, save will fail for (int wbIndex = 2; wbIndex <= app.Workbooks.Count; wbIndex++) { Workbook wb = app.Workbooks[wbIndex]; wb.Close(); } // Delete default worksheet after.Delete(); // Save new workbook destinationWb.SaveAs(destinationFilePath); destinationWb.Close(); app.Quit(); } 

---

Edit: note that you can use the Move method instead of Copy if you have dependencies between sheets, for example. pivot table, charts, formulas, etc. Otherwise, the data source will be disconnected, and any changes on one sheet will not affect the others.

Answer 3

You are looking for Office Autmation libraries in C #.
Here is a sample code to help you get started.

Answer 4

  System.Data.Odbc.OdbcDataAdapter Odbcda; //CSV File strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + SourceLocation + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"; sqlSelect = "select * from [" + filename + "]"; System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim()); conn.Open(); Odbcda = new System.Data.Odbc.OdbcDataAdapter(sqlSelect, conn); Odbcda.Fill(ds, DataTable); conn.Close(); 

• This would read the contents of the excel file in the dataset.
• Create multiple datasets like this, and then merge.

Code taken directly from here .