Thanks for the suggestion. There is no difference between the two. Both return > which leads me to believe that's how MSFT is storing it in the xlsx spreadsheets.

Thanks for the references. I agree, xlsx format is storing the XML data.

I'm a bit of a newbie to perl, so rather than try to figure out how to use XLM::Twig, I'm just going to do a couple regexp substitution strings and call it a day. Thanks you all for the quick replies.

How are you looking at the value?

What does substr($value, 0,1) look like when you display it using the same method? '&' or '<'?

I see '&' which leads me to believe that's how MSFT is storing it in the xlsx spreadsheets.

I was seeing the same thing with Spreadsheet::XLSX. For other reasons (built-in number/date formats not handled) I switched to Spreadsheet::ParseXLSX. With the latter, cells with built-in 'custom' number formats have correct formatted values and the entities are processed back to characters.

edit It looks like my hunch was correct.

Spreadsheet::XLSX (as found in http://search.cpan.org/~dmow/Spreadsheet-XLSX-0.13-withoutworldwriteables/lib/Spreadsheet/XLSX.pm) does not use an XML parser, but instead it messes with regular expressions. The code looks quite scary and seems to be very optimistic about the file format.

Perhaps you should look for a different module, or rewrite Spreadsheet::XLSX using a sane XML parser, like XML::LibXML.

Alexander

Part of some $work->project, I wrote PROCURA::XML::Entities. I'm willing to remove the PROCURA:: part and put it on CPAN. (Feel free to nick the code if you prefer, but in that case retain the credentials).

SYNOPSIS
        use PROCURA::XML::Entities;

        my $a = "Read "perlre" for explanation of '&amp
+;'";
        my $b = decode_entities ($a);

        # $b will now be q{Read "perlre" for explanation of '&'}

        $c = encode_entities ($b);

        # $c should be the same as $a

        use PROCURA::XML::Entities ();
        $decoded = PROCURA::XML::Entities::decode ($a);
        $encoded = PROCURA::XML::Entities::encode ($a);
[download]


Enjoy, Have FUN! H.Merijn

...rewrite Spreadsheet::XLSX using a sane XML parser, like XML::LibXML.

Work in progress.

Likely beyond my Perl skill level at the moment, but I'll keep it in mind. Meanwhile, I wrote a small function to fix the data. I also found code strings in the original data that we decided needed fixin', so this function might be all I need at the moment.



sub FixXML {
    $parm = $_[0];
    $parm =~ s/&/&/g;
    $parm =~ s/>/>/g;
    $parm =~ s/&lt;/</g;
    $parm =~ s/&quot;/"/g;
    $parm =~ s/&apos;/'/g;
    $parm =~ s/&#xA;/\n/g;
    $parm =~ s/&#xa;/\n/g;
    $parm =~ s/&#xD;/\r/g;
    $parm =~ s/&#xd;/\r/g;
    $parm =~ s/&#x9;/\t/g;
    return($parm);
}
[download]