• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11375
  • Last Modified:

Setting Excel Column Width in HTML

Hello,

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/vnd.ms-excel"
      FPath = "c:\" & fn 
      Response.AddHeader "Content-Disposition", "attachment; filename=123.xls" 
 
 
   %>
   <html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
   <head>
    <style>
 
  <!--table
 
  @page
     {mso-header-data:"&XX Day Absence Report\000ADate\: &D\000APage &P";
	  mso-number-format:"\#\,\#\#0\.00"; 
	  mso-page-orientation:landscape; }
 
  -->
	
	</style>
	
	<!--[if gte mso 9]><xml>
		 <x:ExcelWorkbook>
		  <x:ExcelWorksheets>
		   <x:ExcelWorksheet>
		    <x:Name>Book1</x:Name>
		    <x:WorksheetOptions>
		     <x:Print>
		      <x:ValidPrinterInfo/>
		      <x:HorizontalResolution>1200</x:HorizontalResolution>
		      <x:VerticalResolution>1200</x:VerticalResolution>
		     </x:Print>
		     <x:Selected/>
		     <x:DoNotDisplayGridlines/>
		     <x:ProtectContents>False</x:ProtectContents>
		     <x:ProtectObjects>False</x:ProtectObjects>
		     <x:ProtectScenarios>False</x:ProtectScenarios>
		    </x:WorksheetOptions>
		   </x:ExcelWorksheet>
		  </x:ExcelWorksheets>
		  <x:WindowHeight>15840</x:WindowHeight>
		  <x:WindowWidth>23835</x:WindowWidth>
		  <x:WindowTopX>0</x:WindowTopX>
		  <x:WindowTopY>15</x:WindowTopY>
		  <x:ProtectStructure>False</x:ProtectStructure>
		  <x:ProtectWindows>False</x:ProtectWindows>
		 </x:ExcelWorkbook>
		 <x:ExcelName>
		  <x:Name>Print_Titles</x:Name>
		  <x:SheetIndex>1</x:SheetIndex>
		  <x:Formula>=Book1!$A:$A,Book1!$1:$1</x:Formula>
		 </x:ExcelName>
		</xml><![endif]-->
 
	
   </head>
   <BODY>
   <!-- Our table which will be translated into an Excel spreadsheet -->
   <TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1 border=1
			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 %>
   </TABLE>
   </BODY>
   </HTML>

Open in new window

0
APD Toronto
Asked:
APD Toronto
  • 6
  • 4
1 Solution
 
irudykCommented:
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">
0
 
APD TorontoAuthor Commented:
Close, but <td WIDTH="145">  in Excel gives me 15.33
0
 
irudykCommented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
APD TorontoAuthor Commented:
How did you come up with the above formula?

My default font in Excel is also Arial, 10
0
 
irudykCommented:
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).
0
 
irudykCommented:
Another suggestion is to alter the <style> section in your code to be as follows which might result in the width="145" taking effect:
    <style>
 
  <!--table
 
  @page
     {mso-header-data:"&XX Day Absence Report\000ADate\: &D\000APage &P";
	  mso-number-format:"\#\,\#\#0\.00"; 
	  mso-page-orientation:landscape; }
 
  .style0
     {font-size:10.0pt;
	  font-style:normal;
	  font-family:Arial;}
 
  td
     {mso-style-parent:style0;
	  font-size:10.0pt;
	  font-style:normal;
	  font-family:Arial;}
 
  -->
 
	</style>

Open in new window

0
 
APD TorontoAuthor Commented:
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?
0
 
irudykCommented:
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

0
 
APD TorontoAuthor Commented:
19.89 GOOD ENOUGH
But, can you explain how did you get 5302 and 109 respectively, so I can apply it to other widths?
0
 
irudykCommented:
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)...you 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!
0
 
TSE-onlineCommented:
Hello,

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

Regards.

Jean-Christophe
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now