Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > WriteExcel module, parsing formula error

Reply
Thread Tools

WriteExcel module, parsing formula error

 
 
dn.perl@gmail.com
Guest
Posts: n/a
 
      07-07-2009

I am reading a set of strings, and then writing them out to an Excel
sheet using WriteExcel module. Some strings seem to have the format of
a formula, and the module is trying to parse them and failing. I would
like to write the strings as they are, but can live with it if I just
force such strings to be blank and then print them.

if( $subject is problematic ) # $subject =~ m/^=.*=$/ do I
need to trap this condition?
then $subjct = "" ; ==> this will also do.

There are two values for which the statement is failing.

my $normal_format = $workbook->add_format();

A) $subject = "==Contract==" ;
$$rh_worksheet->write( $current_row , $current_col++ ,
$subject , $normal_format ) ;

B) $subject = "=?iso-8859-1?Q?Entrada y registro - =BFC=F3mo cambio mi
contrase=F1a??=" ;
$$rh_worksheet->write( $current_row , $current_col++ ,
$subject , $normal_format ) ;

Even if I print a blank for these problematic subject-strings, my
problem is solved. Being able to print them as they are would be
ideal. When does the module consider a string to be a formula and try
to parse it? If it begins with "=" sign? Or when it begin and ends
with "=" character? Or something else?


Thanks in advance.

 
Reply With Quote
 
 
 
 
Thrill5
Guest
Posts: n/a
 
      07-07-2009
The module is only a wrapper to access the Excel OLE routines and Excel
treats any cell content that begins with an equal sign as a formula. You
could try quoting the string before adding it to force it to be interpreted
as a string instead of a formula.

$subject = qq("$subject") if ($subject =~ /^=/);

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I am reading a set of strings, and then writing them out to an Excel
> sheet using WriteExcel module. Some strings seem to have the format of
> a formula, and the module is trying to parse them and failing. I would
> like to write the strings as they are, but can live with it if I just
> force such strings to be blank and then print them.
>
> if( $subject is problematic ) # $subject =~ m/^=.*=$/ do I
> need to trap this condition?
> then $subjct = "" ; ==> this will also do.
>
> There are two values for which the statement is failing.
>
> my $normal_format = $workbook->add_format();
>
> A) $subject = "==Contract==" ;
> $$rh_worksheet->write( $current_row , $current_col++ ,
> $subject , $normal_format ) ;
>
> B) $subject = "=?iso-8859-1?Q?Entrada y registro - =BFC=F3mo cambio mi
> contrase=F1a??=" ;
> $$rh_worksheet->write( $current_row , $current_col++ ,
> $subject , $normal_format ) ;
>
> Even if I print a blank for these problematic subject-strings, my
> problem is solved. Being able to print them as they are would be
> ideal. When does the module consider a string to be a formula and try
> to parse it? If it begins with "=" sign? Or when it begin and ends
> with "=" character? Or something else?
>
>
> Thanks in advance.
>



 
Reply With Quote
 
 
 
 
Jim Gibson
Guest
Posts: n/a
 
      07-08-2009
In article
<(E-Mail Removed)>,
<"(E-Mail Removed)"> wrote:

> I am reading a set of strings, and then writing them out to an Excel
> sheet using WriteExcel module. Some strings seem to have the format of
> a formula, and the module is trying to parse them and failing. I would
> like to write the strings as they are, but can live with it if I just
> force such strings to be blank and then print them.


Are you talking about the Spreadsheet::WriteExcel module?

If so, then according to the documentation for the write module:

"Excel makes a distinction between data types such as strings, numbers,
blanks, formulas and hyperlinks. To simplify the process of writing
data the write() method acts as a general alias for several more
specific methods:
write_string()
write_number()
write_blank()
write_formula()
write_url()
write_row()
write_col()
The general rule is that if the data looks like a something then a
something is written. Here are some examples in both row-column and A1
notation:"

The documentation then shows that if you use the write method to write
a string that begins with an equal sign, it will use the write_formula
method to write it to the workbook, with these examples:

$worksheet->write('A12', '=A3 + 3*A4' ); # write_formula()
$worksheet->write('A13', '=SIN(PI()/4)' ); # write_formula()

So, you might try forgoing use of the general write method and use the
write_string method instead.

--
Jim Gibson
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
parsing an Excel formula with the re module vsoler Python 20 01-15-2010 07:23 AM
SpreadSHeet::WriteExcel Error Rajpreet Perl Misc 2 07-22-2009 02:46 PM
Spreadsheet::WriteExcel, Excel formula won't calculate Sven Jungnickel Perl Misc 3 07-28-2004 11:18 PM
Spreadsheet::WriteExcel Fabio Perl 2 03-04-2004 08:37 AM
Bad excel docs with Spreadsheet::WriteExcel::Simple Erica Perl Misc 2 09-05-2003 08:15 AM



Advertisments