Combining Excel files into one

Question

I have several hundred Excel files, where each file has some data on the first sheet. I was asked to write a console application in C #, which combines all Excel files together into one document, while maintaining formatting.

A consolidated file is a separate book containing a sheet for each file combined with the document. Unfortunately, Excel files are in binary format, not XML format, so I cannot perform XSL conversion.

Is there a free library or sample code that demonstrates how to combine sheets from multiple Excel documents into one file?

Answer 1

You will need the Microsoft Excel Interop library. http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

You must add the link on the com tab to the Microsoft Excel 11 object library.

http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx

Answer 2

This is a simple task for a neat little program called Bulk File Merger (they have a Mac version called Excel File Merge). I managed to merge more than 300 files in minutes. You can also merge csv files, which takes even less time. In a few seconds, we deleted more than 1300 csv files. Scary.

Answer 3

It's not free (a developer license is $ 425), but GemBox has a very good library, which is much faster than using office objects. We have been using it in my current project for a year and a half, and it consistently works very well for us.

Answer 4

Take a look at Visual Studio Tools for Office (VSTO).
You can open books in code and use many excel functions, as well as copy worksheets, as you said, what you need to do.

For resources, this MSDN forum post demonstrates opening excel files and copying worksheets. Hope this helps you in the right direction.

Answer 5

Of course, you can do Tool-> Compare-> Merge Workbooks. And in Excel, as Joshua said, there is a merge method in COM Interop libraries. If you need help, let me know that I work hard in Excel and can help you get a small fee.