Microsoft KB Archive/253913

= INFO: Techniques for Loading and Retrieving Data with the Spreadsheet Web Component =

Article ID: 253913

Article Last Modified on 8/23/2005

-

APPLIES TO


 * Microsoft Office Spreadsheet Component 9.0 1.0

-



This article was previously published under Q253913



SUMMARY
This article describes the different ways to load or save data from a Spreadsheet Web Component using the interfaces the component supports:
 * HTMLData Property
 * HTMLURL Property
 * CSVData Property
 * CSVURL Property
 * LoadText Method



MORE INFORMATION
This article contains a sample that demonstrates how to load data to a Spreadsheet component from the following sources:
 * data from an HTML Table
 * data from a string that contains HTML Data created by saving the contents of a Spreadsheet component
 * data in CSV (Comma-separated values) format - either hardcoded, generated by an ASP page, or from a Workbook saved in the CSV format
 * data from a tab-delimited text file

Note that when loading data into the Spreadsheet by using one of the properties described, you should set the DataType property of the Spreadsheet accordingly. The sample below illustrates.

Steps to Create Data Sources
To create an HTML table using Microsoft Word:
 * 1) Start a new document in Microsoft Word.
 * 2) On the Insert menu, click Table, and select Table.
 * 3) Type the number 2 in both the Number of Columns and Number of Rows of the Insert Table dialog box and click OK.
 * 4) Type Table Entry 1, Table Entry 2 , Table Entry 3 , and Table Entry 4 in each of the cells of the table.
 * 5) On the File menu, select SaveAs. Select Web Page in the Save As Type dropdown list. Save the file as Table.html in your Inetpub\wwwroot directory.
 * 6) Quit Word.

To create an HTML table using a Text Editor:  Start Microsoft Notepad.  Paste the following HTML into Notepad:  Save the file as TableInit.html in your Inetpub\wwwroot directory. Quit Notepad.

To create a Comma-Delimited Text File (CSV) with Microsoft Excel:
 * 1) Start Microsoft Excel.
 * 2) Enter any values in cells A1:C10.
 * 3) On the File menu, click SaveAs. Select CSV (Comma Delimited) in the Save As Type dropdown list. Save the file as CSVData.csv in your Inetpub\wwwroot directory.
 * 4) Quit Excel.

To create a Tab-Delimited Text File with Microsoft Word:
 * 1) Start Microsoft Word.
 * 2) On the Insert menu, click on Table, and then select Table.
 * 3) Type the number 2 in both the Number of Columns and Number of Rows of the Insert Table dialog box, and click OK.
 * 4) Type Entry 1, Entry 2 , Entry 3 , and Entry 4 in each of the cells of the table.
 * 5) Select the table. Click Table, select Convert, and then select Table to Text.
 * 6) Select the Tabs option in the Convert Table to Text dialog box and click OK.

NOTE: This sample uses the LoadText method with a tab delimiter to load this text file. However, with the LoadText method, you can specify the delimiter so you could use any delimiter of your choice if you modify the arguments for the LoadText method in this sample.
 * 1) On the File menu, select SaveAs. Select Text Only in the Save As Type dropdown list. Save the file as textfile.txt in your Inetpub\wwwroot directory.
 * 2) Quit Word.

Steps to Create Sample
 Start Notepad.  Paste the following code into Notepad: <%@ Language=VBScript %>

<%   Option Explicit

Response.Buffer = True

Dim ct, Max, Min Dim anValues(50)

' Initialize the random number generator Randomize Max = 20 Min = 5

' Loop creating 50 random values for ct = 0 to 49 anValues(ct) = Int((Max - Min + 1) * Rnd + Min) next For ct = 0 To 19 Step 2 Response.Write "Value = " & anValues(ct) & ",Value = " & anValues(ct+1) & Chr(13) & Chr(10) Next

%>                   </li> Save the file as GetData.asp in your Inetpub\wwwroot folder.</li> Start a new text file in Notepad.</li>  Paste the following code into the new text file: <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <TITLE>SAMPLE TO DEMONSTRATE DIFFERENT DATA LOADING TECHNIQUES</TITLE> </HEAD> <BODY> <P> <SELECT id=select1 name=cbxType style="HEIGHT: 22px; WIDTH: 126px"> <OPTION selected value=0>LOAD HTMLURL</OPTION> <OPTION value=1>LOAD HTMLDATA</OPTION> <OPTION value=2>LOAD CSVDATA1</OPTION> <OPTION value=3>LOAD CSVDATA2</OPTION> <OPTION value=4>LOAD CSVURL</OPTION> <OPTION value=5>LOADTEXT</OPTION> <OPTION value=6>SAVE HTMLDATA</OPTION> </SELECT> </P> <P>&#xa0;</P> <P> <OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 height=361 id=Spreadsheet1 style="HEIGHT: 361px; WIDTH: 550px" width="100%"> <PARAM NAME="HTMLURL" VALUE="http://SERVERNAME/tableInit.html"> <PARAM NAME="ViewableRange" VALUE="A1:I30"> </OBJECT>

</P> <OBJECT classid=clsid:CECFFE39-D98A-11D3-95C3-00C04F542B80 id=OWCAddin> </OBJECT>

Dim strData

Sub Window_onLoad Spreadsheet1.TitleBar.Caption = "Sample Spreadsheet" strData = Spreadsheet1.HTMLData cbxType_onChange End Sub

'   This code runs any time the datasource type drop list is changed. '   It reloads the sheet with the selected type of Data

Sub cbxType_onChange ' Set type to the selected Data Type long value nDataType = clng(cbxType.value) set c = Spreadsheet1.Constants ' If the Spreadsheet has to be saved, then do not clear the sheet If nDataType <> 6 Then Spreadsheet1.ActiveSheet.UsedRange.Clear End If   ' Switch on the new data type select case nDataType case 0 ' Load Data from html file specified in URL Spreadsheet1.DataType = "HTMLURL" Spreadsheet1.HTMLURL = "http://SERVERNAME/table.html" case 1 ' Load Data using HTMLData Spreadsheet1.DataType = "HTMLData" Spreadsheet1.HTMLData = strData case 2 ' Load Data using csv data by supplying hardcoded values Spreadsheet1.DataType = "CSVData" Spreadsheet1.CSVData = ",," & Chr(13) & Chr(10) _ & "CSVData1" & ",CSVData2" & Chr(13) & Chr(10) _ & "CSVData3" & ",CSVData4" & Chr(13) & Chr(10) _ & "CSVData5" & ",CSVData6" & Chr(13) & Chr(10) case 3 ' Load CSV Data generated dynamically using an ASP Page Spreadsheet1.DataType = "CSVURL" Spreadsheet1.CSVURL = "http://SERVERNAME/GetData.asp" case 4 ' Load Data using a CSV file on the server Spreadsheet1.DataType = "CSVURL" Spreadsheet1.CSVURL = "http://SERVERNAME/csvdata.csv" case 5 ' Load Text from Tab delimited text file ' The second argument specifies the delimiter to be used Spreadsheet1.DataType = "CSVURL" Spreadsheet1.LoadText "http://SERVERNAME/textFile.txt", _ Chr(9) case 6 ' Save Data using HTMLData to an xls file Dim strFile 'As String Dim ofs               'As FileSystemObject Dim oTs              'As TextStream strFile = "C:\HTMLData.xls" Set ofs = CreateObject("Scripting.FileSystemObject") Set oTs = ofs.CreateTextFile(strFile) Spreadsheet1.DataType = "HTMLData" strData = Spreadsheet1.HTMLData oTs.WriteLine strData oTs.Close Set oTs = Nothing Set fs = Nothing MsgBox "Saved sheet at: " & strFile case else ' Should never get here!! MsgBox "Erroneous Selection", vbCritical, "Error!!!!" end select ' Format the cells Spreadsheet1.Refresh Spreadsheet1.ActiveSheet.UsedRange.Interior.Color = "CornSilk" Spreadsheet1.ActiveSheet.UsedRange.Font.Bold = True Spreadsheet1.ActiveSheet.UsedRange.AutoFitColumns Spreadsheet1.ActiveSheet.UsedRange.Select End Sub

</BODY> </HTML>

NOTE: Replace SERVERNAME in the code above with the name of your server.

</li> Save the file as LoadSave.htm in your Inetpub\wwwroot folder.</li> View LoadSave.htm in your browser. Selecting different options from the dropdown loads data from different sources. Selecting save HTML data saves the Spreadsheet as an Excel Workbook on the client's computer.</li></ol>

Additional Notes
Note that the Spreadsheet data and formatting can be retrieved using the HTMLData property. This sample stores the string returned from the HTMLData property when you choose to saves data to a file. The sample uses the data from this string when you select load HTMLData. Unlike HTMLData, CSVData is only writeable so it can only be used to load data into the Spreadsheet. In the first version of the Spreadsheet component, you cannot read CSVData.

<div class="references_section">