PsAccelerator

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.

Tuesday, June 7, 2011

Create Open XML Word document in F# (update)

This time I will create a word document (open xml) in F# without using the Open XML SDK and using XML libraries. Things have been intentionally kept simple.

I start by modelling a simple word document:

There are Two types of paragraphs: normal and bold ones:

 //reference to the package 
#r "WindowsBase"
 
open System
open System.IO
open System.IO.Packaging
 
type Paragraph =
    | Normal of string
    | Bold of string
 

And a document is a list of paragraphs:

 type Document =
    | Paragraphs of Paragraph list
 

This is the test:

 //test
let list = [Normal("test1"); Normal("test2"); Bold("test3"); Normal("test4")]
let doc = Paragraphs(list)
 

Next we have the to create an xml version of document:

//helpers

// http://weblogs.sqlteam.com/mladenp/archive/2008/10/21/Different-ways-how-to-escape-an-XML-string-in-C.aspx

let encodedXml (text: string) =

    text.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;")

let xml1 = "<node>it's my \"node\" & i like it<node>"

let encode = encodedXml xml1

let addString acc item = acc + "\r\t" + item

let createParagraphNormal text =

    "<w:p>

      <w:r>

        <w:t>" + encodedXml text + 

        "</w:t>

      </w:r>

    </w:p>"

let createParagraphBold text =

    "<w:p>

      <w:r>

        <w:rPr>

         <w:b />

        </w:rPr>

        <w:t>" + encodedXml text + 

        "</w:t>

      </w:r>

    </w:p>"

let createParagraph = function

    | Normal(text) -> createParagraphNormal text

    | Bold (text) -> createParagraphBold text

let createParagraphs = function

    | Paragraphs(list) -> list|> List.map createParagraph |> List.reduce addString

//test

let p = createParagraphs doc

let createDocument doc =

    let startDocument = @"<?xml version=""1.0"" encoding=""utf-8""?>

                    <w:document xmlns:w=""http://schemas.openxmlformats.org/wordprocessingml/2006/main"">

                      <w:body>"

    let endDocument = "  </w:body>

                        </w:document>"

    let content = createParagraphs doc

    startDocument + content + endDocument

Then we have to create a package, add the document and save it to disk:

//add the document to package and save

let createFile doc (fileName:string) =

    using (Package.Open(fileName, FileMode.Create, FileAccess.ReadWrite))(fun package ->

        let uri = new Uri("/word/document.xml", UriKind.Relative )

        let partDocumentXML = 

            package.CreatePart(

                                uri,

                                "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml" )

        using(new StreamWriter(partDocumentXML.GetStream(FileMode.Create, FileAccess.Write)))(fun stream ->

            doc |> createDocument |> stream.Write

        )

        package.CreateRelationship(

                                    uri,

                                    TargetMode.Internal,

                                    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument",

                                    "rId1") |> ignore

    )

//test

let fileName = @"D:\Tmp\test.docx"

createFile doc fileName;;

This is the result:

I have created a map from twitter to my document model

type UserStatus = 

    { UserName : string; 

      ProfileImage : string; 

      Status : string; 

      StatusDate : DateTime }

let mapUserStatus (us:UserStatus) =

    let date = "Date: " + us.StatusDate.ToShortDateString()  + " time: " + us.StatusDate.ToLongTimeString()

    [Normal(date); Bold(us.UserName); Normal(us.Status); Normal("")]

let addUserStatus acc item = item @ acc

let result = xml|> parseTweetXml |> List.map mapUserStatus|> List.reduce addUserStatus

(the details of the twitter part can be found at: http://blogs.msdn.com/b/dsyme/archive/2010/01/10/async-and-parallel-design-patterns-in-f-reporting-progress-with-events-plus-twitter-sample.aspx)

This results in the following document:

Remark: I had to Google “Weiner” to understand the jokes.

Monday, June 6, 2011

Create Open XML Spreadsheet in F# (update)

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"

6: open DocumentFormat.OpenXml

7: open DocumentFormat.OpenXml.Packaging

8: open DocumentFormat.OpenXml.Spreadsheet

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: