Walkthrough: Create a chart in Excel, data from SQL Server
Ken Getz
MCW Technologies
September 2003
Overview: Demonstrate how to create a chart from Microsoft SQL Server data, use the Excel object module and write code in Visual Basic .NET or Microsoft Visual C #.
content:
Introduction
Prerequisites
Start
Intercept event handle
Add input data
Increase chart
in conclusion
Introduction
In this exercise, you will copy the Microsoft SQL Server Northwind sample database to your Excel worksheet, as shown in Figure 1. According to the new data range, you will create a chart as shown in Figure 2.
Figure 1. Formatted data in Excel.
Figure 2. Graphic for data in the Northwind sample database.
Prerequisites
In order to complete this drill, the following software and components must be installed on the computer.
· 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 Example Database.
Note: This sample code is integrated, if you can't log in to the SQL Server computer using your account, you need to coordinate with administrators, and you need to modify the connection information of the code. See the SQLConnection.Connectionstring property of the .NET Framework documentation for details.
Tip: This drill assumes that you are a Visual Basic .NET programmer, you need to set the option strict in your project (or add Option Strict in each of your modules). As you can see, setting Option Strict is ON will add a little code, but this will ensure that you don't perform an unsafe type conversion. Without this setting, you can use this setting more important than the difficulty of adding code in your write code.
Start
To start, build a Visual Studio .NET project using Microsoft Office Excel 2003.
create project
Create an Excel workbook using Visual Studio Tools.
Create an Excel workbook project
1. Start Visual Studio .NET, open the file menu, point to New, click on the project.
2. In the Project Type Panel, expand the Microsoft Office System project, select the Visual Basic project or Visual C # items.
3. Select the Excel workbook in the template panel of the new project dialog.
4. Naming this item is ExcelchartDemo, then saved it in a suitable location.
5, accept the default settings for the Microsoft Office project wizard. Click to complete.
Visual Studio .NET opens thisworkbook.vb or the code editor for thisworkbook.cs file.
Arrangement SpreadSheet In order to load data and establish a chart, you need to add some methods to run the code. Although you can do it in the OPEN event of the workbook, the option is meaningless. In this exercise, you will create a hyperlink in the workbook, then activate the FollowhyperLink event in the workbook.
Create a hyperlink in the workbook
1. Press F5 to take this project, load the Excel and workbook.
2. In Excle, click the Insert menu, select the hyperlink.
3. Set the value of the display text in the Hyper Link Dialog box to create a new chart.
4. Select placed in this text on the link on the left side of the dialog to the panel. Make sure the reference in the dialog box is picked up with the location of your hyperlink.
When you have completed, the dialog should be shown in Figure 3. Click OK to close the dialog box, you should see the new hyperlink in the workbook.
Figure 3. The finished Insert HyperLink Dialog Box
5. Select Save in the File menu.
Close Excel, return to Visual Studio .NET.
Intercept event handling
In order to run your code, you need to make .Workbook.sheetfollowhyperlink events work. In this paragraph, you will increase support for this event in Visual Basic or C #.
Intercept event processing (Visual Basic)
Follow these steps in Visual Basic .NET:
1. Select THISWORKBOOK in the class name drop-down list in the upper left corner of the code editor.
2. Select SheetfollowHyperLink in the Method Name drop-down list box of the code editor. Visual Studio .NET will create an event handling code snippet.
3, modify the thisworkbook_sheetfollowhyperlink program, add the following code:
'Visual Basic
If target.name = "Create New Chart" THEN
LoadDataAndcreateChart ()
END IF
Intercept event processing (C #)
In the C #, follow these steps:
1. In the OfficeCodeBehind class, add new variables for the new variable SheetFollowHyperLink event in the OpenEvent and BeforecloseEvent variables:
// C #
Private Excel.WorkBookevents_SheetFollowhyperLinkeventHandler
SheetfollowhyperLinkevent;
2, add the following program code to this class:
// C #
Protected void thisworkbook_sheetfollowhyperlink
Object SH, Excel.hyperLink Target)
{
}
3, add the following code to thisworkbook.open program:
// C #
SheetfollowhyperLinkevent =
New Excel.WorkBookevents_SheetFollowhyperLinkeventHandler (
Thisworkbook_sheetfollowhyperlink;
THISWORKBOOK.SHEETFOLLOWHYPERLINK = SheetFollowhyperLinkevent;
4, add the following code to thisworkbook_sheetfollowhyperlink program, add the following code:
// C #
IF (target.name == "create new chart")
{
LoadDataAndcreateChart ();
}
Add input data
You need to add code to create SQLDATAREADER, load data from SQL Server, and increase the code to copy data to a new table for the Excel workbook. This drill adds a new table to the workbook so that you can take a few times, you can also increase the code to delete the table used instead of activating the worksheet before the chart.
Tip: The Excel process chart is separated from the ordinary worksheet in their own worksheet. Therefore, the ResetWokbook program must be able to handle two object collections. The Worksheets collection and Charts collection in the Application object.
Enter data into the worksheet
1. Scroll to the top of the code module, add the following statement, which will reduce a lot of typing work.
'Visual Basic
Imports system.data.sqlclient
// C #
Using system.data;
Using system.data.sqlclient;
2. Add the following variables announced after thisapplication and thisworkbook variable.
'Visual Basic
Private Xlsheet as Excel.Worksheet
Private Xlchart as Excel.Chart
// C #
Private Excel.Worksheet Xlsheet = NULL;
Private excel.chart xlchart = null;
3. In the OfficeCodeBehind class, add the following programs, which will refresh workbooks and content for you:
'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.activeesheet 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
{
THISPPLICATION.DISPLAYALERTS = FALSE;
Foreach (Excel.Worksheet WS in thisworkbook.worksheets)
IF (WS! = thisApplication.activeesheet)
{
Ws.delete ();
}
Foreach (Excel.Chart Cht in thisworkbook.charts)
cht.delete ();
}
Finally
{
THISPPLICATION.DISPLAYALERTS = TRUE;
}
}
Tip: Excel usually prompts when deleting a worksheet, the present sample code sets the Application.Displayalerts property to false before deleting, and then set back to true after deleting. 4. Add the following programs in the OfficeCodeBehind class, this program connects to SQL Server in the local computer, using integrated security, then returns a SQLDataAdApter, accepting data for workbooks.
'Visual Basic
Private function getDataReader () AS SQLDATAREADER
DIM CNN AS New SqlConnection ("Server = '.';" & _
"Database = northwind; integrated security = true")
DIM CMD AS New Sqlcommand (_
"SELECT CATGORIES.CATEGORYNAME," & _
"AVG ([ORDER DETAILS] .quantity) AS AVGQTY" & _
"From products inner Join" & _
"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" "
"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, add the following code, this program sets the worksheet to save data from SQL Server, and format the title bar, such as shown in Figure 1:
'Visual Basic
Private sub setupworksheet ()
'Add a new sheet after the agent.
Xlsheet = Directcast (thisworkbook.worksheets.add (_ _
After: = thisworkbook.activeesheet, 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.activeesheet,
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, add the following code, it will get the data from SqlDataReader and put the data into the current position of the new worksheet, format it to display:
'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, add the following code, which calls you to build other programs that are used to enter the EXCEL workbook:
'Visual Basic
Private sub loadingDataAndcreateChart ()
Resetworkbook ()
Setupworksheet ()
DIM DR AS SQLDATAREADER = GetDataReader ()
LoadandFormatdata (DR)
End Sub
// C #
Private void loadingDataAndcreateChart ()
{
RESETWORKBOOK ();
Setupworksheet ();
SqlDataReader DR = getDataReader ();
LoadandFormatdata (DR);
}
8. Select Save all files in the File menu to save the entire solution.
9. Press F5 to run this project, load the Excel and workbook.
10. Click on the previously added links in Excel to verify the code you entered, and the formatted data is shown in the figure.
11, save the workbook, close the Excel returns to Visual Studio .NET.
Increase chart
To complete this drill, you need to add code to create a new chart using the ChartWizard method. This method accepts a large number of optional parameters, which only uses only some of them.
Add the chart to follow these steps:
1. Add the code to the OfficeCodebehind class.
This code creates a chart, call the ChartWizard method of the chart, and eventually add some formatted chart elements:
'Visual Basic
Private sub cretechart ()
'Now crete 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;
}
Tip: If you build your own chart, the best way is to decipher all available options in the Chartwizard method, you can use the Excel macro recording system. That is, in Excel, once you load your own data, select the Tools menu to point to the macro, click Record New Macro. Once you open the macro recorder, select the chart in the insert menu, then prompt you to create a chart, turn off the macro recorder when you are done. You can view the code in the Visual Basic for Application in Excel. (Select Tools Menu, point to Macro, click the Macro Editor to view the code) You can also copy the code to Visual Basic .NET. You need to fix some references. If you have a lot of Excel objects, things will become easier. But in C # can't do this, VBA converts to C # is a challenge. 2. Modify the loadDataAndCreatedChart method, add the CreateChart method. As follows:
'Visual Basic
Private sub loadingDataAndcreateChart ()
Resetworkbook ()
DIM DR AS SQLDATAREADER = GetDataReader ()
Setupworksheet ()
LoadandFormatdata (DR)
CreateChart ()
End Sub
// C #
Private void loadingDataAndcreateChart ()
{
RESETWORKBOOK ();
Setupworksheet ();
SqlDataReader DR = getDataReader ();
LoadandFormatdata (DR);
CreateChart ();
}
3, save your project, press F5 to run.
4, in Excel, click on the Create New Chart link, the test you have successfully loaded data, create and format the chart.
5, click on the link, the test code correctly deletes excess charts and re-established.
6. Turn off Excel to launch Visual Studio .NET.
Conclude
The Chartwizard object makes you more easily establish formatted charts in Excel. This drill demonstrates how to use the Visual Studio Office tool to create a Microsoft Office Excel 2003 project, then use the SQL Server Northwind sample database so that you can use the ChartWizard method to provide an Excel Chart object to create a chart based on SQL Server data.
Original text:
http://msdn.microsoft.com/office/understanding/excel/walkthroughs/default.aspx?pull=/library/en-us/odc_vsto2003_ta/html/odc_vstexlchrt.asp
