Microsoft KB Archive/253913

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:52, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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:

  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.

  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

  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> </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.


REFERENCES

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:

258187 OWebComp.exe Contains Scripting Samples for the Office Web Components



Additional query words: csvURL csvData htmlData htmlURL

Keywords: kbinfo kbofficewebspread KB253913