I'm trying to auto size the columns of my sheet. I'm writing the file and in the end I try to resize all of my columns.
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B1', 'test1111111111111111111111')
->setCellValue('C1', 'test1111111111111')
->setCellValue('D1', 'test1111111')
->setCellValue('E1', 'test11111')
->setCellValue('F1', 'test1')
->setCellValue('G1', 'test1');
foreach($objPHPExcel->getActiveSheet()->getColumnDimension() as $col) {
$col->setAutoSize(true);
}
$objPHPExcel->getActiveSheet()->calculateColumnWidths();
The above code doesn't work. Doesn't change the column size to fit the text
UPDATE
The writer I'm using $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
foreach(range('B','G') as $columnID)
{
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
In case somebody was looking for this.
The resolution below also works on PHPSpreadsheet
, their new version of PHPExcel.
// assuming $spreadsheet is instance of PhpOffice\PhpSpreadsheet\Spreadsheet
// assuming $worksheet = $spreadsheet->getActiveSheet();
foreach(range('A',$worksheet->getHighestColumn()) as $column) {
$spreadsheet->getColumnDimension($column)->setAutoSize(true);
}
Note:
getHighestColumn()
can be replaced withgetHighestDataColumn()
or the last actual column.
What these methods do:
getHighestColumn($row = null)
- Get highest worksheet column.
getHighestDataColumn($row = null)
- Get highest worksheet column that contains data.
getHighestRow($column = null)
- Get highest worksheet row
getHighestDataRow($column = null)
- Get highest worksheet row that contains data.
All of these answers kinda suck... do not use range() it wont work beyond column Z.
Simply use:
$sheet = $spreadsheet->getActiveSheet();
foreach ($sheet->getColumnIterator() as $column) {
$sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
}
Do this after you have written your data so that the column iterator knows how many columns to iterate over.
// Auto-size columns for all worksheets
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
foreach ($worksheet->getColumnIterator() as $column) {
$worksheet
->getColumnDimension($column->getColumnIndex())
->setAutoSize(true);
}
}
for ($i = 'A'; $i != $objPHPExcel->getActiveSheet()->getHighestColumn(); $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}
For Spreedsheet + PHP 7, you must write instead of PHPExcel_Cell::columnIndexFromString
, \PhpOffice\PhpSpreadsheet\Cell::columnIndexFromString
. And at the loop is a mistake, there you must <
not work with <=
. Otherwise, he takes a column too much into the loop.
you also need to identify the columns to set dimensions:
foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject
->getActiveSheet()
->getColumnDimension($col)
->setAutoSize(true);
}
This code snippet will auto size all the columns that contain data in all the sheets. There is no need to use the activeSheet getter and setter.
// In my case this line didn't make much of a difference
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
// Iterating all the sheets
/** @var PHPExcel_Worksheet $sheet */
foreach ($objPHPExcel->getAllSheets() as $sheet) {
// Iterating through all the columns
// The after Z column problem is solved by using numeric columns; thanks to the columnIndexFromString method
for ($col = 0; $col <= PHPExcel_Cell::columnIndexFromString($sheet->getHighestDataColumn()); $col++) {
$sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
}
}
This is example how to use all columns from worksheet:
$sheet = $PHPExcel->getActiveSheet();
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells( true );
/** @var PHPExcel_Cell $cell */
foreach( $cellIterator as $cell ) {
$sheet->getColumnDimension( $cell->getColumn() )->setAutoSize( true );
}
$col = 'A';
while(true){
$tempCol = $col++;
$objPHPExcel->getActiveSheet()->getColumnDimension($tempCol)->setAutoSize(true);
if($tempCol == $objPHPExcel->getActiveSheet()->getHighestDataColumn()){
break;
}
}
for phpspreadsheet:
$sheet = $spreadsheet->getActiveSheet(); // $spreadsheet is instance of PhpOffice\PhpSpreadsheet\Spreadsheet
foreach (
range(
1,
Coordinate::columnIndexFromString($sheet->getHighestColumn(1))
) as $column
) {
$sheet
->getColumnDimension(Coordinate::stringFromColumnIndex($column))
->setAutoSize(true);
}
If you need to do that on multiple sheets, and multiple columns in each sheet, here is how you can iterate through all of them:
// Auto size columns for each worksheet
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));
$sheet = $objPHPExcel->getActiveSheet();
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
/** @var PHPExcel_Cell $cell */
foreach ($cellIterator as $cell) {
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}
}
Here a more flexible variant based on @Mark Baker post:
foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject->getActiveSheet()
->getColumnDimension($col)
->setAutoSize(true);
}
Hope this helps ;)
Come late, but after searching everywhere, I've created a solution that seems to be "the one".
Being known that there is a column iterator on last API versions, but not knowing how to atuoadjust the column object it self, basically I've created a loop to go from real first used column to real last used one.
Here it goes:
//Just before saving de Excel document, you do this:
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
//We get the util used space on worksheet. Change getActiveSheet to setActiveSheetIndex(0) to choose the sheet you want to autosize. Iterate thorugh'em if needed.
//We remove all digits from this string, which cames in a form of "A1:G24".
//Exploding via ":" to get a 2 position array being 0 fisrt used column and 1, the last used column.
$cols = explode(":", trim(preg_replace('/\d+/u', '', $objPHPExcel->getActiveSheet()->calculateWorksheetDimension())));
$col = $cols[0]; //first util column with data
$end = ++$cols[1]; //last util column with data +1, to use it inside the WHILE loop. Else, is not going to use last util range column.
while($col != $end){
$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
$col++;
}
//Saving.
$objWriter->save('php://output');
If you try to iterate with for ($col = 2; $col <= 'AC'; ++ $col){...}
, or with foreach(range('A','AC') as $col) {...}
it will work for columns from A to Z, but it fails pass the Z (Ex. iterate between 'A' to 'AC').
In order to iterate pass 'Z', you need to convert the column to integer, increment, compare, and get it as string again:
$MAX_COL = $sheet->getHighestDataColumn();
$MAX_COL_INDEX = PHPExcel_Cell::columnIndexFromString($MAX_COL);
for($index=0 ; $index <= $MAX_COL_INDEX ; $index++){
$col = PHPExcel_Cell::stringFromColumnIndex($index);
// do something, like set the column width...
$sheet->getColumnDimension($col)->setAutoSize(TRUE);
}
With this, you easy iterate pass the 'Z' column and set autosize to every column.
Source: Stackoverflow.com