Creation of a very simple F# model of a spreadsheet

Today I posted  a F# snipped at http://fssnip.net/5F.

It creates an Open Xml spreadsheet and does not depend on the Open Xml SDK.

To add data to the sheet you need SheetData . I have created a small model op a spreadsheet and some functions to generate the SheetData.

First the model:

//model of spreadsheet

// column * value

type Cell =

    | Text of string * string

    | Number of string * int

    | Formula of string * string

// row number * cells in the row

type Row =

    |Cells of int * Cell list

//list of rows

type Sheet =

    | Rows of Row list

Notice that the model reflects the structure of the Open Xml specifications. Sheets contain Rows and Rows contain Cells.  I have modeled three types of cells inline text, numbers and formulas. There are more. From the specifications (17.18.11 ST_CellType (Cell Type)):

Enumeration Value	Description
b (Boolean)	Cell containing a boolean.
d (Date)	Cell contains a date in the ISO 8601 format.
e (Error)	Cell containing an error.
inlineStr (Inline String)	Cell containing an (inline) rich string, i.e., one not in the shared string table. If this cell type is used, then the cell value is in the is element rather than the v element in the cell (c element).
n (Number)	Cell containing a number.
s (Shared String)	Cell containing a shared string.
str (String)	Cell containing a formula string.

These are the tests:

//test

let cell1 = Text("A","test1")

let cell2 = Number("B", 42)

let cell3 = Text("A","test2")

let cell4 = Number("B", 43)

let cell5 = Formula("B", "SUM(B1:B2)")

let row1 = Cells(1, [cell1; cell2])

let row2 = Cells(2, [cell3; cell4])

let row3 = Cells(3, [cell5])

let sheet = Rows([row1; row2; row3])

This are the required functions to generate the SheetData:

let createTextCell column rowNumber (value:string) =

    let cell = new XElement(xname "c",

                                new XAttribute(xnameEmpty "r", column + rowNumber.ToString()),

                                new XAttribute(xnameEmpty "t", "inlineStr"),

                                    new XElement(xname "is",

                                        new XElement(xname "t", value)))

    cell

//test

let result1 = createTextCell "A" 1 "abc"

let createNumberCell column rowNumber (value:int) =

    let cell =new XElement(xname "c",

                                new XAttribute(xnameEmpty "r", column + rowNumber.ToString()),

                                new XAttribute(xnameEmpty "t", "n"),

                                    new XElement(xname "v", value

                                        ))

    cell

let createFormulaCell column rowNumber (formula:string) =

    let cell =new XElement(xname "c",

                                new XAttribute(xnameEmpty "r", column + rowNumber.ToString()),

                                new XAttribute(xnameEmpty "t", "n"),

                                    new XElement(xname "f", formula

                                        ))

    cell

//test

let result2 = createNumberCell "B" 1 4

let createCell rowNumber = function

    | Text (culumn, value) -> createTextCell culumn rowNumber value

    | Number (culumn, value) -> createNumberCell culumn rowNumber value

    | Formula (culumn, formula) -> createFormulaCell culumn rowNumber formula

//test

let result3 =createCell 1 cell1

let result4 =createCell 1 cell2

let createRow = function

    |Cells (rowNumber, cells) -> 

        let rowElement = new XElement(xname "row", new XAttribute(xnameEmpty "r", rowNumber))

        cells |> List.map (createCell rowNumber) |> rowElement.Add

        rowElement

let result5 = createRow row2

let createSheetData (sheet:Sheet) =

    let createRows =  function

        | Rows rows -> rows|> List.map createRow

    let sheetData = new XElement(xname "sheetData")

    sheet|> createRows |> sheetData.Add

    sheetData

let result6 = createSheetData sheet

This is the result:

Because the sheet contains a formula in cell B3 Excel will calculates the result. 

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.