Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / XML
Article

Yet another way to Generate Excel documents programmatically

Rate me:
Please Sign up or sign in to vote.
4.78/5 (24 votes)
13 Dec 20055 min read 138.3K   1.5K   83   22
XML documents can represent Microsoft Excel spreadsheets. This article provides a working example of a relatively low-tech C# program generating a properly formatted XML document that Microsoft Excel loads and views as one would expect.

Introduction

I needed to be able to generate a simple report showing data from a collection of objects. My initial thought was to generate a simple .txt file and view it in Notepad. However, this seemed very boring and some of my users would want to do some analysis on the data (e.g. averages, sub-totals and so forth). Of course, the natural place for someone to do that kind of work is Microsoft Excel.

I did a bit of searching for a very simple low-tech Microsoft Excel interface example. I didn't try very hard, since in fact, I wanted to figure this out myself anyway.

I knew that one can save Excel spreadsheets in a variety of formats, so I pulled up Excel, created the spreadsheet I wanted, including formatting and multiple data types and saved it as an XML document. I then pulled that up into my favorite text editor and started to reverse engineer it.

As sharp-eyed codeproject.com readers know, this subject has been thoroughly discussed already. I had already written this article before it occurred to me to check codeproject.com for Excel-related articles. There are many of them. However, almost all of them involve a more or less direct interface with Excel (typically by adding a reference to your VS project, instantiating Excel, etc). The approach described in this article provides two advantages:

  1. It's lower tech, and
  2. This approach does not require that Microsoft office be installed / available to the C# program that generates the XML. In some cases, this is a significant advantage. A reader kindly pointed this out to me and I am grateful.

As a final note, all we are doing here is generating XML. C# is not required to do that, so this is applicable to VB and any other language that is capable of generating XML.

Background

Several MS office products allow you to save the associated document to an XML file instead of the usual binary format (e.g. .doc, .xls). The XML that Office 2003 generates is somewhat complicated but still quite readable. To save an Excel file as .xml, go to File, Save-As and then change the output format to "XML Spreadsheet".

Microsoft Office identifies a particular XML file as being an "XML Spreadsheet" via the following:

XML
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?> 

The first line shows the usual xml version information and the second line causes Office to open Excel to process the XML.

The XML itself is broken up into several major sections:

  • Workbook: Root node of the XML, parent to all the other sections.
  • DocumentProperties: Most of the importation one sees when accessing File -> Properties is defined here.
  • Styles: Formatting information defined here is available to different rows, columns and specific cells in the worksheets.
  • Worksheet(s): As many worksheets as you want are defined here.

Worksheets consist of two major sections:

  • Table: This is where all visible data in the spreadsheet is stored.
  • WorksheetOptions: Global options for the worksheet itself.

Finally, the Table section defines two components:

  • Column
  • Row / Cell

With this information in hand, one can plan out how to create the XML document that will then load into Microsoft Excel and look, feel and operate like a document created by a sophisticated, loving user.

Using the code

The code for this project does the following:

  1. Creates a set of objects with data that we want to appear in the spreadsheet. This sample data is modeled after a simple price list.
  2. Generates properly formatted XML that one can then open with Excel.
  3. Launches Excel.

The demo includes a simple menu screen with a link label. Upon clicking the link, it instantiates an ExcelReport object.

The constructor of the ExcelReport object instantiates a handful of SampleData objects.

The simple menu object then invokes the GenerateSelf() method on the ExcelReport object which saves the Excel XML to a hard-coded file name, c:\temp\prices.xml

Once generated, the simple menu program launches Excel.

Points of Interest

  • The technique here has been successfully used to generate far more complicated Excel documents, including multi-sheet documents with relatively complicated graphs.
  • Though I have never done so, Microsoft Word can also save documents in an XML format and this same technique can be used to create Word XML documents with all the Microsoft Word formatting as well.
  • Excel does present one significant problem with documents created this way. Happily, it's quite easy to work around. It is also very strange.
    In the generated document, you will notice in the Table section, a line like this:
    XML
    <Cell><Data ss:Type="Number">25</Data></Cell>

    Though it's not necessary, my preference when generating XML is to split nodes out on their own lines. I find it's easier to read in a text editor. So, I would prefer this:

    XML
    <Cell>
      <Data ss:Type="Number">25</Data>
    </Cell>

    As far as I'm concerned, that's valid XML. However Excel 2003 complains when it opens up that XML document with a message in a dialog box, "Problems During Load". It complains in a bit more detail, "Problems came up in the following areas during load:" and lists the "Table" area.

    It then kindly tells you that the errors are listed in a deep-down directory:
    "This file cannot be opened because of errors. Errors are listed in: C:\Documents and Settings\pagalvin.DFKBZ221\Local Settings\Temporary Internet Files\Content.MSO\D74388D6.log"

    Sadly, in a Win2K environment, you can't copy/paste that file name, so it's a bit tricky to find. (A reader has pointed out that you can do so in WinXP). Worse, it's not so helpful once you do find it:

    XML
    XML PARSE ERROR: Extraneous end-tag
    Error occurs at or below this element stack:
    <ss:Workbook>
      <ss:Worksheet>
        <ss:Table>
          <ss:Row>
            <ss:cell>

    Despite that quirk, it's easy to work around. Keep it all on one line.

History

  • 12/13/2005: Created
  • 12/14/2005: Added some comments in response to user feedback
    1. Pointed out that Excel need not be present where this program runs. This makes this solution useful on a server that does not have Microsoft Office installed on it.
    2. Fixed a typo in the XML where I describe the problem in the Table section.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
Partner at Integrated Systems and Services group (www.issgroup.net).

Graduated Lafayette Collage (Easton, PA) with B.S. C.S. in 1991.

Married with one son.

Living in the great state of New Jersey.


Comments and Discussions

 
Questionmore complex example Pin
aron.sinoai10-Nov-11 4:59
aron.sinoai10-Nov-11 4:59 
GeneralMy vote of 5 Pin
fredatcodeproject23-Sep-11 7:54
professionalfredatcodeproject23-Sep-11 7:54 
GeneralRe: My vote of 5 Pin
Johnny Glenn11-Apr-12 22:10
Johnny Glenn11-Apr-12 22:10 
GeneralRe: My vote of 5 Pin
fredatcodeproject17-Apr-12 23:43
professionalfredatcodeproject17-Apr-12 23:43 
QuestionNeed some info Pin
girlprogrammer2-Aug-11 4:39
girlprogrammer2-Aug-11 4:39 
GeneralRe: Yet another way to Generate Excel documents programmatically Pin
ira15317-May-10 14:10
ira15317-May-10 14:10 
GeneralRe: Yet another way to Generate Excel documents Pin
ira15317-May-10 14:07
ira15317-May-10 14:07 
QuestionNeed more Information about Excel-XML Tags? Pin
bosfan9-Jun-08 23:28
bosfan9-Jun-08 23:28 
AnswerRe: Need more Information about Excel-XML Tags? Pin
iamstarbuck1-Jul-08 19:50
iamstarbuck1-Jul-08 19:50 
GeneralRe: Need more Information about Excel-XML Tags? Pin
bosfan2-Jul-08 2:36
bosfan2-Jul-08 2:36 
GeneralRe: Need more Information about Excel-XML Tags? [modified] Pin
PiyushVarma19-Apr-10 7:12
PiyushVarma19-Apr-10 7:12 
GeneralNo Good For Office 2000, XP Pin
harrach8-May-08 22:47
harrach8-May-08 22:47 
GeneralSimple ideas require smart thought Pin
Roberto Colnaghi11-Jan-08 10:59
Roberto Colnaghi11-Jan-08 10:59 
QuestionCan It work on Excel2000, Excel XP Pin
dddd2187-Oct-07 0:52
dddd2187-Oct-07 0:52 
Questiona new question related to this procedure.. Pin
dadart9-Sep-07 21:42
dadart9-Sep-07 21:42 
GeneralProblems came up.....encoding Pin
williamwash21-Aug-07 17:37
williamwash21-Aug-07 17:37 
QuestionInstantiating an Excel Worksheet Pin
nautic2010-Aug-07 3:28
nautic2010-Aug-07 3:28 
GeneralGood work! Pin
rosnay25-Jun-06 13:49
rosnay25-Jun-06 13:49 
GeneralWorks for me Pin
kubben31-Mar-06 6:23
kubben31-Mar-06 6:23 
GeneralCode file missing Pin
gxdata14-Dec-05 13:59
gxdata14-Dec-05 13:59 
GeneralA few small points Pin
Mark Focas13-Dec-05 16:44
Mark Focas13-Dec-05 16:44 
GeneralRe: A few small points Pin
Aljaz2428-Jun-06 4:09
Aljaz2428-Jun-06 4:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.