Inspired by this Kuro5hin article, I hacked up a slightly nicer JScript implementation to read database tables and generate HTML tables in a browser using DOM functions such as insertRow() and insertCell().
Try it out
If you want to try it out, first create an Excel workbook called Test.xls with a page named DataTest. In DataTest, create one column with a heading StringValue and another column with a heading NumericValue. Enter some test data in those columns, then save the file.
Next, copy the HTML code at the end of this article, change the DSN path appropriately and save it in a file.
Finally, load the HTML file in IE6. When you select the Test button, the data in your workbook should be displayed in the browser.
Notes
If you start Excel and the user interface doesn't initialize properly, the cause is usually the Excel automation server not exiting. Just start Windows Task Manager, and kill the errant Excel.exe process in the Processes tab.
I chose Excel instead of Access as the database because it is easier to test simple programs without having to create a table design.
Sample JScript and HTML to read database
<html>
<head>
<title>DBTest for IE6</title>
<script type="text/javascript">
function readDb(tableId) {
var conn = new ActiveXObject("ADODB.Connection")
var dsn = "Provider=Microsoft.Jet.OLEDB.4.0;"
dsn += "Data Source=C:\\CVS_STUFF\\HTML\\Database\\Test.xls;"
dsn += 'Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'
conn.Open(dsn)
var rs = new ActiveXObject("ADODB.Recordset")
rs.Open("[DataTest$]", conn)
var outputTable = document.getElementById(tableId)
if (!outputTable) {
alert("Cannot find " + tableId)
return
}
if (!rs.bof) {
var rowIndex = 1
rs.MoveFirst()
while (!rs.eof) {
var row = outputTable.insertRow(rowIndex)
var c1 = row.insertCell(0)
var data1 = rs.Fields('StringValue').value
var text1 = document.createTextNode(data1)
c1.appendChild(text1)
var c2 = row.insertCell(1)
var data2 = rs.Fields('NumericValue').value
var text2 = document.createTextNode(data2)
c2.appendChild(text2)
++rowIndex
rs.MoveNext()
}
}
rs.Close()
conn.Close()
}
</script>
</head>
<body>
<h1>DBTest for IE6</h1>
<form action="javascript:readDb('testOut')">
<input type="submit" value="Test"/>
</form>
<table id="testOut">
<tr><th>StringValue</th><th>NumericValue</th></tr>
</table>
</body>
</html>
No comments:
Post a Comment