Here's a Batch/Perl polyglot that reads the first sheet of a spreadsheet, expands the data, and writes it to the fourth sheet:
@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
|