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,679 other followers

Archive for July 28th, 2011

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

Posted in .NET, Development, Software Development, XML, XML/XSD, XSD | 3 Comments »

 
%d bloggers like this: