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:
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 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.
Thanks!
Chin Chee-Kai
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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 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.
Thanks!
Chin Chee-Kai