WriteExcel module, parsing formula error

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

Threaded View

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 -C=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.

Re: WriteExcel module, parsing formula error

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 =~ /^=/);

Quoted text here. Click to load it

Re: WriteExcel module, parsing formula error

In article

Quoted text here. Click to load it

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:
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

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

Site Timeline