使用 VB.NET 从数据库动态创建 Excel 图表

• 下载源文件 - 454 Kb
• 下载演示项目 - 449 Kb

引言

将数据从 DataTable 导出到 Excel，并使用图表进行比较和分析，是报告和演示中最常见的任务之一。 我们可以使用 DataGrid 或其他一些报告工具来开发此类文件，但通过使用 VBA，我们可以根据所需的报告样式和目的生成完全格式化的自动化 Excel 报告。 本文包括此类功能的源代码和完整的演示项目。

代码，真正的乐趣

应用程序中主要有以下三个代码部分

• 数据库连接和工作簿生成
• 数据填充和图表生成
• 自动邮件

主要部分

这部分代码生成一个 Excel 工作簿，并调用其他一些过程来处理数据库、填充数据和自动发送邮件。

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

dbopen 过程

此函数用于打开数据库连接

'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()

dbclose 过程

此函数用于关闭数据库连接和应用程序会话

'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

Generate_Sheet 过程

此过程填充 Excel 表格并绘制图表

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

Automail 过程

此过程用于发送错误消息电子邮件或发送新生成的 Excel 文件

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

使用演示项目

要在 VB.NET 中使用 Excel_automation 项目

1. 将 Excel_Automation_demo.zip 文件解压到指定目录。
2. 运行 Excel_Automation.exe 文件。
3. 将在同一文件夹中创建一个名为 abc.xls 的 Excel 文件，并检查该文件以查看输出。

使用代码

1. 将 Excel_Automation_src.zip 文件解压到指定目录。
2. 在 Visual Studio 2003 中打开演示解决方案 Excel_Automation_src.zip。
3. 如果需要，更改您的数据库文件，然后进行以下更改
   • 在 “Dbopen” 过程中更改您的数据库文件的名称和路径。
   • 在 “Generate_Sheet” 中对报告名称、报告标题、列标题、图表标题等进行适当的更改。
   • 根据您的数据库源修改您的 SQL 查询。

关注点

• 这可用于自动化任何类型的 Excel 报告。
• 相同的代码可用于 Web。
• 您甚至可以根据您想要的格式自定义报告。
• 您可以将此报告通过电子邮件发送到任何所需的电子邮件地址。
• 如果发生错误，您将收到一条错误消息，因此您无需检查其执行情况。

摘要

此代码提供了一种非常友好的方式来生成完全格式化、时尚且图形化的表格。 您可以在任何 Web 表单、Windows 表单或控制台应用程序上实现此代码。