Problem exporting to MS Excel

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • billkeziah
    New Member
    • Aug 2007
    • 1
    #1

    Problem exporting to MS Excel

    I am exporting data from an HTML table to MS Excel using ASP. Everything works fine except that data that is in one HTML cells separated by the "<BR>" export to MS Excel in 2 separate data cells in Excel. How can I make it go into one cell?

    Thanx in advance..
    Tags: None
    • jhardman
      Recognized Expert Specialist
      • Jan 2007
      • 3405
      #2
      This is because Excel uses new line characters as cell and row delimiters. I recommend treating "<br>" as a special character and escaping it to the phrase of your choice ("[br]" works nicely and is unlikely to be used for anything else, also "&lt;br&gt; " might work without any trouble when you retrieve it) then change it back to "<br>" after you retrieve the data. Would this work for your application?

      Jared

        Comment

        • Merlin1857
          New Member
          • Sep 2007
          • 14
          #3
          Try this :

          This code provides for the export of data from SQL server to Excel via two asp pages.
          You could go directly to the 02extoex.asp page without showing 01viewdata.asp which
          is only there to show the user the data first.

          =============== =============== =============== =============== ===

          PAGE ONE CALLED 01viewdata.asp :
          [code=asp]
          <%@ Language=VBScri pt %>
          <!-- #include file="../incfiles/adovbs.inc" -->
          <%
          dim Conn,RS1

          '============== ====CONNECTION CODE=========== ============
          Set Conn = Server.CreateOb ject("ADODB.Con nection")
          Set RS1 = Server.CreateOb ject("ADODB.Rec ordSet")

          strConn = "Provider=SQLOL EDB;Data Source=LAPTOP01 ;Initial Catalog=polpact ;"&_
          "User Id=logger;Passw ord=logger;"
          Conn.Open strConn
          '============== ====CONNECTION CODE=========== ============

          sql1 = "Select * from tblAccessLog WHERE LogID < 10000"

          RS1.Open sql1, Conn

          If RS1.eof Then
          %>



          <html>
          <body>
          <table border="0" width="100%" cellspacing="0" cellpadding="2" >
          <tr>
          <td>&nbsp;<fo nt face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
          </tr>
          </table>

          <%Else%>
          <form method="POST" action="02extoe x.asp">
          <table border=0 width="100%" cellspacing="0" cellpadding="2" >
          <tr>
          <td>
          <input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">&nbsp;
          <input type="hidden" name="InpSQL" size="63" value="<%=sql1% >"></td>
          </tr>
          </table>
          </form>

          <table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#0 00000">
          <tr>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log ID</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log Date</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log Time</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log User</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log IP Rem</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log IP Loc</font></td>
          <td bgcolor="#00008 0"><font face="Arial" size="2" color="#FFFFFF" >Log Page</font></td>
          </tr>
          <%Do While Not RS1.eof%>
          <tr>
          <td><font face="Arial" size="2"><%=RS1 ("LogID")%></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogDate")% ></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogTime")% ></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogUser")% ></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogIPRem") %></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogIPLoc") %></font></td>
          <td><font face="Arial" size="2"><%=RS1 ("LogPage")% ></font></td>
          </tr>
          <%
          RS1.Movenext
          Loop
          End If
          %>
          </table>



          </td></tr>
          </table>
          </body>
          </html>

          <%
          RS1.Close
          Conn.Close

          Set Conn = Nothing
          Set RS1 = Nothing
          %>
          [/code]
          =============== =============== =============== =============== ===

          PAGE TWO CALLED 02extoex.asp :
          [code=asp]
          <%@ Language=VBScri pt %>
          <!-- #include file="../incfiles/adovbs.inc" -->
          <%
          dim Conn,RS1

          sql1 = Request("InpSQL ")

          '============== ====CONNECTION CODE=========== ============
          Set Conn = Server.CreateOb ject("ADODB.Con nection")
          Set RS1 = Server.CreateOb ject("ADODB.Rec ordSet")

          strConn = "Provider=SQLOL EDB;Data Source=LAPTOP01 ;Initial Catalog=polpact ;"&_
          "User Id=logger;Passw ord=logger;"
          Conn.Open strConn
          '============== ====CONNECTION CODE=========== ============

          'sql1 = "Select * from tblAccessLog WHERE LogID < 10000"

          RS1.Open sql1, Conn


          Response.Conten tType = "applicatio n/vnd.ms-excel"
          Response.AddHea der "Content-Disposition", "attachment ; filename=export .xls"
          %>

          <table border="1" width="100%">
          <tr>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log ID</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log Date</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log Time</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log User</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log IP Rem</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log IP Loc</font></td>
          <td bgcolor="#00008 0"><font color="#FFFFFF" >Log Page</font></td>
          </tr>
          <%Do While Not RS1.eof%>
          <tr>
          <td><%=RS1("Log ID")%></td>
          <td><%=RS1("Log Date")%></td>
          <td><%=RS1("Log Time")%></td>
          <td><%=RS1("Log User")%></td>
          <td><%=RS1("Log IPRem")%></td>
          <td><%=RS1("Log IPLoc")%></td>
          <td><%=RS1("Log Page")%></td>
          </tr>
          <%
          RS1.Movenext
          Loop
          %>
          </table>

          <%
          RS1.Close
          Conn.Close

          Set Conn = Nothing
          Set RS1 = Nothing
          %>
          [/code]
          =============== =============== =============== =============== =============
          Last edited by jhardman; Sep 8 '07, 08:01 PM. Reason: put code in code tags. Please use code tags in the future.

            Comment

            Previous template Next
            Working...