Parse
Excel File
Many a times, we need to import data
from excel into PRPC data structures. There are quite a few ways to do so, here
I am describing an OOTB internal activity to import data from Excel (XSLX) into
PRPC clipboard.
As a pre requisite we need a
template excel file uploaded as Rule-File-Binary. Later this template
file is used to interpret the uploaded spread sheets values and map into
appropriate clipboard structures. Always have the first row as heading, and
second row should have following syntax
{.PageListName().PropertyName input}
Next write an activity, to get the
file location of the uploaded excel file (use FileUpload control to upload the
excel sheet), and then call OOTB internal activity named MSOParseExcelFile.
This activity takes two mandatory properties, first is the location and name of
the uploaded excel file, second is the name of the template file.
With this MSOParseExcelFile would
map the contents of the excel file in to appropriate clip board structures.
Here is a scenario illustrating the
same
Scenario: Upload Employee Details
(Name, ID, Manager, Age) from an excel file
Step1: Create Template File and upload it is Rule-File-Binary
Create a entry in Rule-File-Binay and import the created Template
Step 2: Write an activity to call MSOParseExcelFile
Param Supplied:
FSFFileName : Name and Location of the uploaded excel file.
TemplateRFB: Name of the uploaded binary file, it should follow
<Directory name>!<Rule Name>!<extension>
Step 3: Run the activity to check results
PSEDO code to Import Actual file
Import processing
a.
Import the data file and open template
b.
call MSOParserExcelFile
c.
Covert the java page to property page
d.
Get the key values,
e.
To decide add/update Match key in existing page
f.
Update or add the page property
g.
Completed the update
Hope the above article is covers most of the required stuff.
Please leave a comment with your practical experiences on how useful this
article was and also any thing needs further explanations.