[xsl] Re: Producing Excel spreadsheet from XML data

Subject: [xsl] Re: Producing Excel spreadsheet from XML data
From: "Sergej Rinc" <sergej@xxxxxxx>
Date: Tue, 12 Jun 2001 08:49:53 +0200

----- Original Message -----
> Date: Mon, 11 Jun 2001 09:58:40 -0500
> From: "MacEwan, James (Information Services)"
> Thanks to all for your comments.  I have summarized the responses (so far)
> below: <deleted>
> Thomas Passin also suggested producing HTML and then importing into Excel
> (97+).  His second idea was to write script inside Excel to call the XML
> parser.

  This could well be an ideal solution if you rephrase it. See below.

> Importing the CSV is going to give the data, but not all the fancy
> presentation.  Similarly importing the HTML is going to give the data and
> perhaps most of the presentation, but not all of it perfectly.

  Excel 2000 (X2K) *should* have an option to retain all formating when
saving to HTML (non-HTML formatting is retained in XML "islands" in the same
file and/or subdirectory so when you open the file in X2K again it *would*
remain formatting). At least that is my belief from all the Microsoft hype
but I don't use X2K so I cannot tell.

> My solution will probably be as follows:  (1) no XSL transformation occurs
> on Unix (2) FTP the XML document to NT (3) write a VB program that
> the XML and writes interesting bits of data into the right spots into the
> Excel (COM that calls MSXML3 and Excel's automation interfaces).

  Hey, you have a TCP/IP connection between Unix and NT? And you can (or
allready have) install a web server, like Apache/FastTrack on this Unix?
Then you could save yourself some troubles with programming by using this

1. Put/generate your data file in your Unix webserver subdirectory (e.g.
accessible from your NT via an HTTP connection).
2. Change your Excel spreadsheet to include Web Queries. These are possible
in X97 and above. They can grab a value from a file residing at some
3. You would refresh your values either manually (F9) or via program - only
in that case you would have to do some VBA macros.
A correction (added later): when you define a web query in X, you can enable
auto-refreshing of data when the file (which includes a web query) is

  This would probably be easier than your solution (FTP, VB, XML parsing,
installing MSXML3 on clients etc). Still, the problem with macros is
different scripting technology in different X versions (sounds like X files
:-) - most portable macro solution between them would probably indeed be
calling an external program.

  But then again, if only your users need all this, you could teach them to
do F9 at the opening of a critical X file (now I've really written it :-) or
enable auto-refresh at opening. Just simplify as possible.

> I will revisit how Excel 2002 uses XML to solve this problem at some
> time, but it is not an option that is presently available to me.

  Again, from Microsoft hype those new helpers (I don't even remember how do
they call them now) are just sophisticated XML/XSLT snippets which - when
called - grab some information from a local file or from the web. With X2K2
XML should really be a native format to X but let's see if this holds any
water. But it seems hidden XML parsing in X opens new possibilites.

> Thanks all,

  All listers would thank you more if you go for mentioned solution and
share us your thoughts, tips and tricks if not an implementation. Even
better would be to do a web service for your solution (e.g. returning just a
value from your webserver file, like
http://www.yourwebserver.com/data?x=4&y=5 would return a value from fourth
row in the fifth column, or you could use named fields etc). Many ways to do
that allready by usual CGI scripting - check even SOAP::Lite if your IT uses
Perl - and this would *probably* be more generic solution too, e.g. not tied
just to X.

Sergej Rinc, Certified Doc-To-Help Trainer
Master XML, Master XSL, Master WAP
Member, International Webmasters Association http://iwanet.org
mailto:sergej@xxxxxxx, http://sergej.rinc.ws

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list

Current Thread