browse by category or date

We have an ancient system in combo of classic ASP (VBA) and Allaire JRun which just refuse to die 🙂 Recently I was tasked to add a report that generate Excel document.

Did you know that we can simply provide a HTML format and disguise it as XLS document. With that, Excel (2003 and above) will be able to open it?

In the beginning of the asp file, we set the response header:

<%
Response.AddHeader "content-disposition","attachment; filename=Report.xls"
Response.ContentType = "application/vnd.ms-excel"
%>

Next how do we format the data correctly? We can do that with a little bit of CSS:

.number {
   mso-number-format:General;
}
.longdate {
  mso-number-format:"dd\-MMM\-yyyy HH:mm";
}
.text{
  mso-number-format:"\@";
}
.decimal{
  mso-number-format:"0.00";
}

If you want more formats, you can always open Excel, right-click any cell, then click Format Cells.

excel.format

Cool right? Frankly, I only know this trick today 😀

To use it, we simply assign the CSS class to the td element:

<%
dim Dim strconn
Set strconn = Server.CreateObject("ADODB.Connection")

'ODBC Connection to Oracle
strconn.Open ( "DSN=_ODBC_NAME;USERNAME=_DB_USERNAME;PASSWORD=_DB_PASSWORD;SERVICE=_ORACLE_DB_SID" )

Dim sql
sql = ".... Some SQL query ......"
Set rs = Server.CreateObject ( "ADODB.RecordSet" )
rs.Open sql, strconn, adLockOptimistic

While not rs.EOF
%>
	<tr align=center> 
		<td class="text"><%=rs(0)%></td>
		<td><%=rs(1)%></td>
		<td class="number"><%=rs(2)%></td>      
		<td><%=rs(3)%></td>         
		<td><%=rs(4)%></td>
		<td><%=rs(5)%></td>
		<td><%=rs(6)%></td>
		<td><%=rs(7)%></td>
		<td><%=rs(8)%></td>
		<td class="number"><%=rs(9)%></td>
		<td class="number"><%=rs(10)%></td>
		<td class="longdate"><%=rs(11)%></td>
		<td><%=rs(12)%></td>
		<td><%=rs(13)%></td>  
		<td><%=rs(14) %></td>
	</tr>
<%		
	rs.movenext
wend 
%>
GD Star Rating
loading...
How to Correctly Format Data in HTML Excel Document, 3.0 out of 5 based on 1 rating

Possibly relevant:

About Hardono

Howdy! I'm Hardono. I am working as a Software Developer. I am working mostly in Windows, dealing with .NET, conversing in C#. But I know a bit of Linux, mainly because I need to keep this blog operational. I've been working in Logistics/Transport industry for more than 11 years.

Incoming Search

asp, excel, html

No Comment

Add Your Comment