Printing Excel Data to an Internet Browser using the Get String Method

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

You can use the Recordset object's GetString method to print the data contained in an Excel spreadsheet in an Internet browser. This method returns a set of records into a string and is faster than looping through the recordset. The GetString method has the following syntax:

variant = recordset.GetString(StringFormat, NumRows, _

ColumnDelimiter, RowDelimiter, NullExpr)

The first argument (StringFormat) determines the format for representing the recordset as a string. The second argument (NumRows) specifies the number of recordset rows to return. If blank, GetString will return all the rows. The third argument (ColumnDelimiter) specifies the delimiter for the columns within the row (the default is a tab). The fourth argument (RowDelimiter) specifies a row delimiter (the default is a carriage return).

The fifth argument (NullExpr) specifies an expression to represent NULL values (the default is an empty string).

Now let's see how you can use the GetString method to retrieve the data from the Excel file created in an earlier example (or simply substitute the file name with any Excel spreadsheet name you want to read).

1. Open Notepad.

2. Enter the ASP script shown below.

3. Save the ASP file as C:\ExcelWithASP\GetExcel.asp.

4. Close Notepad. GetExcel.asp code

dim myConn dim myExcel dim strCon dim mySQL

' Create the connection object set myConn = Server.CreateObject("ADODB.Connection") ' Specify the connection string strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

strCon=strCon & server.MapPath("WriteToExcel.xls") & ";" strCon=strCon & "Extended Properties=Excel 8.0"

' Open the connection myConn.Open strCon

' Create the Recordset set myExcel=Server.CreateObject("ADODB.Recordset") mySQL="Select * from [Sheet1$]"

' Open the Recordset myExcel.Open mySQL, myConn

' Show data in a table Response.Write "<TABLE BORDER=1><TR><TD>"

' Get the column names For each fld in myExcel.Fields Response.Write fld.Name & "<TD>" Next

Response.Write "</TR><TR><TD>" ' Get the actual data

Response.Write myExcel.GetString(, -1, "</TR><TD>", _ "</TD></TR><TR><TD>", NBSPACE)

' Close the Recordset and release the object myExcel.Close set myExcel = Nothing

' Close the connection myConn.Close set myConn = Nothing

The above ASP script connects to the specified Excel file and retrieves the data located in Sheetl. After reading the column names from the Fields collection, the code uses the above-mentioned GetString method to pull the data:

Response.Write myExcel.GetString(, -1, "</TR><TD>", _ "</TD></TR><TR><TD>", NBSPACE)

Notice that -1 indicates that all rows should be read. The </TR><TD> tags are used for delimiting columns while </TD></TR> <TR><TD> specify the row delimiter. If the cell does not contain any data, a non-breaking space will be entered (NBSPACE) so that there are no gaps in the table structure.

Was this article helpful?

0 0

Post a comment