Excel Files

Business Users like to have data in a spreadsheet format. Since Microsoft Office is installed world-wide, it will be a common request for data in a XLS format. Let’s make believe that we have the business problem to write and read information about the first five presidents to a MS Excel file. The Cliché, there are many ways to skin a cat, does apply to technical solutions.

I installed the write package (Spreadsheet::WriteExcel) and read package (Spreadsheet::ParseExcel) from CPAN to solve my problem. Both packages have more methods and properties than you will ever need. With that said, I am going to discuss the technical algorithm behind the programs instead of the nitty gritty coding.

The sample write MS Excel file program uses the following algorithm.

Create new workbook
Create new worksheet
Create a format sytle
Write header row
Write data rows
Close the workbook

The sample read MS Excel file program uses the following algorithm.

Open existing workbook
Parse existing worksheet
Grab min (a) row count
Grab max (b) row count
For a+1 to b
    Print 'field name'
    Print 'column value'
Close workbook

The table below has starter code that you can use in your next PERL Script to read and write MS Excel files.

This is a screen shot of the read sample PERL Script excuting in a MS DOS command shell.

Again, PERL has a large user community. Before writing something from scratch, you should double check the user groups to see if package has not already been published. You should get very familiar with creating Excel worksheets and summarizing data with pivot tables. These skills will make you an invaluable resource to the Business Line Community.