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(aSheet.SheetId.Value);
        Console.WriteLine(aSheet.Name);
    }
}

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 System;
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 System;
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 System;
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 System;
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} --> Key: {name.Key}");
        }
    }
}

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

References

Things you can do to

• Workbook