?
Solved

writing into excel

Posted on 2004-09-14
6
Medium Priority
?
187 Views
Last Modified: 2010-04-06
I am trying to write into excel  bu  i  doesnt  sem to work
my excel is blank

 here  is code


<%
Response.ContentType = "application/vnd.ms-excel"
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- #Include File = "Include/Settings.asp" -->
<!-- #Include File = "Include/Public.asp" -->


<table border="1" bordercolor="#000000">
      <thead>
      
            <tr>
                  <th bgcolor="black"><font color="white">Date</font></th>
                  <th bgcolor="black"><font color="white">No of Pure Card calls</font></th>
                  <th bgcolor="black"><font color="white">No of Pure Bank calls</font></th>
                  <th bgcolor="black"><font color="white">No of Hybrid calls</font></th>
                                    
      
      </tr>
      </thead>

<tbody>

<%
Dim cnnDB
      Dim I, intPureCard, intPureBank, intHybrid
      Dim mis_Date
      Dim strSQL, strHTML
      Dim rstR
      
            
            
      
      
      ' Setup connection to database

      Set cnnDB = CreateConnection
      
      strSQL = "SELECT tblMIS.* FROM tblMIS"
                                                
      
      Set rstR = Server.CreateObject("ADODB.Recordset")
      rstR.CursorLocation=adUseClient
      rstR.Open strSQL, cnnDB, adOpenStatic, adLockReadOnly, adCmdText
      
      If rstR.BOF And rstR.EOF Then
      
            ' No records returned
      intPureCard = 0
      intPureBank = 0
      intHybrid = 0

            
      Else
      
            While Not rstR.EOF
            
            mis_Date = rstR.Fields("MISDate")
            intPureCard = rstR.Fields("PureCard")
            intPureBank = rstR.Fields("PureBank")
            intHybrid= rstR.Fields("Hybrid")
            %>
            <tr bordercolor="#000000">
                  <td bordercolor="#000000"><%= mis_Date %></td>
                  <td bordercolor="#000000"><%= intPureCard %></td>
                  <td bordercolor="#000000"><% = intPureBank %></td>
                  <td bordercolor="#000000"><%= intHybrid %></td>
                  
</tr>
            <%
          
            rstR.MoveNext
 
            WEnd
            
      
      End If
      
      rstR.Close
      Set rstR = Nothing
      
cnnDB.Close
Set cnnDB = Nothing
      %>
      </tbody>
</table>
0
Comment
Question by:mudugose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12059070
You will have to work with me on this to identify the bug.

Try this first.

<%
 ' Response.ContentType = "application/vnd.ms-excel"  ' <<<<<<<<<<< Comment this line out
%>

and tell if you see the output on the ASP page.

:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12059208
Any feedback about my earlier post? It looks like there is a problem in this line...

    ' Setup connection to database
     Set cnnDB = CreateConnection     ' <<<<<<<<<<<<<<<<<


Try this code. Edit where indicated.
:) SD

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;filename=MyExcelFile.xls"
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- #Include File = "Include/Settings.asp" -->
<!-- #Include File = "Include/Public.asp" -->

<%
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' XML parameters to open an Excel file
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim u_title
u_title = "My Excel File"
Response.Write "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">" & VbCrLf
Response.Write "<head>" & VbCrLf
Response.Write "<!--[if gte mso 9]><xml>" & VbCrLf
Response.Write "<x:ExcelWorkbook>" & VbCrLf
Response.Write "<x:ExcelWorksheets>" & VbCrLf
Response.Write "<x:ExcelWorksheet>" & VbCrLf
Response.Write "<x:Name>"& u_title &"</x:Name>" & VbCrLf
Response.Write "<x:WorksheetOptions>" & VbCrLf
Response.Write "<x:Print>" & VbCrLf
Response.Write "<x:ValidPrinterInfo/>" & VbCrLf
Response.Write "</x:Print>" & VbCrLf
Response.Write "</x:WorksheetOptions>" & VbCrLf
Response.Write "</x:ExcelWorksheet>" & VbCrLf
Response.Write "</x:ExcelWorksheets>" & VbCrLf
Response.Write "</x:ExcelWorkbook>" & VbCrLf
Response.Write "</xml>" & VbCrLf
Response.Write "<![endif]--> " & VbCrLf
Response.Write "</head>" & VbCrLf
Response.Write "<body>" & VbCrLf
%>

<table border="1" bordercolor="#000000">
     <tr>
               <th bgcolor="black"><font color="white">Date</font></th>
               <th bgcolor="black"><font color="white">No of Pure Card calls</font></th>
               <th bgcolor="black"><font color="white">No of Pure Bank calls</font></th>
               <th bgcolor="black"><font color="white">No of Hybrid calls</font></th>
     </tr>

<%
Dim cnnDB
     Dim I, intPureCard, intPureBank, intHybrid
     Dim mis_Date
     Dim strSQL, strHTML
     Dim rstR

    ' Setup connection to database
     Dim strDataPath, strConnectString

     strDataPath = server.MapPath("Insert_database_Path_HERE.mdb")                     ' <<<<< edit this line

     strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
               & " Data Source= " & strDataPath & ";"_
               & " Mode=Share Deny None;User Id=Admin;PASSWORD=;"

     if not IsObject("cnnDB") then
          set cnnDB=Server.CreateObject("ADODB.Connection")
          if cnnDB.state = 0 then
               cnnDB.Open strConnectString
          end if
     end if
     
     strSQL = "SELECT tblMIS.* FROM tblMIS"
     
     Set rstR = Server.CreateObject("ADODB.Recordset")
     rstR.CursorLocation=adUseClient
     rstR.Open strSQL, cnnDB, adOpenStatic, adLockReadOnly, adCmdText
     
     If rstR.BOF And rstR.EOF Then
          ' No records returned
          intPureCard = 0
          intPureBank = 0
          intHybrid = 0
          %>
          <tr bordercolor="#000000">
               <td bordercolor="#000000"><%= mis_Date %></td>
               <td bordercolor="#000000"><%= intPureCard %></td>
               <td bordercolor="#000000"><% = intPureBank %></td>
               <td bordercolor="#000000"><%= intHybrid %></td>      
         </tr>
          <%
     Else
          While Not rstR.EOF
         
                    mis_Date = rstR.Fields("MISDate")
                    intPureCard = rstR.Fields("PureCard")
                    intPureBank = rstR.Fields("PureBank")
                    intHybrid= rstR.Fields("Hybrid")
                    %>
                    <tr bordercolor="#000000">
                         <td bordercolor="#000000"><%= mis_Date %></td>
                         <td bordercolor="#000000"><%= intPureCard %></td>
                         <td bordercolor="#000000"><% = intPureBank %></td>
                         <td bordercolor="#000000"><%= intHybrid %></td>      
                   </tr>
                    <%
                    rstR.MoveNext
          WEnd
     End If
     
     rstR.Close
     Set rstR = Nothing
     
cnnDB.Close
Set cnnDB = Nothing
     %>
</table>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0
 

Author Comment

by:mudugose
ID: 12061630
no luck, I still get an empty excel file.

when i comment  the  excel line, i can see the  html table
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Accepted Solution

by:
sciber_dude earned 90 total points
ID: 12065895
> when i comment  the  excel line, i can see the  html table

HTML Table: Does it look like the output you want? Does it have the data?

Do you have excel loaded on your machine? Did you try to test it on other machines?

Also, try deleting all history, files and cookies and try again.

Did you try the code i gave you with the XML code in it?

:) SD
0
 

Author Comment

by:mudugose
ID: 12073218
I tried  everything....... still no luck :(
0
 

Author Comment

by:mudugose
ID: 12095443
thanks anyway
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question