| Links | Alphabetical Index | Using Javascript to Insert variables into a web page |

Inserting an Excel table into a web page using Javascript

The HTML code required to create a table on a web page can be saved within a separate javascript file. This makes it possible to insert the contents of an external javascript file as a table. Parts of a page contents can therefore be updated without editing the actual HTML code. Excel can be programmed to create the external files.

The external file instructs the HTML code to be inserted at a location within your web page which has been labelled with a DIV tag. The following example has been created by an Excel macro which incorporates various CSS styles in order to enhance the formatting of the table i.e. the background colours and borders

Inserted Table

An Excel macro can generate a Javascript file containing the data in any named range and add the necessary instructions to reproduce the cell formatting - without referring to pre-defined CSS styles. Click to open a simple page containing an instruction to call an external javascript file and also view the VBA code which created it. View the source code of that page to see how the file has been included. This is a similar program to one used for building whole formatted web pages from an Excel range.

The next example is a much simpler table which does not include CSS formatting instructions.

Inserted table
HTML code within this web page

<div id='simpletable'></div>
<script type='text/javascript' src='jsxlsm.js'></script>

The contents of the jsxlsm.js file are as follows:

  document.getElementById('simpletable').innerHTML="<table width='450px'><tr><td>Employee</td><td>Start Date</td><td>Salary</td></tr><tr><td>Brown</td><td align='center'>12/03/01</td><td align='right'>28,987</td></tr><tr><td>Dunton</td><td align='center'>12/01/89</td><td align='right'>23,456</td></tr><tr><td>Elgar</td><td align='center'>01/06/95</td><td align='right'>32,136</td></tr></table>";

The VBA code in Excel which produced the 'jsxlsm.js' file is as follows:

  Sub MakeSimpleJSxl()
' Make HTML table from excel and save as JS innerHTML
Dim FirstRow As Integer, LastRow As Integer, Vtype As Integer
Dim FirstCol As Integer, LastCol As Integer
Dim TempStr As String, MyRow As Integer, MyCol As Integer
Dim TableStr As String, MyFilename As String

MyFilename = "C:\Other docs\Site\jsxlsm.js"
Open MyFilename For Output As #1

TableStr = "<table width='450px'>"

FirstRow = 3 ' location of table in worksheet
LastRow = 6
FirstCol = 1
LastCol = 3

For MyRow = FirstRow To LastRow
TableStr = TableStr & "<tr>"

For MyCol = FirstCol To LastCol
Vtype = 0 ' check the cell data type
If IsNumeric(Cells(MyRow, MyCol).Value) Then Vtype = 3
If IsDate(Cells(MyRow, MyCol).Value) Then Vtype = 2
If IsEmpty(Cells(MyRow, MyCol).Value) Then Vtype = 1

If Vtype = 0 Then ' text
 TempStr = "<td>" & Cells(MyRow, MyCol).Value & "</td>"
End If
If Vtype = 1 Then ' empty
 TempStr = "<td>&nbsp;</td>"
End If
If Vtype = 2 Then ' date
 TempStr = "<td align='center'>" & Format(Cells(MyRow, MyCol).Value, "dd/mm/yy") & "</td>"
End If
If Vtype = 3 Then ' number
 TempStr = "<td align='right'>" & Format(Cells(MyRow, MyCol).Value, "#,##0") & "</td>"
End If

TableStr = TableStr & TempStr
Next MyCol

TableStr = TableStr & "</tr>"
Next MyRow
TableStr = TableStr & "</table>"
Print #1, "document.getElementById('simpletable').innerHTML=" & Chr(34) & TableStr & Chr(34) & ";"
Close #1
End Sub

An alternative technology is to use an HTML iFrame. This allows a small file containing some HTML code (such as a table or paragraph) to be included within a page.

This page is NOT currently included
in the Excel functions guide.
Document is in PDF format Click here for details about
obtaining this file

file: Jsp1Htm.htm Page last updated: Feb15 Open MeadInKent Facebook page 2015 CMIDX S5 P14 N