Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formatting columns in Excel using ASP 1

Status
Not open for further replies.
yimitz

yimitz

Programmer
Apr 5, 2002
54
US
I have written an asp page that outputs data from a database into an excel spreadsheet using:

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=mydata.xls;"


I want to format the cell widths so the width is as long as the data in the cell.

Can I do this by setting the width property of the <td> to the length of the data in the cell or is there a better way?
 
Sort by date Sort by votes
yimitz,

I believe if you don't put any restrictions on the <td> tag, it will adjust to what the maximum length of your data.

fengshui_1998
 
Upvote 0 Downvote
I've tried <td width='50'>, I've tried plain <td>. Neither have an affect on the column width.

The first cell contains a small text with 5 characters the cell below it is blank and the cell below that is text with 25 characters. I want the cell to column to expand to a width of 25 to show the longest cell width.
 
Upvote 0 Downvote
yimitz,

So if it's a plain <td> tag, how many characters is it displaying. If you want it all on one line..

<td nowrap>


fengshui_1998
 
Upvote 0 Downvote
When I don't have the nowrap in <td>, the column width is 10.0 and the data wraps in the cell making the height of the cell bigger, when I put the nowrap in <td nowrap> the cell doesn't wrap but it doesn't show all the data either, the column width stays at 10.0.

I am using office 97 not 2000, would that make a difference?
 
Upvote 0 Downvote
You asked for it, here you go, I apologize if it's hard to follow.

****database connection stuff would go here****

because the columns are actually data in a table that could change daily I have to build the columns from the Team field dynamically. I also needed a break at each Market value which is where the market field routine comes into play.

Response.ContentType = &quot;application/vnd.ms-excel&quot;
Response.AddHeader &quot;Content-Disposition&quot;, &quot;filename=mydata.xls;&quot;

Response.Write &quot;<html><head><title>Daily call summary</title>&quot;
Response.Write &quot;</head><body>&quot;

Response.Write &quot;<table><tr><td valign=top align=left nowrap width=&quot;&quot;30&quot;&quot;><br>Avererage Response (prior day)<br>Average Response (MTD)<br>&quot;
Response.Write &quot;Total CCO per Team<br>Parts used (prior day)<br>Parts Used (MTD)<br>&quot;
Response.Write &quot;Projected part useage<br>Parts target<br>Partstarget % projected<br>&quot;
Response.Write &quot;Total Calls (prior day)<br>Total Calls (MTD)<br>Repeat EMER calls (MTD)<br>&quot;
Response.write &quot;Incomplete for parts (prior day)<br>Incomplete for parts (MTD)<br>&quot;
Response.write &quot;Incomplete for other (prior day)<br>Incomplete for other (MTD)<br>&quot;
Response.write &quot;FCE % (MTD)<br><br>&quot;
Do until objRst.EOF

IF objRst(&quot;market&quot;)<> strMarket then
Response.Write &quot;</tr><tr>&quot;
strMarket=objRst(&quot;market&quot;).value
Response.Write &quot;<th align=center width=&quot;&quot;30&quot;&quot;>&quot;&strMarket&&quot;</th></tr>&quot;
Response.Write &quot;<tr><td valign=top align=left nowrap width=&quot;&quot;30&quot;&quot;><br>Avererage Response (prior day)<br>Average Response (MTD)<br>&quot;
Response.Write &quot;Total CCO per Team<br>Parts used (prior day)<br>Parts Used (MTD)<br>&quot;
Response.Write &quot;Projected part useage<br>Parts target<br>Partstarget % projected<br>&quot;
Response.Write &quot;Total Calls (prior day)<br>Total Calls (MTD)<br>Repeat EMER calls (MTD)<br>&quot;
Response.write &quot;Incomplete for parts (prior day)<br>Incomplete for parts (MTD)<br>&quot;
Response.write &quot;Incomplete for other (prior day)<br>Incomplete for other (MTD)<br>&quot;
Response.write &quot;FCE % (MTD)<br><br>&quot;
Else
Response.Write &quot;<td>&nbsp;</td><td valign=top align=right>&quot;
Response.Write &quot;<b><font color=red>&quot; & objRst.Fields(&quot;Team&quot;).value & &quot;</font></b><br>&quot;& FormatNumber(objRst.Fields(&quot;PDARTs&quot;).value,1)&&quot;<br>&quot;
Response.Write FormatNumber(objRst.Fields(&quot;MTDARTs&quot;).value,1) & &quot;<br>&quot;& objRst.Fields(&quot;CCOs&quot;).value &&quot;<br>&quot;
IF len(objRst.Fields(&quot;PDPartsUses&quot;).value) = 0 then
Response.write &quot;$0<br>&quot;
Else
Response.write &quot;$&quot; & FormatNumber((Round(objRst.Fields(&quot;PDPartsUses&quot;).value,0)),0) & &quot;<br>&quot;
End If
IF len(objRst.Fields(&quot;MTDPartsUses&quot;).value) = 0 then
Response.write &quot;$0<br>&quot;
Else
Response.write &quot;$&quot; & FormatNumber((Round(objRst.Fields(&quot;MTDPartsUses&quot;).value,0)),0) & &quot;<br>&quot;
End If
if objRst.Fields(&quot;DaysWorkeds&quot;).value = 0 then
Response.Write &quot;0<br>&quot;
Else
Response.Write FormatCurrency(Round((objRst.Fields(&quot;MTDPartsUses&quot;)/objRst.Fields(&quot;DaysWorkeds&quot;))*objRst.Fields(&quot;WorkableDayss&quot;)),0 )& &quot;<br>&quot;
End if
Response.Write FormatCurrency(Round(objRst.Fields(&quot;PartsBudgets&quot;)),0 )& &quot;<br>&quot;
if objRst.Fields(&quot;DaysWorkeds&quot;).value = 0 then
Response.Write &quot;0<br>&quot;
Else

Response.Write formatPercent(Round((objRst.Fields(&quot;MTDPartsUses&quot;)/objRst.Fields(&quot;DaysWorkeds&quot;))*objRst.Fields(&quot;WorkableDayss&quot;))/(objRst.Fields(&quot;PartsBudgets&quot;)),1)& &quot;<br>&quot;
End if
Response.Write objRst.Fields(&quot;PDGrossCallss&quot;).value & &quot;<br>&quot;& objRst.Fields(&quot;MTDGrossCallss&quot;).value &&quot;<br>&quot;
Response.Write objRst.Fields(&quot;MTDRecallss&quot;).value & &quot;<br>&quot;
Response.Write objRst.Fields(&quot;PDInc4Partss&quot;).value & &quot;<br>&quot;& objRst.Fields(&quot;MTDInc4Partss&quot;).value & &quot;<br>&quot;
Response.Write objRst.Fields(&quot;PDInc4Others&quot;).value & &quot;<br>&quot;& objRst.Fields(&quot;MTDInc4Others&quot;).value & &quot;<br>&quot;
Response.Write (formatNumber(Round((objRst.Fields(&quot;FCE&quot;).value),3),3)*100)& &quot;%<br><br>&quot;
Response.Write &quot;</td>&quot;
objRst.Movenext
End If
Loop

Response.Write &quot;</tr></table></body></html>&quot;


 
Upvote 0 Downvote
yimitz,

Remove the width=&quot;30&quot;. It may be displaying in pixels, not characters. This simple table file displays all the characters.

<html>
<body>

<table>
<tr><td>1234</td><td>123456789</td></tr>
<tr><td>1234567</td><td>the brown fox</td></tr>
<tr><td>1344555</td><td>more numbers</td></tr>
<tr><td>e4534</td><td>zxcvbnm</td></tr>
</table>
</body>
</html>
 
Upvote 0 Downvote
Thanks for all your help, I removed all the width parameters but still no luck. It must have something to do with the <br> I use to list the data out. I'm just going to tell the end users they can reset the width of the cell themselves.

Thanks again!
 
Upvote 0 Downvote
Have you tried saving a formatted Excel page as .htm? Then you can open it in notepad and copy the code, which includes the styles, into your table and get the exact formatting you want. I do this to get shading, borders, fonts, etc.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
735
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
759
leifoet
leifoet
leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
926
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
663
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
1K
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top