php PHPExcel如何设置自动列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10927462/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to PHPExcel set auto-columns width
提问by Davuz
I'm working with PHPExcel to export data for download. When open downloaded files, with cells have big number, it show "#######" instead of value number. I'm tried setAutoSize()for every columns then call $sheet->calculateColumnWidths()but it still not changes. I see calculateColumnWidths() at here, @Mark Baker says "calculateColumnWidths() increase the value by perhaps 5% to try and ensure that the entire column fits". If number length in cell exceed 5%, it seems doen's resolved the problem
我正在使用 PHPExcel 导出数据以供下载。当打开下载的文件时，单元格的数字很大，它显示“#######”而不是数值数字。我尝试setAutoSize()了每一列然后调用$sheet->calculateColumnWidths()但它仍然没有改变。我在这里看到了 calculateColumnWidths() ，@Mark Baker 说“calculateColumnWidths() 将值增加 5% 以尝试确保整个列适合”。如果单元格中的数字长度超过 5%，则问题似乎已解决
UPDATEThis is my function to auto size columns:
更新这是我自动调整列大小的功能：
function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) {
if (empty($toCol) ) {//not defined the last column, set it the max one
$toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
}
for($i = $fromCol; $i <= $toCol; $i++) {
$sheet->getColumnDimension($i)->setAutoSize(true);
}
$sheet->calculateColumnWidths();
}
采纳答案by Mark Baker
First potential problem may be that you're working with column letters. PHP's incrementor operation will work with column letters, so if $i is 'A', then $i++ will give 'B', and if $i is 'Z' than $i++ will give 'AA'; but you can't use <= as your comparator, as 'AA' is <= 'Z' when executed as a straight comparison.
第一个潜在问题可能是您正在使用列字母。PHP 的增量器操作将使用列字母，所以如果 $i 是 'A'，那么 $i++ 将给出 'B'，如果 $i 是 'Z'，那么 $i++ 将给出 'AA'；但是您不能使用 <= 作为比较器，因为当作为直接比较执行时，'AA' 是 <='Z'。
Instead of
代替
for($i = $fromCol; $i <= $toCol; $i++) {
use
用
$toCol++;
for($i = $fromCol; $i !== $toCol; $i++) {
To add the 5% margin after calling $sheet->calculateColumnWidths() do:
要在调用 $sheet->calculateColumnWidths() 后添加 5% 的边距，请执行以下操作：
for($i = $fromCol; $i !== $toCol; $i++) {
$calculatedWidth = $sheet->getColumnDimension($i)->getWidth();
$sheet->getColumnDimension($i)->setWidth((int) $calculatedWidth * 1.05);
}
回答by Nikos M.
None of the suggestions worked for me so i did a manual calculation (rather easy and fast) (sample code follows) and works perfectly (note fonts/styles are default but it would be easy to adjust for other font or style)
没有任何建议对我有用，所以我进行了手动计算（相当简单和快速）（示例代码如下）并且工作完美（注意字体/样式是默认的，但很容易针对其他字体或样式进行调整）
foreach((array)$data as $sheet_data)
{
$maxwidth = array( );
$objPHPExcel->setActiveSheetIndex( $i++ );
$sheet = $objPHPExcel->getActiveSheet( );
if ( !empty($sheet_data['title']) )
$sheet->setTitle($sheet_data['title']);
if ( !empty($sheet_data['rows']) )
{
foreach((array)$sheet_data['rows'] as $row=>$cols)
{
foreach((array)$cols as $col=>$val)
{
$p = strpos($col,':');
if ( false !== $p )
{
// range
$range = $col; $xy = substr( $col, 0, $p );
$col = substr($xy,0,-1);
// estimate maximum column width by number of characters
$w = mb_strlen( $val );
if ( !isset($maxwidth[$col]) ) $maxwidth[$col] = $w;
elseif ( $w > $maxwidth[$col] ) $maxwidth[$col] = $w;
$sheet->mergeCells( $range );
$sheet->setCellValue( $xy, $val );
$sheet->getStyle( $range )
->getAlignment( )
->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER )
->setVertical( PHPExcel_Style_Alignment::VERTICAL_CENTER )
;
}
else
{
$xy = $col.$row;
// estimate maximum column width by number of characters
$w = mb_strlen( $val );
if ( !isset($maxwidth[$col]) ) $maxwidth[$col] = $w;
elseif ( $w > $maxwidth[$col] ) $maxwidth[$col] = $w;
$sheet->setCellValue( $xy, $val );
$sheet->getStyle( $xy )
->getAlignment( )
->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER )
->setVertical( PHPExcel_Style_Alignment::VERTICAL_CENTER )
;
}
}
}
}
// autosize columns based on calculation + some padding
foreach($maxwidth as $col=>$width)
{
$sheet->getColumnDimension( $col )->setAutoSize( false );
$sheet->getColumnDimension( $col )->setWidth( (int)($width * 1.2) ); // add padding as well
}
}
