jfraire has asked for the wisdom of the Perl Monks concerning the following question:

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); }

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

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel::SaveParser and merged cells
by Anonymous Monk on Feb 07, 2008 at 08:10 UTC

      Thanks. In perluniintro I learned about utf8::is_utf8, which does precisely what I wanted.

      I have filed the bug and submitted the patch.

      Regards,

      Julio

        Hi Julio, I also want to use Spreadsheet::ParseExcel::SaveParser to merged cells, can you share the module you have improved. thanks