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]

...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]

Note that this set of regexes will convert '&amp;lt;' to '<' due to the repeated substitutions. But you could do the &amp; substitution last to avoid that. Percents are also documented as something that gets escaped, so you'll want a substitution for that too.

Thank you, it worked for me