Use Open XML SDK to read an Excel Chart Template (*.crtx)

Question

I'm trying to read the colors used in an Excel Chart Template (mychart.crtx). But I can't figure out how to open the file using the Open XML toolkit. It doesn't appear to be possible using SDK Tool.

Can it be done?

Answer 1

This is indeed not possible with the Open XML SDK, because it only provides the WordprocessingDocument (for .docx etc.), SpreadsheetDocument (for .xlsx etc.), and PresentationDocument (for .pptx etc.) classes for opening Word, Excel, and PowerPoint documents and templates.

However, Office documents and your Excel Chart Templates (.crtx) are all based on the Open Packaging Conventions (OPC). You can use the classes provided in the System.IO.Packaging namespace to work with any OPC-based document, including those Excel Chart Templates.

The following picture shows the structure of a sample ChartTemplate.crtx that I created for testing purposes. I used the Open XML Package Editor for Modern Visual Studios to inspect that package.

Using the System.IO.Packaging classes, the Package class represents whole packages (e.g., ChartTemplate.crtx). The PackagePart class represents XML and other files contained in a package. Each PackagePart has a URI (e.g., /chart/chart.xml, /chart/charts/colors1.xml), a content type, and zero or more relationships to other parts.

The following code snippet opens the sample Package, gets the PackagePart, loads the root XML element from the part and makes certain assertions to demonstrate what it got.

[Fact]
public void LoadRootElement_Chart_SuccessfullyLoaded()
{
    using Package package = Package.Open("Resources\\ChartTemplate.crtx", FileMode.Open, FileAccess.Read);
    PackagePart packagePart = package.GetPart(new Uri("/chart/chart.xml", UriKind.Relative));

    XElement rootElement = LoadRootElement(packagePart);

    Assert.Equal(C.chartSpace, rootElement.Name);
    Assert.NotEmpty(rootElement.Elements(C.chart).Elements(C.title));
    Assert.NotEmpty(rootElement.Elements(C.chart).Elements(C.plotArea));
    Assert.NotEmpty(rootElement.Elements(C.chart).Elements(C.legend));
}

The LoadRootElement() method is straightforward:

private static XElement LoadRootElement(PackagePart packagePart)
{
    using Stream stream = packagePart.GetStream(FileMode.Open, FileAccess.Read);
    return XElement.Load(stream);
}

And I've created a helper class C to provide the required XML namespace and names for use with the XElement class, which, like XNamespace and XName, is defined in the System.Xml.Linq namespace.

private static class C
{
    public static readonly XNamespace c = "http://schemas.openxmlformats.org/drawingml/2006/chart";

    public static readonly XName chart = c + "chart";
    public static readonly XName chartSpace = c + "chartSpace";
    public static readonly XName lang = c + "lang";
    public static readonly XName legend = c + "legend";
    public static readonly XName plotArea = c + "plotArea";
    public static readonly XName title = c + "title";

    public static readonly XName val = "val";
}

As always, the full source code can be found in my CodeSnippets GitHub repository. Look for the ChartTemplateTests class.