OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

ubl-dev message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Creating UBL instances directly from spreadsheet


Does anyone use Excel spreadsheet for data entry and then generate UBL 
instances directly from the spreadsheet?

In trying to work this out, I've done a sample Invoice spreadsheet with 
a fictitious  company and layout.  The aim is to produce correct UBL 
instance  (or actually any targeted XML instance) directly from 
spreadsheet.  The outcome is stored in:

- for the sample spreadsheet:
- for the directly generated XML-Instance:

Here, if you look at the comment in spreadsheet cells, the comment text 
contains both help-text and a XPath-style deposit point for the data in 
the cell.  By naming the XPath in the comment (with the prefix number to 
order the points properly), one can pin-point the precise location  
within the targeted XML instance to store the cell's value (be it 
computed or user-entered).  This allows user to directly edit the 
spreadsheet and the final structure of the XML instance at one go.

Points to examine:
+ All cells are locked to prevent user from changing the text labels.  
(Password for the above sample is "123" without quotation marks)
+ All light-yellow colored cells are unlocked for user's data entry.
+ All light-blue colored cells are automatically computed 
(formula-based) values and are shown to but cannot be changed by user.
+ Both light-yellow and light-blue colored cells will be extracted for 
target XML-Instance processing (ie the cell values will be reflected in 
the XPath specified in the  comment block.
+ The comment block is free text which contains human-readable help 
text.  A new line beginning with "Data:" begins the XPath-style 
specification of deposit point for the cell's value at the targeted 
+ The XPath-look-alike style is actually a modified form of XPath.  It 
is prefixed with "(NNN)" to mandate a sequential ordering of cell values 
as needed in XML instance.  The NNN is any positive integer not 
necessarily contiguous (eg, (010), (020), like good'ol BASIC line 
+ The line entries require easy specification of comment block since 
they are all repeated.  So to make that easier for designing the 
spreadsheet, each element in the modified XPath can have a trailing 
"[expression]" where  expression is evaluated at run-time to give a row 
value (eg, "[ROW]" will return the row string of that row).  In this 
way, the comment blocks can be copied-and-pasted across while still 
ensuring proper orderliness in the final XML-instance.
+ The definitions of any and all namespace and prefixes are found in the 
spreadsheet's Property bag.  Either open up the spreadsheet and choose 
"File-->Property" to define/modify them, or choose "Properties" in 
windows explorer to modify.

This is all work-in-progress for next UBLish.  Please let me know if you 
have further comments, suggestions or  feedbacks.

If you could offer help in designing sample (or even actual) layouts for 
UBL Invoice, Order, Quotation or other documents which you find useful, 
please let me know.


Chin Chee-Kai

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]