<col> (Column Width & Formatting)

Defines column width and column formatting for one or more columns of the worksheet.

example: 
<col min="5" max="5" width="9.140625" style="3"/>

Parent Elements

<cols> (§3.3.1.16)

Attributes

Description

<bestFit> (Best Fit Column Width)

Flag indicating if the specified column(s) is set to 'best fit'. 'Best fit' is set to true under these conditions:

  • The column width has never been manually set by the user, AND

  • The column width is not the default width

  • 'Best fit' means that when numbers are typed into a cell contained in a 'best fit' column, the column width should automatically resize to display the number. Note: In best fit cases, column width shall not be made smaller, only larger.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<collapsed> (Collapsed)

Flag indicating if the outlining of the affected column(s) is in the collapsed state. See description of <row> @collapsed and <outlinePr> element's @summaryBelow and @summaryRight attributes for detailed information.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<customWidth> (Custom Width)

Flag indicating that the column width for the affected column(s) is different from the default or has been manually set.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<hidden> (Hidden Columns)

Flag indicating if the affected column(s) are hidden on this worksheet.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<max> (Maximum Column)

Last column affected by this 'column info' record.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<min> (Minimum Column)

First column affected by this 'column info' record.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<outlineLevel> (Outline Level)

Outline level of affected column(s). Range is 0 to 7. See description of <outlinePr> element's @summaryBelow and @summaryRight attributes for detailed information.

The possible values for this attribute are defined by the XML Schema unsignedByte datatype.

<phonetic> (Show Phonetic Information)

Flag indicating if the phonetic information should be displayed by default for the affected column(s) of the worksheet.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<style> (Style)

Default style for the affected column(s). Affects cells not yet allocated in the column(s). In other words, this style applies to new columns.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<width> (Column Width)

Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore if the cell width is 8 characters wide, the value of this attribute shall be Truncate([8*7+5]/7*256)/256 = 8.7109375.

To translate the value of width in the file into the column width value at runtime (expressed in terms of pixels), use this calculation:

=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})

Using the same example as above, the calculation would be Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels

To translate from pixels to character width, use this calculation:

=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100

Using the example above, the calculation would be Truncate((61-5)/7*100+0.5)/100 = 8 characters.

Note: when wide borders are applied, part of the left/right border shall overlap with the 2 pixel padding on each side. Wide borders do not affect the width calculation of the column.

Note: When the sheet is in the mode to view formulas instead of values, the pixel width of the column is doubled.

The possible values for this attribute are defined by the XML Schema double datatype.

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_Col">
	<attribute name="min" type="xsd:unsignedInt" use="required"/>
	<attribute name="max" type="xsd:unsignedInt" use="required"/>
	<attribute name="width" type="xsd:double" use="optional"/>
	<attribute name="style" type="xsd:unsignedInt" use="optional" default="0"/>
	<attribute name="hidden" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="bestFit" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="customWidth" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="phonetic" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="outlineLevel" type="xsd:unsignedByte" use="optional" default="0"/>
	<attribute name="collapsed" type="xsd:boolean" use="optional" default="false"/>
</complexType>