Solved

Setting Excel Column Width in HTML

Posted on 2009-04-08
11
9,228 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 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)...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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now