Home	Excel	Access
\| 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> </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.

Click here for details about
obtaining this file

file: Jsp1Htm.htm

Page last updated: Feb15

CMIDX S5 P14 N