perl 导出导入 excel

记下来自己的一个样例： 在 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',];

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;
}