•  
    March 15th, 2009stuartwhiteford.NET, C#

    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.

    Tags: , , ,
  •  
    March 2nd, 2009stuartwhiteford.NET, C#

    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 <= 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

    Tags: , , ,
  •  
    February 25th, 2009stuartwhiteford.NET, C#

    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

    Tags: , , ,