• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10779
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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