Create Open XML Spreadsheet in F# (update)

I have updated the code snippet for the creation of a spreadsheet in F#. I rewritten it in a more functional way an I have added an test with data.

1: //reference to the Open Office SDK

 2: #r @"C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll"

 3: //reference to the package

 4: #r "WindowsBase"

 5:

 6: open DocumentFormat.OpenXml

 7: open DocumentFormat.OpenXml.Packaging

 8: open DocumentFormat.OpenXml.Spreadsheet

 9:

10: let createSpreadsheet (filepath:string) (sheetName:string) (sheetData:SheetData) =

11:     // Create a spreadsheet document by supplying the filepath.

12:     // By default, AutoSave = true, Editable = true, and Type = xlsx.

13:    

14:     using (SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)) (fun spreadsheetDocument ->

15:

16:     // Add a WorkbookPart to the document.

17:     let workbookPart = spreadsheetDocument.AddWorkbookPart(Workbook = new Workbook())

18:

19:     // Add a WorksheetPart to the WorkbookPart.

20:     // http://stackoverflow.com/questions/5702939/unable-to-append-a-sheet-using-openxml-with-f-fsharp

21:     let worksheetPart = workbookPart.AddNewPart<WorksheetPart>()

22:     

23:     worksheetPart.Worksheet <- new Worksheet(sheetData:> OpenXmlElement)

24:

25:     // Add Sheets to the Workbook.

26:     let sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets())

27:

28:     // Append a new worksheet and associate it with the workbook.

29:     let sheet = new Sheet(  Id =  StringValue(spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)),

30:                             SheetId =  UInt32Value(1u),

31:                             Name = StringValue(sheetName)

32:                             )

33:     [sheet :> OpenXmlElement] |> sheets.Append

34:     )

35:

36: //helpers

37: let createCellReference (header:string) (index:int) =

38:     StringValue(header + string(index))

39:

40: let createNumberCell number (header:string) (index:int) =

41:     let cell = new Cell(DataType = EnumValue(CellValues.Number), CellReference = createCellReference header index)

42:     let value = new CellValue(Text = number.ToString())

43:     value |> cell.AppendChild|> ignore

44:     cell :> OpenXmlElement

45:

46: let createTextCell text (header:string) (index:int) =

47:     let cell = new Cell(DataType = EnumValue(CellValues.InlineString), CellReference = createCellReference header index)

48:     let inlineString = new InlineString()

49:     let t = new Text(Text = text)

50:     t |> inlineString.AppendChild |> ignore

51:     inlineString |> cell.AppendChild|> ignore

52:     cell :> OpenXmlElement

53:

54: let createContentRow (text, (number1:int), (number2:int), (index:int)) =

55:     let row = new Row(RowIndex = UInt32Value(uint32(index)))

56:     let cell1 = createTextCell text "A" index

57:     let cell2 = createNumberCell number1 "B" index

58:     let cell3 = createNumberCell number2 "C" index

59:     cell1 |> row.Append

60:     cell2 |> row.Append

61:     cell3 |> row.Append

62:     row :> OpenXmlElement

63:

64: //test

65: let createTestSheetData =

66:     let sheetData = new SheetData()

67:     ("test1", 123, 456, 1) |> createContentRow |> sheetData.AppendChild |> ignore

68:     ("test2", 35, 1231, 2) |> createContentRow |> sheetData.AppendChild |> ignore

69:     ("test3", 345, 21, 3) |> createContentRow |> sheetData.AppendChild |> ignore

70:     sheetData

71:

72: let testData = createTestSheetData

73: let result = createSpreadsheet @"D:\Tmp\test.xlsx" "test" testData;;

74:

75:

Create in F# XDocument with XDeclaration and namespace

In this example I will create the following XML in F#:

<?xml version="1.0" encoding="utf-8"?>

<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">

  <w:body>

    <w:p>

      <w:r>

        <w:t>This is a test</w:t>

      </w:r>

    </w:p>

  </w:body>

</w:document>

Transferring C#-code is not straightforward because the constructor of XDocument does not accept the following code: new  XDocument(new XDeclaration("1.0", "utf-8", "true"), new XElement("document"))

//First create some helpers:

let xnameNs str ns = XName.Get(str, ns)

let xname str =

    xnameNs str "http://schemas.openxmlformats.org/wordprocessingml/2006/main"

let ns = XNamespace.Get("http://schemas.openxmlformats.org/wordprocessingml/2006/main")

let dec = new XDeclaration("1.0", "utf-8", "true")

let el =

    new XElement(xname "document",

        new XAttribute(XNamespace.Xmlns + "w", ns),

            new XElement(xname "body",

                new XElement(xname "p",

                    new XElement(xname "r",

                        new XElement(xname "t", "This is a test")

                        ))))

//Add them together:

let document = 

    let doc =new XDocument(dec)

    doc.AddFirst(el)

    doc

//And test:

document.Save(@"D:\Tmp\test1.xml")

Create Open XML Spreadsheet in F#

I also created a spreadsheet with the Open XML SDK and F# and posted the snippet at fssnip.net.

1: //reference to the Open Office SDK

 2: #r @"C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll"

 3: //reference to the package

 4: #r "WindowsBase"

 5:

 6: open DocumentFormat.OpenXml

 7: open DocumentFormat.OpenXml.Packaging

 8: open DocumentFormat.OpenXml.Spreadsheet

 9:

10: let createSpreadsheet (filepath:string) (sheetName:string) =

11:     // Create a spreadsheet document by supplying the filepath.

12:     // By default, AutoSave = true, Editable = true, and Type = xlsx.

13:    

14:     using (SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)) (fun spreadsheetDocument ->

15:

16:     // Add a WorkbookPart to the document.

17:     let workbookpart = spreadsheetDocument.AddWorkbookPart()

18:     workbookpart.Workbook <- new Workbook()

19:

20:     // Add a WorksheetPart to the WorkbookPart.

21:     // http://stackoverflow.com/questions/5702939/unable-to-append-a-sheet-using-openxml-with-f-fsharp

22:     let worksheetPart = workbookpart.AddNewPart<WorksheetPart>()

23:     worksheetPart.Worksheet <- new Worksheet(new SheetData():> OpenXmlElement)

24:

25:     // Add Sheets to the Workbook.

26:     let sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets())

27:

28:     // Append a new worksheet and associate it with the workbook.

29:     let sheet = new Sheet()

30:     sheet.Id <-  StringValue(spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart))

31:     sheet.SheetId <-  UInt32Value(1u)

32:     sheet.Name <-  StringValue(sheetName)

33:     sheets.Append([sheet :> OpenXmlElement])

34:     )

35:

36: let result = createSpreadsheet @"D:\Tmp\test1.xlsx" "test";;

37:

I thought that it would be as straightforward as creating a word document. This was not the case.

This time some objects have be up casted (the :> symbol) to OpenXmlElement objects and some values had to be transfomed form strings to StringValues.

The source: http://stackoverflow.com/questions/5702939/unable-to-append-a-sheet-using-openxml-with-f-fsharp.