.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.
C#
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)
{
lstSales.Add(
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"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:chartingToolkit="clr-namespace:System.Windows.Controls.DataVisualization.Charting;assembly=System.Windows.Controls.DataVisualization.Toolkit"
Title="Window_SalesGraph" Height="635" Width="1029" Loaded="Window_Loaded">
<Window.Resources>
<DataTemplate x:Key="CompanyNameTemplate">
<TextBlock Text="{Binding CompanyName}"></TextBlock>
</DataTemplate>
</Window.Resources>
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="65*" />
<RowDefinition Height="262*" />
</Grid.RowDefinitions>
<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"
Name="chartSales"
Title="Company wise Sales" VerticalAlignment="Top" Width="600" Grid.Row="1">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries x:Name="pieSeries" DependentValuePath="Value" IndependentValuePath="Key"
/>
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
<Button Content="Generate Excel"
Grid.Row="1" Height="23" HorizontalAlignment="Left" Margin="31,160,0,0"
Name="btnGenerateExcelreport" VerticalAlignment="Top" Width="274"
Click="btnGenerateExcelreport_Click"/>
</Grid>
</Window>
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.
C#
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
{
///<summary>
/// Interaction logic for Window_SalesGraph.xaml
///</summary>
public partial class Window_SalesGraph : Window
{
DataAccess objDs;
public Window_SalesGraph()
{
InitializeComponent();
}
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)
{
try
{
//Get the Excel Object
var MyExcel = new MSExcel.Application();
//Add the Workbook
MyExcel.Workbooks.Add();
//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)
{
MessageBox.Show(ex.Message);
}
}
}
}
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()
InitializeComponent()
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)
Try
'Get the Excel Object
Dim MyExcel = New MSExcel.Application()
'Add the Workbook
MyExcel.Workbooks.Add()
'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
MessageBox.Show(ex.Message)
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
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades 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), and Front-end technologies like Angular and React. Follow him on twitter @
maheshdotnet or connect with him on
LinkedIn