Setting Autosize Columns in PHPExcel
You've set up your headers, populated your data rows from a database query, and frozen the panes exactly how you want them. The spreadsheet looks right in your browser preview, but when you save it as an .xlsx file and open it in Excel, column C is still cramped around "John" while column F is unnecessarily wide for short email addresses. You're stuck manually resizing columns because PHPExcel doesn't do this by default.
The solution lies in understanding how the underlying OpenXML format handles cell dimensions. When you use setCellValue(), PHPExcel writes the text content, but it does not automatically calculate the width required to fit that text unless you explicitly tell it to. The method you need is setAutoSize() on the column dimension object.
However, there is a critical nuance: column auto-sizing must be applied before the file is saved. If you set the dimensions after writing all your data but before saving (which is what you should do anyway), Excel will render the correct widths. But if you apply this logic incorrectly or rely on default behavior, the columns remain at their calculated width based on the internal unit system of OpenXML, which often results in very narrow columns that look broken in Excel.
Here is how to implement this correctly within your existing code structure. You need to iterate through your column dimensions and call setAutoSize(true) on each one. Since you are populating rows dynamically via a loop, the most robust approach is to set the auto-size property for every column immediately after setting the values or in a separate pass before saving.
Add this block of code right before you instantiate the $objWriter but after your data population loop:
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensions()->get('F')->setAutoSize(true);
Alternatively, you can loop through the dimensions if you prefer a more scalable approach:
$sheet = $objPHPExcel->getActiveSheet();
foreach ($sheet->getColumnDimensionIterator() as $col) {
$col->getDimension()->setAutoSize(true);
}
Why Does This Matter?
The setAutoSize() method instructs the underlying OpenXML library to calculate the column width based on the longest string currently in that column. It takes into account font metrics, such as character width and line height, to ensure the text isn't cut off. Without this call, Excel defaults to a calculated unit width (often 8 characters) which can be too narrow for long strings like "Company Name" or "Position".
Important Trade-offs and Limitations
While setAutoSize() is powerful, it has specific behaviors you should be aware of:
- Performance Impact: Calculating the exact pixel width required for text involves measuring font metrics. If you are generating massive spreadsheets with hundreds of columns, iterating through every column dimension to set auto-size might add a noticeable delay during generation, though usually negligible for standard reports.
- Font Consistency: Auto-sizing is highly dependent on the active font family and size. Changing the font in your code after setting auto-size but before saving will invalidate the previous calculation. Ensure your font configuration (via
getActiveCellStyle()) is finalized before callingsetAutoSize(). - Merged Cells: If you have merged cells, auto-sizing can sometimes behave unexpectedly because Excel measures the width of the merged range rather than individual cells within it. Your current code does not use merges, so this is not an issue here, but it is a common pitfall in more complex reports.
- Hidden Columns: In your code, you have set column E to be collapsed (
setCollapsed(true)). Auto-sizing will still calculate the width for column E based on its content, even though it is hidden. If you intend to hide columns permanently, you might want to disable auto-sizing for those specific dimensions to save calculation time.
Final Implementation Check
Before saving your file with PHPExcel_IOFactory::createWriter(), ensure that:
- All
setCellValue()calls are complete. - Your column dimension styles (bold, alignment, etc.) are applied if needed, as these can affect width calculations slightly.
- You call
setAutoSize(true)on every column you intend to auto-fit.
Once this is in place, your generated .xlsx file will open with perfectly fitted columns, eliminating the need for manual resizing. This approach ensures that your dynamic data from the $info3 array always renders cleanly, regardless of whether a company name is "Acme" or "Interstellar Solutions".