在OOXML/Excel电子表格中查找所有外部关系

Question

我正在尝试在OOXML电子表格中注册所有外部关系。下面的代码没有提供任何结果。我做错了什么？我怀疑我找错地方了；WorkSheetParts"；。我使用的是Open XML SDK。

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using (var spreadsheet = SpreadsheetDocument.Open(file, false))
{
var list = spreadsheet.WorkbookPart.WorksheetParts.ToList();
foreach (var item in list)
{
int count = item.ExternalRelationships.Count(); // Register the number of embedded objects
if (count == 0) // If no embedded objects, inform user
{
Console.WriteLine($"--> {count} external relationships");
}
else
{
Console.WriteLine($"--> {count} external relationships");
var parts = item.ExternalRelationships.ToList(); // Register each object to a list
foreach (var part in parts) // Inform user of each object
{
Console.WriteLine(part.Uri.ToString());
Console.WriteLine(part.RelationshipType.ToString());
Console.WriteLine(part.IsExternal.ToString());
Console.WriteLine(part.Container.ToString());
}
}
}
}

Answer 1

TL；DR：您错误地遍历了OOXML电子表格文档结构。

快速修复是使用内置的GetAllParts()扩展方法，然后对每个OpenXmlPart条目检查其ExternalRelationships属性(它是一个延迟求值的枚举对象，不是内存中的集合(，并查找ExternalRelationship对象的非空集合。。。或者单个linq表达式，比如：

static List<ExternalRelationship> GetExternalReferences( SpreadsheetDocument spreadsheet )
{
return spreadsheet
.GetAllParts()
.SelectMany( p => p.ExternalRelationships )
.ToList();
}

作为一个更完整的例子：

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
static void Main()
{
DirectoryInfo root = new DirectoryInfo( @"Test_Data" );
foreach( FileInfo xlsx in root.GetFiles( "*.xlsx", SearchOption.AllDirectories ) )
{
SpreadsheetDocument spreadsheet;
try
{
spreadsheet = SpreadsheetDocument.Open( path: xlsx.FullName, isEditable: false );
}
catch( Exception ex )
{
Console.WriteLine( "File "{0}" cannot be opened: {1}", xlsx.FullName, ex.Message );
continue;
}
using( spreadsheet )
{
List<ExternalRelationship> extRels = spreadsheet
.GetAllParts()
.SelectMany( p => p.ExternalRelationships )
.ToList();
if( extRels.Count > 0 )
{
Console.WriteLine( "File "{0}" has {1:N0} external references:", xlsx.FullName, extRels.Count );
foreach( ExternalRelationship r in extRels )
{
Console.WriteLine( "tUri             : "{0}"", r.Uri );
Console.WriteLine( "tRelationshipType: "{0}"", r.RelationshipType );
Console.WriteLine( "tIsExternal      : {0}", r.IsExternal );
Console.WriteLine( "tContainer       : {0}", r.Container );
Console.WriteLine();
}
}
else
{
Console.WriteLine( "File "{0}" has no external references.", xlsx.FullName );
}
}
}
}

---

使用您发布的Test_Data.zip文件时的输出：

File "Test_DataWorkbook_strict.xlsx" has no external references.
File "Test_DataWorkbook_transitional.xlsx" has no external references.
File "Test_DataAnother folder123.xlsx" has no external references.
File "Test_DataAnother folderCreated as Strict_Now Transitional.xlsx" has no external references.
File "Test_DataAnother folderCreated as Transitional_Now Strict.xlsx" has no external references.
File "Test_DataAnother folderPassword protected.xlsx" cannot be opened: End of Central Directory record could not be found.
File "Test_DataAnother folderWith chains to cells in another spreadsheet.xlsx" has 1 external references:
Uri             : "123.xlsx"
RelationshipType: "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath"
IsExternal      : True
Container       : DocumentFormat.OpenXml.Packaging.ExternalWorkbookPart
File "Test_DataAnother folderWith data connection.xlsx" has no external references.
File "Test_DataAnother folderWith embedded objects.xlsx" has 1 external references:
Uri             : "file:///C:/Users/Sepideh/Desktop/Test_Data/Another%20folder/123.xlsx"
RelationshipType: "http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject"
IsExternal      : True
Container       : DocumentFormat.OpenXml.Packaging.ExternalWorkbookPart
File "Test_DataAnother folderAnother folder1234.xlsx" has no external references.
File "Test_DataAnother folderAnother folderrandom1.xlsx" has no external references.
File "Test_DataAnother folderAnother folder 2New.xlsx" has no external references.
File "Test_DataAnother folderAnother folderLast foldersome_filename.xlsx" has no external references.