The Spreadsheet Web Component

Because you already know how to use a Microsoft Excel Workbook object, you will find it extremely easy to work with the Spreadsheet component. This component is very useful for displaying formatted data, performing calculations, or simply providing your users with a quick way to enter, calculate, and store data. The current XP version of the Spreadsheet component supports multiple worksheets containing 262,144 rows and 18,278 columns (ZZZ) each. Creating business solutions that utilize the Spreadsheet component is not complicated because the component uses Excel properties, methods, and events that you are already familiar with. The best way to learn about the Spreadsheet and the other three Office web components is, of course, by example. Our example for the Spreadsheet component is very simple. You will create an HTML form with the embedded Spreadsheet component. This form will have three buttons (see Figure D-2) for performing different operations, such as renaming the active worksheet, populating the worksheet with data, and saving the data to an Excel file. Let's get started.

1 jjj E jrccl ![i.m::rl Component Microiolt Interne! Explarci

Ufj. jn|xj

File Edil View F¿™tes 7rc»s Help fT~,Jj

|J ♦■Bat.

J 3 ¡3 Search jJFavailes -JHslorv irV sd

S 'S

Address

IÖ C-WebCansWebS Bread htm

« * m m 1 j il • *i - &

? B)

B

0

d

e

F

0

H

I -

3

i

S

S

7

B

3

Iii

II

12

13

ja . i

\ Sheatl

'i

J <Ê

Rename this sheet

Rename this sheet j

Figure D-2:

The Spreadsheet web component on a web page. Notice the familiar spreadsheet tools at the top of this ActiveX control. Use the Commands and Options buttons to the left of the Help tool to access additional formatting options. Many options are available via a shortcut menu by right-clicking on any cell.

1. Create a new folder named C:\WebComp.

2. Open Notepad and enter the script shown below.

3. Save the file as C:\WebComp\WebSpread.htm.

<Title>Excel Spreadsheet Component</Title>

<object classid="clsid:0002E551-0000-0000-C000-000000000046"

id="Spread1" width=600 height=300> </object> <br/> <br/>

<button id="btnRename">Rename this sheet</button> <button id="btnFill">Fill with data</button> <button id="btnSave">Save to Excel</button>

<SCRIPT LANGUAGE="VBScnpt">

Dim num ' global variable (Integer)

Sub btnRename_OnClick() Dim curSh ' ActiveSheet set curSh = Spread1.ActiveSheet

Alert "Spreadsheet Web Component contains: " & vbCrLf _ & curSh.rows.count & " rows" & vbCrLf _ & curSh.columns.count & " columns "

If curSh.UsedRange.Address = "$A$1" and _ curSh.Cells(1,1).value = "" then Alert "No data in this sheet."

curSh.name = "My Data" & num End if End Sub

Sub btnFill_OnClick()

Dim conn

Dim rst

Dim strSQL

Dim count

Dim r

Dim c

Dim myData

' establish connection to the database conn="Provider=Microsoft.Jet.OleDB.4.0; Data Source=" & _ "C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb"

' Create a Recordset

Set rst = CreateObject("ADODB.Recordset") ' select all records from Order Details table strSql = "SELECT * FROM [Order Details]"

' Open a static (3) Recordset (and execute the SQL ' statement above) using the open connection rst.Open strSql, conn, 3

' enter field names as column headings For count = 0 to rst.fields.count - 1 r = r + 1

With Spread1.ActiveSheet.Cells(1, r) .Value = rst.Fields(count).Name .Font.Bold = True End with

Next

' store data in the Recordset into the variable ' data is stored as a two-dimensional array myData = rst.GetRows() returnedRows = UBound(mydata, 2) + 1

' enter data into worksheet cells For r = 1 to returnedRows For c = 1 to rst.Fields.Count

Spread1.ActiveSheet.Cells(r+1, c).value = myData(c-1, r-1) Next Next

' close the Recordset rst.close set rst = Nothing End Sub

Sub btnSave_OnClick() Dim strFileName Dim fso Dim txtStream Dim strData strFileName="C:\myDataBook.xls"

set fso = CreateObject("Scripting.FileSystemObject")

set txtStream = fso.CreateTextFile(strFileName)

Spread1.DataType="HTMLData"

strData = Spread1.HTMLData txtStream.WriteLine strData txtStream.Close set txtStream = Nothing

Set fso = Nothing

MsgBox "Your spreadsheet data was saved in " & _ strFileName & " file."

Use the HTML <OBJECT> tag to add an Office web component to a web page. The component type is specified by the control's class identifier. For example, the following code places a Spreadsheet web component on a web page:

<object classid="clsid:0002E551-0000-0000-C000-000000000046"

id="Spread1" width=600 height=300> </object>

You can assign a name to the component by using the Id attribute. Also, you can specify the size and width of the ActiveX control via the width and height attributes. Notice that after you add the web component to a web page, you need to write some script to react to user actions. Each of the three buttons placed on the web page has an OnClick procedure attached to it. In your script, you reference the Spreadsheet web component by using the value of its Id attribute (in this case, Spread1). Figure D-3 illustrates the Spreadsheet web component after it has been filled with data from the Northwind database.

3 Excel Spreadsheet Component - Microsoft Internet Explorer

File Edit View Favorites Tools Help

Address

1

HT® I I

èi-î

% m O

ra

B

c

D 1

E

F

G

H

1

-

n

Oiderfp

PioductlD IJiiitPitce

Ouafitftv Discount

2

10240

11

114.00

12

0

3

10240

42

19.60

10

0

4

10248

72

534.60

5

0

5

10249

14

116.60

9

0

6

10249

51

342.40

40

0

7

10250

41

$7.70

10

0

0

10250

51

142.40

35

0.15

9

10250

65

HE. 60

15

0.15

ID

10251

22

116 .SO

6

0.05

11

10251

57

115.60

15

0.05

12

1025-

S5

116.60

30

0

13

10252

20

164.60

40

0.05

54

10252

33

12.00

25

0.05

-i

\5heeîl

zA

<1

>

| Fill with dala

Rename this sheet

| Fill with dala

Figure D-3: The Spreadsheet web component populated with the data from a database

The btnFill_OnClick procedure (attached to the second button) demonstrates how to connect with the Access database using the Microsoft Jet OleDB provider and the GetRows method of the Recordset object to retrieve the data. If the worksheet already contains data, the existing data will be replaced.

The Spreadsheet web component can be populated with data using several methods not presented here. For example, the user can enter the data manually, copy it from another file, or import data by using special properties of the LoadText method of the Range object (the CSVURL property will allow you to load data from the tab-delimited text file, CSV file, or an ASP script; HTMLURL will load data from the HTML file specified in URL; CSVData loads data using comma-separated values; HTMLData loads data using the HTMLData property; and XMLData loads data using the XMLData property).

Saving the spreadsheet data into an Excel file is accomplished via the third button placed on the web form. The btnSave_OnClick procedure that is attached to this button creates a file using the CreateTextFile method of the FileSystemObject (see details on working with text files in Chapter 8). The data is saved in the HTML format on the client's computer, using the following code:

Spread1.DataType="HTMLData" strData = Spread1.HTMLData txtStream.WriteLine strData

In the above code, the DataType property specifies the spreadsheet's data source and its format. It can be one of the following values: HTMLURL, HTMLData, CSVURL, or CSVData. The HTMLData value used in the example procedure indicates that the data source is the string specified by the HTMLData property. The HTMLData property returns a string that represents the spreadsheet data as a properly formatted HTML string.

Once you have the HTML string, you can write this string to a file by using the WriteLine method of the TextStream object. Recall that the TextStream object was returned by the CreateTextFile method of the FileSystemObject:

set fso = CreateObject("Scripting.FileSystemObject") set txtStream = fso.CreateTextFile(strFileName)

Note: If you get a message saying the the CreateObject method of the Scripting.FileSystem object failed, ensure that the Internet Explorer security setting for Initialize and script ActiveX controls not marked as safe (see the Security tab (Custom Level) in the Internet Options dialog box) is set to either Prompt or Enable.

0 0

Post a comment