PHPExcel auto size column width

PHPExcel auto size column width

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Why PHPExcel calculateColumnWidths() Fails with Excel5

When working with legacy spreadsheet libraries like PHPExcel, it is easy to encounter behavior that contradicts modern expectations. You populate a sheet with varying string lengths, enable auto-sizing on every column dimension, and call calculateColumnWidths(). Logically, you expect the columns to expand to fit the content. However, if you are saving the file using the Excel5 writer format, the calculated widths simply do not appear in the final output.

The root cause lies in how the underlying OLE Compound Document Binary File Format (used by Excel 97-2003) handles column width information compared to the OOXML format used by Excel 2007 and later.

The Limitation of the Excel5 Writer

In PHPExcel, the calculateColumnWidths() method performs a calculation based on the font metrics defined for the active sheet. It determines the pixel or character width required to render the text within a cell. This logic is sound; the library successfully computes the necessary dimensions.

The failure occurs during the serialization phase. When you instantiate a writer using PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'), the resulting file relies on the older BIFF8 specification. This specification does not support storing column width values derived dynamically from text content in the same way OOXML does. Instead, the Excel5 format expects column widths to be explicitly defined as integer pixel counts or specific unit multipliers during the writing process, and it ignores the calculated width attributes if they were not manually set prior to saving.

Consequently, even though your code sets setAutoSize(true) and triggers the calculation, the Excel5 writer strips out or overwrites these dynamic adjustments with default values, resulting in columns that appear too narrow for their content.

Switching to OOXML Format

To achieve auto-sizing functionality, you must switch to the Office Open XML (.xlsx) format. This format stores spreadsheet data in a structured ZIP archive containing XML files. It supports storing column width metadata in a way that is preserved during the save operation and interpreted correctly by Excel clients.

Update your writer instantiation as follows:

// Use OOXML instead of Excel5
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('path/to/your/file.xlsx');

With the Excel2007 writer (or the newer PhpSpreadsheet equivalent), the sequence of operations you outlined becomes effective:

  1. Set cell values with varying lengths.
  2. Iterate through column dimensions to enable auto-size.
  3. Call calculateColumnWidths() before saving.
  4. Save using the OOXML writer.

This combination ensures that the calculated pixel widths are embedded into the XML representation of the worksheet, allowing Excel to render the columns correctly.

Handling Font Variations and Edge Cases

While switching writers solves the primary issue, there are nuances to consider regarding how calculateColumnWidths() determines width. The calculation relies heavily on the font properties assigned to the cells. If a cell contains a bold font or a different typeface than the default, the library uses the metrics of that specific font to compute the width.

Ensure that your column dimension objects are not inheriting a "normal" font setting that conflicts with the actual content. In some scenarios, if a range is merged or if there are complex formatting rules applied via setMergeCells() or conditional formatting, the automatic sizing logic might skip certain cells or calculate based on the maximum width found in a merged group rather than individual cell contents.

Additionally, be aware that calculateColumnWidths() calculates width based on the visible content within the cell. If a cell contains a formula that returns an empty string or a value formatted as text but is technically empty, the column might not expand as expected if there is no actual character data to measure against.

Conclusion

The discrepancy in your original code was not a bug in the calculation logic but a limitation of the Excel5 file format regarding dynamic column sizing. By switching to the Excel2007 writer, you leverage the OOXML standard's robust support for metadata, allowing PHPExcel to preserve the auto-calculated widths. For new projects, always default to OOXML unless strict compatibility with legacy Excel 97-2003 files is required, as modern libraries like PhpSpreadsheet have largely superseded PHPExcel with improved handling of these exact scenarios.

Our network