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.
|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.
Cloud TagsAFTER ALTER TABLE ALTER TRIGGER CPAN perl modules CREATE DATABASE create function create procedure CREATE TABLE create trigger create view database administrator database developer DATA TYPES DDL DECLARE DELETE DROP DATABASE DROP TRIGGER DROP VIEW execute EXISTS FORMAT free code INSERT ISNULL John F. Miner III PASS perl script REPLACE SELECT sp_help sp_helptext SQL Server SQL Server Management Studio SSMS. string function sys.databases sys.objects sys.schemas TRIGGERS TRUNCATE TABLE TSQL UPDATE USER DEFINED VIEW vb script