无法使用 OpenXml 和 F# (FSharp) 附加工作表

发布于 11-02 05:18 字数 2569 浏览 2 评论 0原文

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>

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

〆凄凉。2024-11-09 05:18:16

问题非常微妙,存在于对 Worksheet 构造函数和 Sheets.Append 方法的调用中。这两个方法都是重载的,并且可以采用 seq 或任意数量的单个 OpenXmlElement(通过 [) ]/params 数组)。不同之处在于 OpenXmlElement 类型本身实现 seq 接口。

在 C# 中,当您调用 new Worksheet(new SheetData()) 时,编译器的重载决策会选择第二个重载,隐式创建一个包含 SheetData 的单元素数组价值。但是,在 F# 中,由于 SheetData 类实现 IEnumerable,因此选择第一个重载,它通过枚举创建一个新的 WorkSheet SheetData内容,这不是您想要的。

要解决此问题,您需要设置调用,以便它们使用其他重载(下面的第一个示例)或显式创建单例序列(下面的第二个示例):

worksheetPart.Worksheet <- new Worksheet(new SheetData() :> OpenXmlElement)
...
sheets.Append([sheet :> OpenXmlElement])

The problem is very subtle, and is in your calls to the Worksheet constructor and the Sheets.Append method. Both of these methods are overloaded, and can take either a seq<OpenXmlElement> or any number of individual OpenXmlElements (via a [<System.ParamArray>]/params array). The twist is that the OpenXmlElement type itself implements the seq<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 the SheetData value. However, in F#, since the SheetData class implements IEnumerable<OpenXmlElement>, the first overload is chosen, which creates a new WorkSheet by enumerating the contents of the SheetData, 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):

worksheetPart.Worksheet <- new Worksheet(new SheetData() :> OpenXmlElement)
...
sheets.Append([sheet :> OpenXmlElement])
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文