用perl写了一个读excel文档的小程序

时间：2009-05-21 来源：machine

这几天应几个朋友的要求，帮他们写一个小程序，大概的意思是先从一个excel文件中分析出一些关键字，然后用这些关键字，从网上的一些系统中分析出和这些关键字相关的详细信息，然后再把这些详细信息规范成一定的格式后打印成一个word文档，我觉得用perl来干挺合适，因为要写给别人用（windows下），console的不合适，做个图形界面吧，用Win32::GUI、分析excel用Spreadsheet::ParseExcel、上网搜索信息当然用lwp、最后是生成word，这个我还没想好，是用现成的模块，还是自己分析（反正office 2007 的文档格式都变成xml了，我也研究过一些，自己分析也不难，呵呵）。干了一天，完成了分析excel文件部分，明天在接着干下一个吧

#! perl -w

#

#

use strict;
use Win32::GUI qw(WS_CLIPCHILDREN);
use Win32::GUI::Grid;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::FmtUnicode;
use Cwd;

my $parser = Spreadsheet::ParseExcel->new();
my $oFmtJ = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => 'CP936' );
my $Directory = cwd;
my $desk = Win32::GUI::GetDesktopWindow();
my $dw = Win32::GUI::Width($desk);
my $dh = Win32::GUI::Height($desk);
#隐藏dos窗口

my $DOS = Win32::GUI::GetPerlWindow();
Win32::GUI::Hide($DOS);
#主窗口

my $Window = new Win32::GUI::Window (
    -title => "分析excel文件",
    -pos => [100, 100],
    -size => [300, 600],
    -name => "Window",
    -addstyle => WS_CLIPCHILDREN,
) or die "new Window";
#打开文件按钮

$Window->AddButton(
    -pos => [ 5,5 ],
    -name => "openfile",
    -size => [ 280, 40 ],
    -title => "打开文件",
    );


# Grid窗口

my $Grid = new Win32::GUI::Grid (
    -parent => $Window,
    -name => "Grid",
    -pos => [0, 50],
    -hscroll => 'true',
    -vscroll => 'true',
) or die "new Grid";

# Init Grid

$Grid->SetEditable(2);
#$Grid->SetRows(50);

#$Grid->SetColumns(3);

$Grid->SetFixedRows(1);
#$Grid->SetFixedColumns(1);

#$Grid->AutoSize();

#在这里堵塞，等待消息队列

$Window->Show();
Win32::GUI::Dialog();
Win32::GUI::Show($DOS);
exit(0);

sub Window_Terminate {
  return -1;
}

sub Window_Resize {

  my ($width, $height) = ($Window->GetClientRect)[2..3];
  $Grid->Resize ($width, $height);
}
#点击图表

sub Grid_Click {
    my ($col, $row) = @_;
    my $text=$Grid->GetCellText($col,$row);
    print "$text\n";
  }
#打开文件

sub openfile_Click{
my $file = Win32::GUI::GetOpenFileName(
        -owner => $Window,
        -title => "Open a text file",
        -filter => [
                    'excel file (*.xls)' => '*.xls',
                    'All files' => '*.*',
                   ],
        -directory => $Directory ,
    );
  if(defined $file){
    my $workbook = $parser->Parse($file,$oFmtJ) or die "can't parse this file: $!";
    my $worksheet = ($workbook->worksheets())[0] or die "can't open sheets: $!";

        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();
        $Grid->SetRows($row_max+1);
        $Grid->SetColumns($col_max+1);
        for my $row ($row_min .. $row_max ) {
           for my $col ( $col_min .. $col_max ) {

                my $cell = $worksheet->get_cell( $row, $col );
                $cell ? ($Grid->SetCellText($row, $col, $cell->value())) :($Grid->SetCellText($row, $col, "null")) ;


           }
        }
        $Grid->AutoSize();
  }
}