How to autofix cell width with ExcelXP tagset?

FrankE — Thu, 30 Sep 2010 15:28:26 GMT

Hi guys, I'm stumped. I'm creating an excel workbook via the ExcelXP tagset and I can't seem to figure out how to autofit the width of the cells. I can manually adjust the size of each field but there are hundreds of fields so this is no good.

In VBA it would be: Cells.EntireColumn.AutoFit

Re: How to autofix cell width with ExcelXP tagset?

Cynthia_sas — Thu, 30 Sep 2010 16:00:35 GMT

Hi:
If you use TAGSETS.EXCELXP's suboption for documentation:
[pre]
ods tagsets.excelxp file=..... options(doc='Help');
or
ods tagsets.excelxp file=..... options(doc='All');
[/pre]

you will see (in the SAS log) the options that are available for adjusting column width:
[pre]
Width_Fudge: Default Value '0.75'
Values: None, Number.
By default this value is used along with Width_Points and column width
to calculate an approximate width for the table columns.
width = Data_Font_Points * number_Of_Chars * Width_Fudge.
If 'none' this feature is turned off.

Width_Points: Default Value 'None'
Values: None, Number.
By Default the point size from the data or header style
elements are used to calculate a pseudo column width.
The column width is calculated from the given column width or
the length of the column's header text. If the header is bigger.
In the case the header length is used, so is the header's point size.
This value overrides that point size.
This value is used along with WidthFudge and column width
to calculate an approximate width for the table columns.
width = Width_Points * number_Of_Chars * Width_Fudge.

Default_Column_Width: Default Value 'None'
Values: None, Number, list of numbers.
Most procedures provide column widths, but occasionally a column
will not have a width. Excel will resize the column to fit any
numbers but will not auto-size for character string headings.
In the case that a column does not have a width, this value will be
used instead. The value should be the width in characters.
If the value of this option is a comma separated list.
Each number will be used for the column in the same position. If
the table has more columns, the list will start over again.

Absolute_Column_Width: Default Value 'None'
Values: None, Number, list of numbers.
This option works similarly to the default column width option
The difference is that these widths will be used regardless
of any column widths the procedure might provide.
The value should be the width in characters.
If the value of this option is a comma separated list.
Each number will be used for the column in the same position. If
the table has more columns, the list will start over again.
[/pre]

There is no suboption for doing an autofit on width. The Spreadsheet Markup Language XML is not the same as VBA -- so if there is a way to autofit width, it would have to be an XML method, not a VBA method.

When I use Notepad to look in the XML file created by SAS (for a simple PROC PRINT of SASHELP.CLASS using TAGSETS.EXCELXP), I see this ss:AutoFitWidth attribute:
[pre]
<Table ss:StyleID="_body">
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
[/pre]

So that means that ODS TAGSETS.EXCELXP is sending the AutoFitWidth attribute with a value of 1 and an explicit ss:Width value.

From the Microsoft site:
http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx
it says:
[quote]
Attribute: ss:AutoFitWidth

Description: If this attribute is specified as True ("1"), it means that this column should be autosized for numeric and date values only. We do not autofit textual values.

If both ss:Width and ss:AutoFitWidth exist, the behavior is as follows:

ss:AutoFitWidth="1" and ss:Width is unspecified: Autofit the column width to fit the content.
ss:AutoFitWidth="1" and ss:Width is specified: Set the column to the specified width and only autofit if the size of the content is larger than the specified width.
ss:AutoFitWidth="0" and ss:Width is unspecified: Use the default column width.
ss:AutoFitWidth="0" and ss:Width is specified: Use the specified width.
[/endquote]

I think that this sentence bears repeating (from the Microsoft quote above):
We do not autofit textual values.

Since that statement is coming from Microsoft, that means TAGSETS.EXCELXP must live by these rules. But, since there are suboptions to explicitly specify a width, you can use those suboptions -- either Default_Column_Width or Absolute_Column_Width should work for you. You will find an example of setting column widths in this user group paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

Re: How to autofix cell width with ExcelXP tagset?

vijayanand — Mon, 21 Oct 2013 10:10:34 GMT

Hi, It would really be helpful if the concpets of width points and width fudge are little more eloberative.

Is it in terms of the number of characters that the column width is arrived at after the multiplication as stated above?

What is the physican significance of the option width_points and width_fudge?

Re: How to autofix cell width with ExcelXP tagset?

Cynthia_sas — Tue, 22 Oct 2013 02:44:08 GMT

Hi:

It would be better if you didn't add a new post to the end of a post from 3 years ago. If you have a new question, it is better to make a new post and include a reference to the original post that triggered your question.

For questions about the internals of how TAGSETS.EXCELXP specifies width (after the calculations), your best bet for an answer is to work with SAS Tech Support. I believe that Width_Points and Width_Fudge use the number of characters as specified by either DEFAULT_COLUMN_WIDTH or ABSOLUTE_COLUMN_WIDTH in their formulas, so they are impacted by either the widths specified with these 2 suboption OR it calculates the width based on the size of the header text compared to the size of the cell values and picks the larger of the 2 values as the number of characters to use in the formulas. But that is my understanding, I am not sure it is entirely correct. It would be better for you to check with Tech Support on this.

cynthia

topic Re: How to autofix cell width with ExcelXP tagset? in ODS and Base Reporting

How to autofix cell width with ExcelXP tagset?

Re: How to autofix cell width with ExcelXP tagset?

Re: How to autofix cell width with ExcelXP tagset?

Re: How to autofix cell width with ExcelXP tagset?