Wednesday, June 8, 2011

Creation of a very simple F# model of a spreadsheet

Today I posted  a F# snipped at

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
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:
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)))
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
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
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
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 |> (createCell rowNumber) |> rowElement.Add
let result5 = createRow row2
let createSheetData (sheet:Sheet) =
    let createRows =  function
        | Rows rows -> rows|> createRow
    let sheetData = new XElement(xname "sheetData")
    sheet|> createRows |> sheetData.Add
let result6 = createSheetData sheet

This is the result:

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

No comments:

Total Pageviews