best way to programmatically modify excel spreadsheets

Question

Hi, I'm looking for a library that will allow me to programatically modify Excel files to add data to certain cells. My current idea is to use named ranges to determine where to insert the new data (essentially a range of 1x1), then update the named ranges to point at the data. The existing application this is going to integrate with is written entirely in C++, so I'm ideally looking for a C++ solution (hence why this thread is of limited usefulness). If all else fails, I'll go with a .NET solution if there is some way of linking it against our C++ app.

An ideal solution would be open source, but none of the ones I've seen so far (MyXls and XLSSTREAM) seem up to the challenge. I like the looks of Aspose.Cells, but it's for .NET or Java, not C++ (and costs money). I need to support all Excel formats from 97 through the present, including the XLSX and XLSB formats. Ideally, it would also support formats such as OpenOffice, and (for output) PDF and HTML.

Some use-cases I need to support:

• reading and modifying any cell in the spreadsheet, including formulas
• creating, reading, modifying named ranges (the ranges themselves, not just the cells)
• copying formatting from a cell to a bunch of others (including conditional formatting) -- we'll use one cell as a template for all the others we fill in with data.

Any help you can give me finding an appropriate library would be great. I'd also like to hear some testimonials about the various suggestions (including the ones in my post) so I can make more informed decisions -- what's easy to use, bug-free, cheap, etc?

Answer 1

The safest suggestion is to just use OLE. It uses the COM, which does not require .NET at all.

http://en.wikipedia.org/wiki/OLE_Automation <--about halfway down is a C++ example.

You may have to wrap a few functionalities into functions for usability, but it's really not ugly to work with.

EDIT: Just be aware that you need a copy of Excel for it to work. Also, there's some first-party .h files that you can find specific to excel. (it's all explained in the Wikipedia article)

Answer 2

Have you already tried using the Excel COM interfaces? Obviously Excel needs to be install on the machine, and it's a pain to deal with...

Answer 3

I would argue that a .net solution with COM interop for linking into your C++ application is the best solution. In more than ten years of working with them, I've never seen a COM automation of Excel that didn't leak memory somewhere.

If you need to automate Excel, I recommend Visual Studio Tools for Office. If you don't need to automate, only modify files and those files can be in Office 2007 format, you're better off finding a library that manipulates the files directly instead of opening Excel to do it.

Answer 4

I don't know if this is an option for you, but the new office 2007 formats are in zipped XML format, which makes it very doable to do your own modifications. See here for the specifications.

Answer 5

SpreadsheetGear for .NET will handle your requirements and has an API which is very similar to Excel.

When you insert cells, your defined names (and any other formulas / charts / etc...) will automatically be fixed up to reference the new range (just as they would in Excel). So you would not need to update your defined names (although there is complete support for creating and updating defined names if that is what you want to do).

SpreadsheetGear is a .NET component, but you can build your own wrapper which is callable from C++.

You can see what our customers say and download the free, fully functional evalution here.

Answer 6

I ended up using Aspose.Cells as I mentioned in my original post, since it seemed like the easiest path. I'm very happy with the way it turned out, and their support is very good. I had to create a wrapper around it in C# that exported a COM interface to my C++ application.