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?

Don't use XML::Simple. The library itself tells you not to use it. See [Why is XML::Simple “Discouraged”?](https://stackoverflow.com/q/33267765/1030675) for more details. — choroba, Dec 01 '17 at 09:02
@choroba we've told them in the last question. Dind't help. ;) — simbabque, Dec 01 '17 at 09:03
most of thing i have finished it, now only need to print to the excel file now. — huang, Dec 01 '17 at 09:03
Please **don't post code or error messages as images**. We've told you this before. — simbabque, Dec 01 '17 at 09:04
Have you _read your error message_? Do you know what it means? One of the most important things about programming is paying attention to details. Your error message tells you exactly what's wrong. Since you cut off the line number, I can't tell you exactly where the problem is, but it's fairly obvious. You used something that is a string like it was a hash reference. That won't work. You are accessing your data structure in a wrong way. Output it again with Data::Dumper and and check what you're doing. — simbabque, Dec 01 '17 at 09:06
@simbabque This is a new account, created today; but the file seems the same as in [yesterday's post](https://stackoverflow.com/q/47549993/4653379) — zdim, Dec 01 '17 at 09:08
Also your code looks like you are really struggling with your task. It seems you copy/pasted a lot and don't understand what's going on. There are some things there twice, like the declaration of `$xml`. This code will not even compile. And the actual problem is that you used your `$worksheet` in a wrong way. — simbabque, Dec 01 '17 at 09:08
@zdim Yesterday's post was originally another question but then edited into a different question. I guess the author couldn't post another question within 5 days or so, was in a hurry, and then created this new account. — PerlDuck, Dec 01 '17 at 10:01
@huang: Please don't create new accounts just to get round the new-user restrictions. [As I said to you yesterday](https://stackoverflow.com/questions/47549993/perl-xmlsimple-print-issue#comment82099077_47551350), if you contribute to the site then you will quickly accumulate the reputation that you need in order to be able to post more questions. — Dave Cross, Dec 01 '17 at 11:53
@huang: You show many signs of trying to write code by cutting and pasting examples from many sources without really understanding what you are doing. This is a recipe for disaster. I urge you to step back from this and take the time to understand the code that you are trying to use. Or, alternatively, admit that this is beyond your current skill level and pay a programmer to do it for you. — Dave Cross, Dec 01 '17 at 12:01

score 1 · Answer 1 · answered Dec 01 '17 at 09:27

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.

Perl XML::Simple Print to Excel File

1 Answers1