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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 |
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.
| tst-read-xls-file.pl | Read Sample Program |
| tst-write-xls-file.pl | Write Sample Program |
| read.cmd | Read Batch File |
| write.cmd | Write Batch File |
| presidents | Excel Sample File |
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.
