Dephi for Office Web Control Application Development

A new exciting feature of Microsoft Office 2000 is that it provides Office Web Components (OWC, an ActiveX control group used to create dynamic Web documents ). OWC includes six controls, which implement some Excel functions and provide database access functions, including ChartSpace, Spreadsheet, DataSourceControl, RecordNavigationControl, ExpandControl, and external-Table. The DataSourceControl and RecordNavigationControl controls provide database access functions, which can be replaced by the ADOExpress control. ExpandControl is not covered in this article. If you are interested, you can study it on your own. To use these ActiveX controls, you need to introduce the corresponding Type Library (of course not required for later binding), select the menu command Project | Import Type Library, in the displayed dialog box, select Microsoft Office Web Components 9.0 (Version 1.0), confirm that the Generate Component Wrapper option is selected (supported in Delphi5), and click the Install button, the six components are added to the component panel.

The Type Library file is defined in the MSOWC. DLL file and is located in the/Program Files/Microsoft Office 2000/Office directory. The Help file is MSOWCVBA. CHM, located in/Program Files/Microsoft Office 2000/Office/1033.

ChartSpace component

ChartSpace is used to create charts. It supports 46 types of charts, from line type to Doughnut Exploded type. For details about the types, see ChartChartTypeEnum parameter list in help. It supports multiple data sources, such as other OWC controls, such as Spreadsheet controls, ADO data sources, or static data.

1. charts based on static data

In Delphi, we can store static data in the variant array. The array can be created using the vararraycr & #101; ate function, or the vararrayof function can be used to create a dynamic variant array. The following code demonstrates how to create and assign a value to a static variant array.

VaR

Xvalues: variant;

...

// Create a variant array

Xvalues: = vararraycr & #101; ate ([0, 2], varvariant );

// Load X-axis data

Xvalues [0]: = 'element one ';

Xvalues [1]: = 'element two ';

Xvalues [2]: = 'element two ';

Two Parameters of vararraycr & #101; ate are used to specify the dimension and project type of the array. In the preceding example, we created an array of the variant type with three items. The following code demonstrates how to use the vararrayof function to create a one-dimensional variant array:

Vararrayof ([104737,509 52, 78128,117 797, 52902,801 60, 47491,624 35]);

Fig 1.18

To use the chartspace control, perform the following steps: first, clear the existing content of the chart. Then, add a new chart object to the chart set (a set can contain up to 16 charts). Then, you can provide data (series set), set the title (Title attribute), specify the axis (axes set), and set the illustrations (legend attribute) for the chart ). Figure 1.18 shows the class system relationship of a simple chartspace control.

The following example shows how to use static data to create a chart. First, we need to set some variables:

VaR

Chart: WCChart; // Chart

Series: WCSeries; // Series

XValues: Variant; // X axis Value

Clear original Chart content.

ChartSpace1.Clear;

ChartSpace1.Refresh;

Create a new chart because this is the first chart in the chart set and its index is 0.

Chart: = ChartSpace1.Charts. Add (0 );

Set the chart title.

Chart. HasTitle: = True; // the title of the Chart.

Chart. Title. Caption: = 'sales By Category ';

Then the data is provided. First, create the value of the X axis. Here, the static Variant array is used as the data source.

XValues: = VarArraycr & #101; ate ([0, 7], varVariant );

XValues [0]: = 'beverages ';

XValues [1]: = 'conditions ';

XValues [2]: = 'confections ';

XValues [3]: = 'dairy products ';

XValues [4]: = 'grains & Cereals ';

XValues [5]: = 'meat & poultry ';

XValues [6]: = 'produce ';

XValues [7]: = 'seafood ';

With data, you need to add a new Series object to the SeriesCollection collection.

Series: = Chart. SeriesCollection. Add (0 );

With Series do begin

// Set the Series title...

Caption: = '000000 ';

//... And Data

SetData (chDimCategories, chDataLiteral, XValues );

// The Y-axis data source is a dynamic variant array.

SetData (chDimValues, chDataLiteral, VarArrayOf (

[104737,509 52, 78128,117,

52902,801 60, 47491,624 35]);

// Set the chart type. Clustered Column is used here.

Type _: = chChartTypeColumnClustered;

End;

Next, let's add a new series and create a new chart on top of the existing chart.

Series: = Chart. SeriesCollection. Add (1 );

With Series do begin

Caption: = '000000 ';

SetData (chDimCategories, chDataLiteral, XValues );

SetData (chDimValues, chDataLiteral, VarArrayOf (

[20000,150 00, 36000,560 00,

40000,180 00, 20000,330 00]);

// Set the chart type to line markers.

Type _: = chcharttypelinemarkers;

End;

We also need to add an axis for the chart and set its attributes.

Chart. Axes. Add (chart. Axes [chaxispositionleft]. scaling,

Chaxispositionright, chvalueaxis );

Chart. Axes [chaxispositionleft]. numberformat: = '$ #,## 0 ';

Chart. Axes [chaxispositionright]. numberformat: = '0 ';

Chart. Axes [chaxispositionleft]. majorunit: = 20000;

Chart. Axes [chaxispositionright]. majorunit: = 20000;

Next, add an illustration under the chart.

Chart. haslegend: = true;

Chart. Legend. Position: = chlegendpositionbottom;

Fig 1.19

The generated result 1.19 is shown. Then we will demonstrate how to use a database as a data source to display charts.

2. Database-based charts

Create a database-based chart with two options: Use the DataSource control in OWC and use a data source compatible with Ado. In fact, the two are similar, the main difference is how to define the data source.

(1) Use the DataSource of OWC

First declare the variable:

Var

RSD: RecordsetDef; // data source DataSource

BarChart: WCChart; // chart

PieChart: WCChart;

Next, set the data source and the corresponding SQL statement to obtain the data. Here we use the example database Northwind. mdb with Access.

Begin

DataSourceControl1.ConnectionString: =

'Driver = {Microsoft Access DRIVER (*. mdb)}; '+

'Dbq = C:/DATA/NORTHWIND. MDB '; // modify the path according to your own path.

RSD: = performancecontrol1.recordsetdefs. AddNew (

'Sel & #101; ct * FROM [Category Sales for 1997] ', 3, 'sales ');

Then, clear the chart and specify the Data source:

With ChartSpace1 do begin

Clear;

Refresh;

DataSource: = performancecontrol1.defaultinterface as MSDATASRC _ TLB. DataSource;

Datamember: = RSD. Name;

End;

Because the datasource attribute of the chartspace control is declared as an iunknown interface (see msdatasrc_tlb unit), you need to use the as operator to query the interface.

(2) Use the ADO Data Source

The adoexpress control is used here. The adoconnection control is used to set the data source and cursor type, and the adocommand control is used to provide SQL queries. The variables in the ADO dataset are defined as follows:

VaR

RS: _ recordset; // ADO recordset

The following code executes an SQL query and assigns the returned data to the chart:

RS: = adocommand1.execute;

With chartspace1 do begin

Clear;

Refresh;

Datasource: = RS as msdatasrc_tlb.datasource;

Datamember: = '';

End;

The following code is similar to the previous one. Create a new chart, specify the data type, and add the coordinate axis.

// Set the horizontal chart layout

Chartspace1.chartlayout: = chchartlayouthorizontal;

// Add a new chart

Barchart: = chartspace1.charts. Add (0 );

With barchart do begin

// Set the chart type to bar chart

Type _: = chChartTypeBarClustered;

// The first field is categories.

SetData (chDimCategories, 0, 0 );

// The second field is values.

SetData (chDimValues, 0, 1 );

// Set the axis format

With Axes [chAxisPositionBottom] do begin

NumberFormat-: = '0 ,';

MajorUnit: = 25000;

HasMajorGridlines: = False;

End;

End;

Create a pie chart based on the same data, and the running result is displayed in Figure 1.20.

PieChart: = ChartSpace1.Charts. Add (1 );

With PieChart do begin

// Set the chart type to pie chart

Type _: = chChartTypePie;

SetData (chDimCategories, 0, 0 );

SetData (chDimValues, 0, 1 );

// Set the number of "Explode" segments of the pie chart

SeriesCollection. Item [0]. Explosion: = 20;

// Add illustrations...

HasLegend: = True;

Legend. Position: = chLegendPositionBottom;

//... And title

HasTitle: = True;

Title. Caption: = 'sales by Category for 1997 ';

Title. Font. Set_Bold (True );

Title. Font. Set_Size (11 );

WidthRatio: = 75;

// Display data by percentage

With SeriesCollection. Item [0]. DataLabelsCollection. Add do

Begin

HasValue: = False;

HasPercentage: = True;

Font. Set_Size (7 );

End

Fig 1.20

(3) charts based on Spreadsheet data

In the following example, we will create a chart based on the OWC Spreadsheet control data. After we modify the data in the workbook, the changes will be automatically reflected in the workbook, declare the variable first:

Var

Sheet: WorkSheet; // workbook

Chart: WCChart; // Chart

Filling workbooks with data

Sheet: = Spreadsheet1.ActiveSheet;

With Sheet do begin

Range ['a1', 'a10']. Set_Formula ('= Row ()');

Range ['b1 ', 'b10']. Set_Formula (' = A1 ^ 2 ');

Range ['a12', 'a12']. Set_Formula ('= Max (A1: A10 )');

Range ['b12 ', 'b12']. Set_Formula ('= Max (B1: B10 )');

End;

In the above Code, we insert the row number (from 1 to 10) into column A and the square of the row number into column B. Cells A12 and B12 are set to the maximum values of column A and column B. As we can see, the Spreadsheet control supports expressions like Excel, which has the same binary kernel as Excel. This feature allows us to use the Spreadsheet control to perform background computing without displaying results on the screen.

Similarly, create a chart to connect to the Data source:

With ChartSpace1 do begin

Clear;

Refresh;

// Specify the data source

DataSource: = Sheet. Parent as MSDATASRC_TLB.DataSource;

Chart: = Charts. Add (0 );

// Set the chart type to Scattered Smooth Line Markers.

Chart. Type _: = chChartTypeScatterSmoothLineMarkers;

// Specify the X axis data...

Chart. SetData (chDimXValues, 0, 'a1: a10 ');

//... And Y-axis data

Chart. SetData (chDimYValues, 0, 'b1: b10 ');

End;

The rest is to beautify our charts, such as specifying the axis title and type.

// Display the title of the Axis

With Chart do begin

With Axes [chAxisPositionBottom] do begin

HasTitle: = True;

Title. Caption: = 'X ';

Title. Font. Set_Size (8 );

MajorUnit: = 1;

End;

With Axes [chAxisPositionLeft] do begin

HasTitle: = True;

Title. Caption: = 'x Squared ';

Title. Font. Set_Size (8 );

MajorUnit: = 10;

End;

...

Specify the axis range and set other styles:

Scalings [chDimXValues]. Maximum: =

Sheet. Range ['a12', 'a12']. Value;

Scalings [chDimXValues]. Minimum: = 1;

Scalings [chDimYValues]. Maximum: =

Sheet. Range ['b12 ', 'b12']. Value;

// Set other styles

With SeriesCollection. Item [0] do begin

Marker. Style: = chMarkerStyleDot;

Marker. Size: = 6;

Line. Set_Weight (1 );

End

The running result is 1.21.

Note that the data can be dynamically displayed. When we change the workbook data, the results will be automatically reflected in the chart. Now we know how to use the Spreadsheet control as the data source of the chart. Next we will explain how to use the Spreadsheet control.

Spreadsheet control

The Spreadsheet control provides a programmable kernel that has a huge function library for various computations. The Spreadsheet Object Model (1.22) contains the ActiveSheet attribute for interaction with the active page in the control. The Pane object represents the workspace of the workbook, Range is used to specify the cell Range, and TitleBar represents the title bar of the workbook, the Worksheet is the workbook itself.

Figure 1.21 figure 1.22

There are many ways to load data into a workbook, such as manually entering data, copying and pasting data from the clipboard, loading data from Word or Excel, or reading data from text files and websites. To read data from a text file, you can use the LoadText method of the Spreadsheet control. It requires the following parameters:

L file name in string format;

L delimiter, such as tab or comma;

L A boolean value is used to determine how to process continuous delimiters;

L and text identifiers. The default value is double quotation marks.

The following is an example of LoadText. The delimiter loads data from the employee.txt file and uses the tab character (ASCII 8) as the delimiter:

Spreadsheet1.loadtext ('C:/data/employee.txt ', CHR (9), false ,'"');

In addition, you can specify data by setting the csvdata (comma-separated Data) attribute or htmldata (HTML data) attribute. Note: HTML-format data must be stored in Excel-compatible format. The current version of The Spreadsheet control does not support databases, but we can easily introduce data from the ADO data source. The Code is as follows:

Procedure tform1.button1click (Sender: tobject );

VaR

RS: _ recordset; // ADO recordset

I, J: integer; // count

Numrecs: integer; // number of records

Begin

// Execute SQL query

RS: = adocommand1.execute;

// Move to the first record

Rs. movefirst;

// Clear Spreadsheet

Spreadsheet1.activesheet. cells. item [1, 1]. Sel & #101; CT;

Spreadsheet1.activesheet. usedrange. Clear;

// Set the column title

J: = 0;

For I: = 0 to Rs. Fields. Count-1 do begin

INC (j );

Spreadsheet1.ActiveSheet. Cells. Item [1, J]. Set_Value (

RS. Fields [I]. Name)

End;

// Read data

I: = 1;

While not rs. EOF do begin

For J: = 1 to RS. Fields. Count do

Spreadsheet1.ActiveSheet. Cells. Item [I + 1, J].

Set_Value (VarToStr (RS. Fields [J-1]. Value ));

// Move to the next record

RS. MoveNext;

Inc (I)

End;

NumRecs: = I;

End;

We use the ADOCommand control to read data from the Employees table of the Northwind database, traverse the records, and add the data to the cell of the Spreadsheet control. After reading the data, we can set the cell attributes. The following code can change the font of the first row of cells:

With Spreadsheet1.Range [Spreadsheet1.Cells. Item [1, 1],

Spreadsheet1.Cells. Item [1, RS. Fields. Count]. Font do

Begin

Set_Name ('arial narrow ');

Set_Bold (True );

Set_Size (11 );

End;

At the same time, we can also set the cell to automatically adjust the size according to the content and align the data left:

With Spreadsheet1.Range [Spreadsheet1.Cells. Item [1, 1],

Spreadsheet1.Cells. Item [NumRecs, RS. Fields. Count] do

Begin

AutoFitColumns;

Set_HAlignment (ssHAlignLeft );

End;

The running result is 1.23.

Fig 1.23

Set color

If we want to modify the Color of the elements in the Chart or Spreadsheet control, we will find that the Color attribute is read-only, which is caused by a bug in the Delphi library scanner. to bypass this problem, we can directly use the Set_Color method, but its parameter is of the POleVariant1 type. This type is defined as the OLEVariant type pointer. The following Code demonstrates how to use the Set_Color method:

Var

C: OLEVariant;

...

// Use the predefined color...

C: = OLEVariant ('cornsilk ');

//... Or 16-bit RGB Value

C: = OLEVariant (RGB ($ C0, $ C0, $ C0 ));

...

Spreadsheet1.ActiveSheet. UsedRange. Interior. Set_Color (@ C );

What is it?