Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Line feed within cell/ formatting within cell in Excel

Posted on 2004-04-21
12
Medium Priority
?
694 Views
Last Modified: 2007-12-19
Experts,

I have a search engine and it gives results in HTML.  The results has to be dumped in EXcel sheet according to a certain format.

I have a small trial program that can be executed.   This code prints data on seperate cells.  How can I give line feed inside one single cell ?  Please help me with the code and modifiy it so that I can get Line2 below line1 in a single cell.  If this is not the right place to ask this question, please direct me to the right location.

Here is the code:

<%
Response.Expires = -1000

Const cDocFolderRoot = "virtual"
Const cDocFolderPath = "\data\doctemp\"

Dim oXLS, mXLSFile, mPathInternal, mPathVirtual

mXLSFile = getFileName(Session("UserID"))

     Const cDocFolder = "\data\doctemp"
     Const cXLSext = ".xls"

     Dim thePath, theFile

mPathInternal = Server.MapPath("/" & cDocFolderRoot) & cDocFolderPath
mPathVirtual = cDocFolderRoot & Replace(cDocFolderPath, "\", "/")


' Use Office Web Components
Set oXLS = Server.CreateObject("OWC.Spreadsheet")

'!!! problem: Chr(10) or Chr(13) causing data to be written to the next cell down
oXLS.Cells(1, 1).Value = "Line1" & Chr(10) & "Line2"


'!!! problem: file not written if already exists
oXLS.ActiveSheet.Export mPathInternal & mXLSFile, 0

'!!! doesn't work - client not able to access file from internal map
' Response.Redirect mPathInternal & mXLSFile

Set oXLS = Nothing


Function getFileName(ByVal pDowID)
'
' return an Excel file name
'
     getFileName = "ProdMix_" & getDateStamp(Date) & "_" & pDowID & cXLSext

End Function

Function getDateStamp(ByVal pDate)
'
' return date string in the form yyyy-mmm-dd
'
     Dim yy, mm, dd

     yy = Year(pDate)
     mm = MonthName(Month(pDate), True)
     dd = Day(pDate)
     If dd < 10 Then dd = "0" & dd


     getDateStamp = yy & "-" & mm & "-" & dd

End Function
%>

<html>
<body>

<h2>Excel Test</h2>

<p><a href="<%=mPathVirtual & mXLSFile %>">click here for spreadsheet</a></p>

<p>
<br>
<b>Debug:</b>
<br>
<br>
mPathInternal = <%=mPathInternal %><br>
mPathVirtual = <%=mPathVirtual %><br>
mXLSFile = <%=mXLSFile %><br>
</p>

<p>Refreshed: <%=Date() & " " & Time() %></p>

</body>
</html>
0
Comment
Question by:suran78
[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
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:AshleyBryant
ID: 10881030
You could build your HTML display to look exactly like the format is supposed to be in the excel file and just use Response.ContentType to tell the browser it is receiving an excel file instead of HTML.

<%
If Request.QueryString("display") = "excel" then
      Response.Expires = 0
      Response.Buffer = True
      Response.Clear
      Response.ContentType = "application/vnd.ms-excel"
End If
%>
<html>
<head>
      <title>Your page title</title>
</head>
<body>

<% If Not Request.QueryString("display") = "excel" then %>
<a href="pagename.asp?display=excel">Export to Excel</a>
<% End If %>

'build your table of search results here

</body>
</html>
0
 
LVL 12

Expert Comment

by:AshleyBryant
ID: 10881053
I should've been more clear there regarding the line feed issue.  I personally don't know of a way to tell excel to use a line feed via character code or using a VB Carriage Return (vbCr).  I do know that if you make an HTML table and have line breaks within your td tags, they will be treated as carriage returns in the exported excel file.
0
 

Author Comment

by:suran78
ID: 10881066
I have done this already.  The problem is I have to format lines in single cell.

So, it should look like this in single cell:
------------------
Line1 <br>
Line2
------------------

With Html fomating I am getting
first cell:
line1
--------------------
next cell
line2

suran
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:AshleyBryant
ID: 10881219
Alas, you are correct.  My memory failed me.  If you put a border on your table, you can see that the cell borders will be in the right place, but the items still show up in different cells.
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10881352
In excel VBA we can enter line feed as
Range("A1").Select
ActiveCell.FormulaR1C1 = "this is a test" & Chr(10) & "new line"

You need to activate the cell before inserting the line feed char (10).
In the same way ... you can try...
oXLS.Range("A1").Select
ActiveCell.FormulaR1C1 = "Line1" & Chr(10) & "Line2"

This is just a theory...good luck!


0
 

Author Comment

by:suran78
ID: 10882014
Shailesh,

Thanks fo rreplying. I tried your code, the FormulaR1C1 is giving error.
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10882252
did you try value property?..
ActiveCell.Value = "this is a test" & Chr(10) & "new line"

I was expecting error while range selection!
0
 

Author Comment

by:suran78
ID: 10882434
I am not getting any error by changing formulaR1C1 to value.  But teh spreadsheet is not opening and I am not getting any result.

suran
0
 

Author Comment

by:suran78
ID: 10882490
I tried
ActiveCell.Value = "this is a test" & Chr(10) & "new line"

The "new line" is still coming on next cell but not inside the single cell with "this is a test"

0
 

Author Comment

by:suran78
ID: 10882928
I have got the answer, please check this out, if you are eager to know teh solution:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20959144.html

Thanks for replying and trying.
suran
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10962152
Closed, 250 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

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