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..

Aug 23 '07 #1

Subscribe Post Reply

2556

jhardman

3,406

Expert 2GB

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 "<br>" 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

Aug 23 '07 #2

Merlin1857

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 :

Expand|Select|Wrap|Line Numbers

 
<%@ Language=VBScript %>

<!-- #include file="../incfiles/adovbs.inc" -->

<% 

dim Conn,RS1
 
'==================CONNECTION CODE=======================

Set Conn = Server.CreateObject("ADODB.Connection")

Set RS1 = Server.CreateObject("ADODB.RecordSet")
 
strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_

"User Id=logger;Password=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;<font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>

    </tr>

</table>
 
<%Else%>

<form method="POST" action="02extoex.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="#000000">

    <tr>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log ID</font></td>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Date</font></td>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log Time</font></td>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log User</font></td>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Rem</font></td>

        <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Log IP Loc</font></td>

        <td bgcolor="#000080"><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

%>

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

PAGE TWO CALLED 02extoex.asp :

Expand|Select|Wrap|Line Numbers

 
<%@ Language=VBScript %>

<!-- #include file="../incfiles/adovbs.inc" -->

<% 

dim Conn,RS1
 
sql1 = Request("InpSQL")
 
'==================CONNECTION CODE=======================

Set Conn = Server.CreateObject("ADODB.Connection")

Set RS1 = Server.CreateObject("ADODB.RecordSet")
 
strConn = "Provider=SQLOLEDB;Data Source=LAPTOP01;Initial Catalog=polpact;"&_

"User Id=logger;Password=logger;"

Conn.Open strConn 

'==================CONNECTION CODE=======================
 
'sql1 = "Select * from tblAccessLog WHERE LogID < 10000" 
 
RS1.Open sql1, Conn
 
Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader "Content-Disposition", "attachment; filename=export.xls" 

%>
 
<table border="1" width="100%">

    <tr>

        <td bgcolor="#000080"><font color="#FFFFFF">Log ID</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log Date</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log Time</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log User</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log IP Rem</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log IP Loc</font></td>

        <td bgcolor="#000080"><font color="#FFFFFF">Log Page</font></td>

    </tr>

    <%Do While Not RS1.eof%>

    <tr>

        <td><%=RS1("LogID")%></td>

        <td><%=RS1("LogDate")%></td>

        <td><%=RS1("LogTime")%></td>

        <td><%=RS1("LogUser")%></td>

        <td><%=RS1("LogIPRem")%></td>

        <td><%=RS1("LogIPLoc")%></td>

        <td><%=RS1("LogPage")%></td>

    </tr>

    <%

    RS1.Movenext

    Loop

    %>

</table>
 
<%

RS1.Close

Conn.Close
 
Set Conn = Nothing

Set RS1 = Nothing

%>

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

Sep 8 '07 #3

by: sridevi | last post by:

Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...

.NET Framework

Exporting Years to Excel Problem

by: D | last post by:

I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...

Microsoft Access / VBA

Missing data when exporting to excel

by: G | last post by:

When I export data from access to excel by with "export" or "Analyze with" I seem to loose parts of some fields (long text strings). Is there a way to export it all to excel? Thanks G

Microsoft Access / VBA

Limitations in exporting to Excel?

by: Kenneth | last post by:

How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth

Microsoft Access / VBA

Problem exporting datagrid with numeric values to Excel

by: NancyA | last post by:

I am using the following code to write data from a datagrid to an Excel file: Dim tw As New System.IO.StringWriter Dim hw As New System.Web.UI.HtmlTextWriter(tw) dg.RenderControl(hw)...

ASP.NET

exporting crystal report to Excel

by: Mustufa Baig | last post by:

Hi everybody, I have an ASP.NET website where clients can view their monthly billings by selecting different options. One of the option is the way they want to see the report i.e. whether they...

ASP.NET

CrystalReport.NET exporting to Excel Problem

by: Mustufa Baig | last post by:

I have an ASP.NET website where I am showing off crystal reports to users by exporting them to pdf format. Following is the code: ---------------- 1 Private Sub ExportReport() 2 Dim oStream...

ASP.NET

Exporting ALL data into Excel file with Page Navigation in ASP.NET

by: bienwell | last post by:

Hi, I have a question about exporting data from datagrid control into Excel file in ASP.NET. On my Web page, I have a linkbutton "Export data". This link will call a Sub Function to perform...

ASP.NET

Standard Method for Importing/Exporting to DB

by: Snozz | last post by:

The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...

Visual Basic .NET

gridview not exporting to excel

by: Tom | last post by:

I have a gridview on all of my web pages in my web app and they all export to excel. I have one page where the gridview is binding to a datatable that i created and only the first column is...

ASP.NET

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

Similar topics