WriteExcel module, parsing formula error

Discussion in 'Perl Misc' started by dn.perl@gmail.com, Jul 7, 2009.

  1. Guest

    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.
    , Jul 7, 2009
    #1
    1. Advertising

  2. Thrill5 Guest

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

    <> wrote in message
    news:...
    >
    > 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.
    >
    Thrill5, Jul 7, 2009
    #2
    1. Advertising

  3. Jim Gibson Guest

    In article
    <>,
    <""> 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
    Jim Gibson, Jul 8, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. vsoler
    Replies:
    20
    Views:
    1,539
    John Machin
    Jan 15, 2010
  2. Sven Jungnickel
    Replies:
    3
    Views:
    197
    John McNamara
    Jul 29, 2004
  3. Replies:
    2
    Views:
    133
  4. Replies:
    5
    Views:
    316
    smallpond
    Mar 10, 2009
  5. Rajpreet

    SpreadSHeet::WriteExcel Error

    Rajpreet, Jul 21, 2009, in forum: Perl Misc
    Replies:
    2
    Views:
    100
    Justin C
    Jul 22, 2009
Loading...

Share This Page