Convert excel formula to perl

[Duke of Hazard]

Is there an easy way to convert a formula like this:

C1+B1 * (E23/D2)

into something perl can evaluate like this:

$C1 + $B1 * ($E23/$D2);

My brute force method would be test each individual character in the
excel formula and then write subroutines for addition, and
multiplication. Is there something easier?

I would like the user to be able to type in equation like excel and
have perl do the math on them.

Thanks!

 

[Andrew Bryson]

Is there an easy way to convert a formula like this:

C1+B1 * (E23/D2)

into something perl can evaluate like this:

$C1 + $B1 * ($E23/$D2);
Yes.

My brute force method would be test each individual character in the
excel formula and then write subroutines for addition, and
multiplication. Is there something easier?

You could just use the built in perl functions for arithmetic operations.

I would like the user to be able to type in equation like excel and
have perl do the math on them.

You would need to tell perl how to interpret excel files. There are modules
to do this .

Andrew

 

[A. Sinan Unur]

(e-mail address removed) (Duke of Hazard) wrote in

Is there an easy way to convert a formula like this:

C1+B1 * (E23/D2)

into something perl can evaluate like this:

$C1 + $B1 * ($E23/$D2);

My brute force method would be test each individual character in the
excel formula and then write subroutines for addition, and
multiplication. Is there something easier?

I would like the user to be able to type in equation like excel and
have perl do the math on them.

Well, where do C1, B1, E23 and D2 come from?

My reading of your problem description indicates that you are not trying to
read Excel files. Just 'cause I had a tough day and need to relax, I am
going to try to write something which I think corresponds to what you are
after. (Goes away for a while ...) OK ... The following would be one way, I
guess. (I fully expect and hope to have my errors corrected. After all, I
hadn't tried writing a calculator since high school.)

#! perl

$| = 1;

use strict;
use warnings;

use Regexp::Common 'number';

my %vars;

for(
print '> ';
my $input = <STDIN>;
print '> ')
{
$input =~ s/^\s*//;
$input =~ s/\s*$//;

next if $input eq '';
last if $input =~ /^quit/i;
if($input =~ /^show/i){
for my $var (sort {$a cmp $b} keys %vars) {
print "\t$var = $vars{$var}\n";
}
next;
}

if($input
=~ /^([A-Za-z]{1,2}[0-9]{1,5})\s*=\s*($RE{num}{real})$/) {
$vars{$1} = $2;
next;
}
my $var;
($var, $input) = ($input =~ /^(\S+)\s*=\s*(\S+)$/);
$input =~ s/[\$\%\@\{\}]//g;

my $value = do {
no warnings 'uninitialized';
$input =~ s{([A-Za-z]{1,2}[0-9]{1,5})}/$vars{$1}/ge;
eval $input;
};
print "Error in formula\n" and next if $@;

$vars{$var} = $value if defined $var;
print "$value\n";
}

__END__

D:\Home> perl ef.pl

a1=5
a2 = (22/33)*a1-rand(10) -3.50077311197917
show

a1 = 5
a2 = -3.50077311197917

quit

D:\Home>

Sinan.

 

[A. Sinan Unur]

Just 'cause I had a tough day and need to relax, I am going to
try to write something which I think corresponds to what you are
after.

OK, it definitely is a bad sign when I start having online conversations by
myself, but the following is a little better (had food in the mean time,
brain works slightly better now). One should probably run this code in
Safe:

http://search.cpan.org/~nwclark/perl-5.8.5/ext/Opcode/Safe.pm

#! perl

$| = 1;

use strict;
use warnings;

my %vars;

for(
print '> ';
my $input = <STDIN>;
print '> ')
{
$input =~ s/^\s*//;
$input =~ s/\s*$//;
$input =~ s/[\{\$\%\@\}]//g;

next if $input eq '';
last if $input =~ /^quit/i;

if($input =~ /^show/i) {
show(\%vars);
} elsif(defined (my $value = eval_formula($input, \%vars))) {
print "$value\n";
} else {
print "Error in formula\n";
}
}

sub show {
my ($vars) = @_;
for my $var (sort {$a cmp $b} keys %$vars) {
print "\t$var = $vars->{$var}\n";
}
}


sub eval_formula {
no warnings 'uninitialized';
my ($input, $var) = reverse split '=', $_[0];
if(defined $var) {
($var) = ($var =~/([A-Za-z]{1,2}[0-9]{1,5})/);
}

my $value = do {
$input =~ s{([A-Za-z]{1,2}[0-9]{1,5})}/$_[1]->{$1}/ge;
eval $input;
};
return unless defined $value;

$_[1]->{$var} = $value if defined $var;
return $value;
};

__END__




Sinan.