Re: [ubl-dev] Creating UBL instances directly from spreadsheet

[Chin Chee-Kai <cheekai@softml.net>]

Thanks for your encouraging feedback and suggestion, David.

I like your idea about a click to generate the XML (you meant the 
modified XPaths in the comment blocks?).  As you said, doing it 
manually, which is needed now,  is a necessary one-time "pain" to get 
the spreadsheet cells point at the right place to transform.  It is, 
arguably, similar amount of pain doing in spreadsheet or separately in XSLT.

But let's see what we can come up with in due course.

As for the "drop"ping of XML instance to Hermes H20 server,  perhaps 
using a batch transmit from a directory of generated XML instances?

regards,
Chin Chee-Kai

David RR Webber (XML) wrote:
> Chin,
> This is very cool.  Well done!
> Now all you have to do is figure out how to drop the XML off to the 
> Hermes H2O server - and you have a complete solution!?!
> ; -)
> Would be nice to have a button on the sheet to click to generate the 
> XML - or some help text - rather than having to know how to do that 
> manually in Excel.
> Thanks, DW
>
>     -------- Original Message --------
>     Subject: [ubl-dev] Creating UBL instances directly from spreadsheet
>     From: Chin Chee-Kai <cheekai@softml.net>
>     Date: Mon, September 29, 2008 3:01 am
>     To: UBL-Dev <ubl-dev@lists.oasis-open.org>
>
>     Hi,
>
>     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:
>
>     http://ublish.sourceforge.net/XML-Instance/UBL-INV-20080901-120353-ABX2.xls
>     - for the directly generated XML-Instance:
>
>     http://ublish.sourceforge.net/XML-Instance/UBL-INV-20080901-120353-ABX2.xml
>
>     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
>     XML-Instance.
>     + 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
>     programming).
>     + 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.
>
>     Thanks!
>
>     Regards,
>     Chin Chee-Kai
>