Showing posts with label spreadsheet. Show all posts
Showing posts with label spreadsheet. Show all posts

Wednesday, June 8, 2011

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. 

Wednesday, May 25, 2011

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.

Total Pageviews