Creating Excel Charts Dynamically from Database, using VB.NET






4.83/5 (46 votes)
May 12, 2006
2 min read

381346

13985
An article on generating an Excel sheet with different charts, based on the data in the sheet, and then emailing it.
Introduction
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 DataGrid
s 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.
Try
Dbopen()
'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
File.Delete(Filename)
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
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
'to check the session of excel application
chkexcel = True
oexcel.Visible = True
'this procedure populate the sheet
Generate_Sheet()
'save excel file
obook.SaveAs(Filename)
'end application object and session
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
oexcel.Quit()
oexcel = Nothing
chkexcel = False
'mail excel file as an attachment
automail("send.file@somedomain.com", _
"Auto Excel File", _
"any message", Filename)
End If
Catch ex As Exception
'mail error message
automail("err.mail@somedomain.com", _
"Error Message", ex.Message, "")
Finally
Dbclose()
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 & _
"db.mdb'"
conn.Open()
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.Close()
conn.Dispose()
conn = Nothing
End If
'check and close excel application
If chkexcel = True Then
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
oexcel.Quit()
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:I1").Merge()
osheet.Range("A1").Value = "Excel Automation With Charts"
osheet.Range("A1").EntireColumn.AutoFit()
'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("A3").BorderAround(8)
osheet.Range("B3").Value = "Sale"
osheet.Range("B3").BorderAround(8)
osheet.Range("C3").Value = "Income"
osheet.Range("C3").BorderAround(8)
'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 = _
SQlReader.GetValue(0).ToString
osheet.Range("A" & R).BorderAround(8)
osheet.Range("B" & R).Value = _
SQlReader.GetValue(1).ToString
osheet.Range("B" & R).BorderAround(8)
osheet.Range("C" & R).Value = _
SQlReader.GetValue(2).ToString
osheet.Range("C" & R).BorderAround(8)
End While
SQlReader.Close()
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
'ochart.Location(Excel.XlChartLocation.
' xlLocationAsObject, osheet.Name)
With oChart
'set data range for chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", "C" & R)
.SetSourceData(chartRange)
'set how you want to draw chart i.e column wise or row wise
.PlotBy = Excel.XlRowCol.xlColumns
'set data lables for bars
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
'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
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).
AxisTitle.Characters.Text = "Items"
xlAxisValue = CType(oChart.Axes(, _
Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlAxisValue.Item(Excel.XlAxisType.xlValue).
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
Loop
'display value
osheet.Cells(R, columnno).Value = Htotal
'format colums
With DirectCast(osheet.Columns(columnno), Excel.Range)
.AutoFit()
.NumberFormat = "0,00"
End With
Next
'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)
.SetSourceData(chartRange)
.PlotBy = Excel.XlRowCol.xlRows
.ChartType = Excel.XlChartType.xl3DPie
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent)
.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
attach.Add(filename)
End If
If Trim(mail_to) <> "" Then
myMailItem.to = Trim(mail_to)
End If
myMailItem.SUBJECT = subject
myMailItem.send()
myMailItem = Nothing
myOutlook = Nothing
End Sub
Working with the demo projects
To work with the Excel_automation project in VB.NET:
- Extract the Excel_Automation_demo.zip file to a designated directory.
- Run the Excel_Automation.exe file.
- 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
- Extract the Excel_Automation_src.zip file to a designated directory.
- Open up the demo solution Excel_Automation_src.zip in Visual Studio 2003.
- 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.
- Change the name and path of your database file in the “
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.
Summary
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.