By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,868 Members | 1,974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,868 IT Pros & Developers. It's quick & easy.

How to read an Excel file in ASP

ramanan ram
P: 26
Expand|Select|Wrap|Line Numbers
  1. <%
  2. if session("UserName")="" then
  3. %>
  4. <script>
  5. alert("Sorry, Session timed out");
  6. window.close();
  7. </script>
  8. <%
  9. response.End()
  10. end if
  11. %>
  12. <!-- #include File="../../dbConn.asp"-->
  13. <!-- #include File="../../general/CommonFunctions.asp"-->
  14. <style type="text/css">
  15.  
  16. .style1 {
  17.     font-family: DIN-Regular;
  18.     font-size: 12px;
  19. }
  20. .style2 {
  21.     font-size: 10;
  22.     font-weight: bold;
  23. }
  24. .style4 {font-size: 10}
  25.  
  26. </style>
  27.  
  28. <% 
  29.  
  30.     if request("txtFdate")<>"" and request("txtTdate")<>"" then
  31.         dETDF=split(Request("txtFdate"),"/")
  32.         if len(dETDF(0))=1 then
  33.             dETDF(0)=0&dETDF(0)
  34.         else
  35.             dETDF(0)=dETDF(0)
  36.         end if
  37.  
  38.         dETDF(1)=dETDF(1)
  39.  
  40.         fdate=dETDF(0)&"-" & monthName(dETDF(1),true)&"-" & dETDF(2)
  41.  
  42.         dETDT=split(Request("txtTdate"),"/")
  43.         if len(dETDT(0))=1 then
  44.             dETDT(0)=0&dETDT(0)
  45.         else
  46.             dETDT(0)=dETDT(0)
  47.         end if
  48.  
  49.         dETDT(1)=dETDT(1)
  50.  
  51.         tdate=dETDT(0)&"-" & monthName(dETDT(1),true)&"-" & dETDT(2)
  52.     end if
  53.  
  54.  
  55. %>
  56. <%
  57. if request("hdnA")="Arrival" then
  58.         set rsPipeline1=server.CreateObject("adodb.recordset")
  59.             strsql="select FLIGHTDATE,FLIGHTNO,POA,POID,PARTID,CTNS from ETRANS3103.GENERIC_VIEW_BD@etrans1_link where (FLIGHTDATE  between'"&fdate &"' and '" & tdate&"') and (CONSIGNEEID in ("&session("strcons")&") or BILLTO in ("&session("strbill")&")) group by FLIGHTDATE,FLIGHTNO,POA,POID,PARTID,CTNS ORDER BY FLIGHTDATE DESC"
  60.  
  61.             rsPipeline1.open strsql,conn,2,3
  62. session("ErrorNo")=37
  63. %>
  64. <!-- #include File="../../general/errorDesc1.asp"-->
  65. <%
  66.  
  67.     if not rsPipeline1.eof then
  68.     response.ContentType ="application/vnd.ms-excel"
  69.             strpage="Arrival_Report.xls"
  70.             response.AddHeader "content-disposition", "attachment; filename=" & strpage
  71.  
  72.  
  73.     %>
  74.  
  75. <table border=1>
  76.  
  77. <tr><td colspan="6" class="style1"><br></td></tr>
  78.  
  79.  
  80.  
  81.     <tr align="center"><td colspan="6" align="center" class="style1"><table width="100%" align="center" border="1">
  82.     <tr>
  83. <td  width="500" colspan="3" height="85" valign="middle" align="left">&nbsp;
  84. <img src="http://imports.wtseaairuk.com/images/wt_logo.gif" hspace="3" vspace="3"></td>
  85. <td  height="85" colspan="3" align="center" valign="middle">
  86. <img src="http://ge.wtseaairuk.com/version01/images/ge_logo.gif"  border="0">
  87. &nbsp;</td>
  88. </tr>
  89.     <tr>
  90.         <td colspan="6" align="center" nowrap>
  91.             <span class="style2"><span style="font-weight: bold">ARRIVAL REPORT FOR THE PERIOD FROM</span> <%=fdate%> - <%=tdate%> </span></td>
  92.     </tr>
  93.     <tr><td colspan="6"></td></tr>
  94.     <tr>
  95.         <td align="center" nowrap><span class="style2" style="font-weight: bold">FLIGHT DATE</span></td>
  96.         <td align="center" nowrap><span class="style2" style="font-weight: bold">FLIGHT NO</span></td>
  97.         <td align="center" nowrap><span class="style2" style="font-weight: bold">PORT OF DISCHARGE</span></td>
  98.         <td align="center" nowrap><span class="style2" style="font-weight: bold">P.O NO</span></td>
  99.         <td align="center" nowrap><span class="style2" style="font-weight: bold">STYLE NO</span></td>
  100.         <td align="center" nowrap><span class="style2" style="font-weight: bold">NO OF CARTONS</span></td>
  101.     </tr>
  102. <%
  103.     while not rsPipeline1.eof 
  104. %>
  105.     <tr>
  106.         <td align="right" nowrap><span class="style4"><%=dateDisplay(rsPipeline1("FLIGHTDATE"))%></span></td>
  107.         <td align="left" nowrap><span class="style4"><%=rsPipeline1("FLIGHTNO")%></span></td>
  108.         <td align="left" nowrap><span class="style4"><%=rsPipeline1("POA")%>&nbsp;</span></td>
  109.         <td align="left" nowrap><span class="style4"><%=rsPipeline1("POID")%></span></td>
  110.         <td align="left" nowrap><span class="style4"><%=rsPipeline1("PARTID")%></span></td>
  111.         <td align="right" nowrap><span class="style4"><%=rsPipeline1("CTNS")%></span></td>
  112.     </tr>
  113.     <%
  114.             rsPipeline1.movenext
  115.             wend
  116.             else 
  117.             flag=1
  118.     %>
  119.     </table></td></tr>
  120. <%
  121. end if
  122. elseif request("hndA")="Delivery" then
  123.  
  124. set rsPipeline=server.CreateObject("adodb.recordset")
  125.         strsql="select DELIVERYDATE,COMPANYNAME,POID,PARTID,CONSIGNEENAME,QTY,CTNS from ETRANS3103.GENERIC_VIEW_BD@etrans1_link where DELIVERYDATE between '"&fdate &"' and '" & tdate&"' and (CONSIGNEEID in ("&session("strcons")&") or BILLTO in ("&session("strbill")&")) group by DELIVERYDATE,COMPANYNAME,POID,PARTID,CONSIGNEENAME,QTY,CTNS ORDER BY DELIVERYDATE DESC"
  126.         'response.Write(strSql)
  127.         'response.End()
  128.         if rsPipeline.state=1 then rsPipeline.close
  129.         rsPipeline.open strsql, conn,2,3
  130. session("ErrorNo")=101
  131. %>
  132.     <!-- #include File="../../general/errorDesc1.asp"-->
  133. <%
  134.  
  135. if not rsPipeline.eof then
  136.  
  137.  
  138.    response.ContentType ="application/vnd.ms-excel"
  139.             strpage="Delivery_Report.xls"
  140.             response.AddHeader "content-disposition", "attachment; filename=" & strpage
  141.             'response.end()
  142.  
  143. %>
  144. <tr align="center"><td colspan="6" align="center" class="style1"><table width="100%" align="center" border="1"class="style1">
  145.     <tr>
  146. <td  width="500" colspan="3" height="85" valign="middle" align="left">&nbsp;<img src="http://ge.wtseaairuk.com/version01/images/wt_logo.gif" hspace="3" vspace="3"></td>
  147. <td  height="85" colspan="4" align="center" valign="middle">
  148. <img src="http://ge.wtseaairuk.com/version01/images/ge_logo.gif"  border="0" />&nbsp;</td>
  149. </tr>
  150.     <tr>
  151.         <td align="center" colspan="7">
  152.             <span class="style2"><span style="font-weight: bold">DELIVERY REPORT FOR THE PERIOD FROM</span> <%=fdate%> - <%=tdate%> </span></td>
  153.     </tr>
  154.     <tr>
  155.         <td align="center"><span class="style2" style="font-weight: bold">DELIVERY DATE</span></td>
  156.         <td align="center"><span class="style2" style="font-weight: bold">SUPPLIER</span></td>
  157.         <td align="center"><span class="style2" style="font-weight: bold">CUSTOMER</span></td>
  158.         <td align="center"><span class="style2" style="font-weight: bold">P.O No</span></td>
  159.         <td align="center"><span class="style2" style="font-weight: bold">STYLE NO</span></td>
  160.         <td align="center"><span class="style2" style="font-weight: bold">PIECES</span></td>
  161.         <td align="center"><span class="style2" style="font-weight: bold">CARTONS</span></td>
  162.     </tr>
  163. <%
  164.  
  165.     while not rsPipeline.eof 
  166. %>
  167.     <tr>
  168.         <td align="right"><span class="style4"><%=dateDisplay(rsPipeline("DELIVERYDATE"))%></span><td>
  169.     <td align="left" nowrap><span class="style4"><%=rsPipeline("COMPANYNAME")%></span></td>
  170.     <td align="left"><span class="style4"><%=rsPipeline("CONSIGNEENAME")%></span></td>        
  171.     <td align="left"><span class="style4"><%=rsPipeline("POID")%></span></td>
  172.         <td align="left"><span class="style4"><%=rsPipeline("PARTID")%></span></td>
  173.         <td align="right"><span class="style4"><%=rsPipeline("QTY")%></span></td>
  174.         <td align="right"><span class="style4"><%=rsPipeline("CTNS")%></span></td>
  175.         <%
  176.         'response.write("djfhdjfhdd")
  177.  
  178.         %>
  179.   </tr>
  180. <%
  181.             rsPipeline.movenext
  182.             wend
  183.             else
  184.                 flag=1
  185.  
  186. %>
  187.     </table></td></tr>
  188. <%
  189. end if
  190. end if
  191. response.end()
  192. %>
  193. </table>
  194. <span class="style1">
  195. <%
  196. if flag=1 then
  197. %>
  198. <script>
  199.     alert("Sorry, no such matching records found on the system");
  200.     window.parent.location="ArrivalReport.asp"
  201.     //window.close();
  202. </script>
  203. <%end if%>
  204. <%
  205. response.End()
  206. %>
  207. </span>
here,attached asp coding ,the query return data based on date wise .the query executed perfectly , data showing report wise in excel sheet .now the problem is ,sure i am tell data returned .if i am save the excel sheet size also showing .but i am open the excel sheet nothing inside just unruled empty sheet there.suppose open with notepad or html data its there note:notepad data only having an html coding default its coming <html><body></body></html> i remove this coding after save and open with excel now the data its there</
please give solution..
Aug 2 '12 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.