While I like Flexcel I wonder about the interface: it is quite far away from the Excel Object Model interface and I don't see a good reason (other than maybe historic) for it. Spreadsheet gear's component (http://www.spreadsheetgear.com/support/help/spreadsheetgear.net.6.0/) is much closer to Excel (not that I wanted to change: it's much too expensive and I need VCL). Thoughts?
While of course there are some historic reasons, the design is not historic. There are 2 main reasons for this design:
1) Excel object model is bad. All objects are highly coupled, so for example a cell knows about the range it is in, which knows about the worksheet it is in, which knows about the workbook, etc. Everybody knows about everybody, which is not good OO design. In FlexCel, a cell is just a row and a column, so it is more top down. A worksheet knows about the cells, but the cells don't know about the worksheet.
2) this doens't apply to .NET, but it does apply to Delphi: Having all those links between different objects, makes it almost impossible to manage the memory. I mean, if you have a reference to a range and to a workbook, and you free the workbook, the workbook shouldn't be freed until you free the range. (or you would get an AV).
To workaround this in Delphi OLE interface to automation, all objects are interfaces, so there is a reference count and objects are only free when the last reference is lost. But this has performance implications, and it isn't quite nice, I remember years ago when I worked with OLE automation, many times when a workbook would fail to close because you had some reference to a cell not set to null somewhere. This is in fact a very common problem of people using OLE automation, you can see thousands of questions of "How do I close Excel". Making sure every reference is nil can be complex in a complex app.
3) FlexCel API is a low-level api, close to the iron. We provide (when it is finished in delphi of course...) a higher level API that is Reports. All other parts of FlexCel (the reports, the rendering engine) depend on this public API and are use it intensely. So, the API has to be blazing fast, or reports and exporting, that do a lot of API calls, would get too slow. For that, we can't really use an OO oriented api like the one in OLE automation, because there is a big cost in creating all those objects and references. In delphi it would be worse, because we would have to make everything interfaces (so they get ref counting), and interfaces are slower (they add a try/finally to every method where they are passed as non cost parameters, and this increases a lot the setup time of those methods). In FlexCel for VCL, there are even parts where we had to use pchars instead of strings since strings have the same issues. Some parts of the code went form 20 second to one when we replaced those strings by pchars. This is a true story, the time is not trivial for methods that are called thousands of times. To make this even worse, there is a lock created to ensure that 2 methods don't change the refocunt at the same time, so it will halt other threads while doing the method setup.
Don't get me wrong, everything has its place, and there are places where refcounted interfaces are great, we do use it some times in the API. But you can't just use them everywhere (which would be required for an Automation API), at least not if you need great performance.
One idea that has been in my backlog for a long time is to create an OleAutomation interface over the low level API, so people who wants to use (or that needs to migrate VBA code) can use it, but the other components like Reports and the rendering engine can keep accessing the faster and lower level "original" API. But well, as everything, making this OO api over the exiting API would take resources and time, and at the moment those are better spent n other places. While we might end up doing it once things get more stable, it doens't make much sense to make it now. What I do plan to port soon to Delphi is APIMate, which is kind of a "macro recorder" for FlexCel. The reason many people finds OLE easy is not because the API is much easier, but because they can record a macro, and see what is the code to do what they want to do. APIMate does something similar for FlexCel, and allows you to find the exact code to do what you need.
Very interesting reading, thanks a lot for taking the time to write it! And I am glad about your focus on low-level and performance.
Cheers,
Hans-Peter