Skip to content

Instantly share code, notes, and snippets.

@patcullen
Created May 27, 2014 06:34
Show Gist options
  • Save patcullen/096a79ca8000b367537e to your computer and use it in GitHub Desktop.
Save patcullen/096a79ca8000b367537e to your computer and use it in GitHub Desktop.
A helper for working with Excel files in classic ASP using JScript
<%
var excel = (function () {
var connection = null;
function open(file) {
connection = Server.createObject('ADODB.Connection');
connection.CommandTimeout = 60;
connection.CursorLocation = 3;
connection.Open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+file+';Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";');
return this;
};
function query(sql, params, callback) {
var sql = (params == null ? sql : replaceSQLParams(sql, params));
var rsa = Server.CreateObject("ADODB.Recordset");
rsa.open(sql, connection, 3, 3);
if (typeof callback == 'function') {
callback(resultSet(rsa));
} else {
return resultSet(rsa);
}
return this;
};
function close() {
connection.close();
};
function each(sql, params, callback) {
query(sql, params, function(data) {
data.each(callback);
});
};
function replaceSQLParams(s, p) {
var r = s;
for (var k in p) {
if (typeof p[k] == 'string') {
r = r.replace(new RegExp('@' + k, 'g'), '\'' + p[k].replace(/\'/g, '\'\'') + '\'');
} else {
r = r.replace(new RegExp('@' + k, 'g'), p[k]);
}
}
return r;
};
// below a small interface with the native ado result set objects
function resultSet(nativeResultSet) {
var fieldsCache = null;
function each(f, dontClose) {
for (var i = 0; !eof(); i++)
f(get(), i);
if (!dontClose) rs_close();
};
function asArray() {
var d = [];
while (!eof())
d.push(get());
rs_close();
return d;
};
function get(f) {
if (eof()) return null;
if (f) {
var t = nativeResultSet.fields(f).value;
if (typeof t == 'date') t = new Date(''+t).toISOString();
return t;
} else {
var r = getObject();
next();
return r;
}
};
function getObject() {
var o = {};
var fld = fields();
for (var i = 0; i < fld.length; i++)
o[fld[i]] = get(fld[i]);
return o;
};
function next() {
nativeResultSet.moveNext();
};
function eof() {
return nativeResultSet.eof;
};
function numFields() {
return nativeResultSet.fields.count;
};
function rowCount() {
return nativeResultSet.recordCount;
};
function fields() {
if (fieldsCache == null) {
fieldsCache = [];
var j = numFields();
for (var i = 0; i < j; i++)
fieldsCache.push(nativeResultSet.fields(i).name);
}
return fieldsCache;
};
function rs_close() {
nativeResultSet.close();
};
return {
eof: eof,
each: each,
get: get,
next: next,
fields: fields,
numFields: numFields,
rowCount: rowCount,
close: rs_close,
asArray: asArray
};
};
return {
open: open,
query: query,
close: close,
each: each
};
})();
%>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment