php 如何找出使用PHPExcel从Excel文件中读取的行数和列数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4562527/
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 find out how many rows and columns to read from an Excel file with PHPExcel?
提问by Edward Tanguay
With the following code, I am able to read the cells out of an Excel file with PHPExcel.
使用以下代码，我可以使用 PHPExcel 从 Excel 文件中读取单元格。
I currently manuallydefine how many rows and columns to read.
我目前手动定义要读取的行数和列数。
Is there a way that PHPExcel can tell me how many rows and columns I have to read to get all the data out of the worksheet, e.g. even if some rows and columns are left blank?
PHPExcel 有没有办法告诉我必须读取多少行和列才能从工作表中获取所有数据，例如，即使某些行和列留空？
$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);
$objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
$objReader->setLoadSheetsOnly(array($sheet_name));
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("data/" . $file_name);
echo '<table border="1">';
for ($row = 1; $row < $number_of_rows; $row++) {
echo '<tr>';
for ($column = 0; $column < $number_of_columns; $column++) {
$value = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
echo '<td>';
echo $value . ' ';
echo '</td>';
}
echo '</tr>';
}
echo '</table>';
Solution:
解决方案：
Thanks, Mark, here's the full solution with those functions:
谢谢，马克，这是具有这些功能的完整解决方案：
$file_name = htmlentities($_POST['file_name']);
$sheet_name = htmlentities($_POST['sheet_name']);
$number_of_columns = htmlentities($_POST['number_of_columns']);
$number_of_rows = htmlentities($_POST['number_of_rows']);
$objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
$objReader->setLoadSheetsOnly(array($sheet_name));
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("data/" . $file_name);
$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
echo 'getHighestColumn() = [' . $highestColumm . ']<br/>';
echo 'getHighestRow() = [' . $highestRow . ']<br/>';
echo '<table border="1">';
foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
echo '<tr>';
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
$value = $cell->getCalculatedValue();
echo '<td>';
echo $value . ' ';
echo '</td>';
}
}
echo '</tr>';
}
echo '</table>';
回答by Mark Baker
$objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
and
和
$objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
or
或者
$objPHPExcel->setActiveSheetIndex(0)->calculateWorksheetDimension();
which returns a range as a string like A1:AC2048
它以字符串形式返回一个范围，如 A1:AC2048
although trailing blank rows and columns are included in these.
尽管尾随空白行和列包含在这些中。
EDIT
编辑
or you can use the iterators to loop through the existing rows and columns to get each cell within the worksheets used range. See /Tests/28iterator.php in the production distribution for an example. The iterators can be set to ignore blanks.
或者您可以使用迭代器遍历现有的行和列，以获取工作表使用范围内的每个单元格。有关示例，请参阅生产发行版中的 /Tests/28iterator.php。迭代器可以设置为忽略空白。
回答by Alliswell
From the 1.7.6 and below PHPExcelversions it is possible to get worksheet information without reading whole file:
从 1.7.6 及以下PHPExcel版本可以在不读取整个文件的情况下获取工作表信息：
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$worksheetData = $objReader->listWorksheetInfo($uploadedfile);
$totalRows = $worksheetData[0]['totalRows'];
$totalColumns = $worksheetData[0]['totalColumns'];
回答by Nikolay Ivanov
You can do it much less cell reads than itterating all the rows(columns).
与迭代所有行（列）相比，您可以执行更少的单元格读取。
In my case, the first column is SKU of item and it's mandatory.
就我而言，第一列是商品的 SKU，它是强制性的。
If you expect file with many many rows, in my case it can be 100 000 rows or more, i'm reading the value of the first column at every 10 000 row.
如果您希望文件有很多行，在我的情况下，它可以是 100 000 行或更多，我每 10 000 行读取一次第一列的值。
If cell A10000 is not empty, read A20000 and so on.
如果单元格 A10000 不为空，则读取 A20000 等。
In this way, for a file with 100 000 rows I need max 10 reads of a single cell to decide in which segment of 10 000 rows the file ends.
这样，对于具有 100 000 行的文件，我需要对单个单元格进行最多 10 次读取来决定文件在 10 000 行的哪一段中结束。
For example, let say it's between 30 000 and 40 000 row.
例如，假设它在 30 000 到 40 000 行之间。
Now get the average from above value - 35 000. One read of cell A35000 will further reduce the scope to 5000 rows. Next average (and the single cell read) will further reduce the scope to 2500 and so on.
现在从上面的值中获取平均值 - 35 000。对单元格 A35000 的一次读取将进一步将范围缩小到 5000 行。下一个平均值（和单个单元格读取）将进一步将范围缩小到 2500，依此类推。
Approximately you will need around 13-14 single cell reads, if you know in which 10 000 segment is the end of the file. If you expect file with 100 000 rows add maximum 10 cell reads to determine the exact segment of 10 000 rows. This means maximum of around 25 cell reads for file with 100 000 rows.
如果您知道文件末尾的 10 000 段，大约需要 13-14 次单细胞读取。如果您希望文件具有 100 000 行，则添加最多 10 个单元格读取以确定 10 000 行的确切段。这意味着对于 100 000 行的文件，最多可以读取大约 25 个单元格。
Edit:if you expect empty rows - read little more cells, for example, if you expect no more than 1 consequent empty row, read 2 consequent cells every time, for example A10000 and A10001, one of them should be non-empty, or you are beyond the end of the file. If you expect no more than 2 consequent empty rows, read 3 cells every time, for example A10000, A10001 and A10002, and so on.
编辑：如果您期望空行 - 读取更多单元格，例如，如果您期望不超过 1 个后续空行，则每次读取 2 个后续单元格，例如 A10000 和 A10001，其中一个应该是非空的，或者您已超出文件末尾。如果您预计后续的空行不超过 2 个，则每次读取 3 个单元格，例如 A10000、A10001 和 A10002，依此类推。
回答by fire
I don't think you can do that, you would have to loop through starting from say 1000 and go backwards until you hit the first non-blank cell and that would be your last row or column.
我不认为你能做到这一点，你必须从 1000 开始循环并倒退，直到你遇到第一个非空白单元格，这将是你的最后一行或最后一列。
You can write a macro to do this in excel which may help but I don't know if you can execute it with PHPExcel.
您可以在 excel 中编写一个宏来执行此操作，这可能会有所帮助，但我不知道您是否可以使用 PHPExcel 执行它。
回答by The Sammie
Following @nikolay'sthinking from answer above, I decided to make the first cell of every row mandatory. That way, I just look at each cell of each row first to find out how many rows actually have data, depending on the first row.
遵循@nikolay从上面的答案中得出的想法，我决定强制要求每一行的第一个单元格。这样，我只需先查看每一行的每个单元格，以找出实际有多少行数据，具体取决于第一行。
$uploadedfile = \PHPExcel_IOFactory::load(Yii::getAlias('uploads').'/'.$file_location);
$uploadeddata = $uploadedfile->getActiveSheet()->toArray(null, true, true, true);
//we need to first know how many rows actually have data
//my first two rows have column labels, so i start with the third row.
$row_count = 3;
// read through the data and see how many rows actually have data
//the idea is that for every row, the first cell should be mandatory...
//if we find one that is not, we stop there...
do
{
$row_count++;
} while($uploadeddata[$row_count]['A'] == "null");
//get the actual number of rows with data, removing the column labels
$actual_rows = $row_count-3;
回答by vijay kanaujia
$spreadsheet = new Spreadsheet();
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('xls');
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('file location here');
$worksheet = $spreadsheet->getActiveSheet();
foreach ($worksheet->getRowIterator() AS $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
$cells = [];
foreach ($cellIterator as $cell) {
$cells[] = $cell->getValue();
}
$rows[] = $cells;
}
//blade html
<table class="table table-hover">
<thead>
<tr>
@foreach($rows[0] as $heading)
<th>{{$heading}}</th>
@endforeach
</tr>
</thead>
<tbody>
@foreach($rows as $key=>$val)
@if(!($key == 0))
<tr>
@foreach($val as $value)
<td>{{$value}}</td>
@endforeach
</tr>
@endif
@endforeach
</tbody>
</table>
```
