export data to excel worksheet

hello,
I am needing a way to get so data from and asp page into excel. right now I have a button at the bottom of the page that basicly sends the info to another identicale asp page that has
<%Response.ContentType = "application/vnd.ms-excel"%>
at the top.
while it does export everything exactly from the page into excel, the data is not formatted in a way that the numbers are numbers, its all text so you cant sum columns. is there a way to export that will work better.  

here is the code.
<%@ Language=VBScript %>
 
<!--#include file="../includes/adovbs.inc"-->
<!--#include file="Tools/userinfo.inc"-->
 
<%
		
		'Response.Write "servername = " & servername
	
	UID = Request.ServerVariables("LOGON_USER")
	Uname = ucase(right(UID,7))	
	response.write Uname
If Uname = "U393451" or Uname = "U203835" or Uname = "U136074" or Uname = "U649871" or Uname = "U648568" or Uname = "U648796" or Uname = "U648541" or Uname = "U649155" or Uname = "U920792" or Uname = "U919007" or Uname = "U241344" or Uname = "U648768" or Uname = "U648719" or Uname = "U377183" or Uname = "U643348" or Uname = "U648135" or Uname = "U648227" or Uname = "U373897" or Uname = "U141525" or Uname = "U135734" or Uname = "U730005" then
	vMdbFile = Server.MapPath("\virtual\data\EnergySysVC\TiH.mdb")
	vConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security info=False;Data Source=" & vMdbFile	
 
		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open vConnStr
		
		
	
	
		
	session("Month") = Request.QueryString("Month")
	session("Year") = Request.QueryString("Year")
	session("Site") = Request.QueryString("Site")
	CatArrayM = Array("All","Jan","Feb","March","April","May","June","July","Aug","Sept","Oct","Nov","Dec")
	CatArrayY = Array("2007","2008","2009")
 
 
%>
 
<html>
 
<head>
<title></title>
 
 
<script LANGUAGE="vbscript">
<!--
	sub Myformsubmit(index)
		select case index
			case 1
			myform.Site.value = ""
			MyForm.submit
			case 2
			MyForm.submit
		end select
	end sub
-->
</script>
 
</head>
<%
 
 
%>
<body onload="">
 
 
 
<table width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td name="HeaderCell" id="HeaderCell"><a name="Top">
</td>
  </tr>
  <tr>
    <td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
        <td width="81%">
        <td>
        <td> <td>
      </tr>
      <tr>
        <td width="81%" colspan="2"><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
         
 
<form method="get" name="myform">
 
<table border="0" width="100%">
<tr>
<td width="20%"><b>Year</b></strong></td>
<td width="80%"><select name="Year" id="Year" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(1)">
		<option></option>
		
<%
	for each item in catArrayY
		if item = session("Year") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
			else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
 
		
		
	next
%>
</td>
</tr>
<tr>
<td width="20%"><b>Month</b></strong></td>
<td width="80%"><select name="Month" id="Month" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(1)">
		<option></option>
		
<%
	for each item in catArrayM
		if item = session("Month") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
			else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
 
		
		
	next
%>
</select>
</td><br>
 
</tr>
<tr>
<td width="20%"><strong>Plant Site</strong></td>
<td width="80%"><select name="Site" id="Site" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(2)">
		<option></option>
<%
if session("Month") <> "" then
 
	select case session("Month")
 
		case "Jan"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Feb"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "March"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "April"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "May"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "June"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "July"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Aug"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Sept"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Oct"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Nov"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Dec"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "All"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
 
	end select
 
	for each item in typeArray
		if item = session("Site") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
		else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
	next
end if
%>
</select>
</td></tr>
 
</table>
<p>&nbsp;</p>
</table>
 
<%
If session("Month") <> "" Then
	If session("Month") = "All" then
		sql = "SELECT * FROM tblTIHMetrics WHERE tblTIHMetrics.[Year]='" & Session("Year") & "' AND tblTIHMetrics.[Site]='" & Session("Site") & "';" 
	Else 
		sql = "SELECT * FROM tblTIHMetrics WHERE tblTIHMetrics.[Year]='" & Session("Year") & "' AND tblTIHMetrics.[Month]='" & Session("Month") & "' AND tblTIHMetrics.[Site]='" & Session("Site") & "';"
	End if
 
	set RS = server.createobject("adodb.recordset")
	RS.open sql,Conn,adopenkeyset,adlockoptimistic
 
%>
 
 
<table border="1" width="102%">
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr class="cellcolorlighter">
<td width="5%" align="center" valign="top" class="menuheadcell">Record ID</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Name</td>
<td width="4%" align="center" valign="top" class="menuheadcell">Year</td>
<td width="8%" align="center" valign="top" class="menuheadcell">Plant Site</td>
<td width="4%" align="center" valign="top" class="menuheadcell">Month</td>
<td width="5%" align="center" valign="top" class="menuheadcell">Planned Hours for Job</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Actual Hours for Job</td>
<td width="10%" align="center" valign="top" class="menuheadcell">Percentage of One Man Jobs</td>
<td width="11%" align="center" valign="top" class="menuheadcell">Percentage of Jobs Started in the Afternoon</td>
<td width="6%" align="center" valign="top" class="menuheadcell">TIH Hours per Job</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Total number of Jobs</td>
</tr>
<%
do while not rs.eof
 
%>
<tr>
<td width="6%" valign=top ><%=Rs.fields("ID")%>&nbsp;</td>
<td width="8%" valign=top ><%=Rs.fields("Name")%>&nbsp;</td>
<td width="6%" valign=top ><%=Rs.fields("Year")%>&nbsp;</td>
<td width="9%" valign=top ><%=Rs.fields("Site")%>&nbsp;</td>
<td width="6%" valign=top ><%=Rs.fields("Month")%>&nbsp;</td>
<td width="7%" valign=top><%=Rs.fields("PlannedHoursperJob")%>&nbsp;</td>
<td width="9%" valign=top><%=Rs.fields("ActualHoursperJob")%>&nbsp;</td>
<td width="12%" valign=top><%=Rs.fields("PercentOneManJobs")%>&nbsp;</td>
<td width="13%" valign=top><%=Rs.fields("PercentJobsStartedintheAfternoon")%>&nbsp;</td>
<td width="7%" valign=top><%=Rs.fields("TIHHoursperJob")%>&nbsp;</td>
<td width="9%" valign=top><%=Rs.fields("TotalJobs")%>&nbsp;</td>
 
 
 
</tr>
<%
	rs.movenext
loop
rs.close
set rs = nothing
%>
</table>
</form>
<%
 
end if
'End if
 
 
 
%>
<%
mth = Session("Month")
Ste = Session("Site")
Yr = Session("Year") 
 
Else
error = "You do not have access to run reports. "
Response.Write uname
Response.write error
Response.End
end if
conn.Close
	set conn = nothing
%>
<form method="post" action="TIHExcelReport.asp?YearMonth=<%=Mth%>&Site=<%=Ste%>"><input type="Submit" value="Export to Excel"></form>
            </td>
            <td name="ContentCell2" id="ContentCell2"><p align="left">&nbsp;</p>
            
            <p align="left"><!--webbot bot="PurpleText" PREVIEW="Do not use this space." --></td>
          </tr>
        </table>
        </div></td>
      </tr>
          </table>
    </div></td>
  </tr>
</table>
</body>
</html>
*******************************************************************************************************End of first page************************
<%@ Language=VBScript %>
 <%Response.ContentType = "application/vnd.ms-excel"%>
<!--#include file="../includes/adovbs.inc"-->
<!--#include file="Tools/userinfo.inc"-->
 
<%
		
		'Response.Write "servername = " & servername
	
	UID = Request.ServerVariables("LOGON_USER")
	Uname = ucase(right(UID,7))	
	response.write Uname
If Uname = "U393451" or Uname = "U203835" or Uname = "U136074" or Uname = "U649871" or Uname = "U648568" or Uname = "U648796" or Uname = "U648541" or Uname = "U649155" or Uname = "U920792" or Uname = "U919007" or Uname = "U241344" or Uname = "U648768" or Uname = "U648719" or Uname = "U377183" or Uname = "U643348" or Uname = "U648135" or Uname = "U648227" or Uname = "U373897" or Uname = "U141525" or Uname = "U135734" or Uname = "U730005" then
	vMdbFile = Server.MapPath("\virtual\data\EnergySysVC\TiH.mdb")
	vConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security info=False;Data Source=" & vMdbFile	
 
		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open vConnStr
		
		
	
	
		
	session("Month") = Request.QueryString("Month")
	session("Year") = Request.QueryString("Year")
	session("Site") = Request.QueryString("Site")
	CatArrayM = Array("All","Jan","Feb","March","April","May","June","July","Aug","Sept","Oct","Nov","Dec")
	CatArrayY = Array("2007","2008","2009")
 
 
%>
 
<html>
 
<head>
<title></title>
 
 
<script LANGUAGE="vbscript">
<!--
	sub Myformsubmit(index)
		select case index
			case 1
			myform.Site.value = ""
			MyForm.submit
			case 2
			MyForm.submit
		end select
	end sub
-->
</script>
 
</head>
<%
 
 
%>
<body onload="">
 
 
 
<table width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td name="HeaderCell" id="HeaderCell"><a name="Top">
</td>
  </tr>
  <tr>
    <td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
        <td width="81%">
        <td>
        <td> <td>
      </tr>
      <tr>
        <td width="81%" colspan="2"><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
         
 
<form method="get" name="myform">
 
<table border="0" width="100%">
<tr>
<td width="20%"><b>Year</b></strong></td>
<td width="80%"><select name="Year" id="Year" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(1)">
		<option></option>
		
<%
	for each item in catArrayY
		if item = session("Year") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
			else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
 
		
		
	next
%>
</td>
</tr>
<tr>
<td width="20%"><b>Month</b></strong></td>
<td width="80%"><select name="Month" id="Month" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(1)">
		<option></option>
		
<%
	for each item in catArrayM
		if item = session("Month") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
			else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
 
		
		
	next
%>
</select>
</td><br>
 
</tr>
<tr>
<td width="20%"><strong>Plant Site</strong></td>
<td width="80%"><select name="Site" id="Site" style="Height: 22px; Width: 100px;" onChange="Myformsubmit(2)">
		<option></option>
<%
if session("Month") <> "" then
 
	select case session("Month")
 
		case "Jan"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Feb"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "March"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "April"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "May"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "June"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "July"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Aug"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Sept"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Oct"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Nov"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "Dec"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
		case "All"
			typeArray = Array("Freeport","St. Charles","Seadrift","Plaquemine","Texas City","Fort Sasketchewan")
 
	end select
 
	for each item in typeArray
		if item = session("Site") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
		else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
	next
end if
%>
</select>
</td></tr>
 
</table>
<p>&nbsp;</p>
</table>
 
<%
If session("Month") <> "" Then
	If session("Month") = "All" then
		sql = "SELECT * FROM tblTIHMetrics WHERE tblTIHMetrics.[Year]='" & Session("Year") & "' AND tblTIHMetrics.[Site]='" & Session("Site") & "';" 
	Else 
		sql = "SELECT * FROM tblTIHMetrics WHERE tblTIHMetrics.[Year]='" & Session("Year") & "' AND tblTIHMetrics.[Month]='" & Session("Month") & "' AND tblTIHMetrics.[Site]='" & Session("Site") & "';"
	End if
 
	set RS = server.createobject("adodb.recordset")
	RS.open sql,Conn,adopenkeyset,adlockoptimistic
 
%>
 
 
<table border="1" width="102%">
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr class="cellcolorlighter">
<td width="5%" align="center" valign="top" class="menuheadcell">Record ID</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Name</td>
<td width="4%" align="center" valign="top" class="menuheadcell">Year</td>
<td width="8%" align="center" valign="top" class="menuheadcell">Plant Site</td>
<td width="4%" align="center" valign="top" class="menuheadcell">Month</td>
<td width="5%" align="center" valign="top" class="menuheadcell">Planned Hours for Job</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Actual Hours for Job</td>
<td width="10%" align="center" valign="top" class="menuheadcell">Percentage of One Man Jobs</td>
<td width="11%" align="center" valign="top" class="menuheadcell">Percentage of Jobs Started in the Afternoon</td>
<td width="6%" align="center" valign="top" class="menuheadcell">TIH Hours per Job</td>
<td width="7%" align="center" valign="top" class="menuheadcell">Total number of Jobs</td>
</tr>
<%
do while not rs.eof
 
%>
<tr>
<td width="6%" valign=top ><%=Rs.fields("ID")%>&nbsp;</td>
<td width="8%" valign=top ><%=Rs.fields("Name")%>&nbsp;</td>
<td width="6%" valign=top ><%=Rs.fields("Year")%>&nbsp;</td>
<td width="9%" valign=top ><%=Rs.fields("Site")%>&nbsp;</td>
<td width="6%" valign=top ><%=Rs.fields("Month")%>&nbsp;</td>
<td width="7%" valign=top><%=Rs.fields("PlannedHoursperJob")%>&nbsp;</td>
<td width="9%" valign=top><%=Rs.fields("ActualHoursperJob")%>&nbsp;</td>
<td width="12%" valign=top><%=Rs.fields("PercentOneManJobs")%>&nbsp;</td>
<td width="13%" valign=top><%=Rs.fields("PercentJobsStartedintheAfternoon")%>&nbsp;</td>
<td width="7%" valign=top><%=Rs.fields("TIHHoursperJob")%>&nbsp;</td>
<td width="9%" valign=top><%=Rs.fields("TotalJobs")%>&nbsp;</td>
 
 
 
</tr>
<%
	rs.movenext
loop
rs.close
set rs = nothing
%>
</table>
</form>
<%
 
end if
'End if
 
 
 
%>
<%
mth = Session("Month")
Ste = Session("Site")
Yr = Session("Year") 
 
Else
error = "You do not have access to run reports. "
Response.Write uname
Response.write error
Response.End
end if
conn.Close
	set conn = nothing
%>
<form method="post" action="TIHExcelReport.asp?YearMonth=<%=Mth%>&Site=<%=Ste%>"><input type="Submit" value="Export to Excel"></form>
            </td>
            <td name="ContentCell2" id="ContentCell2"><p align="left">&nbsp;</p>
            
            <p align="left"><!--webbot bot="PurpleText" PREVIEW="Do not use this space." --></td>
          </tr>
        </table>
        </div></td>
      </tr>
          </table>
    </div></td>
  </tr>
</table>
</body>
</html>

Open in new window

jlcannonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Loganathan NatarajanLAMP DeveloperCommented:
can you see the complete report of this code before you send it to the excel?

you can hide the  <%Response.ContentType = "application/vnd.ms-excel"%>  and print the complete report on the HTML page... just check whether everything is OK, ... then you can enable the content type and export it...?






0
jlcannonAuthor Commented:
no the report is exactly what I want except all the data is as text so numbers cannot be sumed and things such as that. that is why I am asking if there is another way for me to export to excel.
0
Loganathan NatarajanLAMP DeveloperCommented:
OK, i understand now,..  ,
0
jlcannonAuthor Commented:
I found a solution that works perfect for me, thanks.
<%@ Language=VBScript %>
 
<html>
<head>
</head>
<body><table border=0>
<tr><td><a href="export_to_excel.asp">Export <%response.write Session("Site")%></a></td></tr>
<tr><td>
<% 
 
 
dim Rs
 
vMdbFile = Server.MapPath("\virtual\data\EnergySysVC\TiH.mdb")
	vConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security info=False;Data Source=" & vMdbFile	
 
		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open vConnStr
 
set Rs=server.createobject("ADODB.recordset")
 
Rs.open "select * from tblTIHMetrics order by 'Site'",Conn,1,3
if Rs.eof <> true then
response.write "<table border=1>" & _
"<tr><td><strong>Name</strong></td><td><strong>Month</strong></td><td><strong>Year</strong></td><td><strong>Site</strong></td><td><strong>Planned Hours Per Job</strong></td>" & _
"<td><strong>Actual Hours Per Job</strong></td><td><strong>Percent of One Mans Job</strong></td><td><strong>Percent of Jobs started in Afternoon</strong></td>" &_
"<td><strong>TIH Hours Per Job</strong></td><td><strong>Total Jobs</strong></td></tr>"
while not Rs.eof
 
response.write "<tr><td>" & Rs.fields("Name") & "</td><td>" & Rs.fields("Month") & "</td><td>" & Rs.fields("Year") & "</td><td>" & Rs.fields("Site") & _
 
"</td><td>" & Rs.fields("PlannedHoursperJob") & "</td><td>" & Rs.fields("ActualHoursperJob") & "</td><td>" & Rs.fields("PercentOneManJobs") & _
"</td><td>" & Rs.fields("PercentJobsStartedintheAfternoon") & "</td><td>" & Rs.fields("TIHHoursperJob") & "</td><td>" & Rs.fields("TotalJobs") & _
 
 
"</td></tr>"
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing
Conn.close
%>
</td></tr>
</table>
</body>
</html>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.