演练：在EXCEL中建立图表，数据引自SQL SERVER
Ken Getz
MCW Technologies
September 2003
概述：向你示范了如何建立一张引自Microsoft SQL Server数据的图表，使用了Excel对象模块并且在Visual Basic .NET或者是Microsoft Visual C＃中编写代码。
内容：
介绍
必备条件
开始
拦截事件句柄
增加输入数据的代码
增加图表
结论
介绍
在此演练中，你将拷贝Microsoft SQL Server Northwind 示例数据库到你的Excel工作表中，如图一所示。根据新的数据范围，你将建立一张图表如图二所示。
Figure 1. 在Excel中格式化过的数据。
Figure 2. 基于Northwind 示例数据库中数据的图表。
必备条件
为了完成此演练，下列软件和组件必须安装在计算机上。
· Microsoft Visual Studio? .NET 2003 or Microsoft Visual Basic? .NET Standard 2003
· Microsoft Visual Studio Tools for the Microsoft Office System
· Microsoft Office Professional Edition 2003
· Microsoft SQL Server or Microsoft SQL Server Desktop Engine (MSDE), (MSDE 7.0 or MSDE 2000), Northwind 示例数据库。
注意：此示例代码使用的是集成安全，如果你不能使用你的帐号登陆到SQL Server计算机上，你需要和管理员协调，并且你需要修改代码的连接信息。详情参阅.NET Framework文档的SqlConnection.ConnectionString属性。
提示：此演练假设你是一个Visual Basic .NET编程者，你需要在你的项目中设置Option Strict为On（或者在你的每一个模块中增加Option Strict）。正如你所看到的，设置Option Strict为On将会增加一点代码， 但是这样就保证你不会执行不安全类型的转换。没有此设置也可以，但是利用此设置的训练比你写代码增加的难度更重要。
开始
为了开始，使用Microsoft Office Excel 2003建立一个Visual Studio .NET项目。
建立项目
使用Visual Studio Tools建立一个Excel工作簿。
建立一个Excel 工作簿项目
1、 开始Visual Studio .NET，打开文件菜单，指向新建，点击项目。
2、 在项目类型面板中，展开Microsoft Office System项目，选择Visual Basic项目或者是Visual C＃项目。
3、 在新项目对话框的模板面板中选择Excel工作簿。
4、 命名此项目为ExcelChartDemo，然后将其保存在合适的位置。
5、 接受Microsoft Office项目向导的缺省设置。点击完成。
Visual Studio .NET为你打开ThisWorkBook.VB或者是ThisWorkbook.cs文件的代码编辑器。
布置Spreadsheet
为了装入数据并建立图表，你需要增加一些方法来运行代码。虽然你能在工作簿的Open事件中做到，然而选项却没有意义了。在此演练中，你将在工作簿中建立一个超级链接，然后激活工作簿中的FollowHyperlink事件。
在工作簿中建立一个超级链接
1、 按F5健运行此项目，装入Excel和工作簿。
2、 在Excle中，点击插入菜单，选中超级链接。
3、 在超级链接对话框中设置显示文本的值为建立新图表。
4、 在对话框左边的链接到面板中选择放在这个文本中。确保对话框中的单元格的引用和你的超级链接的位置相匹配。
当你完成以后，对话框应该如图三所示。点击OK关闭对话框，你应该在工作簿中看到新的超级链接。
Figure 3. The finished Insert Hyperlink dialog box
5、 在文件菜单中选择保存。
关闭Excel，返回Visual Studio .NET。
拦截事件处理
为了运行你的代码，你需要使.Workbook.SheetFollowHyperlink事件起作用。在这一段中，你将在Visual Basic或是C＃中增加使该事件起作用的支持。
拦截事件处理（Visual Basic）
在Visual Basic .NET中按以下步骤操作：
1、 在代码编辑器左上角的类名下拉列表中选择ThisWorkbook。
2、 在代码编辑器的方法名称下拉列表框中选择SheetFollowHyperlink。Visual Studio .NET将为你建立事件处理代码片段。
3、 修改ThisWorkbook_SheetFollowHyperlink程序，增加下列代码：
' Visual Basic
If Target.Name = "Create New Chart" Then
LoadDataAndCreateChart()
End If
拦截事件处理（C＃）
在C＃中按下列步骤操作：
1、 在OfficeCodeBehind类中，在OpenEvent和beforeCloseEvent变量宣布的地方增加新变量SheetFollowHyperlink事件：
// C#
private Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler
sheetFollowHyperlinkEvent;
2、 增加下列程序代码到此类中：
// C#
protected void ThisWorkbook_SheetFollowHyperlink(
Object sh, Excel.Hyperlink Target)
{
}
3、 增加下列代码到ThisWorkbook.Open程序中：
// C#
sheetFollowHyperlinkEvent =
new Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler(
ThisWorkbook_SheetFollowHyperlink);
thisWorkbook.SheetFollowHyperlink += sheetFollowHyperlinkEvent;
4、 增加下列代码到ThisWorkbook_SheetFollowHyperlink程序，增加下列代码：
// C#
if (Target.Name == "Create New Chart")
{
LoadDataAndCreateChart();
}
增加输入数据的代码
你需要增加代码来建立SqlDataReader，装入来自SQL Server的数据，并增加代码将数据拷贝到Excel工作簿的新表中。此次演练增加新表到工作簿中，以便你可以演练多次，你也可以增加代码来删除所用表，而不是建立图表之前激活工作表。
提示：Excel处理图表在他们自己的工作表中，与普通的工作表是分开的。因此ResetWokbook程序必须能够处理两种对象集合。Application对象中的Worksheets集合和Charts集合。
输入数据到工作表中
1、 滚动到代码模块的顶部，增加下列语句，这将减少大量的打字工作。
' Visual Basic
Imports System.Data.SqlClient
// C#
using System.Data;
using System.Data.SqlClient;
2、 增加下列变量宣布在ThisApplication和ThisWorkbook变量之后。
' Visual Basic
Private xlSheet As Excel.Worksheet
Private xlChart as Excel.Chart
// C#
private Excel.Worksheet xlSheet = null;
private Excel.Chart xlChart = null;
3、 在OfficeCodeBehind类中，增加下列程序，它将为你刷新工作簿和内容：
' Visual Basic
Private Sub ResetWorkbook()
' Get rid of all but the original worksheet.
Try
ThisApplication.DisplayAlerts = False
Dim ws As Excel.Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws Is ThisApplication.ActiveSheet Then
ws.Delete()
End If
Next
Dim cht As Excel.Chart
For Each cht In ThisWorkbook.Charts
cht.Delete()
Next
Finally
ThisApplication.DisplayAlerts = True
End Try
End Sub
// C#
private void ResetWorkbook()
{
// Get rid of all but the original worksheet.
try
{
ThisApplication.DisplayAlerts = false;
foreach (Excel.Worksheet ws in ThisWorkbook.Worksheets)
if (ws != ThisApplication.ActiveSheet)
{
ws.Delete();
}
foreach ( Excel.Chart cht in ThisWorkbook.Charts)
cht.Delete();
}
finally
{
ThisApplication.DisplayAlerts = true;
}
}
提示：Excel通常在删除工作表时会提示，本示例代码在删除之前设置Application.DisplayAlerts属性为False，删除之后设置回为True。
4、 增加下列程序在OfficeCodeBehind类中，此段程序连接本地计算机中的SQL Server，使用集成安全，接着返回一个SqlDataAdapter，接受数据供工作簿使用。
' Visual Basic
Private Function GetDataReader() As SqlDataReader
Dim cnn As New SqlConnection("Server='.';" & _
"Database=Northwind;Integrated Security=true")
Dim cmd As New SqlCommand( _
"SELECT Categories.CategoryName," & _
" AVG([Order Details].Quantity) AS AvgQty " & _
"FROM Products INNER JOIN [Order Details] " & _
"ON Products.ProductID = [Order Details].ProductID " & _
"INNER JOIN Categories ON " & _
" Products.CategoryID = Categories.CategoryID " & _
"GROUP BY Categories.CategoryName " & _
"ORDER BY AVG([Order Details].Quantity) DESC, " & _
" Categories.CategoryName", cnn)
cnn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function
// C#
private SqlDataReader GetDataReader()
{
SqlConnection cnn = new SqlConnection(
"Server=.;" +
"Database=Northwind;" +
"Integrated Security=true");
SqlCommand cmd = new SqlCommand(
"SELECT Categories.CategoryName," +
" AVG([Order Details].Quantity) AS AvgQty " +
"FROM Products INNER JOIN [Order Details] " +
"ON Products.ProductID = [Order Details].ProductID " +
"INNER JOIN Categories ON " +
" Products.CategoryID = Categories.CategoryID " +
"GROUP BY Categories.CategoryName " +
"ORDER BY AVG([Order Details].Quantity) DESC, " +
" Categories.CategoryName", cnn);
cnn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
5、 增加下列代码，此段程序建立工作表保存来自SQL Server中数据，并且格式化标题栏如图一所示：
' Visual Basic
Private Sub SetupWorksheet()
' Add a new sheet after the active sheet.
xlSheet = DirectCast(ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.ActiveSheet), Excel.Worksheet)
xlSheet.Name = "Average Order Volume"
' Copy field names to Excel.
' Bold the column headings.
With DirectCast(xlSheet.Cells(1, 1), Excel.Range)
.Formula = "AvgQty"
.Font.Bold = True
End With
With DirectCast(xlSheet.Cells(1, 2), Excel.Range)
.Formula = "CategoryName"
.Font.Bold = True
End With
End Sub
// C#
private void SetupWorksheet()
{
// Add a new sheet after the active sheet.
xlSheet = (Excel.Worksheet)ThisWorkbook.
Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet,
Type.Missing, Type.Missing);
xlSheet.Name = "Average Order Volume";
// Copy field names to Excel.
// Bold the column headings.
Excel.Range rng = (Excel.Range)xlSheet.Cells[1, 1];
rng.Formula = "AvgQty";
rng.Font.Bold = true;
rng = (Excel.Range)xlSheet.Cells[1, 2];
rng.Formula = "CategoryName";
rng.Font.Bold = true;
}
6、 增加下列代码，它将获取来自SqlDataReader中的数据，并将数据装入新工作表当前位置，将其格式化以便显示：
' Visual Basic
Private Sub LoadAndFormatData(ByVal dr As SqlDataReader)
Dim i, j As Integer
' Copy the data in from the SqlDataReader.
i = 1
While dr.Read
i = i + 1
For j = 0 To 1
xlSheet.Cells(i, j + 1) = dr(j).ToString()
Next j
End While
dr.Close()
' Format the columns.
DirectCast(xlSheet.Columns(1), _
Excel.Range).AutoFit()
With DirectCast(xlSheet.Columns(2), _
Excel.Range)
.NumberFormat = "0.00"
.AutoFit()
End With
End Sub
// C#
private void LoadAndFormatData( SqlDataReader dr)
{
int row;
int col;
// Copy the data in from the SqlDataReader.
// Start at row 2.
row = 1;
while (dr.Read())
{
row += 1;
for (col = 0 ; col <= 1; col++)
{
xlSheet.Cells[row, col + 1] = dr[col].ToString();
}
}
dr.Close();
// Format the columns.
((Excel.Range)xlSheet.Columns[1, Type.Missing]).AutoFit();
Excel.Range rng = (Excel.Range)xlSheet.Columns[2, Type.Missing];
rng.NumberFormat = "0.00";
rng.AutoFit();
}
7、 增加下列代码，它将调用你建立用来装入输入到Excel工作簿的其他程序：
' Visual Basic
Private Sub LoadDataAndCreateChart()
ResetWorkbook()
SetupWorksheet()
Dim dr As SqlDataReader = GetDataReader()
LoadAndFormatData(dr)
End Sub
// C#
private void LoadDataAndCreateChart()
{
ResetWorkbook();
SetupWorksheet();
SqlDataReader dr = GetDataReader();
LoadAndFormatData(dr);
}
8、 选择文件菜单中的保存所有文件来保存整个解决方案。
9、 按F5运行本项目，装入Excel和工作簿。
10、 在Excel中点击你先前增加的链接，验证你输入的代码，格式化数据如图一所示。
11、 保存工作簿，关闭Excel返回到Visual Studio .NET。
增加图表
为完成本次演练，你需要增加代码来使用ChartWizard方法建立新图表。此方法接受大量的可选参数，本示例只使用其中的一部分。
增加图表按下面步骤操作：
1、 增加代码到OfficeCodeBehind类中。
这段代码建立图表，调用图表的Chartwizard方法，最终增加一些有格式的图表元素：
' Visual Basic
Private Sub CreateChart()
' Now create the chart.
xlChart = _
DirectCast(ThisWorkbook.Charts.Add(After:=xlSheet), _
Excel.Chart)
Dim cellRange As Excel.Range = _
DirectCast(xlSheet.Cells(1, 1), Excel.Range)
xlChart.ChartWizard( _
Source:=cellRange.CurrentRegion, _
Gallery:=Excel.Constants.xl3DBar, _
PlotBy:=Excel.XlRowCol.xlColumns, _
CategoryLabels:=1, _
SeriesLabels:=1, _
HasLegend:=False, _
Title:=xlSheet.Name)
' Apply some formatting to the chart.
xlChart.Name = xlSheet.Name & " Chart"
With DirectCast(xlChart.ChartGroups(1), _
Excel.ChartGroup)
.GapWidth = 20
.VaryByCategories = True
End With
With xlChart.ChartTitle
.Font.Size = 16
.Shadow = True
.Border.LineStyle = Excel.Constants.xlSolid
End With
End Sub
// C#
private void CreateChart()
{
// Now create the chart.
Excel.Chart xlChart = (Excel.Chart)ThisWorkbook.Charts.
Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);
Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1];
xlChart.ChartWizard(cellRange.CurrentRegion,
Excel.Constants.xl3DBar, Type.Missing,
Excel.XlRowCol.xlColumns, 1, 2, false,
xlSheet.Name, Type.Missing, Type.Missing,
Type.Missing);
// Apply some formatting to the chart.
xlChart.Name = xlSheet.Name + " Chart";
Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
xlChart.ChartTitle.Font.Size = 16;
xlChart.ChartTitle.Shadow = true;
xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}
提示：如果你建立自己的图表，最好的方法是破译出ChartWizard方法中所有可用的选项，可以利用Excel宏录制器。即在Excel中，一旦你装入了你自己的数据，选择工具菜单指向宏，点击记录新宏。一旦你打开了宏录制器，选择插入菜单中的图表，接着提示你建立图表，当你完成时，关闭宏录制器。你可以在Excel中的Visual Basic for Application中查看代码。（选择工具菜单，指向宏，点击宏编辑器来查看代码）你也可以拷贝代码到Visual Basic .NET。你需要修复一些引用。如果你对Excel对象多言就一点，事情就会变得更轻松。但是在C＃中不能这样做，VBA转换为C＃是一种挑战。
2、 修改LoadDataAndCreatedChart方法，增加调用CreateChart方法。如下所示：
' Visual Basic
Private Sub LoadDataAndCreateChart()
ResetWorkbook()
Dim dr As SqlDataReader = GetDataReader()
SetupWorksheet()
LoadAndFormatData(dr)
CreateChart()
End Sub
// C#
private void LoadDataAndCreateChart()
{
ResetWorkbook();
SetupWorksheet();
SqlDataReader dr = GetDataReader();
LoadAndFormatData(dr);
CreateChart();
}
3、 保存你的项目，按F5运行。
4、 在Excel中，点击Create New Chart链接，效验你是否成功装入数据，建立并格式化图表。
5、 在此点击链接，效验代码是否正确删除多余的图表并重新建立。
6、 关闭Excel，推出Visual Studio .NET。
结束语
ChartWizard对象使你在Excel中更容易地建立有格式的图表。本次演练向你示范了如何使用Visual Studio Office工具来建立一个Microsoft Office Excel 2003项目，接着使用SQL Server Northwind示例数据库，使你能使用ChartWizard方法提供Excel Chart对象来建立一个基于SQL Server数据的图表。
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
