# Convert excel formula to perl

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
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!

## Re: Convert excel formula to perl

squash@peoriadesignweb.com (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\n";
}
next;
}

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

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

\$vars = \$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.

## Re: Convert excel formula to perl

> 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->\n";
}
}

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

my \$value = do {
\$input =~ s[0-9])}/\$_[1]->/ge;
eval \$input;
};
return unless defined \$value;

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

__END__

Sinan.

## Re: Convert excel formula to perl

> 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