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"
 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:

Tuesday, May 31, 2011

Create in F# XDocument with XDeclaration and namespace

In this example I will create the following XML in F#:
<?xml version="1.0" encoding="utf-8"?>
<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
  <w:body>
    <w:p>
      <w:r>
        <w:t>This is a test</w:t>
      </w:r>
    </w:p>
  </w:body>
</w:document>
 
Transferring C#-code is not straightforward because the constructor of XDocument does not accept the following code: new  XDocument(new XDeclaration("1.0""utf-8""true"), new XElement("document"))

//First create some helpers:

let xnameNs str ns = XName.Get(str, ns)
let xname str =
    xnameNs str "http://schemas.openxmlformats.org/wordprocessingml/2006/main"
 
let ns = XNamespace.Get("http://schemas.openxmlformats.org/wordprocessingml/2006/main")
 
let dec = new XDeclaration("1.0""utf-8""true")
 
let el =
    new XElement(xname "document",
        new XAttribute(XNamespace.Xmlns + "w", ns),
            new XElement(xname "body",
                new XElement(xname "p",
                    new XElement(xname "r",
                        new XElement(xname "t""This is a test")
                        ))))
 
//Add them together:
 
let document = 
    let doc =new XDocument(dec)
    doc.AddFirst(el)
    doc

//And test:
 
document.Save(@"D:\Tmp\test1.xml")

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.

Tuesday, May 24, 2011

Create Open XML Word document in F#

I needed a word document. I created it 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.Wordprocessing
8: open DocumentFormat.OpenXml.Packaging
9:
10: let testString = "This is a test"
11: let printXml text =
12: printfn "xml: %s" text
13:
14: let createBody (text:string) =
15: let text = new Text(text)
16: let run = new Run()
17: run.AppendChild(text) |> ignore
18: let para = new Paragraph()
19: para.AppendChild(run)|> ignore
20: let body = new Body()
21: body.AppendChild(para)|> ignore
22: body
23:
24: printXml (createBody testString).InnerXml
25:
26: let createDocument (text:string) =
27: let body = createBody text
28: let doc = new Document()
29: doc.AppendChild(body) |> ignore
30: doc
31:
32: printXml (createDocument testString).InnerXml
33:
34: let createWordprocessingDocument (filepath:string) text=
35: using (WordprocessingDocument.Create(filepath, WordprocessingDocumentType.Document)) (fun doc ->
36: let mainPart = doc.AddMainDocumentPart();
37: mainPart.Document <- createDocument text
38: )
39:
40: let result3 = createWordprocessingDocument @"D:\Tmp\test1.docx" testString
val testString : string

Full name: Snippet.testString

type: string
implements: System.IComparable
implements: System.ICloneable
implements: System.IConvertible
implements: System.IComparable
implements: seq
implements: System.Collections.IEnumerable
implements: System.IEquatable
val printXml : string -> unit

Full name: Snippet.printXml
val text : string

type: string
implements: System.IComparable
implements: System.ICloneable
implements: System.IConvertible
implements: System.IComparable
implements: seq
implements: System.Collections.IEnumerable
implements: System.IEquatable
val printfn : Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val createBody : string -> 'a

Full name: Snippet.createBody
Multiple items
val string : 'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------

type string = System.String

Full name: Microsoft.FSharp.Core.string

type: string
implements: System.IComparable
implements: System.ICloneable
implements: System.IConvertible
implements: System.IComparable
implements: seq
implements: System.Collections.IEnumerable
implements: System.IEquatable
val text : 'a
namespace Microsoft.FSharp.Text
val run : 'a
val ignore : 'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
val para : 'a
val body : 'a
val createDocument : string -> 'a

Full name: Snippet.createDocument
val doc : 'a
val createWordprocessingDocument : string -> 'a -> 'b

Full name: Snippet.createWordprocessingDocument
val filepath : string

type: string
implements: System.IComparable
implements: System.ICloneable
implements: System.IConvertible
implements: System.IComparable
implements: seq
implements: System.Collections.IEnumerable
implements: System.IEquatable
val using : 'T -> ('T -> 'U) -> 'U (requires 'T :> System.IDisposable)

Full name: Microsoft.FSharp.Core.Operators.using
val doc : 'a (requires 'a :> System.IDisposable)

type: 'a
implements: System.IDisposable
val mainPart : 'a
val result3 : 'a

Full name: Snippet.result3

It is a direct transformation of the C# code from the help file of the Open XML SDK:

public static void CreateWordprocessingDocument(string filepath)
{
    // Create a document by supplying the filepath. 
    using (WordprocessingDocument wordDocument =
        WordprocessingDocument.Create(filepath, WordprocessingDocumentType.Document))
    {
        // Add a main document part. 
        MainDocumentPart mainPart = wordDocument.AddMainDocumentPart();
 
        // Create the document structure and add some text.
        mainPart.Document = new Document();
        Body body = mainPart.Document.AppendChild(new Body());
        Paragraph para = body.AppendChild(new Paragraph());
        Run run = para.AppendChild(new Run());
        run.AppendChild(new Text("Create text in body - CreateWordprocessingDocument"));
    }
}

Monday, September 29, 2008

F# Winform

When you want to make a F# winform application and you don’t want to open an extra console when you start the application.

How can you do this (F# version 1.9.6.2)?

1. Create a F# application:

clip_image002[1]

[Edit- thanks Brian ]

2. Open the project properties

image

3. In the application tab change the output type

image

 

4. Add reference to the System.Windows.Forms

clip_image010[1]

5. Add your code.

clip_image012[1]

6. Run your app

clip_image014[1]

7. Close.

8. Add your own details.

Monday, September 22, 2008

Creating a parser with F#

I want to write my own html-parser. That is one of the reasons I am learning F#. I have read all the documentation that is available in the three published F# books and tried to build some simple parsers myself and downloaded the examples of Chris Smith.

This resulted in a naïve development approach: start with lexer, than create the parser, compose the AST and finally build your application that consumes the AST.

One of the lessons learned: when you want to create a parser you have to start with analyses of the text type. In case you can step through the text and can determine the action after each token a lexer is what you need. In case the action is determined by server tokens you need a parser.

Example of program that needs a lexer:

  • A program that replacing digits by roman numerals.
  • A simple encryptor

Example of program that needs a lexer and a parser:

  • Xml-parser
  • Code

Total Pageviews