php 如何将工作表的列数作为整数（28）而不是 Excel 字母（“AB”）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4564155/
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 get the number of columns of worksheet as integer (28) instead of Excel-letters ("AB")?
提问by Edward Tanguay
Given:
鉴于：
$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']);
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:
我可以像这样遍历行，但速度非常慢，即在带有“EL”列的工作表的 3MB Excel 文件中，每行大约需要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;
}
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:
当我这样迭代时，它会明显更快（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;
}
Is there a way to get the highest column as an integer (e.g. "28") instead of in Excel-styled letters (e.g. "AB")?
有没有办法将最高列作为整数（例如“28”）而不是 Excel 样式的字母（例如“AB”）？
回答by Mark Baker
$colNumber = PHPExcel_Cell::columnIndexFromString($colString);
returns 1 from a $colString of 'A', 26 from 'Z', 27 from 'AA', etc.
从 'A' 的 $colString 返回 1，从 'Z' 返回 26，从 'AA' 返回 27，等等。
and the (almost) reverse
和（几乎）相反
$colString = PHPExcel_Cell::stringFromColumnIndex($colNumber);
returns 'A' from a $colNumber of 0, 'Z' from 25, 'AA' from 26, etc.
从 $colNumber 0 中返回 'A'，从 25 中返回 'Z'，从 26 中返回 'AA'，等等。
EDIT
编辑
A couple of useful tricks:
几个有用的技巧：
There is a toArray() method for the worksheet class:
工作表类有一个 toArray() 方法：
$this->datasets = $this->objPHPExcel->setActiveSheetIndex(0)->toArray();
which accepts the following parameters:
它接受以下参数：
* @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
或者
Take advantage of PHP's ability to increment character strings Perl Style
利用 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;
}
and if you're processing a large number of rows, you might actually notice the performance improvement of ++$row over $row++
如果您正在处理大量行，您实际上可能会注意到 ++$row 比 $row++ 的性能改进
回答by dqhendricks
Not sure if your class has a built in method, but you could always use the ord() function on each letter of the column index string. You will of course have to subtract out the base value of 'A', and multiply by 26^x for each position from the far right of the string. Something like:
不确定您的类是否具有内置方法，但您始终可以在列索引字符串的每个字母上使用 ord() 函数。您当然必须减去 'A' 的基值，并为字符串最右侧的每个位置乘以 26^x。就像是：
$input_string = 'BC';
$base_value = 64;
$decimal_value = 26;
$column_index = 0;
for ($i = 0; $i < strlen($input_string); $i++) {
$char_value = ord($input_string[$i]);
$char_value -= $base_value;
$char_value *= pow($decimal_value, (strlen($input_string) - ($i + 1)));
$column_index += $char_value;
}
echo $column_index;
Basically this would make 'BC' equal (2 * 26^1) + (3 * 26^0) = 55.
基本上这将使 'BC' 等于 (2 * 26^1) + (3 * 26^0) = 55。
$input_string being the column index string, $base_value being the ord() value of 'A' minus 1, and $decimal_value being the value of A0. Should work up to any number column. Have tested. Hope this helps.
$input_string 是列索引字符串，$base_value 是 'A' 减去 1 的 ord() 值，而 $decimal_value 是 A0 的值。应该适用于任何数字列。有测试。希望这可以帮助。
回答by foochow
This is a somewhat simplified version of dqhendricks answer. I have added to copies, one function assuming you enter the full excel cell reference (ie. "AB12") and the other assuming you enter just the column reference (ie. "AB"). They both return a zero based index.
这是 dqhendricks 答案的简化版本。我添加了副本，一个函数假设您输入完整的 excel 单元格引用（即“AB12”），另一个假设您只输入列引用（即“AB”）。它们都返回一个基于零的索引。
Input Full Cell Reference
输入完整单元格参考
function getIndex ($cell) {
// Strip cell reference down to just letters
$let = preg_replace('/[^A-Z]/', '', $cell);
// Iterate through each letter, starting at the back to increment the value
for ($num = 0, $i = 0; $let != ''; $let = substr($let, 0, -1), $i++)
$num += (ord(substr($let, -1)) - 65) * pow(26, $i);
return $num;
}
Input Column Reference Only
仅输入列参考
function getIndex ($let) {
// Iterate through each letter, starting at the back to increment the value
for ($num = 0, $i = 0; $let != ''; $let = substr($let, 0, -1), $i++)
$num += (ord(substr($let, -1)) - 65) * pow(26, $i);
return $num;
}
The function goes from the back of the string to the front to increase the value of the column. It uses the ord()function to get the numeric value of a character and then has the letter value subtracted to give the local column value. Finally it is multiplied by the current power of 26.
该函数从字符串的后面到前面以增加列的值。它使用该ord()函数获取字符的数值，然后减去字母值以给出本地列值。最后乘以当前的 26 次方。
回答by Maurizio Brioschi
I suggest to convert excel to array, clean it from empty elements and then count the number of columns:
我建议将excel转换为数组，从空元素中清除它，然后计算列数：
protected function getColumnsCheck($file, $col_number) {
if (strstr($file, ".xls") != false && strstr($file, ".xlsx") != false) {
$fileType = PHPExcel_IOFactory::identify($file);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($file);
$columns_empty = $objPHPExcel->getActiveSheet(0)->toArray()[0];
$columns = array_filter($columns_empty);
return ($col_number==count($columns));
}
return false;
}
回答by ISCI
function getNameFromNumber($num) {//(Example 0 = A, 1 = B)
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
getNameFromNumber(0) //return A
getNameFromNumber(0) //返回A
回答by IjorTengab
/**
*
*/
function number_to_alphabet($number) {
$number = intval($number);
if ($number <= 0) {
return '';
}
$alphabet = '';
while($number != 0) {
$p = ($number - 1) % 26;
$number = intval(($number - $p) / 26);
$alphabet = chr(65 + $p) . $alphabet;
}
return $alphabet;
}
/**
* Required PHP 5.6.
* @see: http://php.net/manual/en/language.operators.arithmetic.php
*/
function alphabet_to_number($string) {
$string = strtoupper($string);
$length = strlen($string);
$number = 0;
$level = 1;
while ($length >= $level ) {
$char = $string[$length - $level];
$c = ord($char) - 64;
$number += $c * (26 ** ($level-1));
$level++;
}
return $number;
}
Test:
测试：
for ($x=1; $x<=1000; $x++) {
echo 'number_to_alphabet('.$x.') = ',$y = number_to_alphabet($x),'; ';
echo 'alphabet_to_number('.$y.') = '.alphabet_to_number($y).'; ';
echo PHP_EOL;
}
