Possible to build up an Excel document programmatically?

K Viltersten

I understand that the new versions of MS
Word, MS Excel etc. allow for creation of
a document using XML tags (the technique
or format is called OOXML, i think).

I imagine that the idea behind such
creation resembles principally the way of
creating a GUI using WPF, where one can
mark up what parts of the text mass
should carry this or that format etc.

Is it at all true? I've been googling and
experimenting a bit lately but i wasn't
especially successfull... :(

--
Regards
Konrad Viltersten
--------------------------------
sleep - a substitute for coffee for the poor
ambition - lack of sense to be lazy

Oct 11 '08 #1

Peter Duniho

On Sat, 11 Oct 2008 00:53:52 -0700, K Viltersten <tm**@viltersten.com>
wrote:

I understand that the new versions of MS Word, MS Excel etc. allow for
creation of a document using XML tags (the technique
or format is called OOXML, i think).

I imagine that the idea behind such creation resembles principally the
way of
creating a GUI using WPF, where one can mark up what parts of the text
mass should carry this or that format etc. [...]

IMHO, it resembles WPF only inasmuch as they both are using XML. One
obvious different being that the Office formats describe a static document
(for the most part), whereas WPF has some things in it that provide
connections between a declared GUI and code that works with that.

As far as writing an Office document explicitly using XML goes, sure...I
suppose you could do that. I don't recall whether Microsoft's actually
published their new XML-based format yet, but they probably have. As long
as you comply with the format, you're fine.

But the format is undoubtedly fairly complex. If you have to create a
document without having Office installed, that might be the only viable
approach. But otherwise, you would probably find yourself better off
actually using Office to create the document, through the Office interop
classes.

Pete

Oct 11 '08 #2

K Viltersten

>I understand that the new versions of MS Word, MS Excel etc. allow for

>creation of a document using XML tags (the technique
or format is called OOXML, i think).

<snip>

But the format is undoubtedly fairly complex. If you have to create a
document without having Office installed, that might be the only viable
approach. But otherwise, you would probably find yourself better off
actually using Office to create the document, through the Office interop
classes.

The document will be created on a server with no Office
installed (at least not what i can guarantee). The nice part is
that it won't be a very complicated document, most often,
only some data put together, so it won't be that much work.

What i don't really see is how to get started. Suppose i'd like
to create a document with a blue text saying "hello" in cell B3.
How can i do that? I've tried to save sucha document from
Office but i didn't really got an XML-structure in it.

Some pointers or a very short example would be great.
Thanks.

--
Regards
Konrad Viltersten
--------------------------------
sleep - a substitute for coffee for the poor
ambition - lack of sense to be lazy

Oct 11 '08 #3

Jeroen Mostert

K Viltersten wrote:

I understand that the new versions of MS Word, MS Excel etc. allow for
creation of a document using XML tags (the technique
or format is called OOXML, i think).

I imagine that the idea behind such creation resembles principally the
way of
creating a GUI using WPF, where one can mark up what parts of the text
mass should carry this or that format etc.

Yes and no. The underlying techniques resemble each other but the formats
really don't. You'd need a serious conversion step to get WPF-to-OOXML.

Is it at all true? I've been googling and
experimenting a bit lately but i wasn't
especially successfull... :(

Producing an Excel document programmatically isn't hard, because, as you've
pointed out, it accepts XML. Actually, Excel even accepts *HTML*, so you can
use a GridView with adjusted rendering to produce a quick and dirty Excel
sheet. See http://aspalliance.com/771

This is basically your idea, but using different technologies. It can be
made more complex if you want "true" Excel sheets (with formulas and such)
using a ControlAdapter and knowledge of OOXML, but if you're going this far
you can probably directly output OOXML instead.

--
J.

Oct 11 '08 #4

K Viltersten

>I understand that the new versions of MS Word, MS

>Excel etc. allow for creation of a document using
XML tags (the technique or format is called OOXML,
i think).

Producing an Excel document programmatically isn't
hard, because, as you've pointed out, it accepts
XML.

Sorry, i was unclear here. I wish to build up a
FORMATTED Excel document (i.e. with colors, formulas
and all this nice stuff). Is it still not hard?

...if you want "true" Excel sheets (with formulas
and such) using a ControlAdapter and knowledge of
OOXML, but if you're going this far you can
probably directly output OOXML instead.

I'm unclear on "outputting OOXML directly". Would
you like to elaborate? I thought i needed to set up
a String object containing a XML that will "tell"
the computer to color this blue, color that red and
use this and that formula here and there...

--
Regards
Konrad Viltersten
--------------------------------
sleep - a substitute for coffee for the poor
ambition - lack of sense to be lazy

Oct 11 '08 #5

Jeroen Mostert

K Viltersten wrote:

>>I understand that the new versions of MS Word, MS Excel etc. allow
for creation of a document using XML tags (the technique or format is
called OOXML, i think).

Producing an Excel document programmatically isn't hard, because, as
you've pointed out, it accepts XML.

Sorry, i was unclear here. I wish to build up a FORMATTED Excel document
(i.e. with colors, formulas
and all this nice stuff). Is it still not hard?

This is harder, obviously. Adapting a GridView will get you a table, but
that's about it.

>...if you want "true" Excel sheets (with formulas and such) using a
ControlAdapter and knowledge of OOXML, but if you're going this far
you can probably directly output OOXML instead.

I'm unclear on "outputting OOXML directly". Would
you like to elaborate? I thought i needed to set up
a String object containing a XML that will "tell"
the computer to color this blue, color that red and
use this and that formula here and there...

Yes, that's basically it (though you don't need to go through a string, just
use XmlWriter or XElement). Writing a GridView to a file is a shortcut to
doing this if all you want is a basic table. If you want to build up an
Excel sheet with all the bells and whistles, you'll have to dig into the
OOXML format. You can make your life easier by designing the sheet(s) you
want in Excel first and saving them, so you can adapt the results rather
than generating things from scratch.

--
J.

Oct 11 '08 #6

K Viltersten

>I'm unclear on "outputting OOXML directly". Would

>you like to elaborate? I thought i needed to set up
a String object containing a XML that will "tell"
the computer to color this blue, color that red and
use this and that formula here and there...

If you want to build up an Excel sheet with all the
bells and whistles, you'll have to dig into the
OOXML format. You can make your life easier by
designing the sheet(s) you want in Excel first and
saving them, so you can adapt the results rather
than generating things from scratch.

As far i can see i can't save an Excel document as
XML (OOXML, if you like). Am i missing something? I
took that you ment that i should create a document,
save it as OOXML and then end up having a template
on how to design a formatted document. Did i got it
right? If so, how do i get the OOXML-ized Excel file?

--
Regards
Konrad Viltersten
--------------------------------
sleep - a substitute for coffee for the poor
ambition - lack of sense to be lazy

Oct 11 '08 #7

Peter Duniho

On Sat, 11 Oct 2008 15:57:09 -0700, K Viltersten <tm**@viltersten.com>
wrote:

As far i can see i can't save an Excel document as
XML (OOXML, if you like).

Any time you save an Excel document using the native Excel format, that's
exactly what you're doing.

Am i missing something? I
took that you ment that i should create a document, save it as OOXML and
then end up having a template
on how to design a formatted document. Did i got it
right? If so, how do i get the OOXML-ized Excel file?

Your questions are really much more appropriate for a newsgroup
specifically about the use of Office and/or Excel.

Briefly: the Office 2007 file format is basically a ZIP file with some XML
files contained within. For your own program to create a document in this
format that Excel can read, you need to comply exactly with the
appropriate file format, including creating the XML exactly to the Excel
specification and storing it inside a ZIP-format file with the appropriate
extension (.xlsx for Excel 2007).

As far as the exact file format goes, you need to consult Microsoft's own
documentation for the file format, assuming it exists (I believe that
they've made this format public, but I recall for certain that's true,
never mind do I know off-hand where the documentation might be).

To create one of these files from within Excel for your own reference,
just save your Excel document. The .xlsx file that's written is what
Jeroen is talking about.

Note that this is very different from writing some arbitrary XML file.
Excel does in fact read XML files, and will even provide a default schema
that interprets the XML as a kind of simple textual database file. But
this kind of XML input for Excel will not include any special formatting
or document-specific features that Excel has. It's completely different
from the Excel-specific ".xlsx" XML-based file format.

Pete

Oct 11 '08 #8

K Viltersten

>As far i can see i can't save an Excel document as

>XML (OOXML, if you like).

Any time you save an Excel document using the native Excel format, that's
exactly what you're doing.

>Am i missing something? I
took that you ment that i should create a document, save it as OOXML and
then end up having a template
on how to design a formatted document. Did i got it
right? If so, how do i get the OOXML-ized Excel file?

Your questions are really much more appropriate for a newsgroup
specifically about the use of Office and/or Excel.

Briefly: the Office 2007 file format is basically a ZIP file with some XML
files contained within.

<snipping good info>

Great. I think my brains swallowed it now.
Thanks a lot to both of you, guys!

--
Regards
Konrad Viltersten
--------------------------------
sleep - a substitute for coffee for the poor
ambition - lack of sense to be lazy

Oct 12 '08 #9