php PHPExcel 自动调整列宽

PHPExcel auto size column width

提问by Alkis Kalogeris

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

上面的代码不起作用。不更改列大小以适合文本

UPDATEThe writer I'm using $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

更新我正在使用的作家$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

回答by Mark Baker

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.

如果将列设置为 AutoSize，PHPExcel 将尝试根据列的计算值（任何公式的结果）以及由格式掩码（例如千位分隔符）添加的任何其他字符来计算列宽。

By default, this is an estimatedwidth: a more accurate calculation method is available, based on using GD, which can also handle font style features such as bold and italic; but this is a much bigger overhead, so it is turned off by default. You can enable the more accurate calculation using

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

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

However, autosize doesn't apply to all Writer formats... for example CSV. You don't mention what writer you're using.

但是，自动调整大小并不适用于所有 Writer 格式……例如 CSV。你没有提到你正在使用什么作家。

But you also need to identify the columns to set dimensions:

但您还需要确定要设置维度的列：

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

$objPHPExcel->getActiveSheet()->getColumnDimension()expects a column ID.

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

$objPHPExcel->getActiveSheet()->getColumnDimensions()will return an array of all the defined column dimension records; but unless a column dimension record has been explicitly created (perhaps by loading a template, or by manually calling getColumnDimension()) then it won't exist (memory saving).

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

回答by MrUpsidown

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

回答by Todor Todorov

Here a more flexible variant based on @Mark Baker post:

这是基于@Mark Ba​​ker 帖子的更灵活的变体：

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

Hope this helps ;)

希望这可以帮助 ;）

回答by Nathan

for ($i = 'A'; $i !=  $objPHPExcel->getActiveSheet()->getHighestColumn(); $i++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}

回答by dmi3x

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

回答by Ronnie

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.

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

回答by beck kuo

for phpspreadsheet:

对于 php 电子表格：

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

回答by Gabi Dj

In case somebody was looking for this.

万一有人在找这个。

The resolution below also works on PHPSpreadsheet, their new version of PHPExcel.

下面的分辨率也适用于PHPSpreadsheet他们的新版本 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 with getHighestDataColumn()or the last actual column.

注意：getHighestColumn()可以替换为getHighestDataColumn()或 最后一个实际列。

What these methods do:

这些方法的作用：

getHighestColumn($row = null)- Get highest worksheet column.

getHighestColumn($row = null)- 获取最高的工作表列。

getHighestDataColumn($row = null)- Get highest worksheet column that contains data.

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

getHighestRow($column = null)- Get highest worksheet row

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

getHighestDataRow($column = null)- Get highest worksheet row that contains data.

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

回答by Sadikhasan

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

回答by Alejandro Silva

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

如果您尝试使用 进行迭代for ($col = 2; $col <= 'AC'; ++ $col){...}，或者使用foreach(range('A','AC') as $col) {...}它将适用于从 A 到 Z 的列，但它无法通过 Z（例如，在“A”到“AC”之间进行迭代）。

In order to iterate pass 'Z', you need to convert the column to integer, increment, compare, and get it as string again:

为了迭代 pass '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);
}

With this, you easy iterate pass the 'Z' column and set autosize to every column.

有了这个，您可以轻松迭代传递“Z”列并将自动大小设置为每一列。