2 people like it.

Create Open XML Spreadsheet

Create Open XML Spreadsheet using the open XML SDK

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
//reference to the Open Office SDK
#r @"C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll"
//reference to the package 
#r "WindowsBase"

open DocumentFormat.OpenXml
open DocumentFormat.OpenXml.Packaging
open DocumentFormat.OpenXml.Spreadsheet

let createSpreadsheet (filepath:string) (sheetName:string) =
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
   
    using (SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)) (fun spreadsheetDocument ->

    // Add a WorkbookPart to the document.
    let workbookpart = spreadsheetDocument.AddWorkbookPart(Workbook = new Workbook())

    // Add a WorksheetPart to the WorkbookPart.
    // http://stackoverflow.com/questions/5702939/unable-to-append-a-sheet-using-openxml-with-f-fsharp
    let worksheetPart = workbookpart.AddNewPart<WorksheetPart>()
    worksheetPart.Worksheet <- new Worksheet(new SheetData():> OpenXmlElement)

    // 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(Id = StringValue(spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)),
                          SheetId =  UInt32Value(1u),
                          Name =  StringValue(sheetName)
                          )
    sheets.Append([sheet :> OpenXmlElement])
    )

let result = createSpreadsheet @"D:\Tmp\test1.xlsx" "test";;
val createSpreadsheet : filepath:string -> sheetName:string -> 'a

Full name: Script.createSpreadsheet
val filepath : string
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = System.String

Full name: Microsoft.FSharp.Core.string
val sheetName : string
val using : resource:'T -> action:('T -> 'U) -> 'U (requires 'T :> System.IDisposable)

Full name: Microsoft.FSharp.Core.Operators.using
val spreadsheetDocument : System.IDisposable
val workbookpart : obj
val worksheetPart : obj
val sheets : obj
val sheet : obj
val result : obj

Full name: Script.result

More information

Link:http://fssnip.net/54
Posted:13 years ago
Author:Piet Amersfoort
Tags: open xml , spreadsheet