XLS

最新推荐文章于 2025-12-04 13:50:07 发布

原创最新推荐文章于 2025-12-04 13:50:07 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #compilation #file #table #c

本文介绍了一个使用Perl脚本从数据库中选择记录并将其输出到Excel文件的方法。该脚本利用了Win32::OLE模块来操作Excel，并通过动态计算列名的方式支持不同数量的字段输出。

#!/usr/bin/perl -w
#function:            select records from table and print to Excel file
#author:              ATGC
#version:             1.0
#date of compilation September 6,2004

use strict;
use Win32::OLE;
my $excel_file = 'output.xls';
my($dbh,$sth,$row,$col,@field,$ele,$c_times,$residual,$cols,$cell_end);
unlink $excel_file if (-e $excel_file);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->add;
my $Sheet = $Book->Worksheets(1);
my @array_cols=("","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
$row=1;
$cols=0;
if (@field = $sth->fetchrow)
{
$cols=scalar(@field);
$c_times=int($cols/26);
$residual=$cols%26;
if ($cols<27)
{
  $cell_end = $array_cols[$cols];
}
else
{
  if ($residual == 0)
  {
   $cell_end = $array_cols[$c_times-1]."Z";
  }
  else
  {
   $cell_end = $array_cols[$c_times].$array_cols[$residual];
  }
}
$Sheet->Range("A1:$cell_end$row")->{Value} = [@field];
while(@field = $sth->fetchrow)
{
  $row++;
  $Sheet->Range("A$row:$cell_end$row")->{Value} = [@field];
}
$Book->SaveAs($excel_file);
}
undef($Sheet);
undef($Book);
undef($Excel);