Dear monks,

I am writing an application that reads an excel file and uses it as a template, by modifying it and saving it under a different name. I am using Spreadsheet::ParseExcel::SaveParser but merged cells are not treated correctly. Although the resulting file contains all the data I wrote into it, merged areas are... not merged.

I looked at the source of this module and found a solution that works for me, but it will normally not work if you are using unicode strings.

The problem is that S::PE::SaveParser uses the deprecated set_merge method instead of the newer merge_range method from Spreadsheet::WriteExcel. SaveParser iterates over all the possible cells in the source file, and it applies set_merge on them at the end of its SaveAs method.

So, I have added the following code to apply merge_range on the destination file, at a place where the module iterates over worksheets in the SaveAs method:

# Merged areas
foreach my $area ( @{ $oWkS->{MergedArea} } ) {

    my $cell = $oWkS->Cell($area->[0], $area->[1]);
    my $oFmtN = $oWrEx->addformat();
    $oFmtN->copy($hFmt{$cell->{FormatNo}});
    $oWrS->merge_range( @$area, $cell->{Val}, $oFmtN, 0);
}

[download]

The thing here is the last '0' in the parameter list for merge_range: If it were a true value, merge_range will treat the value as unicode. Otherwise it won't, according to the docs. So, this '0' shouldn't be hardwired.

Finally, my question is this:

How do you detect a unicode string? I would like to learn of a function that takes in a string and returns 0 or undef if the string isn't unicode and 1 if it is.

That way I can turn my personal fix into a more general solution and upload it to the bug tracking tool at CPAN for the author to consider.

Regards,

Julio

In reply to Spreadsheet::ParseExcel::SaveParser and merged cells by jfraire

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.