{"id":2173,"date":"2012-06-07T19:37:18","date_gmt":"2012-06-07T19:37:18","guid":{"rendered":"http:\/\/craftydba.com\/?p=2173"},"modified":"2024-02-17T14:28:49","modified_gmt":"2024-02-17T14:28:49","slug":"excel-files-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2173","title":{"rendered":"Excel Files"},"content":{"rendered":"

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<\/a> format. Let\u2019s 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\u00e9<\/a>, there are many ways to skin a cat, does apply to technical solutions. <\/p>\n

I installed the write package (Spreadsheet::WriteExcel<\/a>) and read package (Spreadsheet::ParseExcel<\/a>) 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.<\/p>\n

The sample write MS Excel file program uses the following algorithm.<\/p>\n

\r\nCreate new workbook\r\nCreate new worksheet\r\nCreate a format sytle\r\nWrite header row\r\nWrite data rows\r\nClose the workbook\r\n<\/pre>\n
The sample read MS Excel file program uses the following algorithm.<\/p>\n
\r\nOpen existing workbook\r\nParse existing worksheet\r\nGrab min (a) row count\r\nGrab max (b) row count\r\nFor a+1 to b\r\n    Print 'field name'\r\n    Print 'column value'\r\nClose workbook\r\n<\/pre>\n
The table below has starter code that you can use in your next PERL Script to read and write MS Excel files.<\/p>\n\n\n\n\n\n\n\n
tst-read-xls-file.pl<\/a>\n<\/td>\nRead Sample Program<\/td>\n<\/tr>\n
tst-write-xls-file.pl<\/a>\n<\/td>\nWrite Sample Program<\/td>\n<\/tr>\n
read.cmd<\/a>\n<\/td>\nRead Batch File<\/td>\n<\/tr>\n
write.cmd<\/a>\n<\/td>\nWrite Batch File<\/td>\n<\/tr>\n
presidents<\/a><\/td>\nExcel Sample File<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
\nThis is a screen shot of the read sample PERL Script excuting in a MS DOS command shell.\n<\/p>\n
 <\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
\nAgain, PERL has a large user community<\/a>.  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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s 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\u00e9, 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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[441,20,12,15,443,450,449,448,451],"class_list":["post-2173","post","type-post","status-publish","format-standard","hentry","category-other","tag-cpan-perl-modules","tag-excel-file","tag-free-code","tag-john-f-miner-iii","tag-perl-script-2","tag-read-excel-file","tag-spreadsheetparseexcel","tag-spreadsheetwriteexcel","tag-write-excel-file"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2173","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2173"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2173\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}