Automating Excel 2010 using F#

I have been searching for a FAQ to tell me how to open a Excel Workbook/Worksheet and also how to Save the File once I have finished.

I notice that in most FAQ and all the books I have purchased on F# one is show how to create a new Workbook/Worksheet but is never shown how to either open or Save it.

Being a newbie to F# I would very much appreciate it if anyone could kindly provide me with either an answer or perhaps a few pointers?

Update

As for why F# and not C# or VB?

I am p开发者_如何学Pythonleased to say that inspite of being a newbie (with the exception of Forth, VBA & Excel 2003, 2007 & 2010 and Visual Basic) I can do this in both VB, VBA & C# and since I've been retired on medical grounds, with plenty of time unfortunately on my hands, I like to continually set myself challenges to keep my little grey cells active and being a sucker for trying new languages....well!

F# is now an intergral part of Visual Studio 2010 so I thought - why not. Consider this - if we are not willing to use or at least try a new languages - I would always be wonder if I might have prefer it to VBA, VB, C# ..... and if you look at it from another point of view, if no one is going to use it - why create it in the first place? I suppose you can say if cave men hadn't experimented and made fire by rubbing two sticks together - where would we be now and would matches have been invented?

Although an complete answer would be good, I prefer a few pointers, to keep my challenge going.

And lastly but not least - thank you for taking the trouble to respond!

I don't think their is a specific F# library for Office, so you will just use the exact same .NET library that you use in VB.NET/C#. F# is a .NET language, so anything that can be done in C# can be done in F# (but you probably already knew that :) ). The API call will be exactly the same, it just that they will be done using the F# syntax instead of the VB/C# one. So for example something that look like this

public void SaveMyWorkbook() {
    string filePath = @"C:\failworkbooks\catfail.xlsx";
    workbook.Save(filepath);
}

Will be expressed in F# as

let filePath = "C:\\failworkbooks\\catfail.xlsx";

let saveWorkbook() = workbook.Save(filePath) |> ignore //if the Save method return something

Now, what you will soon realize is that the API isn't exactly designed to be easily used from a functional language. It can be done, but this task in particuliar is much more tailored to C#/VB.NET.

If you really want to enjoy F#, I suggest you use in area where its strength really show. My personal experience is that functional language are awesome when a lot of math is involved. It is also marvellous if you want to easily introduce parallelism in your application (since F# code is usually side effect free). So anything that require data crunching on a lot of data is perfect for it. But for task that consist mainly of putting together a bunch of API call to an external library, F# is kind of meh. You could say that F# is kind of like a graphic card programming language, while C# a general purpose CPU programming language. A lot of thing run better with C#, but the stuff that run better on F# run really better on it.

But if you really want to go that route, my suggestion is to try to use the Office API as you already know it, but with a F# syntax. If at some point you really have no idea how to do a specific task, ask a question about it on stackoverflow with your code and exactly want you want to do. Those question get answered ridiculously fast compared to broad all-encompassing question, so you won't wait long. (Programmer seem to love precise question with a specific answer ^^)

I hope that it helped a little.

I found this http://iouri-khramtsov.blogspot.co.uk/2011/12/automating-excel-with-f.html helpful advice. Briefly, you'd use something like this:

#r "Microsoft.Office.Interop.Excel"  // Assuming it's a script

let excel = ApplicationClass(Visible = true)

let openFileName = @"C:\MyDir\MyFilenameToOpen.xls"
excel.Workbooks.Open(openFileName)

// Do stuff

let savedFileName = @"C:\MyDir\MyFilename.xls"
workbook.SaveAs(savedFileName)

Using F# with Excel seems like a natural fit.

Getting to a result in Excel requires the use of several immutable values, each driven by formulas. Excel has a brilliant user interface, a lovely model of the world - I love rows, columns and cells - but to automate or customise things requires macros. Why learn this when you can use F#? Formulas and immutable values are fundamental to its design.

Ideally you'd write formulas yourself as a User Defined Function (UDFs) also in F# - see http://excel-dna.net/ . Then, perhaps, you'd want to do something interesting with objects/types - Look for "github com mndrake ExcelObjectHandler" (I don't have enough reputation to post a 3rd link).

Jack