Reading Excel Files in Classic ASP

Recently I had the need to source data from Excel files in the old Classic ASP platform. There are some good resources online which can help you with this, but I thought I'd log my little experience here which may hopefully expedite the process for someone else someday :)

In my experience most people use VB script in their ASP environment,. the past few years I have grown to prefer using JScript. I'll provide my testing in both.

A Little Environment Preface

In my examples I will have a file called unlocodes.xlsx placed in the directory c:\temp\
The content of the Excel file looks like this:

ASP (VB)

Here is a barebones ASP sample connecting to the Excel file.

	<%
	ExcelFile = "c:\temp\unlocodes.xlsx"
	SQL = "SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]"
	Set ExcelConnection = Server.createobject("ADODB.Connection")
	ExcelConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
	SET RS = Server.CreateObject("ADODB.Recordset")
	RS.Open SQL, ExcelConnection
	Response.Write "<table border=""1""><thead><tr>"
	FOR EACH Column IN RS.Fields
	Response.Write "<th>" & Column.Name & "</th>"
	NEXT
	Response.Write "</tr></thead><tbody>"
	IF NOT RS.EOF THEN
	WHILE NOT RS.eof
	Response.Write "<tr>"
	FOR EACH Field IN RS.Fields
	Response.Write "<td>" & Field.value & "</td>"
	NEXT
	Response.Write "</tr>"
	RS.movenext
	WEND
	END IF
	Response.Write "</tbody></table>"
	RS.close
	ExcelConnection.Close
	%>

view raw excel-vb.asp hosted with ❤ by GitHub

This resulted in:

ASP (JScript)

Here is a JScript sample connecting to the Excel file. In my current environment I never use serverside JScript to render HTML, rather only to serve in a JSON based API format. I trimmed down the scaffolding into the bare necessities: an Excel interface and a JSON polyfill that works in ASP JScript.

	<%@ Language=JScript %>
	<!--#include file="excel.asp" --><%/* https://gist.githubusercontent.com/patcullen/096a79ca8000b367537e/raw/7a806439bdf388335f2403ba89d8f51f039fb6a8/excel.asp */%>
	<!--#include file="json.asp" --><%/* https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js */%>
	<%
	excelFile = "c:/temp/unlocodes.xlsx"
	sql = "SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]"
	excel.open(excelFile).query(sql, {}, function(codes) {
	Response.write(
	JSON.stringify(codes.asArray())
	);
	excel.close();
	});
	%>

view raw excel-js.asp hosted with ❤ by GitHub

This resulted in:

Some Extra Notes

I crossed paths with two errors, both of which were resolved by simply choosing the correct connection string.
This error:

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

and this error:

Microsoft JET Database Engine error '80004005'

External table is not in the expected format.

The ConnectionStrings.com website is a great resource for finding a connection string compatible with your installed version of Excel. I found that on my machine with Excel 2010 this connection string worked:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Whereas on our production server we have 2013 installed:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

If you continue to have problems finding the correct driver, or it complaining it's not installed, then be sure to download and install the Microsoft Access Database Engine 2010 Redistributable. This includes the latest ACE drivers which come in 32 and 64 bit flavors. For posterity you may want to try install the 64bit version in command line using the follwoing syntax:

AccessDatabaseEngine_X64.exe /passive

Injecting CSS with Javascript

Why injectCSS? Why make it? What is it solving?

• The primary goal was to create an easy way to include multi-line (lots) of CSS using Javascript.
• Can reduce number of files in a JS plugin or project (My goal was one).
• A test run at jsperf.com shows that injecting CSS rules for a vast number of elements is very fast.

There are already some existing CSS injection tools, but I wanted something dead-simple. There is a great library called VeinJS, which does so much more than mine in many different ways. But after perusing through the examples I knew that I would have to dismantle my CSS too much. What I wanted was something like what you will see in the example below.

Example Usage

With RequireJS

	require(['injectCSS'], function(injectCSS) {
	injectCSS(function(){/*
	.case1 {
	border: 3px solid #0f0;
	}
	*/});
	injectCSS('.case2 { border: 3px solid #00f; } ');
	});

view raw injectCSS_demo1.js hosted with ❤ by GitHub

With no dependencies

	<!DOCTYPE html>
	<head>
	<script src="injectCSS.js"></script>
	</head>
	<body>
	<div class="test case1">test case 1</div>
	<div class="test case2">test case 2</div>
	<script>
	(function(){
	injectCSS(function(){/*
	.case1 {
	border: 3px solid #0f0;
	}
	*/});
	injectCSS('.case2 { border: 3px solid #00f; } ');
	})();
	</script>
	</body>
	</html>

view raw injectCSS_demo2.html hosted with ❤ by GitHub

Compatability

Tested successfully on:

• Chrome (+mobile)
• Firefox
• Safari (+mobile)
• IE 10+

Due to the older IE browsers not allowing the innerHTML property to be set on certain elements, this will not work in them.

Download

The code is on GitHub. Download, use and modify as you please.