Excel Web Apps web page mash-up using F# services

Celso Axelrud (interpoaaustin@hotmail.com)

1/24/2012

INTRODUCTION

The following solution shows how to create your own web pages that mash-up Excel Web Apps with other content including web services that can operate, as reading and writing, on the Excel cells.  

For example, this solution allows continuous update of values on Excel from several data sources providing dynamic behavior for formulas and charts.

Also, the Excel worksheet, in the dynamic web page, is operational and can be modified by the user including new formulas and charts. Then, it can be saved for future use.

Details about the Excel Web Apps mash-up are available at http://www.excelmashup.com/.

The solution allows users to design their web pages in Excel either in personal computer or in the cloud at Skydrive. The Excel workspace can include all features available in the Web Apps including several sheets, formulas and charts.

The full artircle, including example and source code, is available at:

https://skydrive.live.com/#!/view.aspx?cid=BDC87EF39B001785&resid=BDC87EF39B001785%21307

Silverlight and F#

The following example is a Silverlight application using F#.

The server and client are written in F# (except for a standard one line code in C# to register the event handlers). So, basically, only one language is used for all parts.

The UI is separated from the logic and it is developed using Visual Studio XAML editor (WYSIWYG).

The application is a simple Hello-World using a Button and a TextBlock.

But, the example is quite generic because it allows cross-domain network access. It means that the web-page and the web-service can be hosted by multiple and different servers.

The client solution is composed by three projects, one Silverlight Application (C#), one Silverlight Class Library (C#) and one F# Library, as follow.

An F# script is used to define programmatically a self-hosted server. Check the full article at:

https://skydrive.live.com/#!/view.aspx?cid=BDC87EF39B001785&resid=BDC87EF39B001785%21285

WCF Excel Client using F#/Excel Automation

 

These previous posts presented examples of a data/historian server using WCF and F#:

• WCF with F# interactive – Data Server example - Dec/25/2010 http://caxelrud.blogspot.com/2010/12/wcf-with-f-interactive-data-server.html  
• Data and Historian Service using WCF and F# interactive – rev 2 http://caxelrud.blogspot.com/2011/05/data-and-historian-service-using-wcf.html  

This post extends these examples with a WCF Excel client using F#/Excel Automation.

 

Check the following link for documentation and script code:
http://cid-bdc87ef39b001785.office.live.com/view.aspx/Fsharp/WCF%5E_Excel%5E_Client%5E_1.docx

Data and Historian Service using WCF and F# interactive - rev 2

This blog presents the revision 2 from the material from the blog:
WCF with F# interactive – Data Server example - Dec/25/2010
http://caxelrud.blogspot.com/2010/12/wcf-with-f-interactive-data-server.html
These are the improvements:
- The data value is of type Object. The examples show how to work with basic types as float, integer, strings and other types as dateTime. It can also use array of the basic types.
- The data server also stores status and time-stamp.
- The data server optionally can operate as a historian using an in-memory circular buffer storing values (objects), status and time-stamps.

Check the code and examples at:
http://cid-bdc87ef39b001785.office.live.com/view.aspx/Fsharp/DataServer%5E_doc%5E_1%5E_0.docx

F# interactive - how to hide/show the console

The F# interactive allows great object visibility and interactive capabilities even in running applications like server processes. So, it is a good idea to keep the F# interactive console available even in production versions of the code. But, it is not acceptable to leave several consoles open in the computer screen. The idea of this document is to show that consoles can be hidden and be shown as needed.
The following code shows how to manage application (start) and hide/show the F# interactive console applications.

//module ApplMgm

open System
open System.Diagnostics
open System.Collections.Generic
open System.Runtime.InteropServices;

System.Console.Title<-"AppMgm"

[< dllimport("user32.dll")>]
extern IntPtr FindWindow(string lpClassName,string lpWindowName);
[<dllimport("user32.dll")>]
extern bool ShowWindow(IntPtr hWnd, int nCmdShow)

let fsharpPath= @"C:\Program Files (x86)\Microsoft F#\v4.0\fsi.exe"

type Process={Path:string;ExecName:string;ConsoleName:string}

type AppMgm()=
    let mutable applications= new Dictionary()
    let fsharpPath= @"C:\Program Files (x86)\Microsoft F#\v4.0\fsi.exe"

    member this.Apps with get() = applications and set a = applications <- a
    member this.fsharpFullPath= @"C:\Program Files (x86)\Microsoft F#\v4.0\fsi.exe"
    member this.start(keyName:string) =
        let procPath=applications.[keyName].Path
        let procName=applications.[keyName].ExecName
        Process.Start(fsharpPath,@"--readline+ --load:"+"\""+procPath+procName+"\"") |>ignore
        ()
    member this.hide(keyName:string) =
        let consName=applications.[keyName].ConsoleName
        let hWnd = FindWindow(null, consName) 
        if (hWnd <> 0n) then
            let b1=ShowWindow(hWnd, 0); // 0 = SW_HIDE
            ()        
    member this.show(keyName:string) =
        let consName=applications.[keyName].ConsoleName
        let hWnd = FindWindow(null, consName) 
        if (hWnd <> 0n) then
            let b1=ShowWindow(hWnd, 1); // 1 = SW_SHOWNORMA
            ()        
//--------------------------------------------------------------------------------------------------
//Configuration
let A= new AppMgm()
let d= new Dictionary()
let projPath= @"C:\Users\caxelrud\Documents\Visual Studio 2010\Projects\Utilities\"

d.Add("ap1",{Path=projPath;ExecName="app_1.fsx";ConsoleName="Application1"})
d.Add("ap2",{Path=projPath;ExecName="app_2.fsx";ConsoleName="Application2"})
//d.["ap2"]
A.Apps<-d
//A.Apps

A.start("ap1")
A.hide("ap1")
A.show("ap1")

F# Linear Algebra with Blas and Lapack

I am testing the F# MathProvider library (http://mathprovider.codeplex.com/).
Mathprovider site has a very interesting section called "About the Performance" that I recommend.


For now, I tried the following sample code with no problems.

// reference F# PowerPack & MathProvider
#r @"FSharp.PowerPack.dll"
//#r @"D:\FSharp\math-provider\MathProvider\MathProvider\bin\Release\MathProvider.dll"
#r @"C:\cs\Others\MathProvider\Net 4.0\MathProvider.dll"
#time

// rename the module name
module L = MathProvider.LinearAlgebra

// set the directory of the Lapack runtime (blas.dll & lapack.dll)
System.Environment.CurrentDirectory <- @"C:\cs\Others\MathProvider\LapackRuntime"
//System.Environment.CurrentDirectory <- @"D:\FSharp\math-provider\LapackRuntime\Netlib" 
//System.Environment.CurrentDirectory <- @"D:\FSharp\math-provider\LapackRuntime\MKL"

// start the native provider, otherwise (implementent) F# implementation will be used
L.startProvider()

// create two 3x3 matrices
let A = matrix [ [12.; -51.; 4.; ]; [6.; 167.; -68.;]; [-4.; 24.; -41.; ] ]
let B = matrix [ [ 2.; 1.; 1.;] ; [ 1.; 2.; 1.;]; [ 1.; 1.; 2.;] ]

// determinant 
let det = L.det A
// inverse
let inv = L.inv A
// qr decomposition
let q, r  = L.qr  A
// lu decomposition
let p, l, u  = L.lu  A
// cholesky decomposition
let ch  = L.chol B
// svd decomposition
let v, s, ut = L.svd A
// eigen decomposition for symetric matrix
let a, b = L.cov A A  |> L.eigenSym 

(* result *)

(*
val A : matrix = matrix [[12.0; -51.0; 4.0]
                         [6.0; 167.0; -68.0]
                         [-4.0; 24.0; -41.0]]
val B : matrix = matrix [[2.0; 1.0; 1.0]
                         [1.0; 2.0; 1.0]
                         [1.0; 1.0; 2.0]]
val det : float = -85750.0
val inv : matrix = matrix [[0.06081632653; 0.02326530612; -0.03265306122]
                           [-0.006040816327; 0.005551020408; -0.009795918367]
                           [-0.009469387755; 0.0009795918367; -0.02693877551]]
val r : matrix = matrix [[-14.0; -21.0; 14.0]
                         [0.0; -175.0; 70.0]
                         [0.0; 0.0; -35.0]]
val q : matrix = matrix [[-0.8571428571; 0.3942857143; 0.3314285714]
                         [-0.4285714286; -0.9028571429; -0.03428571429]
                         [0.2857142857; -0.1714285714; 0.9428571429]]
val u : matrix = matrix [[12.0; -51.0; 4.0]
                         [0.0; 192.5; -70.0]
                         [0.0; 0.0; -37.12121212]]
val p : (int -> int)
val l : matrix = matrix [[1.0; 0.0; 0.0]
                         [0.5; 1.0; 0.0]
                         [-0.3333333333; 0.03636363636; 1.0]]
val ch : matrix = matrix [[1.414213562; 0.7071067812; 0.7071067812]
                          [0.0; 1.224744871; 0.4082482905]
                          [0.0; 0.0; 1.154700538]]
val v : matrix = matrix [[-0.2543778627; -0.5139835724; -0.81921474]
                         [0.9464104305; 0.0419982359; -0.3202240549]
                         [0.1989954776; -0.8567712854; 0.4757559925]]
val ut : matrix = matrix [[0.00960262784; 0.922507462; -0.385859783]
                          [-0.07574450365; 0.3854399898; 0.9196188256]
                          [-0.9970810196; -0.0203960004; -0.0735761066]]
val s : Vector = vector [|190.5672437; 32.85688323; 13.69492038|]
val b : Matrix = matrix [[0.9970810196; -0.07574450365; -0.00960262784]
                                [0.0203960004; 0.3854399898; -0.922507462]
                                [0.0735761066; 0.9196188256; 0.385859783]]
val a : vector = vector [|187.5508443; 1079.574776; 36315.87438|]
*)

Dynamic Compilation and Execution using F#

Dynamic Compilation and Execution using F#
Celso Axelrud
Feb/17/2011

The following example shows how to compile a module from a string inside an executable and make it available to be used by the same executable.
For better understanding of the steps, the example uses F# interactive.

Here is the code:

#r "FSharp.Compiler.dll" 
#r "FSharp.Compiler.CodeDom.dll" 
#I @"C:\Users\caxelrud\Documents\Visual Studio 2010\Projects\NLMPC" 
open System 
open System.IO 
open System.CodeDom.Compiler 
open Microsoft.FSharp.Compiler.CodeDom 

(* For this example, copy Fsharp.Core.* from C:\Program Files (x86)\Reference Assemblies\Microsoft\FSharp\2.0\Runtime\v4.0 and FSharp.Powerpack.dll to the local directory*)

let CompileFSharpString(str, assemblies, output) = 
        use pro = new FSharpCodeProvider() 
        let opt = CompilerParameters(assemblies, output) 
        let res = pro.CompileAssemblyFromSource( opt, [|str|] ) 
        printfn "%A" res.Errors
        if res.Errors.Count = 0 then  
             Some(FileInfo(res.PathToAssembly))  
        else None ;;
 
let (++) v1 v2   = Path.Combine(v1, v2)
let path= @"C:\Users\caxelrud\My Documents\Visual Studio 2010\Projects\NLMPC"    
let defaultAsms  = [|path++"System.dll"; path++"FSharp.Core.dll"; path++"FSharp.Powerpack.dll"|]  
let randomFile() = path ++ Path.GetRandomFileName() + ".dll";;    
//let randomFile() = __SOURCE_DIRECTORY__ ++ Path.GetRandomFileName() + ".dll"    
 
type System.CodeDom.Compiler.CodeCompiler with  
    static member CompileFSharpString (str, ?assemblies, ?output) = 
        let assemblies  = defaultArg assemblies defaultAsms 
        let output      = defaultArg output (randomFile())
        printfn "%A %s" assemblies output  
        CompileFSharpString(str, assemblies, output);;      
 
// Module definition 
let library = "  
module Temp.Main 
let f(x,y) = sin x + cos y 
" ;;

// Create the assembly 
let fileinfo = CodeCompiler.CompileFSharpString(library);; 
 
// Just testing in the interactive env. 
#r "zmm30vu5.eqq.dll" 

let a = Temp.Main.f(0.5 * Math.PI, 0.0);;     // val a : float = 2.0 
 
// Purely reflective invocation of the function. 
let asm = Reflection.Assembly.LoadFrom(fileinfo.Value.FullName) 
let mth  = asm.GetType("Temp.Main").GetMethod("f") 
 
// Wrap weakly typed function with strong typing. 
let f(x,y) = mth.Invoke(null, [|box (x:float); box (y:float)|]) :?> float;; 

// Call the function 
let b = f (0.5 * Math.PI, 0.0);;              // val b : float = 2.0