Open XML SDK Code Examples

Writing code in C# with Open XML SDK.

Find IDs and names of all worksheets

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

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
    Sheets allSheets = spreadsheet.WorkbookPart.Workbook.Sheets;
    foreach (Sheet aSheet in allSheets)
    {
        Console.WriteLine($"--> ID: {aSheet.SheetId.Value}, Name: {aSheet.Name}");
    }
    if (allSheets == null)
    {
        Console.WriteLine("--> No worksheets exist");
    }
}

Find conformance

The OOXML standard specifies, that strict conformance must be specified in a conformance class, but if the spreadsheet has Transitional conformance it may be specified in the same class, but the conformance class may be left out alltogether and if so, then the spreadsheet defaults to Transitional conformance. Therefore, we have no sure way of determining Transitional conformance. It may only be determined through logic.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using (var spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
    var info = spreadsheet.WorkbookPart.Workbook;
    if (info != null)
    {
        Console.WriteLine(info.Conformance); // Value may be "strict" or "transitional"
    }
    else
    {
        Console.WriteLine(transitional); // By logic, value is "transitional"
    }
}

Another method to find conformance is through the boolean property "StrictRelationshipFound".

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using (var spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
    bool? strict = spreadsheet.StrictRelationshipFound; // a boolean
    if (strict == true)
    {
        Console.WriteLine("strict");
    }
    else
    {
        Console.WriteLine("transitional");
    }
}

Find namespaces

Woorkbook

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using (var spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
    var info = spreadsheet.WorkbookPart.Workbook.NamespaceDeclarations;
    foreach (var item in info)
    {
        Console.WriteLine(item);
    }
}

Worksheets

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using (var spreadsheet = SpreadsheetDocument.Open(file, false))
{
    var allSheets = spreadsheet.WorkbookPart.Workbook.Sheets;
    foreach (Sheet aSheet in allSheets)
    {
        Console.WriteLine(aSheet.Name);
        Worksheet sheet = ((WorksheetPart)spreadsheet.WorkbookPart.GetPartById($"rId{aSheet.SheetId}")).Worksheet;

        var names = sheet.NamespaceDeclarations.ToList();
        foreach (var name in names)
        {
            Console.WriteLine($"Namespace: {name.Value} --> XML Prefix: {name.Key}");
        }
    }
}

Use AddNamespaceDeclaration(String, String) and RemoveNamespaceDeclaration(String) to change namespaces.

Find all embedded objects (OLE and images)

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using (var spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
    var list = spreadsheet.WorkbookPart.WorksheetParts.ToList();
    foreach (var item in list)
    {
        int count_ole = item.EmbeddedObjectParts.Count(); // Register the number of OLE
        int count_image = item.ImageParts.Count(); // Register number of images
        int count = count_ole + count_image; // Sum
        if (count == 0) // If no embedded objects, inform user
        {
            embedobj_message = $"--> {count} embedded objects detected";
            Console.WriteLine(embedobj_message);
            return embedobj_message;
        }
        else
        {
            embedobj_message = $"--> {count} embedded objects detected";
            Console.WriteLine(embedobj_message);
            var embed_ole = item.EmbeddedObjectParts.ToList(); // Register each OLE to a list
            var embed_image = item.ImageParts.ToList(); // Register each image to a list
            int embedobj_no = 0;
            foreach (var part in embed_ole) // Inform user of each object
            {
                embedobj_no++;
                Console.WriteLine($"--> Embedded object #{embedobj_no}");
                Console.WriteLine($"----> Content Type: {part.ContentType.ToString()}");
                Console.WriteLine($"----> URI: {part.Uri.ToString()}");

            }
            foreach (var part in embed_image) // Inform user of each object
            {
                embedobj_no++;
                Console.WriteLine($"--> Embedded object #{embedobj_no}");
                Console.WriteLine($"----> Content Type: {part.ContentType.ToString()}");
                Console.WriteLine($"----> URI: {part.Uri.ToString()}");
            }
        }
    }
}

References

Things you can do to

• Workbook
• Worksheets