The System i and Office 2007
December 13, 2006 Michael Sansoterra
|
It seems like only yesterday that I recall people touting the benefits of upgrading to Office 97. Well here we are 10 years later at the release of Office 2007. From a System i5 developer’s perspective, I’ll briefly cover a few of the benefits of upgrading to Office 2007 by discussing some enhancements to this latest version of the Microsoft productivity suite. First of all, the Office 2007 user interface has been redone using the new “ribbon” user interface. The main difference with this new interface is that drop down menus are now replaced with toolbar-like icons. Instead of each menu item on the menu bar revealing a drop down menu with menu options, each of the menu items changes the “ribbon” tool bar to reveal a graphical representation of each function (i.e. what was formerly a menu option). So far, I don’t like it as it’s a pretty big departure from prior versions of Office. I often have to go to help to find what was once a familiar menu option in prior versions of Office. Figure 1 below shows a picture of the new ribbon interface in Word.
Like its Office 97 predecessor from a decade ago, the introduction of Office 2007 again brings a version of Office with a brand new file format for applications such as Word, Excel, and PowerPoint. This is good and bad. This new file format is good because it uses the OpenXML format instead of the traditional proprietary binary format. The bad news is that the new default format isn’t backwards compatible with previous versions of Office. That means early adopters must remember to use the “Save As Type” option to save the document in the prior version’s format. Thankfully, document type mismatches will be easily identifiable as the new formats have new file extensions: .docx, .xlsx, etc. The major benefit of these XML document types is that it will be easier to generate true Office documents from other platforms, including the i5/OS and OS/400 platform. In the past, System i developers have created generic exports in comma delimited or rich text formats or resorted to open source Java APIs to generate Office documents. However, generic formats are limited in their capabilities and the free APIs have had some issues coping with document complexity, implementing all available features, or performance. Hopefully, a new generation of open source Java or even RPG APIs will soon be available to generate documents in the OpenXML format. Since XML is text based and the OpenXML document standards are available to anyone, all available features of a Word document or Excel spreadsheet should be reproducible. Another benefit of this new document structure is that existing documents can be modified by performing various substitutions within the XML text. It will be “easy” to create a template document and then use an XML parser to modify or insert text. Also, Microsoft’s Web site noted it is possible to programmatically peruse through Word documents and replace an old logo with a new one. Indexing applications such as Google Desktop or Microsoft’s Windows Desktop Search should eventually be able to do a much better job of indexing the content of these documents as well. And beware: While the new document format is XML based, generating the new XML documents will still take the average developer a while to learn how to construct and manipulate–which is why I’m hoping for new open source APIs! Other general enhancements to Office 2007 include the ability to generate XPS (XML Paper Specification) and PDF documents. How long has this feature been overdue? At least ten years! Originally part of the beta product, this capability is now only available as a separate downloadable “add-in” via the Microsoft Office Update Web site. It is unfortunate that Microsoft has chosen to not include this with the base Office product, since any applications that use it will also have to distribute this add-in. Apparently, this decision was made under threat of legal action by Adobe, the creator of the PDF format and the development tools that read and create it. By the way, I can’t resist mentioning that the standard clip art collection in Office 2007 appears to be the same old terrible clip art collection that has been used for at least the last ten years. I’m still on dial-up at home, which makes getting to the online clipart collection a chore. One of these days Microsoft may include a decent clipart collection with the installation CD. Moving to the Excel spreadsheet, the biggest thrill is that the 65,536 row limit has finally been lifted. The new limit is 1,048,576 rows. It is a relief to note that this limitation will no longer be a worry when programmatically creating spreadsheets. The number of available columns has been increased as well to a whopping 65,536, although I don’t ever recall someone running out of columns. Focusing on the Microsoft Access database, it too now has the ability to export reports in an industry standard format such as Adobe’s PDF. The proprietary Access report format was often a stumbling block for applications that required distributed reports. It is shame Microsoft let this shadow hover over an otherwise great report writing tool as this limitation, in my opinion, sent many developers to use other reporting products, such as Crystal Reports. Another nice surprise for Access developers is the new “attachment” data type. This data type is the successor to the “OLE Object” data type for storing binary data (pictures, movies, sounds, and so forth) in Access. If you’ve ever attempted to store binary data such as pictures or sound files with the OLE Object data type you know the database balloons in size. The new attachment data type will preserve the data type in its original size and format. When exporting Access data to the System i, I hoped that the attachment data type would translate to a BLOB in DB2, but it does not. It translates incorrectly to a CLOB, so there will still have to be a little extra programming done to share binary data from an Access database. Other Access features include a new drop down calendar control and the ability to store rich text in memo fields. (Memo is the equivalent of the varchar(max) data type in SQL Server 2005.) There are other new features aimed at new or hobbyist developers, such as the ability to create “total rows” in data sheets and the ability to store multiple values in a single table column. However, we’ll have to see how many seasoned Access developers use these features. The one thing missing from Access that I was really hoping for is the ability to base an Access form or report on an ADO or ODBCDirect recordset. This old limitation is still present. The inability to do this seriously hinders the development of a traditional Access database against non-Access data sources such as DB2. Obviously, there are plenty of other new features in the various Office products I haven’t mentioned – I just focused on some of the features that I will find useful during my normal routine as a casual Office developer who often integrates with DB2. Overall, this Office 2007 release has some nice things, such as the removal of the row limitations in Excel and some nice developer enhancements in Access. But I don’t perceive the average user or developer making huge productivity gains. However, the most notable of the enhancements is the adoption of the OpenXML format, which will allow developers an easier way to create and manipulate existing documents. Getting users standardized on this new document format will eventually catapult new and existing software to generate dazzling documents with advanced features instead of the current common “simpleton” Excel spreadsheet exports and Word mail merges. Currently Office 2007 is only available to volume licensing customers, but will be made available to everyone else on January 30, 2007. ADDITIONAL RESOURCES To review the new XML document format for Microsoft Word, see http://msdn2.microsoft.com/en-us/library/ms771890.aspx |
