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:
No comments:
Post a Comment