Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Need help formatting text fields in Excel ouput

I have a page that is dumping my rs output into an Excel file. The page is laying out nicely but the one thing I'm having trouble with is formatting the fields to be text fields. And the reason I am doing this is because some of the data are numbers with leading zeros, like 012 or 01Y. For testing, I have replaced any instance of '0' with a capital O. But this is not going to work when I roll out this app. Can anyone help specify a field fomat from an ASP page?

relevant code:

<%Response.ContentType = "application/vnd.ms-excel"%>
<!--#include file="../Connections/jonDSN.asp" -->
<% Server.ScriptTimeout = 1000 %>

<html>
<head>
<title>List to Excel</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#ffffff" class="default">

<%
dim fs,tfile
set fs=Server.CreateObject("Scripting.FileSystemObject")
set tfile=fs.CreateTextFile("c:\inetpub\wwwroot\jobs\list.xls")

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_jonDSN_STRING
rs.Source = "ugly select statement"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

while not rs.eof

tfile.WriteLine "<table border=0 cellpadding=2 cellspacing=0>"
tfile.WriteLine "<tr>"
tfile.WriteLine "<td class=default align=left valign=top>"
tfile.WriteLine "<table border=0 cellpadding=2 cellspacing=0>"
tfile.WriteLine "<tr>"
tfile.WriteLine "<td class=default align=left valign=top>"
tfile.WriteLine "<b>" & rs("masterproductcode") & "</b>"
tfile.WriteLine "</td>"


--------------------
this line [tfile.WriteLine "<b>" & rs("masterproductcode") & "</b>"]
 is the one that I need formatted to always be a text field

Thanks in advance!
Shadie
0
shadie
Asked:
shadie
  • 9
  • 9
  • 3
1 Solution
 
peterxlaneCommented:
I had this same problem a week ago.  The code below should fix it. If you come across other formatting issues, then open up Excel, create a cell formatted the way you want it and then save the excel as XML.  Then open it with Notepad and look at the style that it created for that cell.


<html>
<head>
<title>List to Excel</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style>
.twodecplaces
{
mso-number-format:Fixed;
white-space:normal;
}
</style>
</head>

<body bgcolor="#ffffff" class="default">
...
tfile.WriteLine "<td class=twodecplaces align=left valign=top>"
tfile.WriteLine "<b>" & rs("masterproductcode") & "</b>"
tfile.WriteLine "</td>"
0
 
kevp75Commented:
you could do something like this as well:

instead of:tfile.WriteLine "<b>" & rs("masterproductcode") & "</b>"
make it:tfile.WriteLine "<b>'" & rs("masterproductcode") & "</b>"

the extra single quote makes excel recognize it as a text field

HTH
0
 
shadieAuthor Commented:
peterxlane, kevp75,
Thanks for weighing in, unfortunately, neither of these work. That is to say, kevp, yours works, just not the result I would like. I've tried similar solutions, even replaced the 0's with O's but I would still rather have the real data.

I don't know if I mentioned this, don't think so, but my desired result is to have the asp page create an Excel file on the server. I can then have a scheduler take that file and email it. I thought maybe both of your solutions were for outputting the Excel file to the asp page so I changed the code so it would render in the browser. This returned the same results. It's as if no cell formatting is being passed.
0
Technology Partners: 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!

 
shadieAuthor Commented:
the other thing I failed to mention is I created a sample XLS file and saved it out as html, looked at the code and copied the style verbatim to my page. That too failed.
0
 
peterxlaneCommented:
That is actually EXACTLY what I am doing with mine.  My script is actually saved with a .VBS extension and is scheduled with Task Scheduler to run each night and email out the result.  I was having the exact same issue until I added the stylesheet and it is all working fine now.  I will put together a barebones example so that you can confirm that it will create the desired result.

0
 
peterxlaneCommented:
Give this a try and see what happens and see what it produces for you.  I put in kevp75's suggestion as well, but for me it comes up with the single quotes in the finished spreadsheet (but the number is formatted properly).




Sub Main
      On Error Resume Next
      strFile = "complete path to your file here"
      strValue = "3.50"            
      Set oFSO = CreateObject("Scripting.FileSystemObject")
      Set oExcelFile = oFSO.CreateTextFile(strFile, True)      

      oExcelFile.Write "<html xmlns:o=""urn:schemas-microsoft-com:office:office""" & vbcrlf
      oExcelFile.Write "xmlns:x=""urn:schemas-microsoft-com:office:excel""" & vbcrlf
      oExcelFile.Write "xmlns=""http://www.w3.org/TR/REC-html40"">" & vbcrlf
      oExcelFile.Write "<head>" & vbcrlf
      oExcelFile.Write "<style>" & vbcrlf      
      oExcelFile.Write ".twodecplaces" & vbcrlf
      oExcelFile.Write "{" & vbcrlf
      oExcelFile.Write "mso-number-format:Fixed;" & vbcrlf
      oExcelFile.Write "white-space:normal;" & vbcrlf      
      oExcelFile.Write "}" & vbcrlf
      oExcelFile.Write "</style>" & vbcrlf
      oExcelFile.Write "</head>" & vbcrlf
      oExcelFile.Write "<body>" & vbcrlf      
      oExcelFile.Write "<table border=""1"">" & vbcrlf
      oExcelFile.Write "      <tr>" & vbcrlf
      oExcelFile.Write "            <td><b><u>Unformatted</u></b></td>" & vbcrlf
      oExcelFile.Write "            <td><b><u>Formatted</u></b></td>" & vbcrlf
      oExcelFile.Write "            <td><b><u>kevp75's method</u></b></td>" & vbcrlf
      oExcelFile.Write "      </tr>" & vbcrlf
      oExcelFile.Write "      <tr>" & vbcrlf
      oExcelFile.Write "            <td>" & strValue & "</td>" & vbcrlf
      oExcelFile.Write "            <td class=""twodecplaces"">" & strValue & "</td>" & vbcrlf
      oExcelFile.Write "            <td>'" & strValue & "'</td>" & vbcrlf
      oExcelFile.Write "      </tr>" & vbcrlf
      oExcelFile.Write "</table>" & vbcrlf
      oExcelFile.Write "</body>" & vbcrlf
      oExcelFile.Write "</html>" & vbcrlf      


      oExcelFile.Close
      Set oFSO = Nothing
      MsgBox "DONE"
      If Err.Number <> 0 Then
            MsgBox "ERROR: " & Err.Description
      End If
End Sub

Call Main
0
 
shadieAuthor Commented:
kevp75,
I used your code exactly as you sent, with the addition of the path to my file and changed out the variable value to 090 and here are the results:

Unformatted     Formatted     kevp75's method
90                   90                 '090'

Is there something messed up with my server/system/etc.? That the style sheet is not working? Obviously, the kevp75 method works but it also includes the ' before and after my output. This is really not desireable. Certainly, I can instruct the recipient of my report to ignore but I'd rather not... the final file will be quite large and this may be a huge distraction. I guess what I'm now struggling with is why wouldn't the "formatted" version work and my disbelief that the only way to trick Excel into formatting something as a string is to append 'in front of the data' where you need it. I mean, ASP is a Microsoft language. Why shouldn't the two play nicely?!
0
 
peterxlaneCommented:
my code was written to handle decimal places, so I will have to look at it to see what would need to be done to handle this format.  Let me see what I can come up with.

0
 
peterxlaneCommented:
Give this a try:

Sub Main
      On Error Resume Next
      strFile = "complete path to your file here"
      strValue = "090"            
      Set oFSO = CreateObject("Scripting.FileSystemObject")
      Set oExcelFile = oFSO.CreateTextFile(strFile, True)      

      oExcelFile.Write "<html xmlns:o=""urn:schemas-microsoft-com:office:office""" & vbcrlf
      oExcelFile.Write "xmlns:x=""urn:schemas-microsoft-com:office:excel""" & vbcrlf
      oExcelFile.Write "xmlns=""http://www.w3.org/TR/REC-html40"">" & vbcrlf
      oExcelFile.Write "<head>" & vbcrlf
      oExcelFile.Write "<style>" & vbcrlf      
      oExcelFile.Write ".twodecplaces" & vbcrlf
      oExcelFile.Write "{" & vbcrlf
      oExcelFile.Write "mso-number-format:""\@"";" & vbcrlf
      oExcelFile.Write "white-space:normal;" & vbcrlf      
      oExcelFile.Write "}" & vbcrlf
      oExcelFile.Write "</style>" & vbcrlf
      oExcelFile.Write "</head>" & vbcrlf
      oExcelFile.Write "<body>" & vbcrlf      
      oExcelFile.Write "<table border=""1"">" & vbcrlf
      oExcelFile.Write "      <tr>" & vbcrlf
      oExcelFile.Write "            <td><b><u>Unformatted</u></b></td>" & vbcrlf
      oExcelFile.Write "            <td><b><u>Formatted</u></b></td>" & vbcrlf
      oExcelFile.Write "            <td><b><u>kevp75's method</u></b></td>" & vbcrlf
      oExcelFile.Write "      </tr>" & vbcrlf
      oExcelFile.Write "      <tr>" & vbcrlf
      oExcelFile.Write "            <td>" & strValue & "</td>" & vbcrlf
      oExcelFile.Write "            <td class=""twodecplaces"">" & strValue & "</td>" & vbcrlf
      oExcelFile.Write "            <td>'" & strValue & "'</td>" & vbcrlf
      oExcelFile.Write "      </tr>" & vbcrlf
      oExcelFile.Write "</table>" & vbcrlf
      oExcelFile.Write "</body>" & vbcrlf
      oExcelFile.Write "</html>" & vbcrlf      


      oExcelFile.Close
      Set oFSO = Nothing
      MsgBox "DONE"
      If Err.Number <> 0 Then
            MsgBox "ERROR: " & Err.Description
      End If
End Sub

Call Main
0
 
shadieAuthor Commented:
that returns the same results as before (when I changed to add my 090). I still get 90, 90, '090'. I tried to remove the single quote here:
<td>'" & strValue & "'</td>" & vbcrlf

and then got 90, which is wrong.
0
 
shadieAuthor Commented:
Sorry, BTW, I see now that I've been addressing some of my replies to the wrong person :)
0
 
peterxlaneCommented:
What version of Office are you running?  

and no problem on the name thing... I hadn't even noticed.
0
 
shadieAuthor Commented:
ooof,  hopefully this isn't the cause!

I'm running 97 SR-2
0
 
peterxlaneCommented:
Did you know that it was 2006?  :-)

try the following:
Create a new spreadsheet.
Right Click on cell A1 and choose "format cells"
Choose "Text" as the category and click "OK"
Type 090 in cell A1 and make sure that it is displayed with the 0 in front
Save it as html. (hopefully you can still do that with Ofice 97)
Open the the html file with notepad and post your results here.



Additionally, do you have the option of saving the Excel file as XML?  And do you know what version of Excel the people receving the email will have?

0
 
shadieAuthor Commented:
yeah, I heard something about it being 2006. I don't get out much. But, isn't the latest version 2002?? Maybe Microsoft hadn't heard yet... Sorry, I'm a Mac guy from way back.

I did find some help regarding the "save as html" thing. With the version of Office that I'm running, I'm at the mercy of my company and how frequently we upgrade things. I don't have the add-in installed to enable me to save as html. I need to get hold of my Sys-Admin to see if he can install the add-in. As soon as I can get that done, I'll get back to you with the html output. Additionally, I don't know what version my report recipients are running. My guess is it's probably 97, but they work at our warehouse so it could be 3.x (joking but you never know). I'll find out.

Thanks for your patience and persistence on this one!
0
 
kevp75Commented:
:)
i don't think XML was even out when that version of office was mainstream

usually when I need to do this it works with the single quote.  But, it's never displayed it (office 2000)

try peter's second submission of code, with one exception

on this line:oExcelFile.Write "          <td>'" & strValue & "'</td>" & vbcrlf
 there is a single quote in front of and in back of strValue.  do it with the one in front and get rid of the one after it, like this:
oExcelFile.Write "          <td>'" & strValue & "</td>" & vbcrlf


0
 
kevp75Commented:
i think part of the problem maybe that we are trying to output an Excel spreadsheet using HTML to build the table.  What about doing it without the html, and try to write out the file as a delimited file.
0
 
shadieAuthor Commented:
peterxlane,
Thanks for sticking with this one! I jumped on a co-workers computer, also running Office 97 but with the appropriate add-ins. I created the spreadsheet, saved as html and got this from it, which I plugged into your last code submission.

I changed your line:
oExcelFile.Write "<td class=""twodecplaces"">" & strValue & "</td>" & vbcrlf

to:

oExcelFile.Write "<td STYLE=""vnd.ms-excel.numberformat:@"">" & strValue & "</td>" & vbcrlf

and it worked! Thanks again! You don't know how long I've been playing around with this. There are half-dozen other projects that I can now apply this to but haven't wanted to get my hands dirty on them until I could get this to work.
0
 
peterxlaneCommented:
Actually, I was kind of thinking the same thing when it was mentioned that the version of Excel was a huge unknown and had the potential of being really old.  I am curious to see what would happen if a CSV file was created.  I am guessing that there would still be a formatting issue.
0
 
peterxlaneCommented:
Posted my last response before I saw your last one.

I will have to try that and see if it still works for newer versions of Excel as well.  Hopefully it does, in case the warehouses are running newer versions...  I'll let you know.  I am running Office 2003 by the way.
0
 
shadieAuthor Commented:
I remember going down the CSV route in a previous attempt and not getting it to work either.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now