How to combine multiple Excel files into single Excel workbook with multiple sheets?

Question

I need to develop a service (either WCF or Java) that can take multiple single-sheet Excel files and combine them into a single Excel workbook with multiple sheets. The Excel files are actually exported from crystal reports as individual files. Following are some of the requirements of the service:

1) Should preserve all the formatting including images, color, font etc. 2) Should allow inserting of custom macros into the target file. The macros needs to be run only on the client side when they open the file.

I would like to know what technology/libraries is best suited for above functionality. Should I develope the service in WCF+VSTO or in Java using libraries like Apache POI, JXLS etc?

Answer 1

I obviously can't design the whole thing for you, but I can certainly suggest that you learn about the Excel API, because I'm sure you're going to have to use it to achieve what you are trying to.

These days Microsoft offers it in the form of Visual Studio Tools for Office. Although the preceding MSDN link will help you get started, you may also want to purchase this book.

Answer 2

As far as I know, Excel is the only option if you want to manipulate VBA code.

SpreadsheetGear for .NET, Actuate e.Spreadsheet for Java and various other tools will enable you to combine multiple single sheet workbooks into a single workbook with varying levels of performance and fidelity, but I am quite sure that none of them have APIs for adding VBA code (SpreadsheetGear and some of the others will read and write VBA code, but not allow you to add VBA code).

Disclaimer: I own SpreadsheetGear LLC

Answer 3

Use the Automation (COM/ActiveX) features of Excel.

You can do this through Visual Basic or C++ or any other language (e.g MATLAB or JSDB) that has decent COM/ActiveX support.

I've never been able to find the docs for the Excel object model, but here's an example on MSDN.