[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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

0
jlcannon
Asked:
jlcannon
  • 2
  • 2
1 Solution
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now