[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Setting Excel Column Width in HTML

Posted on 2009-04-08
11
Medium Priority
?
10,551 Views
Last Modified: 2012-05-06
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
Comment
Question by:APD_Toronto
[X]
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
11 Comments
 
LVL 23

Expert Comment

by:irudyk
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">
0
 

Author Comment

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

Expert Comment

by:irudyk
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.
0
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.

 

Author Comment

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

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

Expert Comment

by:irudyk
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).
0
 
LVL 23

Expert Comment

by:irudyk
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:
    <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
 

Author Comment

by:APD_Toronto
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?
0
 
LVL 23

Expert Comment

by:irudyk
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

0
 

Author Comment

by:APD_Toronto
ID: 24108335
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
 
LVL 23

Accepted Solution

by:
irudyk earned 1500 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)...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
 

Expert Comment

by:TSE-online
ID: 37150675
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

656 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