PHPExcel根据某行设置单元格宽度

关于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并不是特别美观 , 比如说列最长内容比该列标题要长的多时 , 标题行就会产生大量空白。由此想出两种解决方案：

1.   根据标题行 ( 某一行 ) 内容长设置宽度；
2.   自动适应基础上添加规则，遍历得到的最大宽度不能超过标题宽度得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); 

 

 

第二种方案没有用到 , 所以没有尝试更改 , 如果有需求者大家可以一起讨论。

 

 

以上均为个人见解 , 小弟初来乍到如有不足 , 还请多多包涵！欢迎大家发表自己的见解 , 互相学习进步！谢谢！

 

　　

 

    

转载于:https://www.cnblogs.com/wordGe/articles/6202087.html