Unable to append a sheet using OpenXml with F# (FSharp)

Question

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>

Answer 1

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])