Perl writes to Excel

This script features:

Fetch data from the database and write to Excel file; (according to the official website, this module has been discarded)

Use Spreadsheet::P arseexcel and Spreadsheet::WriteExcel best. The former is used for reading Excel files, the latter for writing Excel files. Spreadsheet::P arseexcel can only read 95-2003-formatted Excel documents, and for Office Excel, install spreadsheet::xlsx .

#!/usr/bin/env perluse strict;use dbi;use encode;use ole::storage_lite;use  Spreadsheet::writeexcel::big, #数据库信息my   $host  = {    user =>  ' Root ',    pass =>  ' 123456 ',    host =>  ' 127.0.0.1 ',    database =>  ' aries_host_info ',     port  => 3306,    table=> ' host ',}; #输出文件格式my   ($sec, $min, $hour, $mday, $mon, $ Year)  =  (localtime) [0..5];($sec, $min, $hour, $mday, $mon, $year)  =  (sprintf ("%02d",  $sec) , sprintf ("%02d",  $min), sprintf ("%02d",  $hour), sprintf ("%02d",  $mday), sprintf ("%02d",  $mon  + 1), $year  + 1900);my  $date = "$year $mon$mday$hour$min"; #输出excel名字    mail entity attachments my   $excel _file= "${date}.xls";my  @cols = (' a:a ', ' b:b ', ' c:c ', ' d:d ', ' e:e ', ' f:f ', ' g:g ', ' h:h ', ' i:i ', ' j:j ', ' K: K ', ' l:l ', ' m:m ', ' n ' All ', ' o:o ',' P:p ', ' q:q ', ' r:r ', ' s:s ', ' t:t ', ' u:u ', ' v:v ', ' w:w ', ' x:x ', ' y:y ', ' z:z ', ' aa:a ', ' bb:b ', ' cc:c ', ' dd:d ', ' ee:e ', ' ff:f ', ' Gg:g ', ' hh:h ', ' ii:i ', ' jj:j ', ' kk:k ', ' ll:l ', ' mm:m ', ' nn:n ', ' oo:o ', ' pp:p ', ' qq:q ', ' rr:r ', ' ss:s ', ' tt:t ', ' uu:u ', ' vv:v ' , ' ww:w ', ' xx:x ', ' yy:y ', ' zz:z '); #连接数据库my   $DBH =dbi->connect ("Dbi:mysql: $host->{database};host= $host- >{host};p ort= $host->{port} ", $host->{user}, $host->{pass},{raiseerror=>1}); #设置字符集 $dbh->do   ("set character_set_client =  ' UTF8 '"); $dbh->do  ("set character_set_ connection =  ' UTF8 '); $DBH->do  ("set character_set_results =  ' UTF8 '"); #sql   Statement my  $sql =qq (select * from  $host->{table};);my  $sth = $dbh->prepare ($sql)  or die  ' unable to perpare our query: '. $dbh->errstr. ' \ n ";my  $results = $sth->execute ()  or die  ' Unable to execute our query : '. $dbh->errstr. ' \ n "; #打印出sql的select行数到桌面, if ($results  == 0) {    print  "query result  : n/a";} else{#print   "$sql  cmd find  $results  rows.\n";    print  " Query Result: $results   line. \ n ";     } #从数据库查询结果的列名my   @cols_name  = @{$sth->{' name '}};if  ($ #cols_name  > $ #cols) {print  "result table fields overflow! ( max num. >  ". ($ #cols + 1). ") \ n "; exit;} print  "Writing excel...\n"; #创建excel文件my   $excel  = spreadsheet::writeexcel::big->new ($excel _ File)  | |  die  "excel  file creation failed:  $!"; #创建  excel sheetmy  $sheet  =  $excel->add_worksheet (' Anbound '); #excel文件格式my  $ title_style =  $excel->add_format (); $title _style->set_size (one); $title _style->set_bold (); $ Title_style->set_align (' center ');my  $sheet _col = 0;    #列信息 # Outputs the result to excel  File for  (my  $i =0;  $i <scalar @cols_name ; $i + +)     #列信息 {$sheet->set_column ($cols [$i], length ($cols _name[$i]) +20) Sheet->write ($sheet _col, $i, $cols _name[$i], $title _style);} #冻结表首行 $sheet->freeze_panes (1, 0);while  (my  @row  =  $sth->fetchrow_array) {          $sheet _col++;        for   (my  $i =0;  $i < scalar  @cols_name  ; $i + +)          {                 next if  ($row [$i] eq  ');               #无信息, do not write                  encode::_utf8_on ($row [$i]);          #把 $row [i] Treating            &nbsp as a utf8.;     $sheet->write ($sheet _col,  $i, $row [$i]);          }}print  "Excel write complete!\n";

This article is from the "Big Wind" blog, please be sure to keep this source http://lansgg.blog.51cto.com/5675165/1648322

Perl writes to Excel