如何获得工作表的列数为整数(28)而不是Excel字母("AB")? [英] How to get the number of columns of worksheet as integer (28) instead of Excel-letters ("AB")?

问题描述

给出:

$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秒:

I can iterate through the rows like this but it is very slow, i.e. in a 3MB Excel file with a worksheet that has "EL" columns, it takes about 1 second per row:

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"到一个数字:

When I iterate like this, it it significantly faster (approx. 2000 rows in 30 seconds), but I will have to convert the letters e.g. "EL" to a number:

$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")获得?

推荐答案

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

从$ colString的'A'返回1，从'Z'返回26，从'AA'返回27，等等.

returns 1 from a $colString of 'A', 26 from 'Z', 27 from 'AA', etc.

和(几乎)反向

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

从$ colNumber 0返回'A'，从25返回'Z'，从26返回'AA'，等等.

returns 'A' from a $colNumber of 0, 'Z' from 25, 'AA' from 26, etc.

编辑

一些有用的技巧:

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

There is a toArray() method for the worksheet class:

$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

尽管它确实使用了迭代器，但速度会稍慢

although it does use the iterators, so would be slightly slower

OR

利用PHP的功能增加字符串Perl样式

$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 ++的性能有所提高

and if you're processing a large number of rows, you might actually notice the performance improvement of ++$row over $row++

这篇关于如何获得工作表的列数为整数(28)而不是Excel字母("AB")?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！