Setting Column Width with PHPExcel Not Working

Question

I am trying to format the cell widths of a document that I am creating with PHPExcel, yet when I use the getColumnDimension('A')->setWidth(7) method, it is not being produced accurately in the generated xlsx file.

I formatted the document in Excel so that I could find the values of the cells that I want and I get two values, one in Excel Units and the other in Inches.

For example:

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('7');

will end up giving me a column width of 6.17 instead of 7. I have already tried using both strings and int values for, nothing changes. Any ideas?

Solution

This is because MS Excel adjusts the figure itself to its own internal units: there's a whole section in the developer documentation (section 4.6.28, entitled "Setting a column’s width") that explains this.

Quoting from the sidebar in that section:

The measure for column width in PHPExcel does not correspond exactly to the measure you may be used to in Microsoft Office Excel. Column widths are difficult to deal with in Excel, and there are several measures for the column width.

1. Inner width in character units (e.g. 8.43 this is probably what you are familiar with in Excel)
2. Full width in pixels (e.g. 64 pixels)
3. Full width in character units (e.g. 9.140625, value -1 indicates unset width)

PHPExcel always operates with 3) "Full width in character units" which is in fact the only value that is stored in any Excel file, hence the most reliable measure. Unfortunately, Microsoft Office Excel does not present you with this measure. Instead measures 1) and 2) are computed by the application when the file is opened and these values are presented in various dialogues and tool tips.

The character width unit is the width of a '0' (zero) glyph in the workbooks default font. Therefore column widths measured in character units in two different workbooks can only be compared if they have the same default workbook font.

If you have some Excel file and need to know the column widths in measure 3), you can read the Excel file with PHPExcel and echo the retrieved values.

(my emphasis in bold)

Comments

[Ajay Patidar]

Also before set with you need to disable auto size $objPHPExcel->getActiveSheet()->getColumnDimension('A')->set‌​AutoSize(false);