-
Notifications
You must be signed in to change notification settings - Fork 2
Open XML SDK Code Examples
Writing code in C# with Open XML SDK.
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");
}
}
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");
}
}
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.
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()}");
}
}
}
}
Check for links to other spreadsheets such as linked values in cells and linked OLE objects (unembedded). It does not check for external data connections. Check for RTD functions is unknown, because I do not have a test sample with RTD functions.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
List<ExternalRelationship> extRels = spreadsheet // Find all external relationships
.GetAllParts()
.SelectMany(p => p.ExternalRelationships)
.ToList();
if (extRels.Count > 0) // If external relationships
{
int extrel_number = 0;
Console.WriteLine($"--> {extRels.Count} external relationships detected");
foreach (ExternalRelationship rel in extRels)
{
extrel_number++;
Console.WriteLine($"--> #{extrel_number} external relationship detected");
Console.WriteLine($"----> ID: {rel.Id}");
Console.WriteLine($"----> Target URI: {rel.Uri}");
Console.WriteLine($"----> Relationship type: {rel.RelationshipType}");
Console.WriteLine($"----> External: {rel.IsExternal}");
Console.WriteLine($"----> Container: {rel.Container}");
}
}
else // If no external relationships, inform user
{
Console.WriteLine($"--> {extRels.Count} external relationships");
}
}
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Validation;
using (var spreadsheet = SpreadsheetDocument.Open(filepath, false))
{
var validator = new OpenXmlValidator();
var validation_errors = validator.Validate(spreadsheet).ToList();
int error_count = validation_errors.Count;
int error_number = 0;
if (validation_errors.Any()) // If errors
{
Console.WriteLine($"--> File format is invalid - Spreadsheet has {error_count} validation errors");
foreach (var error in validation_errors)
{
error_number++;
Console.WriteLine($"--> Error {error_number}");
Console.WriteLine("----> Description: " + error.Description);
Console.WriteLine("----> ErrorType: " + error.ErrorType);
Console.WriteLine("----> Node: " + error.Node);
Console.WriteLine("----> Path: " + error.Path.XPath);
Console.WriteLine("----> Part: " + error.Part.Uri);
if (error.RelatedNode != null)
{
Console.WriteLine("----> Related Node: " + error.RelatedNode);
Console.WriteLine("----> Related Node Inner Text: " + error.RelatedNode.InnerText);
}
}
}
else // If no errors
{
Console.WriteLine("--> File format is valid");
}
}