It's been a while since I've posted anything to my blog so I figured I would share something I've been working on over the last month. A client hired us to refactor some applications for them, but as part of our requirements we asked that they upgrade to CF8 since they are running on CF 5. Naturally, since this application was built on CF 5, some things just are not worth hacking through to make work on CF 8, such as specific COM objects that create Excel worksheets.
I saw numerous postings on using POI to create spreadsheets with CF, but it just seemed like a very tedious and bulky process. After a bit of research I found
Ben Nadel's post on SpreadsheetML. After a bit of testing, it seemed that SpreadsheetML fit the bill. It generates Excel files many times faster than the current COM-based solution and was easy to write . Thanks for the info Ben!
Now, let me say that I wish MS would have used OpenXML, but one can't hold your breath for stuff like this. Overall, though, I will say I am impressed with SpreadsheetML. It allows me to add formulas, format data, add styles, multiple worksheets and workbooks.
One of the big quirks with building Excel with SpreadsheetML is the horrible debugging process. It wasn't until a few days ago that I figured out that the log file it tells you the errors are located in actually do exist.

As you can see from the image, it says the log is located in C:\Users\username\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO. I am using Vista, so naturally this would be different on XP or Windows 2000/2003 (usually in the Documents and Settings structure). However, if you browse to the Temporary Internet Files folder you will not see a folder named Content.MSO, even though hidden files and folders are turned off in my settings. What I discovered is that the folder stays hidden, and you can only view the contents if you type the path into Windows Explorer. Silly Windows! I don't recall this ever being an issue on XP, so maybe it's Vista specific.
Now that I've found my error logs, let's take a look at what it tells me is wrong:
XML ERROR in Style
REASON: Illegal Tag
FILE: C:\Users\TJ2B84~1.SAN\AppData\Local\Temp\value_tech_summary_table_08-28.xls
GROUP: Borders
TAG: Font
OK, so that's pretty generic! There's the major downside to developing with SpreadsheetML, debugging is a serious PITA. However, once you get used it (it isjust XML afterall), you can readily spot issues and resolve them.
Probably the coolest thing about working with SpreadsheetML was how easy it was to convert an existing document from Excel format to Excel XML (SpreadsheetML) format . I simply opened the Excel file, chose File>>Save As and then set the "Save as type" to "XML Spreadhseet 2003". Then I used eclipse to open the resulting XML file and edited it, plugging in my CF code to generate dynamic values. Of course, MS code is a bit sloppy so it took some time to clean up the files and minimize the number of styles used, and other unnecessary code, but it sure beat having to create these complex workbooks from scratch. It was also very helpful in educating me how SpreadsheetML uses styles, formatters and formulas.
Now that I am nearing the end of the project I would definitely say I would use SpreadsheetML again, and probably recommend it to anyone who needs to create or recreate MS Excel spreadsheets. If you've used the CFComet stuff in the past and it's holding you back from upgrading to a newer version of CF, don't be afraid to jump in and give SpreadsheetML a try!