Creating an HTML table from a Foxpro table or cursor

Use this generic function to help generate your web pages

If you need to generate HTML code from within your application, here's a function which will help. Put simply, it converts the contents of a VFP table or cursor to an HTML table. The resulting table can be inserted into a web page for viewing in a browser.

To use the function, just call it with a single parameter: the alias of your table or cursor. The only pre-requisite is that the file in question must already be open.

The function will return a character string. This will contain the complete HTML table, ready for you to insert into a web page. For example, the following line of code will generate a table from the Customer file, and store the result in a variable called lcCustH:

lcCustH = CursorToHTML("Customer")

The generated table will have one row for each record and a column for each field. The top row will contain column headers, these being the same as the field names but converted to proper case and with underscores changed to spaces. If the input table has an active index or a FILTER or FIELDS setting in force, the output table will reflect this.

If you want to check the appearance of the generated table during development, you can do so by executing a line of code like the following from the Command Window:

_CLIPTEXT = CursorToHTML("Customer")

This will place the generated table in the Windows clipboard. You can then switch to a web authoring tool, such as FrontPage, and paste the clipboard contents into the source of a blank HTML page.

Table attributes

As you will see, the generated table has no special settings for cell padding, border widths, border colours, cell alignment or similar HTML table attributes. If you have a smattering of HTML, you should be able to add these features if you wish. If you are not sure how to do so, paste the table into your authoring tool as described above, set the required attributes interactively, and paste the relevant HTML attributes into the function.

Here then is the function. To incorporate it into your application, just copy and paste it from this page.

FUNCTION CursorToHTML
* Generates an HTML table from a Foxpro table or cursor.
* The resulting string is a formatted HTML table which 
* can be inserted into a web page. Each column represents 
* a field from the cursor. The first row contains the 
* field names (in proper case, with underscores converted 
* to spaces).

* Parameter:
* tcAalias: Alias of table or cusor, which
* must already be open. The parameter is 
* mandatory.
LPARAMETER tcAlias

LOCAL lcRetVal, lnI, lcColHead, lcCell

* Check the parameters (these two lines 
* require SET ASSERT ON)
ASSERT PCOUNT()>0 MESSAGE "Parameter required"
ASSERT USED(tcAlias) ;
  MESSAGE "Alias "+tcAlias+" not found"

SELECT (tcAlias)

* string to hold returned value
lRetVal = ""

* define the table
lcRetVal = "<TABLE>"

* insert column headings from field names
lcRetVal = lcRetVal + "<TR>"
FOR lnI = 1 to FCOUNT()
  lcColHead = PROPER(STRTRAN(FIELD(lnI),"_"," "))
  lcRetVal = lcRetVal +"<TH>"+lcColHead + "</TH>"
ENDFOR
lcRetVal = lcRetVal + "</TR>"

* scan the cursor, creating a row for each record
SCAN
  lcRetVal = lcRetVal + "<TR>"
  FOR lnI = 1 TO FCOUNT()
    lcCell = TRANSFORM(EVALUATE(FIELDS(lnI)))
    lcRetVal = lcRetVal + "<TD>"+lcCell + "</TD>"
  ENDFOR
  lcRetVal = lcRetVal + "</TR>"
ENDSCAN

* end the table
lcRetVal = lcRetVal + "</TABLE>"
RETURN lcRetVal

ENDFUNC

You might be wondering why we are offering this function when Microsoft has included an HTML table generator of its own with Visual FoxPro. The Microsoft offering, which can be found by looking in _GENTHML, is used with VFP's Save as HTML command and the Web Publishing Wizard. It has a lot more features than ours, including the ability to apply styles to the generated code. The advantage of our version is that it is vastly simpler and more compact: fewer than 40 lines, compared to around 730 for the Microsoft version.

Mike Lewis Consultants Ltd. May 2000.