Creating Reports with Ruby and Microsoft Office

For many businesspeople, knowing how to use a computer means knowing how to use Microsoft Office running under Microsoft Windows on a PC. While adoption of open source office software like OpenOffice.org and open source operating systems like Linux is on the rise--and many developers, including myself, strongly prefer them--Microsoft software in ubiquitous in corporate America. Of course, this is less of an issue for web-based applications, since HTML is well standardized, and with a relatively small amount of effort, you can produce applications that work across platforms. However, occasionally, you'll still need to interact directly with applications running on Microsoft Windows systems, which can be difficult, since Microsoft Office programs have proprietary, relatively closed formats.

In this chapter, we'll look at some ways to create reports with Ruby and Microsoft Office programs, and then work through an example of producing a web-based system that sends its data to an Access database.

Microsoft Excel, Word, and Access are familiar to many users. Managers and other businesspeople are trained to manipulate data in Excel, and letting them use Excel as an input format gives them a great deal more power. In fact, you may not even get a choice whether to use Excel, since it may be already deeply ingrained in the business model or directly supplied to you by vendors. Microsoft Word is typically the word processor of choice for organizations of all types. Microsoft Access is also a convenient platform. End users can use data from Access to import data into Word and Excel; mail merges, for example, are often done using data from Access.

Let's look at some ways that you can create reports that interact with these familiar Microsoft Office programs.

Suppose you need to read data supplied in a Microsoft Excel file. Since this is a proprietary, closed format, there's no easy way to parse it. Fortunately, others in the open source community have already done the work in the form of the parseexcel gem. (Unfortunately, the parse-excel gem did not work with the Excel files I generated while writing this chapter, so I cannot recommend this technique at this time; however, by the time you read this, the situation may have changed.)

If you simply want to display an Excel spreadsheet in the browser, you can consider using unexcel, which is an open source Perl script that takes Excel files and converts them to HTML. You can find this tool at http://sourceforge.net/projects/unexcel.

When you need to directly export data to Excel, you can use the spreadsheet-excel gem, as discussed in detail in Chapter 4. Additionally, the example in Chapter 13 demonstrates how to use a trick to easily export an HTML file containing tabular data to Excel (and OpenOffice.org as well).