Click here to Skip to main content
11,705,763 members (50,481 online)
Click here to Skip to main content

Create Column Charts Using OWC11

, 5 Jul 2008 CPOL 118.4K 5.7K 68
Rate this:
Please Sign up or sign in to vote.
A column chart (simple,stacked, or 100% stacked column) representation using Office Web Components.

Introduction

This is my very first article to any open source site. Since I've gotten a lot of useful articles from this site, I'll be glad if I can to contribute to this great site. This is a simple application to get some knowledge of using the OWC11 in a .NET application for chart representation.

Office Web Components (OWC) are a group of OLE classes implemented as ActiveX controls in Microsoft Office 2000, Office XP, and Office 2003. The Office Web Components have been discontinued in Office 2007, and are not included, except as a part of the Office Project Server 2007. OWC can be used by any COM-compliant Component Object Model programming language.

Background

Any application that uses the Office Web Components 11 requires that the OWC 11 package be installed on the system. OWC 11 is a separate download available on the Microsoft Website or in the Microsoft Office 2003 Installation Kit. Office Web Components 11 does not require support from Microsoft Office.

Office 2003 Web Components do not provide event handling by default. We need to do some alterations. So, please refer this link first: HOW TO: Handle Events for the Office 2003 Web Components in Visual Studio.NET. Using this link, we can get AxOWC11.dll. Add this newly-compiled AxOWC11.dll to the Toolbox. Now, the Toolbox contains the AxChartSpace control, the AxPivotTable control, and the AxSpreadsheet control.

Column Chart

A column chart displays values and series groups as sets of vertical columns that are grouped by category. Values are represented by the height of the columns as measured by the y-axis. Category labels are displayed on the x-axis. Column charts are typically used to compare values between categories. There are three types of column charts:

  • Column
  • Stacked Column
  • 100% Stacked Column

A column chart displays series as individual columns, grouped by category. The height of each column is determined by the series value.

A stacked column chart displays all series stacked in a single column for each category. The height of each column is determined by the total of all the series values for the category.

A 100% stacked column chart displays all series stacked in a single column for each category. The height of each column is always the full height of the chart. The series values are displayed as percentages of each column.

The abbove definition of the column chart is picked from the MSDN. For more details, please refer: Column Chart.

Steps for Creating the Chart

First of all, from the Toolbox property window, select the AxChartSpace control and drag it on to the design surface. After this step, our form looks like the following:

MainScreen.jpg

Figure-1

Now, in the code window, write the following statement:

Imports owc11 = Microsoft.Office.Interop.Owc11

Since the code only shows how can we create the Column Chart, the code is not efficient. Here, we are skipping some statements which fill dummy data in the DataGridView. Now, we declare two variables for holding data for the categories (related to the x-axis) and the values (related to the y-axis):

Dim categories(3)
For i As Integer = 0 To 3
    categories(i) = DataGridView1.Columns(i + 1).HeaderText.ToString
Next
Dim values(3)
Dim chConstants

For clearing the contents of the chart workspace, use the following statement. The following statement removes any old charts that may already exist and adds a chart to the chart space.

AxChartSpace1.Clear()
AxChartSpace1.Charts.Add()
chConstants = AxChartSpace1.Constants

Now, add series to the existing chart. Using the following statement, you can add as much series as you want.

AxChartSpace1.Charts(0).SeriesCollection.Add()

Now, we supply the data to each series as follows:

Dim MaxTotal As Integer = 0
For j As Integer = 0 To DataGridView1.Rows.Count - 2
AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimCategories, _
              chConstants.chDataLiteral, categories)
    For i As Integer = 1 To DataGridView1.Columns.Count - 1
        values(i - 1) = Val(DataGridView1.Rows(j).Cells(i).Value)
    Next
    AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimValues, _
                            chConstants.chDataLiteral, values)
    AxChartSpace1.Charts(0).SeriesCollection(j).Caption = _
                  DataGridView1.Rows(j).Cells(0).Value
    MaxTotal = MaxTotal + Val(DataGridView1.Rows(j).Cells(4).Value)
Next

We can also set the color for each series as follows:

AxChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = "DarkOrange"
AxChartSpace1.Charts(0).SeriesCollection(1).Interior.Color = "Cyan"
AxChartSpace1.Charts(0).SeriesCollection(2).Interior.Color = "Yellow"
AxChartSpace1.Charts(0).SeriesCollection(3).Interior.Color = "Red"
AxChartSpace1.Charts(0).SeriesCollection(4).Interior.Color = "Black"

Notice one thing in the above statements. I supply the color for a series as a string. It does not support the Color enum. At this point, we decide which type of chart we want (simple, stacked, or 100 % stacked column). Also, we can draw charts with 3D effect. Use the following statement for a simple and a 3D stacked column.

If cboChartType.Text = "3D" Then
    AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked3D
    '(See Figure-2)
Else
    AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked
    (See Figure-3)
End If

StackedColumn3D.JPG

Figure-2

StackedColumn.JPG

Figure-3

And for a simple and 3D 100% stacked column, use the following statement:

If cboChartType.Text = "3D" Then
    AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked1003D
    (see Figure-4)
Else
    AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked100
    (see Figure-5)
End If

100PercentStackedColumn3D.JPG

Figure-4

100PercentStackedColumn.JPG

Figure-5

And finally, for a simple and 3D column, use the following statement:

If cboChartType.Text = "3D" Then
    AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumn3D
    (see Figure-6)
End If

SimpleColumn3D.JPG

Figure-6

SimpleColumn.JPG

Figure-7

Along with the above statements, the way we provide the data to all the charts is also responsible for getting the correct results. In all the charts, the variable MaxTotal also plays a role which decides the scaling of the chart.

AxChartSpace1.Charts(0).Axes(1).Scaling.Maximum = MaxTotal
AxChartSpace1.Charts(0).Axes(1).MajorUnit = MaxTotal / 4
AxChartSpace1.Charts(0).Axes(1).Scaling.Minimum = 0

Now, in the final step, we assign proper title and font to the chart axes, as follows:

AxChartSpace1.Charts(0).Axes(0).HasTitle = True
AxChartSpace1.Charts(0).Axes(0).Title.Caption = "Time Range"
AxChartSpace1.Charts(0).Axes(0).Title.Font.Name = "Arial"
AxChartSpace1.Charts(0).Axes(0).Title.Font.Size = 9

AxChartSpace1.Charts(0).Axes(1).HasTitle = True
AxChartSpace1.Charts(0).Axes(1).Title.Caption = "Number of Members"
AxChartSpace1.Charts(0).Axes(1).Title.Font.Name = "Arial"
AxChartSpace1.Charts(0).Axes(1).Title.Font.Size = 9

Points of Interest

When I use the OWC 11 chart component in real applications, I get some strange results. If we declare the variable categories as follows:

Dim categories(3) As String

then the chart object does not display any series. However, the legend shows all the series names. One more thing, if we change the series color before supplying the data, then the series does not display the proper color.

I hope you've enjoyed this little tutorial. In future, I will provide you a lot more stuff. And, I am expecting your valuable suggestions.

Finally, please excuse me for my English.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rupesh Kumar Swami
Technical Lead CodeBit Technologies
India India
Possess following degrees:

* MCA from Rajasthan University, Jaipur(RAJ.), INDIA.
* PGDCA from Rajasthan University,Jaipur(RAJ.), INDIA.
* BSc (Maths) from Maharishi Dayanand Saraswati University, Ajmer(RAJ.), INDIA.


Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

You may also be interested in...

Comments and Discussions

 
QuestionHow to use AxOWC11.dll for AxChartSpace control in 64bit Pin
PawanGuru6-May-15 4:02
memberPawanGuru6-May-15 4:02 
GeneralColumn Chart using OWC11 Pin
Zackariya Yoosuf5-Jun-11 19:11
memberZackariya Yoosuf5-Jun-11 19:11 
GeneralExample runs, compiled source does not Pin
Member 121979914-May-10 3:08
memberMember 121979914-May-10 3:08 
Generalrun exe file without opening vb.net Pin
wafaajaradat3-Mar-09 17:41
memberwafaajaradat3-Mar-09 17:41 
GeneralRe: run exe file without opening vb.net Pin
Rupesh Kumar Swami3-Mar-09 20:09
memberRupesh Kumar Swami3-Mar-09 20:09 
Generalvb.net Pin
Member 210019820-Dec-08 0:55
memberMember 210019820-Dec-08 0:55 
GeneralRe: vb.net Pin
Rupesh Kumar Swami20-Dec-08 18:13
memberRupesh Kumar Swami20-Dec-08 18:13 
GeneralRe: vb.net Pin
Member 210019830-Dec-08 17:32
memberMember 210019830-Dec-08 17:32 
GeneralRe: vb.net Pin
Rupesh Kumar Swami30-Dec-08 22:18
memberRupesh Kumar Swami30-Dec-08 22:18 
Questioncan we do in vb6 Pin
deepakmathur12-Dec-08 0:38
memberdeepakmathur12-Dec-08 0:38 
AnswerRe: can we do in vb6 Pin
Rupesh Kumar Swami12-Dec-08 2:20
memberRupesh Kumar Swami12-Dec-08 2:20 
Generalin vb.net Pin
Member 210019818-Dec-08 17:34
memberMember 210019818-Dec-08 17:34 
Generalhow to add components of owc in vb.net,please let me know Pin
Member 210019823-Dec-08 23:30
memberMember 210019823-Dec-08 23:30 
GeneralRe: how to add components of owc in vb.net,please let me know Pin
Member 210019823-Dec-08 23:59
memberMember 210019823-Dec-08 23:59 
Generaldatagrid1 Pin
Member 210019824-Dec-08 17:36
memberMember 210019824-Dec-08 17:36 
GeneralRe: can we do in vb6 Pin
Member 210019824-Dec-08 0:26
memberMember 210019824-Dec-08 0:26 
Generaldatagridview1 Pin
Member 210019824-Dec-08 18:35
memberMember 210019824-Dec-08 18:35 
GeneralRe: datagridview1 Pin
Rupesh Kumar Swami24-Dec-08 22:40
memberRupesh Kumar Swami24-Dec-08 22:40 
GeneralRe: datagridview1 Pin
Member 210019826-Dec-08 19:57
memberMember 210019826-Dec-08 19:57 
GeneralHi rupesh Pin
Member 408299624-Sep-08 23:00
memberMember 408299624-Sep-08 23:00 
GeneralRe: Hi rupesh Pin
Rupesh Kumar Swami25-Sep-08 4:33
memberRupesh Kumar Swami25-Sep-08 4:33 
GeneralRe: Hi rupesh Pin
Member 408299625-Sep-08 19:33
memberMember 408299625-Sep-08 19:33 
GeneralThanks Pin
Rafael Linares4-Sep-08 11:24
memberRafael Linares4-Sep-08 11:24 
GeneralRupesh Pin
SAMir Nigam23-Jul-08 18:36
memberSAMir Nigam23-Jul-08 18:36 
GeneralRe: Rupesh Pin
Rupesh Kumar Swami24-Jul-08 3:33
memberRupesh Kumar Swami24-Jul-08 3:33 
Thanks Samir. Same for you .

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
My Article

GeneralRe: Rupesh Pin
SAMir Nigam24-Jul-08 18:07
memberSAMir Nigam24-Jul-08 18:07 
GeneralCongrats !!! Pin
ashu fouzdar21-Jul-08 19:21
memberashu fouzdar21-Jul-08 19:21 
GeneralRe: Congrats !!! Pin
Rupesh Kumar Swami21-Jul-08 23:04
memberRupesh Kumar Swami21-Jul-08 23:04 
QuestionHow to generate .pdf (Crystal Report) from AxMicrosoft.Office.Interop.Owc11.AxChartSpace in C# Pin
janpalmerv13-Jul-08 22:42
memberjanpalmerv13-Jul-08 22:42 
AnswerRe: How to generate .pdf (Crystal Report) from AxMicrosoft.Office.Interop.Owc11.AxChartSpace in C# Pin
Rupesh Kumar Swami19-Jul-08 2:58
memberRupesh Kumar Swami19-Jul-08 2:58 
AnswerRe: How to generate .pdf (Crystal Report) from AxMicrosoft.Office.Interop.Owc11.AxChartSpace in C# [modified] Pin
Rupesh Kumar Swami23-Sep-08 2:18
memberRupesh Kumar Swami23-Sep-08 2:18 
Generalhello i am sur Pin
SURENDER kumar suthar10-Jul-08 23:19
memberSURENDER kumar suthar10-Jul-08 23:19 
GeneralRe: hello i am sur Pin
Rupesh Kumar Swami11-Jul-08 20:06
memberRupesh Kumar Swami11-Jul-08 20:06 
GeneralRam Ram Saa! Pin
suresh suthar5-Jul-08 1:31
membersuresh suthar5-Jul-08 1:31 
GeneralRe: Ram Ram Saa! Pin
Rupesh Kumar Swami5-Jul-08 2:14
memberRupesh Kumar Swami5-Jul-08 2:14 
GeneralRe: Ram Ram Saa! Pin
suresh suthar5-Jul-08 2:37
membersuresh suthar5-Jul-08 2:37 
Generalexxcelent research Pin
eyes200728-Jun-08 22:23
membereyes200728-Jun-08 22:23 
GeneralRe: exxcelent research Pin
Rupesh Kumar Swami29-Jun-08 22:07
memberRupesh Kumar Swami29-Jun-08 22:07 
GeneralFormatting Pin
Jim Crafton25-Jun-08 6:43
memberJim Crafton25-Jun-08 6:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150819.1 | Last Updated 6 Jul 2008
Article Copyright 2008 by Rupesh Kumar Swami
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid