A tale of ASP, Excel and a RecordSet
I’ve worked on a number of
‘classic’ ASP (VBScript) sites that needed to dynamically create an
Excel spreadsheet containing information contained in a
Recordset. As with anything, software development included, there are a number of ways to do the same thing. One approach I have taken is to pass a
Recordset to a
CreateExcelWorkbook function. Simplifying things a bit, while also displaying some other things you can do with the worksheet:
<% function CreateExcelWorkbook(rs)
'### Relative path for saving Excel file
EXCEL_PATH = "c:\Inetpub\wwwroot\excel\"
'### Field type constants
adEmpty = 0
adSmallInt = 2
adInteger = 3
adSingle = 4
adDouble = 5
adCurrency = 6
adDate = 7
adBSTR = 8
adError = 10
adBoolean = 11
adDecimal = 14
adTinyInt = 16
adUnsignedTinyInt = 17
adUnsignedSmallInt = 18
adUnsignedInt = 19
adBigInt = 20
adUnsignedBigInt = 21
adFileTime = 64
adGUID = 72
adBinary = 128
adChar = 129
adWChar = 130
adNumeric = 131
adUserDefined = 132
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adChapter = 136
adPropVariant = 138
adVarNumeric = 139
adVarChar = 200
adLongVarChar = 201
adVarWChar = 202
'### Generate filename based upon time stamp
sMonth = cStr(Month(Now()))
sYear = cStr(Year(Now()))
sDay = cStr(Day(Now()))
sHour = cStr(Hour(Now()))
sMin = cStr(Minute(Now()))
sSec = cStr(Second(Now()))
filename = sYear + sMonth + sDay + sHour+ sMin+ sSec + ".xls"
'### Create Excel object
Set objExcel = server.CreateObject("Excel.Application")
objExcel.Application.Visible = false
objExcel.Application.SheetsInNewWorkbook = 1
objExcel.Application.DisplayAlerts = false
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Sheets(1)
iCol = 1
'### Create a header row of the field names
for i= 0 to rs.Fields.Count - 1
objSheet.Cells(1,cInt(iCol)).Value= rs.Fields.Item(i).Name
objSheet.Cells(1,cInt(iCol)).Font.Bold = -1
iCol= iCol + 1
next
iCol = 1
while not rs.EOF
for i = 0 to rs.Fields.Count - 1
objSheet.Cells(iRow,i+1).Value = rs(i)
'objSheet.Cells(iRow,i+1).Value = rs("fieldname")
'### Format cell based upon field type
Select Case rs.Fields.Item(i).Type
Case adCurrency,adDouble
objSheet.Cells(iRow,i+1).Select
objSheet.Cells(iRow,i+1).Style = "Currency"
Case adDBDate,adDBTime,adDBTimeStamp
objSheet.Cells(iRow,i+1).NumberFormat = "m/d/yy h:mm;@"
End Select
next
iRow = iRow + 1
rs.movenext
wend
'### Some column sizing
objSheet.Columns("A:E").EntireColumn.AutoFit
objSheet.Columns("F:F").ColumnWidth = 5.00
objSheet.Columns("G:G").ColumnWidth = 5.00
objSheet.Cells(2,1).Select
'### Freeze header row
objExcel.ActiveWindow.FreezePanes = -1
objWorkbook.SaveAs EXCEL_PATH + filename, FileFormat=xlNormal
Set objSheet = Nothing
objWorkbook.Close False
Set objWorkbook = Nothing
objExcel.quit
Set objExcel = Nothing
'### Return the filename
CreateExcelWorkbook = filename
end function
%>
You can use this function in an ASP page:
<% on error resume next
STR_SQL = "SELECT * FROM tblData”
Set DataConn= Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30 ‘###CONN_STRING is the connection string to your data
DataConn.open CONN_STRING
Set Data_RS = DataConn.Execute(STR_SQL)
Data_RS.MoveFirst
if not Data_RS.EOF then
filename = CreateExcelWorkbook(Data_RS)
end if
Data_RS.close
set Data_RS = nothing
DataConn.Close
set DataConn = nothing
‘### open the Excel spreadsheet
Response.redirect("excel/" + filename)
%> I also have another
3 part post that deals with working with
Excel documents.
Labels: ASP, Code
AddrMon
October 2005
Subscribe!