Wednesday, May 25, 2011

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.

No comments:

Total Pageviews