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

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

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:

1. Start Microsoft Notepad.
2. Paste the following HTML into Notepad:

   <TABLE border=9>
   <TR><td><p><A HREF="http://www.microsoft.com"> Microsoft Home</A></p></td></TR>
   <TR><td><p><A HREF="http://support.microsoft.com/ofd"> 
   Information about Office Development</A></p></td></TR>
   <TR><td><p><A HREF="http://support.microsoft.com/support">Support HomePage </A></p></td></TR>
   <TR><td><p><A HREF="http://msdn.microsoft.com"> MSDN Online</A></p></td></TR>
   <TR><td><p><A HREF="http://www.scripting.com/">Scripting Page</A></p></td></TR>
   <TR><td><p><A HREF="http://microsoft.com/Office">Office Home</A></p></td></TR>
   </TABLE>
   					

3. Save the file as TableInit.html in your Inetpub\wwwroot directory.
4. 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.
7. 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.
8. Quit Word.

Steps to Create Sample

1. Start Notepad.
2. 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 
   
   %>
   					

3. Save the file as GetData.asp in your Inetpub\wwwroot folder.
4. Start a new text file in Notepad.
5. 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>
   
   <script language=vbscript>
   
   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 
   
   </script>
   
   </BODY>
   </HTML> 
   
   					

   NOTE: Replace SERVERNAME in the code above with the name of your server.
   
   
6. Save the file as LoadSave.htm in your Inetpub\wwwroot folder.
7. 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.

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.

For additional information about scripting the Office web components, including sample code, click the article number below to view the article in the Microsoft Knowledge Base: