I have the following information in an xml file I need to amend:
<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:workbookPr codeName="ThisWorkbook"/>
<x:bookViews>
<x:workbookView firstSheet="0" activeTab="0"/>
</x:bookViews>
<x:sheets>
<x:sheet name="Sheet1" sheetId="2" r:id="rId2"/>
</x:sheets>
<x:definedNames/>
<x:calcPr calcId="125725"/>
</x:workbook>
In general, the prefix "x:" can be anything, including the empty prefix.
I now need to add a child element "x:sheet" to the "x:sheets" element in the file. However, if the file contents had no prefixes then I would need to add a child element "sheet" to the "sheets" element in the file.
Since the prefix, in general, can be anything I use the following perl code to scan and amend the file.
use strict;
use warnings;
use XML::Twig;
my $fileName1='/folder1/file1.xml';
my $fh1;
my $fh1_filename='/folder1/file1_NEW.xml';
my $tw1=new XML::Twig(
map_xmlns => {
'http://schemas.openxmlformats.org/officeDocument/2006/relationships' => 'r',
'http://schemas.openxmlformats.org/spreadsheetml/2006/main' => 's'
},
keep_original_prefix => 1,
pretty_print => 'indented',
twig_handlers => {'/s:workbook/s:sheets' => sub{Add_Sheet_1(@_)}}
);
$tw1->parsefile($fileName1);
open($fh1, '>:encoding(UTF-8)', $fh1_filename);
$tw1->flush($fh1);
close $fh1;
}
#
#
#
sub Add_Sheet_1{
my ( $twig, $tag) = @_;
my $var1='x:sheet';
$tag->insert_new_elt( 'last_child', => $var1=>{name=>'Sheet_N1',sheetId=>'200'});
}
For the case where the prefix is "x:" this code generates the desired result.
However, in general, the prefix is unknown. My handler maps the unknown prefix to the prefix "s:" So finding the correct element poses no problem.
However, I cannot find any way of adding a new child element with the correct prefix.
Is there any way I can get the original prefix from the file and use it when adding my new child element?
If you know the URI of the particular namespace you're interested in (In the variable $schema_uri
in the below), you can look up the prefix (If any) being used in the document for it by iterating through all assigned prefixes looking for a match:
sub Add_Sheet_1{
my ($twig, $tag) = @_;
my $ns = "";
foreach my $prefix ($twig->parser->current_ns_prefixes) {
if ($twig->original_uri($prefix) eq $schema_uri) {
$ns = "$prefix:";
last;
}
}
my $name = $ns . "sheet";
$tag->insert_new_elt('last_child', $name, {name => 'Sheet_N1', sheetId => '200'});
}
This works with a prefix (x:sheet
) and when it's the default namespace (sheet
) in my testing.