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

by ·9 Comments

Introduction

I’m always eager to find ways of making my life easier, so recently I’ve been searching for a method of creating a Microsoft Word document using purely managed code (none of that Object Model awfulness). I’d been playing around with version 1.0 of the Open XML SDK and while this works fine, it’s not strongly typed, so requires you to manipulate the XML directly.

Brian Jones has an excellent post about the SDK in his blog: – http://blogs.msdn.com/brian_jones/archive/2008/10/06/open-xml-format-sdk-2-0.aspx, which also includes an example of how to create a basic Word document in C#. This post hopes to demonstrate how to create a document that includes a header and an image.

If you’re following along with this, you’ll need the SDK if you don’t have it already, you can get it from: – http://go.microsoft.com/fwlink/?LinkId=127912.

Creating the Console Application

Open up Visual Studio and create a new Console Application. In this demo I’ve called it DocumentBuilder. Next add a reference to the DocumentFormat.OpenXML and WindowsBase dlls.

In this example, to include the image, I’ve stored it as a Resource, so add a new Resources File to your project (I’ve called it DocumentResources.resx) and add your image, the image should get copied into a new Resources folder in your project.

Add these using statements to the Program.cs file: –

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using d = DocumentFormat.OpenXml.Drawing;

Now add the following constants to the Program class: –

private const double EMU_PER_PIXEL = 9525;
private const string GRAPHIC_DATA_URI = @"http://schemas.openxmlformats.org/drawingml/2006/picture";

To keep a bit of structure about the program I’ve implemented three methods, one to build the document itself, one to build the header and finally one to insert the image. Firstly, add the following code for the BuildDocument method: –

private static void BuildDocument(string fileName)
{
    using (WordprocessingDocument w = WordprocessingDocument.Create(fileName, WordprocessingDocumentType.Document))
    {
        MainDocumentPart mp = w.AddMainDocumentPart();
        Document d = new Document();
        Body b = new Body();
        Paragraph p = new Paragraph();
        Run r = new Run();
        Text t = new Text();
        t.Text = "This is some body text.";
        r.Append(t);
        p.Append(r);
        b.Append(p);
        HeaderPart hp = mp.AddNewPart<HeaderPart>();
        string headerRelationshipID = mp.GetIdOfPart(hp);
        SectionProperties sectPr = new SectionProperties();
        HeaderReference headerReference= newHeaderReference();
        headerReference.Id = headerRelationshipID;
        headerReference.Type = HeaderFooterValues.Default;
        sectPr.Append(headerReference);
        b.Append(sectPr);
        d.Append(b);
        hp.Header = BuildHeader(hp, "This is some header text.");
        hp.Header.Save();
        mp.Document = d;
        mp.Document.Save();
        w.Close();
    }
}

Next, add the following for the BuildHeader method: –

private static Header BuildHeader(HeaderPart hp, string title)
{
    // Add an ImagePart.
    ImagePart ip = hp.AddImagePart(ImagePartType.Jpeg);
    string imageRelationshipID = hp.GetIdOfPart(ip);
    using (Stream imgStream = ip.GetStream())
    {
        System.Drawing.Bitmap logo = DocumentResources.sw;
        logo.Save(imgStream, System.Drawing.Imaging.ImageFormat.Jpeg);
    }
    Header h = new Header();
    Paragraph p = new Paragraph();
    Run r = new Run();
    Drawing drawing = BuildImage(imageRelationshipID, "sw.gif", 48, 48);
    r.Append(drawing);
    p.Append(r);
    r = new Run();
    RunProperties rPr = new RunProperties();
    TabChar tab = new TabChar();
    Bold b = new Bold();
    Color color = new Color { Val = "006699" };
    FontSize sz = new FontSize { Val = 40 };
    Text t = new Text { Text = title };
    rPr.Append(b);
    rPr.Append(color);
    rPr.Append(sz);
    r.Append(rPr);
    r.Append(tab);
    r.Append(t);
    p.Append(r);
    h.Append(p);
    return h;
}

Lastly, add the BuildImage method: –

private static Drawing BuildImage(string imageRelationshipID, string imageName,
    int pixelWidth, int pixelHeight)
{
    int emuWidth = (int)(pixelWidth * EMU_PER_PIXEL);
    int emuHeight = (int)(pixelHeight * EMU_PER_PIXEL);
    Drawing drawing = new Drawing();
    d.Wordprocessing.Inline inline = new d.Wordprocessing.Inline { DistanceFromTop = 0, DistanceFromBottom = 0, DistanceFromLeft = 0, DistanceFromRight = 0 };
    d.Wordprocessing.Anchor anchor = new d.Wordprocessing.Anchor();
    d.Wordprocessing.SimplePosition simplePos = new d.Wordprocessing.SimplePosition { X = 0, Y = 0 };
    d.Wordprocessing.Extent extent = new d.Wordprocessing.Extent { Cx = emuWidth, Cy = emuHeight };
    d.Wordprocessing.DocProperties docPr = new d.Wordprocessing.DocProperties { Id = 1, Name = imageName };
    d.Graphic graphic = new d.Graphic();
    // We don’t have to hard code a URI anywhere else in the document but if we don’t do it here 
    // we end up with a corrupt document.
    d.GraphicData graphicData = new d.GraphicData { Uri = GRAPHIC_DATA_URI };
    d.Pictures.Picture pic = new d.Pictures.Picture();
    d.Pictures.NonVisualPictureProperties nvPicPr = new d.Pictures.NonVisualPictureProperties();
    d.Pictures.NonVisualDrawingProperties cNvPr = new d.Pictures.NonVisualDrawingProperties { Id = 2, Name = imageName };
    d.Pictures.NonVisualPictureDrawingProperties cNvPicPr = new d.Pictures.NonVisualPictureDrawingProperties();
    d.Pictures.BlipFill blipFill = new d.Pictures.BlipFill();
    d.Blip blip = new d.Blip { Embed = imageRelationshipID };
    d.Stretch stretch = new d.Stretch();
    d.FillRectangle fillRect = new d.FillRectangle();
    d.Pictures.ShapeProperties spPr = new d.Pictures.ShapeProperties();
    d.Transform2D xfrm = new d.Transform2D();
    d.Offset off = new d.Offset { X = 0, Y = 0 };
    d.Extents ext = new d.Extents { Cx = emuWidth, Cy = emuHeight };
    d.PresetGeometry prstGeom = new d.PresetGeometry { Preset = d.ShapeTypeValues.Rectangle };
    d.AdjustValueList avLst = new d.AdjustValueList();
    xfrm.Append(off);
    xfrm.Append(ext);
    prstGeom.Append(avLst);
    stretch.Append(fillRect);
    spPr.Append(xfrm);
    spPr.Append(prstGeom);
    blipFill.Append(blip);
    blipFill.Append(stretch);
    nvPicPr.Append(cNvPr);
    nvPicPr.Append(cNvPicPr);
    pic.Append(nvPicPr);
    pic.Append(blipFill);
    pic.Append(spPr);
    graphicData.Append(pic);
    graphic.Append(graphicData);
    inline.Append(extent);
    inline.Append(docPr);
    inline.Append(graphic);
    drawing.Append(inline);
    return drawing;
}

All that remains is to call the BuildDocument from the Main method: –

public static void Main(string[] args)
{
    BuildDocument(@"C:\TempTest.docx");
}

Press F5 to run your app and you should get your Word document. If you’re using your own image in the header you’ll notice that it’s a bit mis-shapen (unless you happen to have picked a 48 x 48 pixel image). To correct this just change the pixelHeight and pixelWidth parameters passed into the BuildImage method to suit.

References

by ·Comments Off on TPEG and Google Maps

Introduction

If, like me, you have a bizarre fascination with maps you can’t have failed to notice that Google Maps are appearing on an ever-increasing number of websites, some for valid and useful reasons, some not so much. Now, if you head over to https://maps.google.com/ and zoom in on a major US city you might notice that there is a Traffic button at the top right of the map. Click this and you’ll get traffic data overlaid on the map, looking a bit like Figure 1 below.


Figure 1: New York Traffic

Now if you zoom in somewhere in the UK you will get this traffic info, unless you’re here in Scotland, but the overlays seem a bit off to me and I’ve yet to see any icons that will display message windows with more information. I’ve no doubt that Google will address these issues in due course, but in the meantime there is a way of getting detailed traffic info (albeit without the nice coloured overlays) onto a Google Map, namely TPEG.

What’s TPEG?

TPEG stands for Transport Protocol Experts Group and they have agreed standards for Binary and XML transmission of Traffic and Travel Information (TTI). For more information have a look at the TPEG links in the References section at the end of this post. In the UK, Auntie Beeb is helpfully publishing TPEG data in both Binary and XML formats (and you thought they were all about scamming phone voters). The XML document for road traffic information lives at http://www.bbc.co.uk/travelnews/tpeg/en/local/rtm/rtm_tpeg.xml and there’s a corresponding public transport document at http://www.bbc.co.uk/travelnews/tpeg/en/pti/pti_tpeg.xml. In the next few steps I’ll demonstrate how to display a subset of this data on a very simple ASP.NET website using Google Maps.

Creating the Website

I’m going to be using Visual Studio 2008 for this but 2005 will do just the same. Open up Visual Studio and create a new Web Site project. In addition to the App_Data folder that’s created for you, add an App_Code and a Bin folder to the project. Firstly, download the zip file from Subgurim.NET which wraps the functionality of a Google Map into an ASP.NET web control and extract the GMaps.dll into the Websites Bin folder.

Next, get yourself a copy of the road traffic information XML file (just download it from the link above) and copy it into the App_Data folder.

Now, to make working with the data a little bit easier, we’ll create a schema definition for the XML (we can’t download one because it’s based on a DTD currently) and then use xsd.exe to get ourselves a class file. Open up the rtm_tpeg file in Visual Studio, go to the XML menu and select Create Schema. When you get the resulting rtm_tpeg.xsd file on the screen save that into the App_Data folder too.

Open the Visual Studio Command Prompt, navigate to your App_Data folder, and execute the following command to create a C# class in your App_Code folder: xsd.exe rtm_tpeg.xsd /c /language:cs /o:../App_Code. If you’re working in VB.NET simply change the language switch to /language:vb. The structure of your Website should new resemble Figure 2.


Figure 2: Website Structure

Now, we can add the map control to our web page and start adding information windows to it. Open up your default.aspx page and edit it so that it resembles the following: –

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="default.aspx.cs" Inherits="_default" %>
<%@ Register Assembly="GMaps" Namespace="Subgurim.Controles" TagPrefix="gmap" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml">
    <head runat="server">
        <title>TPEG and Google Maps</title>
        <style type="text/css">
        html, body
        {
            margin: 0px 0px 0px 0px;
            padding: 0px 0px 0px 0px;
            height: 100%;
        }
        v:*
        {
            behavior: url(#default#VML);
        }
        </style>
    </head>
    <body>
        <form id="mapForm" runat="server">
            <gmap:GMap ID="map" runat="server" Width="100%" Height="100%" />
        </form>
    </body>
</html>

Open the default.aspx.cs and modify it to look like: –

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;
using Subgurim.Controles;
 
public partial class _default : System.Web.UI.Page
{
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            map.setCenter(new GLatLng(55.864536, -4.263103));
            map.GZoom = 12;
            map.addControl(new GControl(GControl.preBuilt.SmallMapControl));
            string uri = “http://www.bbc.co.uk/travelnews/tpeg/en/local/rtm/rtm_tpeg.xml”;
            BindData(uri);
        }
    }
 
    private void BindData(string uri)
    {
        XmlDocument xml = new XmlDocument();
        xml.Load(uri);
        XmlNodeReader nr = new XmlNodeReader(xml.DocumentElement);
        XmlSerializer xs = new XmlSerializer(typeof(tpeg_document));
        tpeg_document tpeg = (tpeg_document)xs.Deserialize(nr);
        for (int i = 0; i < tpeg.Items.Length; i++)
        {
            if (tpeg.Items[i] is tpeg_message)
            {
                tpeg_message message = tpeg.Items[i] as tpeg_message;
                if (message.Item is road_traffic_message)
                {
                    road_traffic_message rtmessage = message.Item as road_traffic_message;
                    for (int j = 0; j < rtmessage.Items.Length; j++)
                    {
                        if (rtmessage.Items[j] is location_container)
                        {
                            location_container loc = rtmessage.Items[j] as location_container;
                            for (int k = 0; k < loc.Items.Length; k++)
                            {
                                if (loc.Items[k] is location_coordinates)
                                {
                                    location_coordinates coords = loc.Items[k] as location_coordinates;
                                    for (int l = 0; l < coords.Items.Length; l++)
                                    {
                                        if (coords.Items[l] is WGS84)
                                        {
                                            WGS84 latLong = coords.Items[l] as WGS84;
                                            double latitude = double.Parse(latLong.latitude);
                                            double longitude = double.Parse(latLong.longitude);
                                            StringBuilder sb = new StringBuilder();
                                            sb.Append(“<p>”);
                                            sb.Append(message.summary[0].Value);
                                            sb.Append(“</p>”);
                                            GMarker marker = new GMarker(new GLatLng(latitude, longitude));
                                            map.addInfoWindow(new GInfoWindow(marker, sb.ToString(), false));
                                            map.addGMarker(marker);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Briefly, we set centre the map on Glasgow, set the zoom level and add the small map control. Set the uri parameter to the XML TPEG feed (if you find that this takes a while to download you can always use the saved version in your App_Data folder). The BindData() method de-serialises the XML into our tpeg_document class and then starts to loop through the road traffic messages in the document. Without going into the gory details of the TPEG schema, the message contains a summary, which we write to the infoWindow, and location co-ordinates which allow us to add the marker icon in the right place.

Hit the F5 key to run the application, allowing Visual Studio to modify your web.config file if you like, and marvel (if you’re easily impressed) at live traffic information on a Google Map.


Figure 3: Glasgow Traffic

What Next

You’ll notice on the figure above that you often get two markers closely spaced on the same stretch of road. These tend to be the start and end points of heavy congestion and Google would display these as coloured overlays on the map. We can render a polyline on top of the map given a start and end point but the problem with that is it will be a straight line and won’t follow the road (probably). If you can find out how to overlay a line following the road then you’d be getting pretty close to what Google can do.

References

Maps

TPEG