Your skill will accomplish
what the force of many cannot
(( Resolved )) Search Entire Excel Workbook For Textby ImJustAFriend (Sexton)
|on Oct 16, 2012 at 13:45 UTC||Need Help??|
ImJustAFriend has asked for the
wisdom of the Perl Monks concerning the following question:
Hello Monks... been a visitor for years, and have benefited from the knowledge bestowed upon these boards. I now need to seek some Perl wisdom that I can't find through the search feature of this monastery.
I have a few XML files fed from one Excel spreadsheet, one XML file "template" per device type. We have been manually editing template XML files, but now I want to automate this process. I have code that works fine for one device, but I need to expand the logic to include the other device types (which have different XML templates). I would like to keep this as "one script, one Excel file". To do so, I need to figure out how to search the Excel file, across multiple worksheets, for a specific "name" in the XML then gather the value from the worksheet.
The XML looks like this:
The worksheets look like this (fake data):
There are 12 worksheets, all formatted as above, for the XML creation. Each worksheet has the same number of columns (one per device type), but could have anywhere from 1 to 100 rows of data. A-H in the first row represent different devices for which data would be pulled.
So my script logic currently says "start reading the XML from the top of the file. If the line matches 'option name', pull the data from Excel in worksheet x, row y, column z... else, print the line to the output XML file and read next line." Worksheet x, row y, and column z are all either created from script parameters (column z), or calculated from loop counts.
That code works spectacularly for devices A-D in my Excel mock-up, but E-H have a different XML template to follow. So, I am thinking to change my logic to say something like "start reading the XML from the top of the file. If the line matches 'option name', search through the entirety of the Excel file until the matching parameter name is found and pull the appropriate data... else, print the line to the output XML file and read next line." I have been searching for 3 days for how to search an entire Excel workbook for specific text, but thus far I am not having any luck.
So, Perl Monks... can someone please point me in the right direction for searching an Excel workbook for a specific string?
Thank you very much!!ImJustAFriend