Merge Excel Files Into One

Question

I have a few hundred Excel files, where each file has some data on the first sheet. I've been asked to write a console application in C# which merges all of the Excel files together into a single document while retaining the formatting.

The consolidated file is a single workbook containing a sheet for each file merged into the document. Unfortunately, the Excel files are in binary rather than XML format, so I can't perform an XSL transformation.

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

Answer 1

Look into Visual Studio Tools for Office (VSTO).
You can open workbooks in code and use a lot of the excel functionality, as well as copying worksheets like you've said you need to do.

For resources, this MSDN forum post demonstrates opening excel files and copying worksheets. Hopefully this will get you on the right direction.

Answer 2

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

You should be able to add a reference, in the com tab, to Microsoft Excel 11 Object Library.

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

Answer 3

This is an easy task for a neat little program called Bulk File Merger (they have a Mac version called Excel File Merger). I managed to merge over 300 files in a matter of minutes. You can also merge csv files, which takes even less time. We banged out over 1300 csv files in seconds. Scary.

Answer 4

It's not free (a developer license is $ 425) but GemBox has a very good library that is way faster than using the office objects. We use it in my current project since a year and a half, and it has consistently performed very well for us.

Answer 5

Of course you can do Tool->Compare->Merge Workbooks. And within Excel as Joshua mentioned there is the merge method in the COM Interop libraries. If you need some help let me know I work heavily in Excel and may be able to help you out on a small fee basis.