Read and Write Excel tables in Perl

It is convenient to read and write Excel files in Perl.CodeFor future reference.

To read and write Excel components, you must install the following commands:

Perl-mcpan-e shell-> install Spreadsheet: writeexcel
Perl-mcpan-e shell-> install Spreadsheet: parseexcel

 

#! /Usr/bin/perluse Spreadsheet: writeexcel; # Write Excel Data use spreadsheet: parseexcel; # Read Excel Data # Read data # Use: loadstringsfromexcel (filename ); sub loadstringsfromexcel {my $ parser = Spreadsheet: parseexcel-> New (); my $ workbook = $ parser-> parse (@ _ [0]); # Open the input file my $ totalcount = 0; If (! Defined $ workbook) # Whether to enable successfully {print "failed to open @ _ [0] \ n"; die $ parser-> error (),". \ n ";}$ sheets_count = $ workbook-> worksheet_count (); # number of sheets # access all sheetfor ($ Index = 1; $ index <= $ sheets_count; $ index ++) {my $ worksheet = $ workbook-> Worksheet (@ sheets [$ index-1]); my $ result; If (! Defined $ worksheet) # failed to read sheet {print "cocould not get the worksheet \ n"; last ;}else {$ result = loadwordingsfromsheet ($ worksheet ); $ totalcount + = $ result ;}} printf "\ ntotal found $ totalcount strings \ n"; print "finished! \ N ";}# read the strings in sheet. loadstringsfromexcel calls sub loadwordingsfromsheet {my $ sheet =$ _ [0]; # gets the passed-in sheet if (! Defined $ sheet) {die "cocould not get argument! \ N ";}# obtain the minimum and maximum row numbers in sheet my ($ minrow, $ maxrow) = $ sheet-> row_range (); print" now, checking ", $ sheet-> get_name (), "\ n"; # print the sheet name $ COUNT = 1; # Read the data in the first column of each row in sequence for ($ I = $ minrow; $ I <= $ maxrow; $ I ++) {# obtain the data in the first column, get_cell (row number, column number) $ STR = ($ sheet-> get_cell ($ I, 0)-> value (); $ STR = trim ($ Str ); print $ STR, "\ n"; $ count ++;} return $ count ;}# write to excel # Use writedatatoexcel (File Name) sub writedatatoexcel {my $ workbook = Spreadsheet:: Writeexcel-> New (@ _ [0]); # Open the Excel file if (! Defined $ workbook) # Whether to enable successfully {print "failed to open @ _ [0] \ n"; die $ parser-> error (),". \ n ";} My $ worksheet = $ workbook-> add_worksheet (); # create a sheetif (! Defined $ sheet) {die "cannot create new sheet! \ N ";}# write the first row title write (row number, column number, content) $ worksheet-> write (, 'id '); $ worksheet-> write (0, 1, 'rule'); # other processing}