in reply to Can I Create/Access an excel file ?
@rem = '--*-Perl-*-- @echo off if exist C:\Perl\bin\perl.exe goto ok if exist D:\Perl\bin\perl.exe goto ok if exist E:\Perl\bin\perl.exe goto ok echo Perl not properly installed goto endofperl :ok perl "%~dpnx0" %* goto endofperl @rem '; #!perl #line 14 # use strict; # use warnings; use Win32::OLE; # use existing instance if Excel is already running eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel"; } my $file = shift; my $book = $ex->Workbooks->Open($file) or die "Can't open $file"; my $source = $book->Worksheets(1) or die "Can't access first sheet"; my $target = $book->Worksheets(4) or die "Can't access fourth sheet"; my $startrow = 3; my $stoprow = 199; my $array = $source->Range("A$startrow:Q$stoprow")->{Value}; my $writerow = $startrow; for (@$array) { my @vals = (); for (@$_) { push @vals,$_ } next unless defined($vals[0]); my @bitlist = (); for (my $pos = 0; $pos < scalar @vals; ++$pos) { push @bitlist,$pos if $vals[$pos] eq '>=0'; } my $max_val = 2 ** (scalar @bitlist); for my $val ( 0..$max_val-1 ) { my @newvals = @vals; for my $bit (@bitlist) { $newvals[$bit] = ($val/2 == $val>>1) ? 1 : 0; $val >>= 1; } for ( @newvals ) { s/>0/1/; # >0 becomes 1 s/'?([0-9]+)/'$1/; # Make sure ' is present for numbers } $target->Range("A$writerow:Q$writerow")->{Value} = [@newvals]; for (@newvals) { s/^'//; } my @single = ($newvals[3].$newvals[4].$newvals[5].$newvals[6]. $newvals[7].$newvals[8].$newvals[9].$newvals[10]. $newvals[11].$newvals[12].$newvals[13].$newvals[14]) +; $target->Range("C$writerow:C$writerow")->{Value} = [@single]; ++$writerow; print join("\t",@newvals)."\n"; } } # save and exit $book->Save(); $book->Close(); undef $book; undef $ex; __END__ :endofperl
|
|---|