by ·Comments Off on Get a reference to the parent object when deserializing XML in C#

This is the code I used in response to an answer to a question I posted on stackoverflow.

Briefly, I was wondering how to hold a reference to the parent object in one of it’s children when deserializing the following hierarchical XML: –

<report xmlns="http://schemas.stuartwhiteford.com/">
    <id>1</id>
    <dateCreated>2009-03-16</dateCreated>
    <title>Report Title</title>
    <subTitle>Report SubTitle</subTitle>
    <sections>
        <section>
            <id>2</id>
            <title>Section</title>
        </section>
    </sections>
</report>

The answer was to use the DataContractSerializer, which is used to serialize and deserialize data sent in Windows Communication Foundation (WCF) messages. For this to work though, we need to modify our XML slightly to include references between the parent and child elements: –

<report z:Id="1" xmlns="http://schemas.stuartwhiteford.com/" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/">
    <id>1</id>
    <dateCreated>2009-03-16</dateCreated>
    <title>Report Title</title>
    <subTitle>Report SubTitle</subTitle>
    <sections>
        <section z:Id="2">
            <report z:Ref="1" xsi:nil="true"/>
            <id>4</id>
            <title>Section</title>
        </section>
    </sections>
</report>

Here’s the code required to deserialize: –

using System;
using System.Collections.ObjectModel;
using System.IO;
using System.Runtime.Serialization;
using System.Xml;
 
namespace ReportSample
{
 
    [DataContract(Name = "report", Namespace = "http://schemas.stuartwhiteford.com/")]
    public class Report
    {
 
        private int _id;
        private DateTime _dateCreated;
        private string _title;
        private string _subTitle;
        private Sections _sections;
 
        [DataMember(Name = "id", Order = 1)]
        public int ID
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }
 
        [DataMember(Name = "dateCreated", Order = 2)]
        public DateTime DateCreated
        {
            get
            {
                return _dateCreated;
            }
            set
            {
                _dateCreated = value;
            }
        }
 
        [DataMember(Name = "title", Order = 3)]
        public string Title
        {
            get
            {
                return _title;
            }
            set
            {
                _title = value;
            }
        }
 
        [DataMember(Name = "subTitle", Order = 4)]
        public string SubTitle
        {
            get
            {
                return _subTitle;
            }
            set
            {
                _subTitle = value;
            }
        }
 
        [DataMember(Name = "sections", Order = 5)]
        public Sections Sections
        {
            get
            {
                return _sections;
            }
            set
            {
                _sections = value;
            }
        }
 
    }
 
    [DataContract(Name = "section", Namespace = "http://schemas.stuartwhiteford.com/")]
    public class Section
    {
 
        private Report _report;
        private int _id;
        private string _title;
 
        [DataMember(Name = "report", Order = 1)]
        public Report Report
        {
            get
            {
                return _report;
            }
            set
            {
                _report = value;
            }
        }
 
        [DataMember(Name = "id", Order = 2)]
        public int ID
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }
 
        [DataMember(Name = "title", Order = 3)]
        public string Title
        {
            get
            {
                return _title;
            }
            set
            {
                _title = value;
            }
        }
 
        public string Key
        {
            get
            {
                return _id.ToString();
            }
        }
 
    }
 
    public class Sections : KeyedCollection<string, Section>
    {
 
        protected override string GetKeyForItem(Section item)
        {
            return item.Key;
        }
 
    }
 
    class Program
    {
 
        static void Main(string[] args)
        {
            try
            {
                DataContractSerializer dcs = new DataContractSerializer(typeof(Report), null, int.MaxValue, false, true, null);
                FileStream fs = new FileStream(@"C:\OfficeReportsReportSample.xml", FileMode.Open);
                XmlDictionaryReader reader = XmlDictionaryReader.CreateTextReader(fs, new XmlDictionaryReaderQuotas());
                Report r = (Report)dcs.ReadObject(reader, true);
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                Console.ReadLine();
            }
        }
 
    }
 
}

References

by ·1 Comment

Let’s finish this trilogy off Revenge Of The Sith style (going through the motions, nobody really cares anymore, etc.). I have to admit to cheating a bit with this one. I used the DocumentReflector tool that comes with the SDK, mainly because my attempts to create a presentation from scratch were proving fruitless and it seems you need a fair amount of code just to create the most basic of presentations.

Given the increased amount of code I’ve decided to provide it as a download rather that display it in the page. You can get it from here.

That’s enough Open XML for now. Something different next time I think.

by ·7 Comments

Introduction

I dislike Excel. A lot. It’s not that it isn’t a useful tool, it’s just that every time it crosses my path someone has tried to make it do something it really doesn’t want to do. Let’s face it, it’s not an RDBMS by any stretch of the imagination and it’s a long way from being a fully functional reporting engine. However, cross my path it does, and it’s likely to do so for the foreseeable future, so I might as well try and play nice with it.

As a follow up to Creating a Word Document with the Open XML SDK 2.0 I though I’d see how easy (or otherwise) it was to create a Workbook using similar tactics.

Creating the Console Application

I’ve added a second Console Application (imaginatively called WorkbookBuilder) to the Document Builder solution created last time round, and this time imported the following namespaces: –

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Firstly, add the following BuildWorkbook method to your new Program class.

private static void BuildWorkbook(string fileName)
{
    try
    {
        using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookPart = s.AddWorkbookPart();
            WorksheetPart worksheetPart = workbookPart.AddNewPart();
            string relId = workbookPart.GetIdOfPart(worksheetPart);
            Workbook workbook = new Workbook();
            FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            DateTime date = new DateTime(2009, 1, 1);
            int salesLastYear = 25185;
            int salesThisYear = 25348;
            for (UInt32 i = 1; i &lt;= 52; i++)
            {
                Row contentRow = CreateContentRow(i, date, salesLastYear, salesThisYear);
                sheetData.AppendChild(contentRow);
                date = date.AddDays(7);
                salesLastYear += (int)(salesLastYear * 0.031);
                salesThisYear += (int)(salesThisYear * 0.027);
            }
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            s.WorkbookPart.Workbook = workbook;
            s.WorkbookPart.Workbook.Save();
            s.Close();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        Console.ReadLine();
    }
}

Second, add the CreateContentRow to the mix: –

private static Row CreateContentRow(UInt32 index, DateTime date, int salesLastYear, int salesThisYear)
{
    Row r = new Row { RowIndex = index };
    Cell cell1 = CreateTextCell("A", index, date.ToString());
    Cell cell2 = CreateNumberCell("B", index, salesLastYear);
    Cell cell3 = CreateNumberCell("C", index, salesThisYear);
    r.Append(cell1);
    r.Append(cell2);
    r.Append(cell3);
    return r;
}

Next, add the CreateTextCell and CreateNumberCell methods: –

private static Cell CreateTextCell(string header, UInt32 index, string text)
{
    Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
    InlineString istring = new InlineString();
    Text t = new Text { Text = text };
    istring.Append(t);
    c.Append(istring);
    return c;
}
 
private static Cell CreateNumberCell(string header, UInt32 index, int number)
{
    Cell c = new Cell { CellReference = header + index };
    CellValue v = new CellValue { Text = number.ToString() };
    c.Append(v);
    return c;
}

Finally, add the call to the BuildWorkbook method from the Main method: –

public static void Main(string[] args)
{
    BuildWorkbook(@"C:\Test.xlsx");
}

Hit F5 to run the application as before.

I’m sure you’ll agree that this is hardly the most involved of Workbooks but I have to admit I’m nonetheless impressed with the speed of execution of these applications. If you’ve ever had to use the Office Object Model you’ll know that just instantiating the Application Classes can be quite time consuming.

Score one for Excel? No, I don’t think so, this isn’t technically Excel!

References