perl 导出导入 excel

hyzqb

　　

记下来自己的一个样例： 在 2003中测试过了OK

use utf8;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const "Microsoft Excel";
use Encode;
my $Encode = "GBK";
my $Columns = [
{
col=>'A',title=>'name',value_field=>'a'
},
{
col=>'B',title=>'age',value_field=>'b',
},
{
col=>'C',title=>'sex',value_field=>'c',
},
{
col=>'D',title=>'address',value_field=>'d',
},
{
col=>'E',title=>'phone',value_field=>'e',
},
];
my $data = [
{a=>'菠菜',b=>'b',c=>'c',d=>'d',e=>'e'},
{a=>'1',b=>'2',c=>'3',d=>'4',e=>'5'},
{a=>'a',b=>'b',c=>'c',d=>'d',e=>'e'},
];
my $excel =  Win32::OLE->new('Excel.Application');
$excel->{Visible} = 1;
$excel->{DisplayAlerts} = 0;
my $wb = $excel->Workbooks->Add();
$wb->{Visible} = 1;
my $ws = $wb->Sheets(1);
$ws->Select;
#绘制标题
my $range = $ws->Range($Columns->[0]->{col}.'1'.':'.$Columns->[-1]->{col}.'1');
$range->{HorizontalAlignment} = xlCenter;
$range->{Font}->{Bold} = True;
$range->{Font}->{ColorIndex} = 2;
$range->{Interior}->{ColorIndex} = 10;
foreach my $column (@$Columns){
$ws->Range($column->{col}.1)->{'Value'} = encode($Encode,$column->{title});
}
#填充数据
my $row = 2;
foreach my $item (@$data){
foreach my $column (@$Columns){
$ws->Range($column->{col}.$row)->{'Value'} = encode($Encode,$item->{$column->{value_field}});
}
$row++;
}
#~~~~~~~
my $fill_range = $ws->Range('A1:E5');
$fill_range->{Columns}->AutoFit();
$fill_range->{Rows}->AutoFit();
foreach my $edge (xlInsideHorizontal, xlInsideVertical, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft) {
with ($fill_range->Borders($edge),
LineStyle =>xlContinuous,
Weight => xlThin ,
ColorIndex => 1);
}
2012-07霸气外露的更新：$ws->Range('A1:'.["A".."ZZ"]->[$ws->{UsedRange}->{Columns}->{Count}-1].$ws->{UsedRange}->{Rows}->{Count}); NB
　　

perl 导入Excel 包括中文也可以

#!/usr/bin/perl -w
use strict;
use utf8;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const "Microsoft Excel";
use Encode;
use Data::Dump 'dump';
my $Encode = "GBK";
my $CFG = {
start_row=>2,
col_map_key=>{
A=>'name',
B=>'age',
},
};
my $excel =  Win32::OLE->new('Excel.Application');
$excel->{Visible} = 1;
$excel->{DisplayAlerts} = 0;
my $wb = $excel->Workbooks->Open("D:/aaa.xls") or die "open excel failed $!";
my $ws = $wb->Sheets(1);
$ws->Select;
my $excel_data;
foreach my $row ($CFG->{start_row}..$ws->{UsedRange}->{Rows}->{Count}){
my $line;
foreach my $col (keys %{$CFG->{col_map_key}}){
$line->{$CFG->{col_map_key}->{$col}} = encode("utf8",decode($Encode,$ws->Range($col.$row)->{'Value'}));
}
push @{$excel_data},$line;
}