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: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 
69: 
70: 
71: 
72: 
73: 
//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) (sheetData:SheetData) =
    // 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(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)
                            )
    [sheet :> OpenXmlElement] |> sheets.Append
    )

//helpers
let createCellReference (header:string) (index:int) =
    StringValue(header + string(index))

let createNumberCell number (header:string) (index:int) =
    let cell = new Cell(DataType = EnumValue(CellValues.Number), CellReference = createCellReference header index)
    let value = new CellValue(Text = number.ToString())
    value |> cell.AppendChild|> ignore
    cell :> OpenXmlElement

let createTextCell text (header:string) (index:int) =
    let cell = new Cell(DataType = EnumValue(CellValues.InlineString), CellReference = createCellReference header index)
    let inlineString = new InlineString()
    let t = new Text(Text = text)
    t |> inlineString.AppendChild |> ignore
    inlineString |> cell.AppendChild|> ignore
    cell :> OpenXmlElement

let createContentRow (text, (number1:int), (number2:int), (index:int)) =
    let row = new Row(RowIndex = UInt32Value(uint32(index)))
    let cell1 = createTextCell text "A" index
    let cell2 = createNumberCell number1 "B" index
    let cell3 = createNumberCell number2 "C" index 
    cell1 |> row.Append
    cell2 |> row.Append
    cell3 |> row.Append
    row :> OpenXmlElement

//test
let createTestSheetData = 
    let sheetData = new SheetData()
    ("test1", 123, 456, 1) |> createContentRow |> sheetData.AppendChild |> ignore
    ("test2", 35, 1231, 2) |> createContentRow |> sheetData.AppendChild |> ignore
    ("test3", 345, 21, 3) |> createContentRow |> sheetData.AppendChild |> ignore
    sheetData

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

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 sheetData : 'a
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 createCellReference : header:string -> index:int -> 'a

Full name: Script.createCellReference
val header : string
val index : int
Multiple items
val int : value:'T -> int (requires member op_Explicit)

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

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
val createNumberCell : number:'a -> header:string -> index:int -> 'b

Full name: Script.createNumberCell
val number : 'a
val cell : obj
val value : obj
System.Object.ToString() : string
val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
val createTextCell : text:'a -> header:string -> index:int -> 'b

Full name: Script.createTextCell
val text : 'a
val inlineString : obj
val t : obj
val createContentRow : text:'a * number1:int * number2:int * index:int -> 'b

Full name: Script.createContentRow
val number1 : int
val number2 : int
val row : obj
Multiple items
val uint32 : value:'T -> uint32 (requires member op_Explicit)

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

--------------------
type uint32 = System.UInt32

Full name: Microsoft.FSharp.Core.uint32
val cell1 : obj
val cell2 : obj
val cell3 : obj
val createTestSheetData : obj

Full name: Script.createTestSheetData
val sheetData : obj
val testData : obj

Full name: Script.testData
val result : obj

Full name: Script.result

More information

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