关于PHPExcel设置单元格宽度的问题,原类中自带 setAutoSize 方法可以设置 $_autoSize 属性 , 选择是否适应一列最大宽度 , 默认值fasle;
1 $objPHPExcel = new PHPExcel(); 2 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 3 4 ... 5 6 $objPHPExcel -> getActiveSheet() -> getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(0)) -> setAutoSize(true);
原理是循环遍历计算所有单元格宽度 , 比较得到最大值作为该列宽度;
实际应用发现这样生成得Excel并不是特别美观 , 比如说列最长内容比该列标题要长的多时 , 标题行就会产生大量空白。由此想出两种解决方案:
- 根据标题行 ( 某一行 ) 内容长设置宽度;
- 自动适应基础上添加规则,遍历得到的最大宽度不能超过标题宽度得n倍;
------------------------------------------------------------------------- 直接贴代码 -------------------------------------------------------------------------
第一种方案需要扩展两个文件
文件一:PHPExcel \ Worksheet \ ColumnDimension.php
1 //添加私有属性 $_relySize 2 3 /** 4 * Rely size? 5 * 6 * @var bool 7 */ 8 9 private $_relySize = false; 10 11 //添加获取 $_relySize 的方法 12 13 /** 14 * Get Auto Size 15 * 16 * @return bool 17 */ 18 19 public function getRelySize() { 20 return $this->_relySize; 21 } 22 23 //添加设置 $_relySize 的方法 24 25 /** 26 * Get Auto Size 27 * 28 * @param bool $pValue 29 * @return PHPExcel_Worksheet_ColumnDimension 30 */ 31 32 public function setRelySize($pValue = false) { 33 $this->_relySize = (int) $pValue; 34 return $this; 35 }
文件二:PHPExcel \ Worksheet.php — 找到 calculateColumnWidths 方法修改如下
/** * Calculate widths for auto-size columns * * @param boolean $calculateMergeCells Calculate merge cell width * @param number $row Calculate merge cell width * @return PHPExcel_Worksheet; */ public function calculateColumnWidths($calculateMergeCells = false) { // initialize $autoSizes array $autoSizes = array(); $relySizes = array(); //$this->getColumnDimensions() //获取所有列 foreach ($this->getColumnDimensions() as $colDimension) { if ($colDimension->getAutoSize()) { $autoSizes[$colDimension->getColumnIndex()] = -1; } if ($colDimension->getRelySize()) { $relySizes[$colDimension->getColumnIndex()] = $colDimension->getRelySize(); } } // There is only something to do if there are some auto-size columns if (!empty($autoSizes)) { // build list of cells references that participate in a merge $isMergeCell = array(); foreach ($this->getMergeCells() as $cells) { foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) { $isMergeCell[$cellReference] = true; } } // loop through all cells in the worksheet foreach ($this->getCellCollection(false) as $cellID) { $cell = $this->getCell($cellID); //$this->_cellCollection->getCurrentColumn() if (isset($autoSizes[$this->_cellCollection->getCurrentColumn()])) { // Determine width if cell does not participate in a merge if (!isset($isMergeCell[$this->_cellCollection->getCurrentAddress()])) { // Calculated value // To formatted string $cellValue = PHPExcel_Style_NumberFormat::toFormattedString( $cell->getCalculatedValue(), $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode() ); $autoSizes[$this->_cellCollection->getCurrentColumn()] = max( (float) $autoSizes[$this->_cellCollection->getCurrentColumn()], (float)PHPExcel_Shared_Font::calculateColumnWidth( $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(), $cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), $this->getDefaultStyle()->getFont() ) ); } } } // adjust column widths foreach ($autoSizes as $columnIndex => $width) { if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth(); $this->getColumnDimension($columnIndex)->setWidth($width); } } if (!empty($relySizes)) { $isMergeCell = array(); foreach ($this->getMergeCells() as $cells) { foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) { $isMergeCell[$cellReference] = true; } } foreach ($relySizes as $col => $value) { $cell = $this->getCell($col.$value); if (isset($relySizes[$this->_cellCollection->getCurrentColumn()])) { if (!isset($isMergeCell[$this->_cellCollection->getCurrentAddress()])) { $cellValue = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode() ); $relySizes[$this->_cellCollection->getCurrentColumn()] = (float)PHPExcel_Shared_Font::calculateColumnWidth($this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(), $cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), $this->getDefaultStyle()->getFont()) * 2; } } } foreach ($relySizes as $columnIndex => $width) { if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth(); $this->getColumnDimension($columnIndex)->setWidth($width); } } return $this; }
执行调用
$objPHPExcel = new PHPExcel(); $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); ... //根据第一行内容设置单元格宽度 $objPHPExcel -> getActiveSheet() -> getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(0)) -> setRelySize(1);
第二种方案没有用到 , 所以没有尝试更改 , 如果有需求者大家可以一起讨论。
以上均为个人见解 , 小弟初来乍到如有不足 , 还请多多包涵!欢迎大家发表自己的见解 , 互相学习进步!谢谢!