PHPExcel auto size column width

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');

253527 次浏览
小开
最佳答案

If a column is set to AutoSize, PHPExcel attempts to calculate the column width based on the calculated value of the column (so on the result of any formulae), and any additional characters added by format masks such as thousand separators.

默认情况下，这是一个 estimated宽度: 一个更准确的计算方法是可用的，基于使用 GD，它也可以处理字体样式特征，如粗体和斜体; 但这是一个更大的开销，所以它是默认关闭。可以使用以下命令启用更精确的计算

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

但是，自动大小并不适用于所有的 Writer 格式... ... 例如 CSV。你没有提到你正在使用的是什么样的 Writer 格式。

但是您还需要确定设置尺寸的列:

foreach(range('B','G') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
->setAutoSize(true);
}

$objPHPExcel->getActiveSheet()->getColumnDimension()需要列 ID。

$objPHPExcel->getActiveSheet()->getColumnDimensions()将返回所有已定义的列维度记录的数组; 但是除非已经显式创建了列维度记录(可能通过加载模板，或者通过手动调用 getColumnDimension()) ，否则它将不存在(节省内存)。

小开

这里有一个更灵活的变体，基于@Mark Baker 的帖子:

foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject->getActiveSheet()
->getColumnDimension($col)
->setAutoSize(true);
}

希望这有所帮助;)

小开

下面是如何使用工作表中所有列的示例:

$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 );
}
小开

如果您需要对多个工作表和每个工作表中的多个列执行此操作，以下是您如何遍历所有工作表的方法:

// 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);
}
}
小开 
foreach(range('B','G') as $columnID)
{
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
小开

来晚了，但在搜索了所有地方之后，我发现了一个似乎是“唯一”的解决方案。

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.

开始了:

//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');
小开 
for ($i = 'A'; $i !=  $objPHPExcel->getActiveSheet()->getHighestColumn(); $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}
小开

您还需要确定设置尺寸的列:

foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject
->getActiveSheet()
->getColumnDimension($col)
->setAutoSize(true);
}
小开 
$col = 'A';
while(true){
$tempCol = $col++;
$objPHPExcel->getActiveSheet()->getColumnDimension($tempCol)->setAutoSize(true);
if($tempCol == $objPHPExcel->getActiveSheet()->getHighestDataColumn()){
break;
}
}
小开

此代码段将自动调整所有工作表中包含数据的所有列的大小。不需要使用 activeSheetgetter 和 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);
}
}
小开

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').

为了迭代传递‘ Z’，您需要将列转换为整数、递增、比较，然后再次将其作为字符串获取:

$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);
}

通过这种方法，您可以轻松地迭代传递‘ Z’列并将 autosize 设置为每个列。

小开

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.

小开

以防有人在找这个。

下面的分辨率也适用于他们的新版 PHPExcel PHPSpreadsheet。

// 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 with getHighestDataColumn() or the last actual column.

这些方法的作用:

获得最高的工作表列。

getHighestDataColumn($row = null)-获取包含数据的最高工作表列。

getHighestRow($column = null)-获取最高的工作表行

getHighestDataRow($column = null)-获取包含数据的最高工作表行。

小开

对于 phpspadsheet:

$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);
}
小开 
// Auto-size columns for all worksheets
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
foreach ($worksheet->getColumnIterator() as $column) {
$worksheet
->getColumnDimension($column->getColumnIndex())
->setAutoSize(true);
}
}
小开

不要使用 range () ，它不会在 Z 列之外工作。

简单使用:

$sheet = $spreadsheet->getActiveSheet();
foreach ($sheet->getColumnIterator() as $column) {
$sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
}

在写入数据之后执行此操作，以便列迭代器知道要迭代多少列。

小开

试试这个，我是这样解决的。

$Sheet = $excel->getActiveSheet();
$lABC1 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$lABC2 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');


for($I = 0; $I < count($lABC1); $I++):
$Sheet->getColumnDimension($lABC1[$I])->setAutoSize(true);
for($J = 0; $J < 6; $J++){
$Sheet->getColumnDimension($lABC2[$J].$lABC1[$I])->setAutoSize(true);
}
endfor;

提交答案