Click here to Skip to main content
15,172,351 members
Articles / Programming Languages / SQL
Posted 12 May 2006


131 bookmarked

Creating Excel Charts Dynamically from Database, using VB.NET

Rate me:
Please Sign up or sign in to vote.
4.83/5 (49 votes)
12 May 20062 min read
An article on generating an Excel sheet with different charts, based on the data in the sheet, and then emailing it.

Sample Image - Excel_Automation.jpg


Exporting data from a DataTable to Excel, with charts for comparisons and analysis, is one of the most common tasks for reporting and presentations. We can develop such files by using DataGrids or some other reporting tools, but by using VBA, we can generate fully formatted automated Excel reports according to the desired reporting style and purposes. This article includes the source code and full demo project for such a functionality.

Code, the real fun

There are the following three main sections of code in the application:

  • Database connection and workbook generation
  • Data population and chart generation
  • Auto mail

Main part:

This part of the code generates an Excel workbook and calls some other procedures for database handling, data population, and auto-mailing.

    'File name and path, here i used abc file 
    'to be stored in Bin directory in the sloution directory
    Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
    'check if file already exists then 
    'delete it to create a new file
    If File.Exists(Filename) Then
    End If
    If Not File.Exists(Filename) Then
        chkexcel = False
        'create new excel application
        oexcel = CreateObject("Excel.Application")
        'add a new workbook
        obook = oexcel.Workbooks.Add
        'set the application alerts not 
        'to be displayed for confirmation
        oexcel.Application.DisplayAlerts = True
        'check total sheets in workboob
        Dim S As Integer = oexcel.Application.Sheets.Count()
        'leaving first sheet delete all the remaining sheets
        If S > 1 Then
            oexcel.Application.DisplayAlerts = False
            Dim J As Integer = S
            Do While J > 1
                J = oexcel.Application.Sheets.Count()
           End If
        'to check the session of excel application
        chkexcel = True

        oexcel.Visible = True
        'this procedure populate the sheet
        'save excel file
        'end application object and session
        osheet = Nothing
        oexcel.Application.DisplayAlerts = False
        oexcel.Application.DisplayAlerts = True
        obook = Nothing
        oexcel = Nothing
        chkexcel = False
        'mail excel file as an attachment
        automail("", _
                 "Auto Excel File", _
                 "any message", Filename)
    End If
Catch ex As Exception
    'mail error message
    automail("", _
             "Error Message", ex.Message, "")
End Try

The dbopen procedure:

This function is used to open the database connections:

'open connection for db.mdb stroed in the base directory
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _ 
                        "Data Source='" & _
                        AppDomain.CurrentDomain.BaseDirectory & _

The dbclose procedure:

This function is used to close the database connections and application sessions:

'check and close db connection
If conn.State = ConnectionState.Open Then
    conn = Nothing
End If
'check and close excel application
If chkexcel = True Then
    osheet = Nothing
    oexcel.Application.DisplayAlerts = False
    oexcel.Application.DisplayAlerts = True
    obook = Nothing
    oexcel = Nothing
End If

The Generate_Sheet procedure:

This procedure populates the Excel sheet and draws the charts:

Console.WriteLine("Generating Auto Report")
osheet = oexcel.Worksheets(1)
'rename the sheet
osheet.Name = "Excel Charts"
osheet.Range("A1:AZ400").Interior.ColorIndex = 2
osheet.Range("A1").Font.Size = 12
 osheet.Range("A1").Font.Bold = True
osheet.Range("A1").Value = "Excel Automation With Charts"
'format headings
osheet.Range("A3:C3").Font.Color = RGB(255, 255, 255)
osheet.Range("A3:C3").Interior.ColorIndex = 5
osheet.Range("A3:C3").Font.Bold = True
osheet.Range("A3:C3").Font.Size = 10
'columns heading
osheet.Range("A3").Value = "Item"
osheet.Range("B3").Value = "Sale"
osheet.Range("C3").Value = "Income"

'populate data from DB
Dim SQlQuery As String = "select * from Sales"
Dim SQLCommand As New OleDbCommand(SQlQuery, conn)
Dim SQlReader As OleDbDataReader = SQLCommand.ExecuteReader
Dim R As Integer = 3
While SQlReader.Read
    R = R + 1
    osheet.Range("A" & R).Value = _
    osheet.Range("A" & R).BorderAround(8)
    osheet.Range("B" & R).Value = _
    osheet.Range("B" & R).BorderAround(8)
    osheet.Range("C" & R).Value = _
    osheet.Range("C" & R).BorderAround(8)
End While

SQlReader = Nothing
'create chart objects
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects
'set chart location
MyCharts1 = MyCharts.Add(150, 30, 400, 250)
oChart = MyCharts1.Chart
'use the follwoing line if u want 
'to draw chart on the default location
'         xlLocationAsObject, osheet.Name)

With oChart
    'set data range for chart
    Dim chartRange As Excel.Range
    chartRange = osheet.Range("A3", "C" & R)
    'set how you want to draw chart i.e column wise or row wise
    .PlotBy = Excel.XlRowCol.xlColumns
    'set data lables for bars
    'set legend to be displayed or not
    .HasLegend = True
    'set legend location
    .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
    'select chart type
    '.ChartType = Excel.XlChartType.xl3DBarClustered
    'chart title
    .HasTitle = True
    .ChartTitle.Text = "Sale/Income Bar Chart"
    'set titles for Axis values and categories
    Dim xlAxisCategory, xlAxisValue As Excel.Axes
    xlAxisCategory = CType(oChart.Axes(, _
                     Excel.XlAxisGroup.xlPrimary), Excel.Axes)
    xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
                        AxisTitle.Characters.Text = "Items"
    xlAxisValue = CType(oChart.Axes(, _
                  Excel.XlAxisGroup.xlPrimary), Excel.Axes)
    xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
                     AxisTitle.Characters.Text = "Sale/Income"
End With

'set style to show the totals
R = R + 1
osheet.Range("A" & R & ":C" & R).Font.Bold = True
osheet.Range("A" & R & ":C" & R).Font.Color = RGB(255, 255, 255)
osheet.Range("A" & R).Value = "Total"
osheet.Range("A" & R & ":C" & R).Interior.ColorIndex = 5
osheet.Range("A" & R & ":C" & R).BorderAround(8)
'sum the values from column 2 to 3
Dim columnno = 2
For columnno = 2 To 3
    Dim Htotal As String = 0
    Dim RowCount As Integer = 4
    Do While RowCount <= R
        Htotal = Htotal + osheet.Cells(RowCount, columnno).value
        osheet.Cells(RowCount, columnno).borderaround(8)
        RowCount = RowCount + 1
    'display value
    osheet.Cells(R, columnno).Value = Htotal
    'format colums
    With DirectCast(osheet.Columns(columnno), Excel.Range)
        .NumberFormat = "0,00"
    End With

'add a pie chart for total comparison
MyCharts = osheet.ChartObjects
MyCharts1 = MyCharts.Add(150, 290, 400, 250)
oChart = MyCharts1.Chart
With oChart
    Dim chartRange As Excel.Range
    chartRange = osheet.Range("A" & R, "C" & R)
    .PlotBy = Excel.XlRowCol.xlRows
    .ChartType = Excel.XlChartType.xl3DPie

    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "Sale/Income Pie Chart"
    .ChartTitle.Font.Bold = True
End With

The Automail procedure:

This procedure is used to send an error message email or to send a newly generated Excel file:

Public Sub automail(ByVal mail_to As String, _
       ByVal subject As String, ByVal msg As String, _
       ByVal filename As String)
    Dim myOutlook As New Outlook.Application()
    Dim myMailItem, attach As Object

    myMailItem = myOutlook.CreateItem(Outlook.OlItemType.olMailItem)
    myMailItem.Body = msg
    If File.Exists(filename) Then
        attach = myMailItem.Attachments
    End If

    If Trim(mail_to) <> "" Then = Trim(mail_to)
    End If
    myMailItem.SUBJECT = subject
    myMailItem = Nothing
    myOutlook = Nothing
End Sub

Working with the demo projects

To work with the Excel_automation project in VB.NET:

  1. Extract the file to a designated directory.
  2. Run the Excel_Automation.exe file.
  3. An Excel file with the name abc.xls will be created in the same folder, and check the file to have a look at the output.

Working with the code

  1. Extract the file to a designated directory.
  2. Open up the demo solution in Visual Studio 2003.
  3. Change your database file, if you want, and then make the following changes:
    • Change the name and path of your database file in the “Dbopen” procedure.
    • Make proper changes for the report name, report heading, columns headings, chart heading etc. in “Generate_Sheet”.
    • Modify your SQL query according to your database source.

Points of interest

  • This can be used to automate any kind of Excel reports.
  • The same code can be used for the web.
  • You can even customize the report according to your desired format.
  • You can email this report to any desired email address(es).
  • In case of error, you will receive an error message so you do not need to check its execution.


This code provides a very friendly way to generate fully formatted, stylish, and graphical sheets. You can implement this code on any web form, Windows form, or console application.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Malik Nasir
Program Manager ZonG, China Mobile Company
Pakistan Pakistan
I am working as Manager Enterprise Services where key responsibilities are to support end users on their day to day IT related issues.
The other function I am responsible is IT IPCC Services Support Since September 2013 and my key responsibilities are to maintain IPCC Uptime along with end users support at both CMPak call centers. IPCC enhancements and new feature development is also part of my job role.
The third area that was handed over to me in March 2015 is in-house apps support, where key responsibility is to provide support for all the application developed by IT department and to maintain the availability of these applications.
Previously I was working as Manager Automation & Planning and this is my key area of expertise. My responsibilities were to manage team in Project Planning, System Analysis, Requirements gathering, Preparation of SRS, Presentations to client about the requirements, getting Approvals on SRS from the Client, Database Design, Development Tracking, System Deployments, end user Feedback along with Change Management.

Planning and Defining Scope, Resource Planning, Time Estimating, Creating Charts and Schedules, Risk Analysis
Managing Risks and Issues, Monitoring and Reporting Progress, Team Leadership, Working with Vendors, Scalability were the key area of working along with an efficient problem-solver in professional way with envisions business and technical perspectives to develop workable solutions.

I started my career as developer in 2004 and promoted as team lead IT Automation in 2007 and then as manager so I spend 7 years in Software Development & Project Planning.

Comments and Discussions

QuestionNot able to set Xaxis data it automatically comes of excel sr no Pin
Adesh Saroha12-Mar-15 7:19
MemberAdesh Saroha12-Mar-15 7:19 
GeneralMy vote of 3 Pin
Niravvasoya9-Jan-15 20:45
MemberNiravvasoya9-Jan-15 20:45 
BugError in it(osheet = oexcel.Worksheets(1)) Pin
jawaharraj894-Apr-12 21:29
Memberjawaharraj894-Apr-12 21:29 
GeneralMy vote of 5 Pin
momsaysnogf15-Aug-11 7:47
Membermomsaysnogf15-Aug-11 7:47 
Generalanother way Pin
RyanALEX16-Aug-10 20:09
MemberRyanALEX16-Aug-10 20:09 
GeneralWhat's true to today is true tomorrow Pin
RedDk29-Apr-10 8:49
MemberRedDk29-Apr-10 8:49 
GeneralRequest for form design Pin
santy_santhosh20-Jul-09 21:08
Membersanty_santhosh20-Jul-09 21:08 
GeneralAlternative [modified] Pin
FilipKrnjic9-Jul-09 2:06
MemberFilipKrnjic9-Jul-09 2:06 
GeneralThat is really nice and simple code Thanks Pin
Derek Pescod6-Dec-08 13:04
MemberDerek Pescod6-Dec-08 13:04 
QuestionCreating Excel charts dynmiaclly have samll problem Pin
vamsi11127-Nov-08 23:51
Membervamsi11127-Nov-08 23:51 
GeneralVery Good Pin
Fahim A26-Nov-08 23:45
MemberFahim A26-Nov-08 23:45 
GeneralUnable to use chart type Column - Line Pin
Member 260396012-Nov-08 4:50
MemberMember 260396012-Nov-08 4:50 
GeneralDo you have any idea about Scatter charts labeling Pin
hira_mannan4-Sep-08 22:30
Memberhira_mannan4-Sep-08 22:30 
GeneralSupported 2000 - 2007 Pin
klaydze31-Jul-08 17:35
Memberklaydze31-Jul-08 17:35 
Generaluse graph with my application Pin
Anubhava Dimri25-Jul-08 3:48
MemberAnubhava Dimri25-Jul-08 3:48 
Generalcreate graph using excel Pin
Anubhava Dimri25-Jul-08 3:47
MemberAnubhava Dimri25-Jul-08 3:47 
Generali want learn macro coading Pin
dharmendra.keshari5-Jul-08 20:30
Memberdharmendra.keshari5-Jul-08 20:30 
GeneralSetting Category names... Pin
ms_philly_b28-May-08 9:43
Memberms_philly_b28-May-08 9:43 
GeneralSave data into excel file [modified] Pin
CLLee23-Apr-08 23:43
MemberCLLee23-Apr-08 23:43 
GeneralErrors when Run this project Pin
sugarcandi28-Mar-08 8:12
Membersugarcandi28-Mar-08 8:12 
Generalwhich component is add to the reference in .net to access excel sheet Pin
sandip khairnar11-Feb-08 23:34
Membersandip khairnar11-Feb-08 23:34 
GeneralRe: which component is add to the reference in .net to access excel sheet Pin
Malik Nasir12-Feb-08 0:18
MemberMalik Nasir12-Feb-08 0:18 
Generalrepresenting data in tabular format Pin
adithya52127-Aug-07 21:52
Memberadithya52127-Aug-07 21:52 
GeneralChanging Bar Colors Pin
cofrari6-Jun-07 10:48
Membercofrari6-Jun-07 10:48 
AnswerRe: Changing Bar Colors Pin
dougcordova6-Oct-07 8:07
Memberdougcordova6-Oct-07 8:07 

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

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