DotNetCurry Logo

Windows Presentation Foundation (WPF) 4.0 and Enhanced Office Programming in .NET 4.0

Posted by: Mahesh Sabnis , on 8/7/2010, in Category WPF
Views: 36963
Abstract: .NET 4.0 comes with COM Interop enhancements to make Office programming with Word, Excel etc now much easier for developers. With C# 4.0, new features like dynamic, named and optional parameters features are provided, which ultimately enhance programming capabilities.
.NET 4.0 comes with COM Interop enhancements to make Office programming with Word, Excel etc now much easier for developers. With C# 4.0, new features like dynamic, named and optional parameters features are provided, which ultimately enhance programming capabilities.
In this article, I will be using WPF 4.0 and its charting toolkit for drawing a Sales Chart for a company. This sales data then will be exported to Excel application and the chart will be drawn on excel worksheet using Excel charts. To interact with excel, ‘Microsoft.Office.Interop.Excel’ reference is added in the project.


Step 1: Open VS2010 and create a WPF 4.0 application. Name this as ‘WFP40_COMInterop_Enhancement’. To this project, add a reference to ‘Microsoft.Office.Interop.Excel’.
Step 2: In the WPF project, add a new class file and add the following classes in it. The class ‘Sales’ is a data class and class ‘DataAccess’ connects to database server and reads data from Sales Table.
using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;
namespace WFP40_COMInterop_Enhancement
    public class Sales
        public int CompanyId { get; set; }
        public string CompanyName { get; set; }
        public decimal Q1_Sales { get; set; }
        public decimal Q2_Sales { get; set; }
        public decimal Q3_Sales { get; set; }
        public decimal Q4_Sales { get; set; }
    public class DataAccess
        public ObservableCollection<Sales> GetSalesDetails()
            SqlConnection Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");
            SqlDataAdapter AdSales = new SqlDataAdapter("Select * from Sales", Conn);
            DataSet Ds = new DataSet();
            AdSales.Fill(Ds, "Sales");
            ObservableCollection<Sales> lstSales = new ObservableCollection<Sales>();
            foreach (DataRow item in Ds.Tables["Sales"].Rows)
                                new Sales()
                                    CompanyId = Convert.ToInt32(item["CompanyId"]),
                                    CompanyName = item["CompanyName"].ToString(),
                                    Q1_Sales = Convert.ToDecimal(item["Q1"]),
                                    Q2_Sales = Convert.ToDecimal(item["Q2"]),
                                    Q3_Sales = Convert.ToDecimal(item["Q3"]),
                                    Q4_Sales = Convert.ToDecimal(item["Q4"])
            return lstSales;
VB.NET (Converted Code)
Imports System
Imports System.Collections.ObjectModel
Imports System.Data.SqlClient
Imports System.Data
Namespace WFP40_COMInterop_Enhancement
      Public Class Sales
            Public Property CompanyId() As Integer
            Public Property CompanyName() As String
            Public Property Q1_Sales() As Decimal
            Public Property Q2_Sales() As Decimal
            Public Property Q3_Sales() As Decimal
            Public Property Q4_Sales() As Decimal
      End Class
      Public Class DataAccess
            Public Function GetSalesDetails() As ObservableCollection(Of Sales)
                  Dim Conn As New SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI")
                  Dim AdSales As New SqlDataAdapter("Select * from Sales", Conn)
                  Dim Ds As New DataSet()
                  AdSales.Fill(Ds, "Sales")
                  Dim lstSales As New ObservableCollection(Of Sales)()
                  For Each item As DataRow In Ds.Tables("Sales").Rows
                        lstSales.Add(New Sales() With {.CompanyId = Convert.ToInt32(item("CompanyId")), .CompanyName = item("CompanyName").ToString(), .Q1_Sales = Convert.ToDecimal(item("Q1")), .Q2_Sales = Convert.ToDecimal(item("Q2")), .Q3_Sales = Convert.ToDecimal(item("Q3")), .Q4_Sales = Convert.ToDecimal(item("Q4"))})
                  Next item
                  Return lstSales
            End Function
      End Class
End Namespace
Step 3: Open ‘MainWindow.xaml’ and write the following XAML code. This code adds  ComboBox, Chart and other WPF elements
<Window x:Class="WFP40_COMInterop_Enhancement.MainWindow"
        Title="Window_SalesGraph" Height="635" Width="1029" Loaded="Window_Loaded">
        <DataTemplate x:Key="CompanyNameTemplate">
            <TextBlock Text="{Binding CompanyName}"></TextBlock>
            <RowDefinition Height="65*" />
            <RowDefinition Height="262*" />
        <TextBlock Height="55" HorizontalAlignment="Left"
                   Margin="230,17,0,0" Name="textBlock1"
                   Text="Company Wise Sales Inforamtion"
                   VerticalAlignment="Top" Width="589"
                    TextAlignment="Center" FontSize="30"
                    Foreground="Red" FontWeight="ExtraBold" Grid.RowSpan="2" />
        <ComboBox Grid.Row="1" Height="23" HorizontalAlignment="Left" Margin="125,26,0,0"
                  Name="lstCompanyName" VerticalAlignment="Top" Width="120" ItemsSource="{Binding}"
                   ItemTemplate="{StaticResource CompanyNameTemplate}"SelectionChanged="lstCompanyName_SelectionChanged" />
        <TextBlock Grid.Row="1"Height="23" HorizontalAlignment="Left" Margin="12,26,0,0"
                   Name="textBlock2" Text="CompanyName" VerticalAlignment="Top" Width="94" />
        <chartingToolkit:Chart Height="450" HorizontalAlignment="Left" Margin="338,16,0,0"
                                Title="Company wise Sales" VerticalAlignment="Top" Width="600" Grid.Row="1">
                <chartingToolkit:PieSeries x:Name="pieSeries" DependentValuePath="Value" IndependentValuePath="Key"
        <Button Content="Generate Excel"
                Grid.Row="1" Height="23" HorizontalAlignment="Left" Margin="31,160,0,0"
                Name="btnGenerateExcelreport" VerticalAlignment="Top" Width="274"
Step 4: Open MainWindow.Xaml.cs and add the following code. This code gets sales data from the DataAccess class. On the SelectionChanged event of the Combobox, the data is put in a KeyValuePait<U,V> class so that it can used to bind to the Chart control form the charting toolkit. On the click event of the button, code is written for creating an Excel object, Workbook creation, writing data in cells in specific range and then finally creating chart using ChartType property of the Excel object.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Controls.DataVisualization.Charting;
using MSExcel = Microsoft.Office.Interop.Excel;
namespace WFP40_COMInterop_Enhancement
    /// Interaction logic for Window_SalesGraph.xaml
    public partial class Window_SalesGraph : Window
        DataAccess objDs;
        public Window_SalesGraph()
        private void Window_Loaded(object sender, RoutedEventArgs e)
            objDs = new DataAccess();
            this.DataContext = objDs.GetSalesDetails();
        string compName;
        KeyValuePair<string, decimal>[] arrSalesData;
        private void lstCompanyName_SelectionChanged(object sender, SelectionChangedEventArgs e)
            var lstSalesDetails = objDs.GetSalesDetails();
            Sales compItem = lstCompanyName.SelectedItem as Sales;
            compName = compItem.CompanyName;
            var companywiseSales = (from sales in lstSalesDetails
                                    where sales.CompanyName == compName
                                    select sales).ToList();
            arrSalesData =
               new KeyValuePair<string, decimal>[]
                    new KeyValuePair<string,decimal>(companywiseSales[0].CompanyName,companywiseSales[0].Q1_Sales),
                    new KeyValuePair<string,decimal>(companywiseSales[0].CompanyName,companywiseSales[0].Q2_Sales),
                    new KeyValuePair<string,decimal>(companywiseSales[0].CompanyName,companywiseSales[0].Q3_Sales),
                    new KeyValuePair<string,decimal>(companywiseSales[0].CompanyName,companywiseSales[0].Q4_Sales),
            chartSales.DataContext = arrSalesData;
            PieSeries thePieChart = chartSales.Series[0] as PieSeries;
            thePieChart.ItemsSource = arrSalesData;
        private void btnGenerateExcelreport_Click(object sender, RoutedEventArgs e)
                //Get the Excel Object
                var MyExcel = new MSExcel.Application();
                //Add the Workbook
                //Define the Range in which the data of the Sales Information is Displayed
                MyExcel.Cells[1, 1] = "Company Name"; //The company Name
                MyExcel.Cells[1, 2] = "Q1 Sales";
                MyExcel.Cells[1, 3] = "Q2 Sales";
                MyExcel.Cells[1, 4] = "Q3 Sales";
                MyExcel.Cells[1, 5] = "Q4 Sales";
                //Pue the data in Cell This is the Sales daat of all four quarters
                MyExcel.Cells[2, 1] = compName;
                MyExcel.Cells[2, 2] = arrSalesData[0].Value;
                MyExcel.Cells[2, 3] = arrSalesData[1].Value;
                MyExcel.Cells[2, 4] = arrSalesData[2].Value;
                MyExcel.Cells[2, 5] = arrSalesData[3].Value;
                //Display the Excel
                MyExcel.Visible = true;
                //Now to Generate the Excel Graph we must Read the Range
                //Here The type casting is not required (Specification of .NET 4.0)
                MSExcel.Range dataRange = MyExcel.Cells[2, 5];
                //Select the Active Workbook for drawing the Chart
                MSExcel.Chart saleChart = MyExcel.ActiveWorkbook.Charts.Add(MyExcel.ActiveSheet);
                saleChart.ChartWizard(Source: dataRange.CurrentRegion, Title: "Quarter wise sale from :" + compName);
                saleChart.ChartType = MSExcel.XlChartType.xlPie;
            catch (Exception ex)
VB.NET (Converted Code)
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Windows
Imports System.Windows.Controls
Imports System.Windows.Controls.DataVisualization.Charting
Imports MSExcel = Microsoft.Office.Interop.Excel
Namespace WFP40_COMInterop_Enhancement
      ''' <summary>
      ''' Interaction logic for Window_SalesGraph.xaml
      ''' </summary>
      Partial Public Class Window_SalesGraph
            Inherits Window
            Private objDs As DataAccess
            Public Sub New()
            End Sub
            Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
                  objDs = New DataAccess()
                  Me.DataContext = objDs.GetSalesDetails()
            End Sub
            Private compName As String
            Private arrSalesData() As KeyValuePair(Of String, Decimal)
            Private Sub lstCompanyName_SelectionChanged(ByVal sender As Object, ByVal e As SelectionChangedEventArgs)
                  Dim lstSalesDetails = objDs.GetSalesDetails()
                  Dim compItem As Sales = TryCast(lstCompanyName.SelectedItem, Sales)
                  compName = compItem.CompanyName
                  Dim companywiseSales = (
                      From sales In lstSalesDetails
                      Where sales.CompanyName = compName
                      Select sales).ToList()
                  arrSalesData = New KeyValuePair(Of String, Decimal)()
Dim TempKeyValuePair As KeyValuePair = New KeyValuePair(Of String,Decimal)(companywiseSales(0).CompanyName,companywiseSales(0).Q1_Sales), New KeyValuePair(Of String,Decimal)(companywiseSales(0).CompanyName,companywiseSales(0).Q2_Sales), New KeyValuePair(Of String,Decimal)(companywiseSales(0).CompanyName,companywiseSales(0).Q3_Sales), New KeyValuePair(Of String,Decimal)(companywiseSales(0).CompanyName,companywiseSales(0).Q4_Sales),
                  chartSales.DataContext = arrSalesData
                  Dim thePieChart As PieSeries = TryCast(chartSales.Series(0), PieSeries)
                  thePieChart.ItemsSource = arrSalesData
            private void btnGenerateExcelreport_Click(Object sender, RoutedEventArgs e)
                        'Get the Excel Object
                        Dim MyExcel = New MSExcel.Application()
                        'Add the Workbook
                        'Define the Range in which the data of the Sales Information is Displayed
                        MyExcel.Cells(1, 1) = "Company Name" 'The company Name
                        MyExcel.Cells(1, 2) = "Q1 Sales"
                        MyExcel.Cells(1, 3) = "Q2 Sales"
                        MyExcel.Cells(1, 4) = "Q3 Sales"
                        MyExcel.Cells(1, 5) = "Q4 Sales"
                        'Pue the data in Cell This is the Sales daat of all four quarters
                        MyExcel.Cells(2, 1) = compName
                        MyExcel.Cells(2, 2) = arrSalesData(0).Value
                        MyExcel.Cells(2, 3) = arrSalesData(1).Value
                        MyExcel.Cells(2, 4) = arrSalesData(2).Value
                        MyExcel.Cells(2, 5) = arrSalesData(3).Value
                        'Display the Excel
                        MyExcel.Visible = True
                        'Now to Generate the Excel Graph we must Read the Range
                        'Here The type casting is not required (Specification of .NET 4.0)
                        Dim dataRange As MSExcel.Range = MyExcel.Cells(2, 5)
                        'Select the Active Workbook for drawing the Chart
                        Dim saleChart As MSExcel.Chart = MyExcel.ActiveWorkbook.Charts.Add(MyExcel.ActiveSheet)
                        saleChart.ChartWizard(Source:= dataRange.CurrentRegion, Title:= "Quarter wise sale from :" & compName)
                        saleChart.ChartType = MSExcel.XlChartType.xlPie
                  Catch ex As Exception
                  End Try
Step 5: Run the application, select company from the combobox and the Pie chart will be generated. Then click on ‘Generate Excel’ button, the new excel workbook will be generated. Sheet name ‘Chart1’ will show the Pie chart generated and sheet name ‘Sheet1’ will display the sales data.
Conclusion: With the COM Interop enhancements on .NET 4.0, creating MS Office interactive applications becomes more easier.
The entire source code of this article can be downloaded over here
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Tanush on Monday, February 27, 2012 7:42 PM
I have few combo box called Start date, end date, Part name, Line,
Dimension, CavityName, SubGroup. Select any item from these combo box and click load button i will get a chart.
Now What I wanted to do is, to export this chart and the combo box values to
Excel, when i click a button.
I have written code to create image from the chart and save it in my project bin folder. Now I'm not knowing
how to export the combo box + saved image to chart.
private void btnExport_Click(object sender, RoutedEventArgs e)
code that creates image from the chart.
                RenderTargetBitmap bm = new RenderTargetBitmap((int)mcChart.ActualWidth, (int)mcChart.ActualHeight, 96, 96, PixelFormats.Default);
                PngBitmapEncoder enc = new PngBitmapEncoder();
                FileStream fs = new FileStream("chart.png", FileMode.Create);
            catch (Exception ex)

Please help me. It's WPF
Comment posted by Gustavo Gonçalves on Thursday, March 14, 2013 11:02 AM

I'm creating a UserControl component of type DataVisualization.Chart. I'm building component based on the example of url: This chart will generate charts of the type chosen as either columns, bars, lines, Pie ... In the construction of the component, i'm inserting the Data Source than is passed through another View. So far so good. The chart for all types are generated normally.
What I need to do now are 3 modifications in the component. But I can't find a way to resolve these modifications:

* When the user places the mouse over the value generated by the graph, the ToolTip appears the value of the object .. What I need is that the ToolTip Text appear as IndependentValuePath, which would be the name + value in the following format: "Name (value)";
* When a graph is generated, it inserts as a kind of label depending on the graph is the x axis or the y axis (as pictured). I need to remove it;
* For graphs of type Column (and probably others yet to be confirmed), the outline of the rectangle must be the same color as the internal color. Besides being in the same color, each rectangle, which will change color according to a pre-defined range, which will be passed as the DataSource of the previous View .. What would be this: if the value is below 200, the color is red, if between 200 and 350, will be yellow ... and so on. I'll have about 5 limits;

How I can stylize these graphs and tooltip?

Best regards,
Comment posted by sudeep on Wednesday, July 31, 2013 5:18 AM
nice one mwn...................................