无法使用 OpenXml 和 F# (FSharp) 附加工作表
OpenXml 文档中的 CreateSpreadsheetWorkbook 示例方法直接转换为 F#。问题似乎出在 Sheets 对象的 Append 方法上。代码执行时没有错误,但生成的 xlsx 文件缺少应附加的内部 Xml,并且 Excel 无法读取该文件。我怀疑问题源于将功能性 F# 结构转换为 System.Collections 类型,但我没有直接证据证明这一点。
我已经在 C# 和 VB.NET 中运行了类似的代码(即文档示例),它完美执行并创建了一个可读的完整 xlsx 文件。
我知道我可以直接处理 XML,但我想了解 F# 和 OpenXml 之间不匹配的本质。有什么建议吗?
代码几乎直接来自示例:
namespace OpenXmlLib
open System
open DocumentFormat
open DocumentFormat.OpenXml
open DocumentFormat.OpenXml.Packaging
open DocumentFormat.OpenXml.Spreadsheet
module OpenXmlXL =
// this function overwrites an existing file without warning!
let CreateSpreadsheetWorkbook (filepath: string) =
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
let spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
// Add a WorkbookPart to the document.
let workbookpart = spreadsheetDocument.AddWorkbookPart()
workbookpart.Workbook <- new Workbook()
// Add a WorksheetPart to the WorkbookPart.
let worksheetPart = workbookpart.AddNewPart<WorksheetPart>()
worksheetPart.Worksheet <- new Worksheet(new SheetData())
// Add Sheets to the Workbook.
let sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets())
// Append a new worksheet and associate it with the workbook.
let sheet = new Sheet()
sheet.Id <- stringValue(spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart))
//Console.WriteLine(sheet.Id.Value)
sheet.SheetId <- UInt32Value(1u)
// Console.WriteLine(sheet.SheetId.Value)
sheet.Name <- StringValue("TestSheet")
//Console.WriteLine(sheet.Name.Value)
sheets.Append (sheet)
// Console.WriteLine("Sheets: {0}", sheets.InnerXml.ToString())
workbookpart.Workbook.Save()
spreadsheetDocument.Close()
工作表已创建,但为空:
sheet.xml:
<?xml version="1.0" encoding="utf-8" ?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />
workbook.xml:
<?xml version="1.0" encoding="utf-8" ?>
- <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
- <x:sheets>
<x:sheet name="TestSheet" sheetId="1" r:id="R263eb6f245a2497e" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
</x:sheets>
</x:workbook>
The CreateSpreadsheetWorkbook example method from the OpenXml documentation does translate directly to F#. The problem seems to be the Append method of the Sheets object. The code executes without error, but the resulting xlsx file is missing the inner Xml which should have been appended, and the file is unreadable by Excel. I suspect the problem stems from the conversion of functional F# structures into a System.Collections type, but I do not have direct evidence for this.
I have run similar code in C# and VB.NET (i.e. the documentation example) and it executes perfectly and creates a readable, complete xlsx file.
I know that I could deal with the XML directly, but I would like to understand the nature of the mismatch between F# and OpenXml. Any suggestions?
The code is almost directly from the example:
namespace OpenXmlLib
open System
open DocumentFormat
open DocumentFormat.OpenXml
open DocumentFormat.OpenXml.Packaging
open DocumentFormat.OpenXml.Spreadsheet
module OpenXmlXL =
// this function overwrites an existing file without warning!
let CreateSpreadsheetWorkbook (filepath: string) =
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
let spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
// Add a WorkbookPart to the document.
let workbookpart = spreadsheetDocument.AddWorkbookPart()
workbookpart.Workbook <- new Workbook()
// Add a WorksheetPart to the WorkbookPart.
let worksheetPart = workbookpart.AddNewPart<WorksheetPart>()
worksheetPart.Worksheet <- new Worksheet(new SheetData())
// Add Sheets to the Workbook.
let sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets())
// Append a new worksheet and associate it with the workbook.
let sheet = new Sheet()
sheet.Id <- stringValue(spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart))
//Console.WriteLine(sheet.Id.Value)
sheet.SheetId <- UInt32Value(1u)
// Console.WriteLine(sheet.SheetId.Value)
sheet.Name <- StringValue("TestSheet")
//Console.WriteLine(sheet.Name.Value)
sheets.Append (sheet)
// Console.WriteLine("Sheets: {0}", sheets.InnerXml.ToString())
workbookpart.Workbook.Save()
spreadsheetDocument.Close()
The sheet is created, but empty:
sheet.xml:
<?xml version="1.0" encoding="utf-8" ?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />
workbook.xml:
<?xml version="1.0" encoding="utf-8" ?>
- <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
- <x:sheets>
<x:sheet name="TestSheet" sheetId="1" r:id="R263eb6f245a2497e" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
</x:sheets>
</x:workbook>
问题非常微妙,存在于对
Worksheet
构造函数和Sheets.Append
方法的调用中。这两个方法都是重载的,并且可以采用seq
或任意数量的单个OpenXmlElement
(通过[) ]
/params
数组)。不同之处在于OpenXmlElement
类型本身实现seq
接口。在 C# 中,当您调用
new Worksheet(new SheetData())
时,编译器的重载决策会选择第二个重载,隐式创建一个包含SheetData
的单元素数组价值。但是,在 F# 中,由于SheetData
类实现IEnumerable
,因此选择第一个重载,它通过枚举创建一个新的WorkSheet
SheetData
的内容,这不是您想要的。要解决此问题,您需要设置调用,以便它们使用其他重载(下面的第一个示例)或显式创建单例序列(下面的第二个示例):
The problem is very subtle, and is in your calls to the
Worksheet
constructor and theSheets.Append
method. Both of these methods are overloaded, and can take either aseq<OpenXmlElement>
or any number of individualOpenXmlElement
s (via a[<System.ParamArray>]
/params
array). The twist is that theOpenXmlElement
type itself implements theseq<OpenXmlElement>
interface.In C#, when you call
new Worksheet(new SheetData())
, the compiler's overload resolution picks the second of the overloads, implicitly creating a one-element array containing theSheetData
value. However, in F#, since theSheetData
class implementsIEnumerable<OpenXmlElement>
, the first overload is chosen, which creates a newWorkSheet
by enumerating the contents of theSheetData
, which is not what you want.To fix this, you need to set up your calls so that they use the other overload (first example below) or explicitly create a singleton sequence (second example below):