|
<![CDATA[
# write2excel.pl # 07.09.05
use strict;
use Win32::OLE;
use Win32::OLE::Variant;
use Encode qw(from_to);
my ($ex,$sheet,$book,%t,$n,@fld);
# Read data (unicode)
open(IN,"map.txt") or die "Can't open the file map.txt.\n";
while(<IN>){
last if (/^END/);
if ( /^ITEM/ ) {
@fld = split;
push(@{ $t{NO} },$fld[1]);
push(@{ $t{NAME} },$fld[2]);
push(@{ $t{UNIT} },$fld[3]);
}
}
close(IN);
# EXCELの座標を確定
for $n ( 0 .. $#{ $t{NO} } ) {
$t{N} = $n + 4;
$t{XY} = 'A' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_NO} },$t{XY});
$t{XY} = 'C' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_NAME} },$t{XY});
$t{XY} = 'E' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_UNIT} },$t{XY});
}
# Get the object
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";
$ex->{DisplayAlerts} = 'False';
# open the sample file map.xls
$book = $ex->Workbooks->Open("C:\\TEMP\\map.xls");
# write to a particular cell
$sheet = $book->Worksheets('Sheet1');
# EXCELファイルにデータを書き込む
for $n ( 0 .. $#{ $t{NO} } ) {
$t{XY} = $t{XY_NO}[$n];
$t{NO1} = $t{NO}[$n];
$sheet->Range("$t{XY}")->{'Value'} = "$t{NO1}";
$t{XY} = $t{XY_NAME}[$n];
$t{NAME1} = $t{NAME}[$n];
from_to($t{NAME1}, "utf8", "Shift_JIS");
$sheet->Range("$t{XY}")->{'Value'} = $t{NAME1};
$t{XY} = $t{XY_UNIT}[$n];
$t{UNIT1} = $t{UNIT}[$n];
from_to($t{UNIT1}, "utf8", "Shift_JIS");
$sheet->Range("$t{XY}")->{'Value'} = $t{UNIT1};
}
# 中間ファイルをセーブ
# Save the excel file
$book->SaveAs("C:\\TEMP\\map1.xls");
undef $book;
undef $ex;
__END__
-------------------------------------------------------------------------------------
# write2excel.pl
# 07.08.23
use strict;
use Win32::OLE;
use File::Copy;
my ($ex,$sheet,$book,%t,$n,@fld);
# Read data
open(IN,"map.txt") or die "Can't open the file map.txt.\n";
while(<IN>){
last if (/^END/);
if ( /^ITEM/ ) {
@fld = split;
push(@{ $t{NO} },$fld[1]);
push(@{ $t{NAME} },$fld[2]);
push(@{ $t{UNIT} },$fld[3]);
}
}
close(IN);
# EXCELの座標を確定
for $n ( 0 .. $#{ $t{NO} } ) {
$t{N} = $n + 30;
$t{XY} = 'A' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_NO} },$t{XY});
$t{XY} = 'C' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_NAME} },$t{XY});
$t{XY} = 'E' . $t{N};
$t{XY} = $t{XY} . ':' . $t{XY};
push(@{ $t{XY_UNIT} },$t{XY});
}
# Get the object
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
$ex->{DisplayAlerts} = 'False';
# open the sample file map.xls
$book = $ex->Workbooks->Open('map.xls');
# write to a particular cell
$sheet = $book->Worksheets('Sheet1');
# EXCELファイルにデータを書き込む
for $n ( 0 .. $#{ $t{NO} } ) {
$t{XY} = $t{XY_NO}[$n];
$t{NO1} = $t{NO}[$n];
$sheet->Range("$t{XY}")->{'Value'} = "$t{NO1}";
$t{XY} = $t{XY_NAME}[$n];
$t{NAME1} = $t{NAME}[$n];
$sheet->Range("$t{XY}")->{'Value'} = "$t{NAME1}";
$t{XY} = $t{XY_UNIT}[$n];
$t{UNIT1} = $t{UNIT}[$n];
$sheet->Range("$t{XY}")->{'Value'} = "$t{UNIT1}";
}
# 中間ファイルをセーブ
# Save the excel file
$book->SaveAs("map1.xls");
undef $book;
undef $ex;
# map2.xlsが開いたとき,コピーが失敗
copy("map1.xls","map2.xls") or die "Copy failed:$!";
exit;
-------------------------------------------------------------------------------------
# open the sample file map.xls
$book = $ex->Workbooks->Open("C:\\www\\cgi-bin\\map.xls");
### ==>絶対Pathを書くこと!!!
copy("C:\\database\\map1.xls","C:\\database\\map2.xls") or die "Copy failed:$!";
### ==>絶対Pathを書くこと!!!
# Save the excel file
$book->SaveAs("C:\\database\\map1.xls");
### ==>絶対Pathを書くこと!!!
Can't call method "Worksheets" on an undefined value at write_excel_tmp.pl line
44.
OLE exception from "Microsoft Office Excel"
Win32::OLE(0.1701) error 0x800a03ec
システムエラーが起きました： Can't call method "Worksheets" on an undefined value at ./pro/msc023.pl line 53.
[Sat Sep 01 17:12:58 2007] [warn] pid file C:/www/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run?
[Sat Sep 01 17:12:58 2007] [notice] Apache/2.2.4 (Win32) configured -- resuming normal operations
[Sat Sep 01 17:12:58 2007] [notice] Server built: Jan 9 2007 23:17:20
[Sat Sep 01 17:12:58 2007] [notice] Parent: Created child process 3148
[Sat Sep 01 17:12:58 2007] [notice] Child 3148: Child process is running
[Sat Sep 01 17:12:58 2007] [notice] Child 3148: Acquired the start mutex.
[Sat Sep 01 17:12:58 2007] [notice] Child 3148: Starting 250 worker threads.
[Sat Sep 01 17:12:58 2007] [notice] Child 3148: Starting thread to listen on port 80.
[Sat Aug 04 16:22:31 2007] [error] [client 127.0.0.1] Premature end of script headers: view.pl
[Sat Aug 04 16:03:21 2007] [error] [client 127.0.0.1] Premature end of script headers: view.pl
[Sat Aug 04 16:03:21 2007] [error] [client 127.0.0.1] Error executing run mode 'mode3': DBI selectrow_array: invalid number of parameters: handle + 0
[Sat Aug 04 16:03:21 2007] [error] [client 127.0.0.1] Usage: $h->selectrow_array($statement [, \\%attr [, @bind_params ] ]) at WidgetView.pm line 116.
[Sat Aug 04 16:03:21 2007] [error] [client 127.0.0.1] at C:/www/cgi-bin/view.pl line 4
[Sat Aug 04 13:53:43 2007] [error] [client 127.0.0.1] Error executing run mode 'mode3': DBI selectrow_array: invalid number of parameters: handle + 0
[Sat Aug 04 13:53:43 2007] [error] [client 127.0.0.1] Usage: $h->selectrow_array($statement [, \\%attr [, @bind_params ] ]) at WidgetView.pm line 115.
[Sat Aug 04 13:53:43 2007] [error] [client 127.0.0.1] at C:/www/cgi-bin/view.pl line 4
2038 ==> It is dealing with the file ENQ 5061504 TOSHIN MARU DALIAN.xls
Win32::OLE(0.1701) error 0x80020005: "種類が一致しません。" at excel2txt.pl line
122
==>間違った計算式の入力？次式にエラー
==>$scalar = Variant(VT_DATE, $scalar);
Win32::OLE::Variant=SCALAR(0x3b23e2c)
==>2月5日
Excelの日付を表現することができない
http://tuka.s12.xrea.com/index.xcg?p=Perl
-------------------------------------------------------------------------------------
use strict;
use Win32::OLE;
my ($ex,$sheet,$book,%t,$n,$n1,@fld,$ref);
# Read data
open(IN,"map1.txt") or die "Can't open the file map1.txt.\n";
@{ $t{list} } = qw/REF DATE NAME THANK LIST1 LIST2 LIST3 LIST4 LIST5 LIST6 LIST7/;
@{ $t{X0} } = qw/B B B B B E B B B A F/;
@{ $t{Y0} } = qw/4 5 9 13 17 17 23 27 31 32 36/;
@{ $t{Direction} } = qw/Y Y Y Y Y Y Y Y X X Y/;
while(<IN>){
last if (/^END/);
chomp;
@fld = split(/==>/);
for $n ( 0 .. $#{ $t{list} } ) {
$t{list1} = $t{list}[$n];
if ( /^$t{list1}/ ) {
push(@{ $$ref{$t{list1}} },$fld[1]);
next;
}
}
}
# EXCELの座標を確定
for $n ( 0 .. $#{ $t{list} } ) {
$t{x1} = $t{X0}[$n];
$t{y1} = $t{Y0}[$n];
$t{XY1} = $t{x1} . $t{y1};
$t{list1} = $t{list}[$n];
$t{leng1} = $#{ $$ref{$t{list1}} };
$t{D1} = $t{Direction}[$n];
if ( $t{D1} eq 'Y' ) {
$t{y1} = $t{y1} + $t{leng1};
$t{XY2} = $t{x1} . $t{y1};
} else {
for $n1 ( 1 .. $t{leng1} ) {
++($t{x1});
}
$t{XY2} = $t{x1} . $t{y1};
}
$t{XY} = $t{XY1} . ':' . $t{XY2};
push(@{ $t{XYs} },$t{XY});
}
# Get the object
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";
$ex->{DisplayAlerts} = 'False';
# open the sample file map.xls
$book = $ex->Workbooks->Open("C:\\database\\samples\\sample_ENQ.xls");
# write to a particular cell
$sheet = $book->Worksheets("Sheet1");
for $n ( 0 .. $#{ $t{list} } ) {
$t{XY} = $t{XYs}[$n];
$t{list1} = $t{list}[$n];
$t{D1} = $t{Direction}[$n];
if ( $t{D1} eq 'X' ) {
$sheet->Range("$t{XY}")->{'Value'} = \@{ $$ref{$t{list1}} };
} else {
$t{x1} = $t{X0}[$n];
$t{y1} = $t{Y0}[$n];
for $n1 ( 0 .. $#{ $$ref{$t{list1}} } ) {
$t{y2} = $t{y1} + $n1;
$t{XY} = $t{x1} . $t{y2};
$sheet->Range("$t{XY}")->{'Value'} = $$ref{$t{list1}}[$n1];
}
}
}
# Save the excel file
$book->SaveAs("C:\\database\\output\\ENQ.xls");
undef $book;
undef $ex;
__END__
]]>
|