How to create Silverlight application from Excel graph

(Click on image above to test the application - Silverlight 2, 3 or 4 runtime required)

TUTORIAL SUMMARY:

1) From Excel to Silverlight
Start with "Hello Silverlight World!" application. Than using Paste2Xaml to convert Excel graph to Silverlight xaml. Showing the graph in a Silverlight application (in web browser).

2) Startup animation
Simple change of XAML to add the startup animation (no code change required).

3) Dynamic content
Added code to show the net profit when the user moved mouse over the bars - .

LEVEL: Beginner

Requirements:

Visual Studio 2008 SP1 or Visual Studio 2010
Silverlight Tools for Visual Studio 2008 - get from Silverlight - Get Started
Paste2Xaml - get from Downloads
Microsoft Excel 2007 (optional) *

* 2003 version of Microsoft Excel can also be used - results are slightly different. See additional info for more details.

STEP 1
From Excel to Silverlight

First download the sample package (includes data files for the tutorial and final solution):

Silverlight 3:		Silverlight 2 (old):

This tutorial will start from stretch and will gide you step by step to the final solution. But if you wish you can check the final source code at any as it is inculded in the sample package.

The Silverlight application will be created with Visual Studio 2008.

So firstly we need to create a new Silverlight Application (Visual C#) in Visual Studio 2008. Name the Application "SilverlightExcel". In the next dialog select to add new Web solution for hosting Silverlight.

Page.xaml will open.

We will start with simple Hello world application.

Inside Grid element write:

<TextBlock>Hello Silverlight World!</TextBlock>

When typing the "Hello Silverlight World!" text should be already shown in the upper Design window.
The Page.xaml should now look like:

<UserControl x:Class="SilverlightExcel.Page"
    xmlns="http://schemas.microsoft.com/client/2007"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Width="400" Height="300">
    <Grid x:Name="LayoutRoot" Background="White">
        <TextBlock>Hello Silverlight World!</TextBlock>
    </Grid>
</UserControl>

Press F5 to run the application.

The Silverlight should start in a web browser.

Now it is time to create out excel graph.

First run the Paste2Xaml application.

Now open the sample excel file SilverlightExcel.xls. (from the package file)

If you do not have Microsoft Excel 2007, you can open the excel_2007.emf in Paste2Xaml. The file was created with pasting the excel graph into Paste2Xaml and exporting it into emf (Enhanced Metafiles).

Excel screenshot

The Excel contains a simple table with Net profit by years. There is also a simple 2D graph.

Select the graph and copy it (CTRL + C).

Switch to Paste2Xaml and click on the Paste button (third button in the lower left).
The excel graph will be shown in the application - now using WPF rendering.

Now we will add names to the bars of the graph. This way it will be possible to access each bar by its name.

Double click on the bar for year 2003 (on the graph). Rename dialog is shown. Enter "bar2003" and click ok.
Rename the bars for other years as well ("bar2004", etc.)

Paste2Xaml with excel graph

The screenshot shown the Paste2Xaml with pasted graph from excel and rename dialog opened. Note that the Objects tree view on the rights side is already showing the names of the bars.

Now click the export button (the last button).
The Export dialog will open.

Select "Silverlight 2.0 XAML" for Export as.

Paste2Xaml export options

Click "COPY to clipboard".

Switch to Visual Studio and replace the TextBlock element with the content from clipboard.

We can clean the xaml a little bit. First remove the namespace definitions from Canvas element (xmlns and xmlns:s). Replace the Width and Height of the UserControl with the Width and Height of the Canvas. Remove the Width and Height from Canvas.

It is time to test our graph in Silverlight. Just press F5.
The code for Page.xaml can be seen in excel_step1.xaml from package.

It was simple? Isn't it. In a few clicks an excel graph is shown in web browser.

STEP 2
Startup animation

To show the power of Silverlight we will add a startup animation. How to animate a graph? Simply by animating the bars to grow from the bottom up. This will be achieved by animating the Y scale of the bars from 0% to 100%.

This can be done by changing the xaml (no code required).

First we will add a Canvas around the bars element.
Find the Polygon element with Name "bar2003". Insert a new line before it and type "<Canvas>". The Visual Studio adds Canvas closing element. Move it after the bar2007.

Now all the bars are enclosed within a Canvas. So if we transform the canvas, the bars will be affected as well.

We will add a RenderTransform with ScaleTransform to the Canvas. Note that Silverlight does not support LayoutTransofor as WPF.

After the Canvas and before the bar2003 Polygon add the following:

<Canvas.RenderTransform>
<ScaleTransform x:Name="BarsCanvasScale" ScaleY="0" CenterY="251"/>
</Canvas.RenderTransform>

The bars are gone!

This is because the ScaleY is set to 0. You can already try and change the ScaleY to some other value - for example to 0.3. The effect will be immediatley shown in the Design window in Visual Studio. Setting it to 1 will show the original bars. We have also named the ScaleTransform so it can be easily accessed by the animation.

Note that we have to set the CenterY property. This moves the center of the scale to the bottom line of the bars. The value is simply the biggest Y value from the Polygon's Points data.

We will animate the ScaleY value from 0 to 1 (100%).

Add the following code after the Canvas.RenderTransform:

<Canvas.Triggers>
    <EventTrigger RoutedEvent="Canvas.Loaded">
        <EventTrigger.Actions>
            <BeginStoryboard>
                <Storyboard>
                    <DoubleAnimation From="0" To="1" Duration="0:0:1"
                     Storyboard.TargetName="BarsCanvasScale"
                     Storyboard.TargetProperty="ScaleY" />
                </Storyboard>
            </BeginStoryboard>
        </EventTrigger.Actions>
    </EventTrigger>
</Canvas.Triggers>

We have added a Trigger to the Canvas that on Canvas's Loaded event start an animation. The animation is a DoubleAnimation - it animates a ScaleY property that is double type. The duration of the animation is one second.

Run the application. When the application is started the bars should nicely grow.

To see more about animations see Animation Overview.

ScaleTransform that is used in this sample is only one of the available Transformations. There are also: RotateTransform, TranslateTransform, SkewTransform and MatrixTransform.

This ends the step 2 of this tutorial.
The code for Page.xaml can be seen in excel_step2.xaml file from package.

STEP 3
Dynamic content

First we will select the bar when the user moved the mouse over it.

If we were in WPF we could use OuterGlowBitmapEffect. But because Silverlight does not have all the functionality, we will just make the bar's line thickness bigger.

First we will add MouseEnter and MouseLeave events to all bars. Because we have named the bars with the year number we can find each bar Polygon with a simple for loop. The following code registers the events:

private const int START_YEAR = 2003;
private const int END_YEAR = 2007;

private void RegisterBarEvents()
{
    for (int year = START_YEAR; year <= END_YEAR; year++)
    {
        Polygon oneBar = this.FindName("bar" + year.ToString()) as Polygon;

        if (oneBar != null)
        {
            oneBar.MouseEnter += new MouseEventHandler(oneBar_MouseEnter);
            oneBar.MouseLeave += new MouseEventHandler(oneBar_MouseLeave);
        }
    }
}

The RegisterBarEvents method is called from the Page constructor after InitializeComponent().

Now we should implement the oneBar_MouseEnter and oneBar_MouseLeave. In oneBar_MouseEnter we will just set the Polygons's StrokeThickness to 1 (from 0) and set Polygon's Stroke to Black.
In oneBar_MouseLeave we should set the Polygon to its previous state (without border).

Here is the implementation:

private Polygon _selectedPolygon;

void oneBar_MouseEnter(object sender, MouseEventArgs e)
{
    Polygon currentBar = sender as Polygon;

    if (currentBar != null)
    {
        currentBar.StrokeThickness = 1;
        currentBar.Stroke = new SolidColorBrush(Colors.Black);

        _selectedPolygon = currentBar;
    }
}

void oneBar_MouseLeave(object sender, MouseEventArgs e)
{
    if (_selectedPolygon != null)
    {
        _selectedPolygon.StrokeThickness = 0;
        _selectedPolygon.Stroke = null;

        ValueTextBlock.Visibility = Visibility.Collapsed;

        _selectedPolygon = null;
    }
}

Test the new functionality. F5.

Nice, isn't it? Much better than just static image. And no need to struggle with javascript. Here we have almost all the power of .Net 3.5, with Visual Studio 2008 debugging.

The last thing that we will add to our graph is a value of net profit displayed on top of the bar when the user moves the mouse over the bar.

Firstly we wil add a Dictionary with the values of net profit for each year:

private Dictionary<int, int> _yearProfits;

private void FillYearProfits()
{
    _yearProfits = new Dictionary<int, int>();

    _yearProfits.Add(2003, 66000);
    _yearProfits.Add(2004, 79000);
    _yearProfits.Add(2005, 85000);
    _yearProfits.Add(2006, 116000);
    _yearProfits.Add(2007, 210000);
}

private int GetProfitForBar(string barName)
{
    int year;
    int profit;

    try
    {
        year = Int32.Parse(barName.Substring(3));

        profit = _yearProfits[year];
    }
    catch
    {
        profit = 0;
    }

    return profit;
}

There is also a GetProfitForBar method that is used to get the profit value from the bar name - so for "bar2004" the method will return 85000.

We call FillYearProfits from Page constructor - before call to RegisterBarEvents.

Now add a TextBlock that will display the net profit value. At the end of xaml (after the last Path and before the closing Canvas element) add the TextBlock element that will display the net profit value of the selected bar. This could be also done in code behind but with xaml it is easier to check what we did with the VS designer preview.

The Visibility of the TextBlock is currently set to Collapsed.

The net profit value will be displayed on top of the selected bar. To do this we will need to know where the bar is positioned. All the bars are created with Polygon shapes that contains Points collection - the position of the bar can be get from it. The following method gets a PointCollection and returns a Rect struct that contains the bounds (position and size) of the element defined by the PointCollection:

private Rect GetPolygonBounds(PointCollection points)
{
    double minX, minY;
    double maxX, maxY;

    if (points == null || points.Count == 0) return Rect.Empty;

    maxX = 0;
    maxY = 0;
    minX = double.MaxValue;
    minY = double.MaxValue;

    foreach (Point onePoint in points)
    {
        if (onePoint.X < minX)
            minX = onePoint.X;

        if (onePoint.Y < minY)
            minY = onePoint.Y;

        if (onePoint.X > maxX)
            maxX = onePoint.X;

        if (onePoint.Y > maxY)
            maxY = onePoint.Y;
    }

    return new Rect(minX, minY, maxX - minX, maxY - minY);
}

Now we have to add the code to display the TextBlock.

Add the following code to the oneBar_MouseEnter method after setting _selectedPolygon field:

        ValueTextBlock.Text = string.Format("${0:#,000.00}",
                              GetProfitForBar(currentBar.Name));
        ValueTextBlock.Measure(new Size(double.PositiveInfinity,
                                        double.PositiveInfinity));

        Rect polygonBounds = GetPolygonBounds(currentBar.Points);

        ValueTextBlock.Visibility = Visibility.Visible;

        Canvas.SetLeft(ValueTextBlock,
            polygonBounds.X + (polygonBounds.Width / 2) -
            (ValueTextBlock.ActualWidth / 2));

        Canvas.SetTop(ValueTextBlock, polygonBounds.Y - 20);

The code firstly sets the ValueTextBlock Text to the net profit value of the currently selected bar. Now size of the ValueTextBlock is measured - the parameter to the Measure method just says that the control can use all the space it needs.

Now we can position the ValueTextBlock to the appropriate location.

All that is left to do is to hide the ValueTextBlock when the mouse leaves the bar. Add the following to oneBar_MouseLeave:

ValueTextBlock.Visibility = Visibility.Collapsed;

That is it.
If everything went well (i.e. if my instructions were clear enough) our application should now dynamically show the selected bar with the net profit value.

The final solution with the full source is included in the package.

> TRY THE APPLICATION

You are also invited to read the next tutorial that will show some advance Silverlight techniques. It will also show how to convert svg files to the Silverlight.

Additional notes:

Microsoft Office 2007 uses an advanced rendering for showing graphs, word art and other elements. This means that the images are usually not rendered with vector elements like polygons and rectangles but bitmap images. When such images are converted to xaml with Paste2Xaml the results are not good. So it is recommended that if you need to convert and image to xaml, switch off all advanced effects like shadows, 3d, etc. The best option here is to use Office 2003 which is using vector elements - for the office users the end effect is not so nice, but for converting the element to xaml this can be much better.

Silverlight 2 is in beta phase, so there can be some problems with it. It is also possible that some of the classes, properties and methods will be renamed or changed when it comes to final. So I recommend some patience when working with it. However Silverlight in my openion has a really bright future and is really worth trying.

Please promote the site by linking to it or by increasing its rating on dotnetkicks - click on image:



\| Home \| Blog \| Users Forum \| Contact Us \| Terms and Conditions \| Privacy Policy \|

Copyright © 2006-2012 AB4D d.o.o. All Rights Reserved.