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()
Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
If File.Exists(Filename) Then
File.Delete(Filename)
End If
If Not File.Exists(Filename) Then
chkexcel = False
oexcel = CreateObject("Excel.Application")
obook = oexcel.Workbooks.Add
oexcel.Application.DisplayAlerts = True
Dim S As Integer = oexcel.Application.Sheets.Count()
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
chkexcel = True
oexcel.Visible = True
Generate_Sheet()
obook.SaveAs(Filename)
osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
oexcel.Quit()
oexcel = Nothing
chkexcel = False
automail("send.file@somedomain.com", _
"Auto Excel File", _
"any message", Filename)
End If
Catch ex As Exception
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:
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:
If conn.State = ConnectionState.Open Then
conn.Close()
conn.Dispose()
conn = Nothing
End If
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)
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()
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
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)
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
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects
MyCharts1 = MyCharts.Add(150, 30, 400, 250)
oChart = MyCharts1.Chart
With oChart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", "C" & R)
.SetSourceData(chartRange)
.PlotBy = Excel.XlRowCol.xlColumns
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
.HasLegend = True
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
.HasTitle = True
.ChartTitle.Text = "Sale/Income Bar Chart"
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
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)
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
osheet.Cells(R, columnno).Value = Htotal
With DirectCast(osheet.Columns(columnno), Excel.Range)
.AutoFit()
.NumberFormat = "0,00"
End With
Next
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.
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.