The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,497 other followers

Excel XML Spreadsheet: Date.Type is mandatory :)

Posted by jpluimers on 2011/07/28

When you generate Excel XML Spreadsheets, and you load it in Excel, it will report some errors in your Temporary Internet Files directory.

For instance, when you forget to fill the Data.Type attribute, then you get a log file with a message like this:

XML ERROR in Table
REASON:	Missing Tag
FILE:	V:\export.xml
GROUP:	Cell
TAG:	Data
ATTRIB:	Type

Note that some errors won’t be reported. For instance if you forget to put your Data in a Cell.Data (and put it in Cell); then Excel just show empty worksheet.

There seems to be no XSD for the XML Spreadsheet format, so you have to get yourself familiar with the XML in Excel and the Spreadsheet Component documentation on MSDN.

You can view where to put your stuff in the XML Spreadsheet Tag Hierarchy. That list doesn’t have individual links to the tags, you need the XML Spreadsheet Reference for that.

To make things more practical for myself, I perfomed these steps:

  1. created a spreadsheet with all of the data variations I expected
  2. saved this spreadsheet as XML
  3. generated the XSD from that XML
  4. imported the XSD to generate wrapper classes and interfaces

Even with that, you will need to accommodate for many peculiarities.

Hopefully I will find some time to write more those down soon.

The first is ss:Data:

  • It contains the actual data of a cell
  • It has a required ss:Type attribute, which is an enumeration of Number, DateTime, Boolean, String, and Error.
  • When it is String, then x:Ticked should be 1 (meaning True) when the string can be parsed as non-String type (and you would type the value into excel starting with a Tick mark (‘ aka single quote or apostrophe)

–jeroen

3 Responses to “Excel XML Spreadsheet: Date.Type is mandatory :)”

  1. […] disscution – Site… on “Yoda Conditions” …Excel XML Spreadshee… on Excel XML Spreadsheet: Date.Ty…neil on VMware Converter 4.01: […]

  2. […] I found some time to continue my series that started with Excel XML Spreadsheet: Date.Type is mandatory […]

  3. […] Recently, I blogged about the Excel XML Spreadsheet: Date.Type is mandatory. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: