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

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 2

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 3

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.