Perl XML::Simple Print to Excel File

Question

Question: I want to print the xml data to the excel file, but fail to print to the excel file.

xml file: data.xml

<?xml version="1.0" encoding="UTF-8"?>
<ns1:BoardTestXMLExport numberOfIndictedComponents="11" testerTestStartTime=".00000:00" testTime=".00000:00" repairStationId="vvts03" testStatus="Reviewed Passed" testerTestEndTime=".00000:00" xmlns:ns1="http://tempuri.org/BoardTestXMLExport.xsd" numberOfIndictedPins="0" numberOfComponentsTested="113" numberOfJointsTested="0" numberOfDefects="11" repairStatus="Reviewed Passed">
          <ns1:BoardXML imageId="0" serialNumber="R174842442 1P00232G01 R02 1C31228G01 R07 JH1711327" assemblyRevision="1P00232G01-LF-SS" boardType="1P00232G01-LF-SS" boardRevision="1511417735000"/>
          <ns1:StationXML testerName="huaemsaoi004" stage="V510"/>
          <ns1:RepairEventXML numberOfVariationOkDefects="0" numberOfFalseCalledPins="0" numberOfRepairedComponents="0" numberOfVariationOkPins="0" numberOfRepairedPins="0" numberOfRepairLaterPins="0" numberOfFalseCalledDefects="11" numberOfActiveDefects="0" numberOfVariationOkComponents="0" repairEndTime="2017-11-23T14:31:05.000+08:00" repairStartTime="2017-11-23T14:30:58.000+08:00" numberOfRepairLaterDefects="0" repairOperator="1066689" numberOfRepairLaterComponents="0" numberOfActiveComponents="0" numberOfActivePins="0" numberOfRepairedDefects="0" numberOfFalseCalledComponents="11"/>
          <ns1:TestXML name="r1">
                    <ns1:IndictmentXML algorithm="rep-3a99979127l-lf" indictmentType="Left off, Right off">
                              <ns1:RepairActionXML repairOperator="1066689" repairTime="2017-11-23T14:30:58.000+08:00" repairActionType="-" indictmentType="Left off, Right off" comment="-" repairStatus="False Call"/>
                              <ns1:ComponentXML packageId="EP-3A99979127L-LF" partId="EP-3A99979127L-LF" designator="r1"/>
                    </ns1:IndictmentXML>
          </ns1:TestXML>
</ns1:BoardTestXMLExport>

my code as below:

# use module
 use strict;
 use warnings;
 use XML::Simple;
 use Data::Dumper;
 use Excel::Writer::XLSX;

# create object
 my $xml = XML::Simple->new;

# create object
 my $xml = XML::Simple->new;

 #create excel object 
 my $OutFile = "Output". "\." . "xlsx";
 my $workbook  = Excel::Writer::XLSX->new($OutFile);
 my $worksheet = $workbook->add_worksheet('SotredProcedures');

 # Create a format for the column headings
 my $header = $workbook->add_format();
 $header->set_bold();
 $header->set_color('red');
# Create a Text Wrap format
 my $format1 = $workbook->add_format();
 $format1->set_text_wrap();
 $worksheet->write(0, 0, "RepairStationID", $header);
 $worksheet->write(0, 1, "BoardType", $header);
 $worksheet->write(0, 2, "ComponentsTested", $header);
 $worksheet->write(0, 3, "NumberOfDefects", $header);
 $worksheet->set_column(0, 1, 12);
 $worksheet->set_column(1, 2, 20);
 $worksheet->set_column(1, 3, 20);
 $worksheet->set_column(1, 4, 20);
# read XML file
 my $data = $xml->XMLin("data.xml",KeyAttr=>"ns1");
print  $data ->{"ns1:BoardXML"}->{"boardType"}->{$worksheet};

Result: fail to print the boardtype to the excel file, output as below:

how can i do for it?

Answer 1

Your error message says that you are trying to access the string 1P00232G01-LF-SS as a hash reference. That comes from this line:

print  $data->{"ns1:BoardXML"}->{"boardType"}->{$worksheet};

Your $worksheet is an object that represents the sheet in your Excel file. You cannot use it as a hash key, and the value of $data->{"ns1:BoardXML"}->{"boardType"} is a string. There is no hash reference there to access data from.

You already know how to write (not print) data to your worksheet. You do it above:

$worksheet->write( 0, 1, "BoardType", $header );

Now you need to do the same thing to write your data to the sheet.

$worksheet->write(1, 1, $data ->{"ns1:BoardXML"}->{"boardType"}, $format1);

Remember that you need to change the row your're writing to. That's now row 1, so the first argument to write needs to change.