Tip:
Highlight text to annotate it
X
0:00:00.000,0:00:03.000
Hi, and welcome to FCell 2.0!
0:00:03.000,0:00:07.000
FCell is an exciting product that intergrates
.NET and Excel,
0:00:07.000,0:00:12.000
Today we will demonstrate a number of
impressive functionality, incl. writing User
Defined Functions, asynchronous functions,
0:00:12.000,0:00:17.033
object handlers and Service Oriented
Architecture. Let us begin.
0:00:18.033,0:00:21.000
FCell is commercially available.
0:00:21.000,0:00:26.000
You can go to the website fcell.io and you can
download it and use it today.
0:00:26.000,0:00:31.000
We support both 64 and 32 bit versions of Excel
on Windows Vista and later.
0:00:33.033,0:00:39.033
To get started we need to create a new
workbook, like so, through the FCell ribbon
which has been added to your Excel.
0:00:44.066,0:00:52.000
Once we have done that, you can see that the
language tools are now available. We are going
to get started with F#.
0:00:52.033,0:00:57.033
In the code editor we can add a simple functions
such as adding 2 numbers together.
0:01:06.000,0:01:13.000
To load this all we have to do is to press the
Build button and the new function is
immediately available in Excel.
0:01:15.066,0:01:22.033
To make a change all we need to do is go back
to the editor, make the change and press the
Build button again.
0:01:22.033,0:01:27.033
The spreadsheet is re-evaluated and the new
value appears like so.
0:01:28.066,0:01:31.000
Now let's try a more complicated function. Let's
work with arrays.
0:01:31.000,0:01:36.000
Here we are going to scale an array by a scalar,
like so.
0:01:44.066,0:01:49.066
FCell is smart enough to map the output
dimensionality correctly as you would expect
both vertically and horizontally.
0:02:04.000,0:02:11.066
Now, how about working with non built-in types
such as the .NET DateTime. FCell automatically
converts DateTimes
0:02:11.066,0:02:16.066
and other commonly used types so that they
appear correctly in Excel. All that is needed is a
little bit of formatting.
0:02:27.033,0:02:34.000
You may notice here that this function is not
recalculated each time we press the Calculate
Now button.
0:02:34.033,0:02:39.033
This is because by default functions are not
volatile. We can easily fix this by adding the
0:02:56.066,0:03:01.066
Now when we recalculate we can see that the
time has been updated.
FCell has built-in extensibility which enables
user defined types. For example, we are going
to add Fmat numerics library.
In this spreadsheet we have a new type called
Matrix. Matrix is not a built-in type for Excel or
It is a new type that is being added. We are
going to use this type to do elementwise
multiplication of 2 matrices, like so.
0:04:08.066,0:04:15.033
The way we were able to allow Excel to use this
type is through explicit cast that FCell is able to
automatically pick up and use.
0:04:17.000,0:04:25.066
Right now this functionality is embedded directly
in the spreadsheet. What if we wanted to export
the dll and use it in other spreadsheets?
0:04:25.066,0:04:31.033
To do this we simply go File-> Save Dll
0:04:34.000,0:04:39.000
We close the spreadsheet and create a blank
spreadsheet, which as we can see does not
have the UDF.
0:04:44.000,0:04:55.066
We can reference the newly created dll from
Excel and now all of these functions in that
assembly are available in every spreadsheet
0:04:55.066,0:05:00.066
we use on this computer.
0:05:01.066,0:05:11.000
Now let's look at async UDFs. We are exposing
here a function called maxFactor, which takes
a n integer and returns max prime factor of n.
0:05:19.066,0:05:28.033
For large integers this function takes a long
time. When we run it normally we can see that it
blocks the UI and there's nothing we can do at
0:05:28.033,0:05:33.033
this stage but hope and wait.
0:05:39.000,0:05:44.000
Using FCell we can easily fix this problem by
simply wrapping the function with an async
0:05:58.033,0:06:03.033
We can see that it no longer blocks the UI.
0:06:20.066,0:06:25.066
Let's scale up this function to a large range of
integers.
0:06:35.066,0:06:40.066
We can see that this function is automatically
parallelized and we are using all of the cores on
this machine.
0:06:42.066,0:06:50.033
Now what if we wanted to give the user
additional feedback of the processing while it is
going on. We can do this easily by composing
0:06:50.033,0:06:58.033
together asynchronous and reactive
programming. To push to the UI we are going to
use F# events.
0:07:04.033,0:07:09.033
We simply create an event and whenever we find
a prime we trigger the event to all of the
listeners.
0:07:17.066,0:07:27.033
We create a function that uses the reactive
extension scan to subscribe to the event and
return the number of prime numbers found.
0:07:28.000,0:07:33.000
This is particularily useful for creating real-time
dashboards where the information such as stock
prices can easily be pushed into the
0:07:33.000,0:07:38.000
spreadsheet.
0:08:09.000,0:08:16.033
Here we are going to demonstrate object
handlers for directly using instances of complex
types created in Excel.
0:08:16.033,0:08:26.000
Here we have defined a new complex type for
random vectors. The constructor takes an
integer n and creates a vector of n random
0:08:26.000,0:08:32.000
numbers. We can then have an instance
method which calculates the average of those
numbers.
0:08:32.066,0:08:40.066
To do this, all we need is 2 static methods with
the attribute XlConverter. These static
methods convert between the random vector and
0:08:40.066,0:08:47.000
a primitive value that is understood by Excel. In
this case we are using a string to represent the
object. These conversion functions allow you to
0:08:47.000,0:08:53.066
use your existing .NET types with FCell.
0:08:54.000,0:09:02.066
You can see here that we are creating 2
instances and running an instance method
Mean which needs the object handler as first arg
0:09:22.066,0:09:30.000
So now we are going to demonstrate accessing
functionality via web services, also known as
SOA.
0:09:30.000,0:09:37.033
Here we are able to connect to an external web
service with just a single line of code using what
is called a WSDL Type Provider.
0:09:37.033,0:09:44.000
This type provider looks up the metadata of the
service and generates an API for it that is really
easy to use.
0:09:45.033,0:09:53.066
We can actually go to the web service and see
the exposed API through the browser. This
simple calculator web service exposes 4
0:09:53.066,0:09:58.066
functions: add, divide, multiply and subtract.
0:10:00.000,0:10:09.000
To use the API we simply create an instance of
the end point called calc and go . and see
through code completion that all of the functions
0:10:09.000,0:10:16.000
are there including async versions of those
functions.
0:10:19.033,0:10:24.033
Let's try it out by adding 2 numbers via the
service.
0:10:24.033,0:10:29.033
Now let's try it again with the async version of
the same function.
0:10:46.033,0:10:51.033
Finally, we are going to show you how easy it is
to integrate with the Ribbon.
0:10:51.033,0:10:56.033
Here we can create a button from which the user
can trigger a function call.
0:11:03.033,0:11:11.066
Here we are triggering the function
OnButtonClick. This function pulls in values
from the workbook type provider which provides
0:11:11.066,0:11:20.066
properties for all named ranges in the
workbook. In this case we have named ranges
x, y and z. We extract the value of x and y and
0:11:20.066,0:11:32.033
we use the SOA service to add the 2 numbers
together and setting the result back to range z.
To trigger it we just need to press the button.
0:11:38.000,0:11:47.066
Thank you for listening. For the latest updates
follow us on Twitter @FCellAddIn and visit our
website fcell.io