php - 如何将工作表的列数获取为整数 (28) 而不是 Excel 字母 ("AB")？

Question

给定:

$this->objPHPExcelReader = PHPExcel_IOFactory::createReaderForFile($this->config['file']);
$this->objPHPExcelReader->setLoadSheetsOnly(array($this->config['worksheet']));
$this->objPHPExcelReader->setReadDataOnly(true);
$this->objPHPExcel = $this->objPHPExcelReader->load($this->config['file']);

我可以像这样遍历行，但它非常慢，即在一个 3MB 的 Excel 文件中，工作表有 “EL”列，大​​约需要每行 1 秒:

foreach ($this->objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row)
{
    $dataset = array();
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach ($cellIterator as $cell)
    {
        if (!is_null($cell))
        {
            $dataset[] = $cell->getCalculatedValue();
        }
    }
    $this->datasets[] = $dataset;
}

当我像这样迭代时，它明显更快(30 秒内大约 2000 行)，但我将不得不转换字母，例如“EL”到一个数字:

$highestColumm = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); // e.g. "EL"
$highestRow = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

$number_of_columns = 150; // TODO: figure out how to get the number of cols as int
for ($row = 1; $row < $highestRow + 1; $row++) {
    $dataset = array();
    for ($column = 0; $column < $number_of_columns; $column++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
    }
    $this->datasets[] = $dataset;
}

有没有办法将最高列作为整数(例如“28”)而不是 Excel 样式的字母(例如“AB”)？

Answer 1

$colNumber = PHPExcel_Cell::columnIndexFromString($colString);

从“A”的 $colString 返回 1，从“Z”返回 26，从“AA”返回 27，等等。

和(几乎)相反

$colString = PHPExcel_Cell::stringFromColumnIndex($colNumber);

从 0 的 $colNumber 返回“A”，从 25 返回“Z”，从 26 返回“AA”，等等。

编辑

几个有用的技巧:

工作表类有一个 toArray() 方法:

$this->datasets = $this->objPHPExcel->setActiveSheetIndex(0)->toArray();

接受以下参数:

* @param  mixed    $nullValue          Value returned in the array entry if a cell doesn't exist
* @param  boolean  $calculateFormulas  Should formulas be calculated?
* @param  boolean  $formatData         Should formatting be applied to cell values?
* @param  boolean  $returnCellRef      False - Return a simple array of rows and columns indexed by number counting from zero
*                                      True - Return rows and columns indexed by their actual row and column IDs

虽然它确实使用了迭代器，所以会稍微慢一些

或

利用 PHP 的 increment character strings Perl Style 功能

$highestColumm = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); // e.g. "EL" 
$highestRow = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();  

$highestColumm++;
for ($row = 1; $row < $highestRow + 1; $row++) {     
    $dataset = array();     
    for ($column = 'A'; $column != $highestColumm; $column++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCell($column . $row)->getValue();
    }
    $this->datasets[] = $dataset;
}

如果您正在处理大量行，您实际上可能会注意到++$row 相对于 $row++ 的性能改进