Setting Excel Column Width in HTML

Posted on 2009-04-08
Last Modified: 2012-05-06

I have the code below, which creates a Excel file using HTML <taeble> tags.

everything works okay, except for <td width=20> - this is totally being ignored, or it gives me a totally different width when I open it in Excel.

Any ideas?
<%@ Language=VBScript %>
      'Change HTML header to specify Excel's MIME content type
      Response.Buffer = TRUE
      Response.ContentType = "application/"
      FPath = "c:\" & fn 
      Response.AddHeader "Content-Disposition", "attachment; filename=123.xls" 
   <html xmlns:o="urn:schemas-microsoft-com:office:office"
     {mso-header-data:"&XX Day Absence Report\000ADate\: &D\000APage &P";
	  mso-page-orientation:landscape; }
	<!--[if gte mso 9]><xml>
   <!-- Our table which will be translated into an Excel spreadsheet -->
			style="border-right-style: solid; border-right-width: 2;
        		   border-left-style: solid; border-left-width: 2;
        		   border-top-style: solid; border-left-width: 2;
        		   border-bottom-style: solid; border-left-width: 2">
  <tr><td WIDTH="20"> <b> # </b> </td> </tr>
 <% For i = 1 To 500 %>
	<tr><td> <%=i%> </td> </tr>
<% Next %>

Open in new window

Question by:APD_Toronto
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
  • 6
  • 4
LVL 23

Expert Comment

ID: 24098634
I think the width you are specifying is in pixels. So try using the following formula to convert to the Excel width you desire:
5 + 7 x desired width
So for a width of 20, you would need to set the pixel width value to 145 (5 + 7 x 20 = 145)
So try using:
<td WIDTH="145">

Author Comment

ID: 24101873
Close, but <td WIDTH="145">  in Excel gives me 15.33
LVL 23

Expert Comment

ID: 24102192
Hmm, well in my case the default font I have set for Excel is Arial 10 point.  If your font is different, then that would be the cause.  If so, I'm not sure how one can definitively set the width to a value that is not dependent on what the user's default Excel font setting is.
Independent Software Vendors: 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!


Author Comment

ID: 24102770
How did you come up with the above formula?

My default font in Excel is also Arial, 10
LVL 23

Expert Comment

ID: 24106843
I came up with the formula by going into Excel and setting the column width to 1. Excel balloon indicates Width: 1 (12 pixels). Then I changed the width to 2. Excel balloon indicates Width: 2 (19 pixels).  So, changing from 1 to 2 increased the pixel count by 7, so 7 pixels = an incremental width of 1.  But, with the width set at 1 the pixel count was 12.  So, I assumed a base value of 5 thereby making the formula 5 + 7 x width.
Of course, as it would appear, this does not seem to work for all users, all of the time (i.e. works for me, but not for you).
LVL 23

Expert Comment

ID: 24107634
Another suggestion is to alter the <style> section in your code to be as follows which might result in the width="145" taking effect:
     {mso-header-data:"&XX Day Absence Report\000ADate\: &D\000APage &P";
	  mso-page-orientation:landscape; }

Open in new window


Author Comment

ID: 24107931
I replaced my <style> code with yours, and I still get 15.33.

I noticed that if you got Save As .htm in Excel, it generates the correct code in HTML, including column widths, so when you import the HTML into Excel, everything is correct.

The problem is with this is I cannot track down where Excel sets the width in the html code.  Can you?
LVL 23

Expert Comment

ID: 24108087
Hmm, well that is strange.  When I make the <style> changes, the regardless of what my default font setting in Excel are, I get a sheet that appears with all cells that are Arial 10pt with column A at a width of 20 (145 pixels).
Okay, well maybe try creating the table column and cells as follows:

  <col width=145 style='mso-width-source:userset;mso-width-alt:5302;width:109pt'>
  <tr><td width=145 style='width:109pt'><b> # </b> </td> </tr>
 <% For i = 1 To 500 %>
	<tr><td width=145 style='width:109pt'> <%=i%> &nbsp;</td> </tr>
<% Next %>

Open in new window


Author Comment

ID: 24108335
But, can you explain how did you get 5302 and 109 respectively, so I can apply it to other widths?
LVL 23

Accepted Solution

irudyk earned 500 total points
ID: 24108500
19.89 is close...I was getting something similar until I modified the <style> section to set the sheet's font and font size to Arial 10.  Makes me think that if you were to change your default Excel font settings, to say Arial 16, that the 19.89 would likely be less (but maybe not) should probably test that out to see what you come up with.
To explain how I got the values...I saved the file and then opened it in an HTML editor, search for the cells and noted what the values were set as.  I'm not sure if there is some kind of magical formula that will give you these values.  I guess you could try altering the width to see what you come up with/if there is some kind a correlation.
I still find it odd that you get something that is different from what I get, but I suppose that is how it goes sometime :)
Also, since I can't replicate the issue you are having, I'm really at a disadvantage as to offering any further suggestions to you, but I hope I got you further along the process...Good luck!

Expert Comment

ID: 37150675

I found some rules to apply to get what you expect.
1) Define only ONE table in your html.
2) Only <col /> definition will work. So you can avoid some width definition in <td> attributes.
3) If you have 3 colums with the same width only define one with a span=3 inside like this.
4) You MUST define at least both a width in pixels and one in point like this

<col width=25 span=3 style='width:19pt' /> AND IT WILL BE ENOUGH TO WORK.

25 represents the width in pixels.

Here is how to convert pixels in points : Points=Pixels * 72 / 96 . Results must be rounded width ceil when >.5 and with floor when <.5



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculating percentage 2 29
SP result not being displayed 5 39
Excel Named Range 31 44
Count number of rows containing same number is Column A 5 22
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remaiā€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 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