[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Re: editing XML with Spreadsheets, most likely Excel

• From: "Stephen Green" <stephengreenubl@g...>
• To: "bryan rasmussen" <rasmussen.bryan@g...>
• Date: Sat, 1 Dec 2007 22:30:47 +0000

An afterthought:
I've not tried it but maybe, if you use XSLT to create XML output from
the underlying XML of the spreadsheet, prior to the transformation, a
validation step could use Schematron. I found that with the app I was
using there was no output of a message from the xsl:message but
there was implementation of the xsl:message's 'terminate' which might
be enough perhaps. On the other hand I've merely used a macro for
validation of the spreadsheet prior to transformation to the output file
and it works well enough if the macro puts a result string somewhere
where the XSLT of the transformation can pick it up as a precondition
to the transformation. It's all very cool in my opinion, one of the nicer
fruits of all the excellent XML work of so many people - many thanks All.

On 01/12/2007, Stephen Green <stephengreenubl@g...> wrote:
> Hi Bryan
>
> One challenge with transforming from, say, ODF spreadsheet table with
> XSLT (such as with an XML Filter) is getting around the merging of empty
> cells in a row. This means that there is a need to calculate the physical
> location within the row of the logical cell where the data you need is held.
> It is doable though, even with straight XSLT 1.0 and the result is satisfactory.
>
> You need not just stick to XML output; CSV or fixed width files are other
> options where relevant. I've not tried this with UBL instances or schemas
> but got close to it once and it is an interesting project as far as I remember.
>
> Having standard locations for the UBL business entities in the spreadsheet
> a bit like the way UN standardized layouts for paper printing or invoices,
> etc would help a lot for UBL instances. For UBL schema files there is a
> 'standard' layout for the business entity data and metadata already - well
> two standards: UBL's and CEFACT's - so transformation from spreadsheet
> to standard (NDR) schema is relatively straightforward, helped by the 'all
> global' NDR (Naming and Design Rules). I remember there being a few
> challenges to using XSLT which led to scripting languages being used
> in preference to XSLT.
>
> I'd say it is a satisfying way to get to an XML document if you have time
> and patience to overcome the challenges.
>
> I've tried Infopath too with Excel and found challenges there with the
> complexity of, say, UBL (with many multiple occurrence complex types).
> Again the amount of work involved seems only to be warranted if there
> is stability in the spreadsheet layout. I thought it more useful to use XSLT
> with the underlying XML of the spreadsheet when the spreadsheet layout
> has been fixed sufficiently rather than a proprietary binding such as Infopath
> (although I found I liked working with the Infopath concept).
>
> [Opinions and preferences here are just my own of course based on my
> experiences using the products, standards and techniques mentioned.]
>
> Best regards
>
> --
> Stephen Green
>
> Partner
> SystML, http://www.systml.co.uk
> Tel: +44 (0) 117 9541606
>
> http://www.biblegateway.com/passage/?search=matthew+22:37 .. and voice
>
>
> On 01/12/2007, bryan rasmussen <rasmussen.bryan@g...> wrote:
> > Hi,
> >
> > I know lots of people on this list have had some experience editing
> > generating XML with Excel or other spreadsheet technologies, I've some
> > slight knowledge of it as it is the technique used in UBL to
> > edit/generate schemas. I never much cared for it as I thought it made
> > the XML structures harder to understand. I was wondering if anyone had
> > any specific things they like about the method, things they hate? What
> > are the limits to numbers of files that can be edited in one
> > spreadsheet, if for example one has related files in a particular
> > worksheet?
> >
> > Cheers,
> > Bryan Rasmussen
> >
> > _______________________________________________________________________
> >
>


-- 
Stephen Green

Partner
SystML, http://www.systml.co.uk
Tel: +44 (0) 117 9541606

http://www.biblegateway.com/passage/?search=matthew+22:37 .. and voice

• Follow-Ups:
  • Re: editing XML with Spreadsheets, most likely Excel
    • From: Dave Pawson <davep@d...>

• References:
  • editing XML with Spreadsheets, most likely Excel
    • From: "bryan rasmussen" <rasmussen.bryan@g...>
  • Re: editing XML with Spreadsheets, most likely Excel
    • From: "Stephen Green" <stephengreenubl@g...>