
平台论坛博客文库

› 论坛 › 程序设计 › Perl › 求助：Win32::OLE(0.1709) error 0x80010108

求助：Win32::OLE(0.1709) error 0x80010108 [复制链接]

leo870625

白手起家

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2011-01-24 11:06 |只看该作者 |倒序浏览

本帖最后由 leo870625 于 2011-01-24 11:10 编辑

#!/usr/bin/perl
use strict; # Declare using Perl strict syntax
use DBI; # If you are using other Perl's package, declare here
use DBD::Teradata;
use Data::Dumper;
use Win32::OLE;# qw(in with);
#use Win32::OLE::Const 'Microsoft Excel';
#use Win32::OLE::Variant;
$Win32::OLE::Warn = 3;
my $AUTO_HOME = $ENV{"AUTO_HOME"};
my $AUTO_ETC = "$AUTO_HOME/etc";
my $AUTO_EXCEL = "D:\\perl_script";
my $ETLDB = "PTEMP";#$ENV{"AUTO_DB"}; #The repository database of ETL.
my $DSOURCE = $ENV{"AUTO_DSN"};
my $DIRDELI;
my $LOGON_FILE;
my $LOGON_STR;
my $INS_SQL = "";
my $DEL_SQL = "";
my $os = $^O;
$os =~ tr [A-Z][a-z];
if ( $os eq "mswin32" ) {
$DIRDELI = "\\";
unshift(@INC, "$AUTO_HOME\\bin");
require etl_nt;
} else {
$DIRDELI = "/";
unshift(@INC, "$AUTO_HOME/bin");
require etl_unix;
}
sub trimstr {
my @out = @_;
for (@out) {
s/^\s+//; # trim left
s/\s+$//; # trim right
}
return @out == 1
? $out[0] # only one to return
: @out; # or many
}
#处理从Excel中读取的单元格的信息，截取单元格开始和结束的空格
sub transStr{
my ($str)=@_;
chomp $str;
$str = trimstr($str);
return $str;
}
if ($ETLDB eq '') {
$ETLDB = 'PETL';
}
sub DBconnect() {
my ($logoncmd, $userpw) = split(' ',$LOGON_STR);
chop($userpw);
my $tdpid;
($tdpid, $userpw) = split('/',$userpw);
if ($userpw eq "") {
$userpw=$tdpid;
}
my ($USER, $PASSWD) = split(',' , $userpw);
my $dbh = DBI->connect("dbi:ODBC:${DSOURCE}", $USER, $PASSWD);
unless ( defined($dbh) ) {
# sleep(60);
$dbh = DBI->connect("dbi:ODBC:${DSOURCE}", $USER, $PASSWD);
die "cann't connect database $!" unless(defined($dbh));
}
return $dbh;
}
sub del_ins_Data {
my ($sqlText) = @_;
my $dbh = DBconnect();
my $sth = $dbh->prepare($sqlText);
my $ret = $sth->execute();
$sth->finish();
$dbh->disconnect();
}
#遍历目录，找到该目录下的所有符合要求的excel文件，对每一个文件调用ReadExcel函数，
#符合要求的Excel文件是指：文件名包含"供数接口FTP配置信息"的文件
sub readExcel {
my $CurrentDir = ${AUTO_EXCEL};
$CurrentDir =~ s/\//\\/g;
my ($Excel,$Book,$Sheet);
$Excel = (Win32::OLE -> GetActiveObject('Excel.Application')
|| Win32::OLE -> new('Excel.Application', 'Quit')) or die "errors\n";
my @dirs = ($CurrentDir);
my($DIR, $FILE);
while($DIR = pop(@dirs)){
local *DH;
opendir(DH,$DIR)||die "Can not open the $DIR dir.";
foreach(readdir DH){
if($_ eq '.' || $_ eq '..'){
next;
}
$FILE = $_;
if(($FILE =~ /.xls$/) and ($FILE =~ /供数接口FTP配置信息/)){
$Book = $Excel->Workbooks->Open("${CurrentDir}${DIRDELI}${FILE}") or die $!;
my $sheetCnt = $Book->Worksheets->Count();
my $pos = 1;
my $sheetName;
#Find all sheets in the current excel.
while( $pos <= $sheetCnt ){
$Sheet = $Book->Worksheets($pos);
$Sheet -> Activate();
$sheetName = trimstr($Sheet -> {name});
chomp $sheetName;
if( $sheetName eq '供数接口FTP配置信息' ) {
my $i = 2;
my $j = 1;
my ($ETL_SYSTEM,$ETL_JOB,$REMOTE_HOST,$LOGON_FILE,$REMOTE_PATH,$DESCRIPTION_T,$ENABLE);
$DEL_SQL = "DELETE FROM ${ETLDB}.FTP_TABLE_INFO;";
del_ins_Data(${DEL_SQL});
while (1) {
if( trimstr($Sheet->Cells($i,1)->{'Value'}) eq '' ){
last;
}
$ETL_SYSTEM = $Sheet->Cells($i,$j)->{'Value'};
$ETL_JOB = $Sheet->Cells($i,$j+1)->{'Value'};
$REMOTE_HOST = $Sheet->Cells($i,$j+2)->{'Value'};
$LOGON_FILE = $Sheet->Cells($i,$j+3)->{'Value'};
$REMOTE_PATH = $Sheet->Cells($i,$j+4)->{'Value'};
$DESCRIPTION_T = $Sheet->Cells($i,$j+5)->{'Value'};
$ENABLE = $Sheet->Cells($i,$j+6)->{'Value'};
$ETL_SYSTEM = transStr($ETL_SYSTEM);
$ETL_JOB = transStr($ETL_JOB);
$REMOTE_HOST = transStr($REMOTE_HOST);
$LOGON_FILE = transStr($LOGON_FILE);
$REMOTE_PATH = transStr($REMOTE_PATH);
$DESCRIPTION_T = transStr($DESCRIPTION_T);
$ENABLE = transStr($ENABLE);
$INS_SQL = "INSERT INTO ${ETLDB}.FTP_TABLE_INFO(ETL_SYSTEM,ETL_JOB,REMOTE_HOST,LOGON_FILE,REMOTE_PATH,DESCRIPTION_T,ENABLE) VALUES('$ETL_SYSTEM','$ETL_JOB','$REMOTE_HOST','$LOGON_FILE','$REMOTE_PATH','$DESCRIPTION_T','$ENABLE');";
del_ins_Data(${INS_SQL});
$i ++;
}
print "Inserted successfully!"
}
$Book->Close();
$Excel->Quit();
}
}
}
closedir(DH);
}
}
sub main {
readExcel();
}
#####################################################################################################
# ------------ program section ------------
$LOGON_FILE = "${AUTO_HOME}/etc/LOGON_ETL";
open(LOGONFILE_H, "${LOGON_FILE}");
$LOGON_STR = <LOGONFILE_H>;
close(LOGONFILE_H);
# Get the decoded logon string
$LOGON_STR = `${AUTO_HOME}/bin/IceCode.exe "$LOGON_STR"`;
open(STDERR, ">&STDOUT");
main();