php 如何使用 PHPExcel 读取 xlsx 文件的第二页?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/4170275/
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 do I read sheet two of an xlsx file with PHPExcel?
提问by Tom
I know how to read my xlsx spreadsheet and loop through the first sheet.
我知道如何阅读我的 xlsx 电子表格并循环浏览第一张表格。
It has 5 sheets and I am having trouble getting to any other than the first.
它有 5 张纸,除了第一张之外,我无法找到任何一张纸。
Here is the code I am using which was straight from the documentation.
You can see I tried to utilize setActiveSheet, but that threw the error Call to undefined method PHPExcel::setActiveSheet()
.
这是我正在使用的直接来自文档的代码。您可以看到我尝试使用 setActiveSheet,但这引发了错误Call to undefined method PHPExcel::setActiveSheet()
。
Code:
代码:
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("cmt_school_data.xlsx");
//$objPHPExcel->setActiveSheet(1);
$objWorksheet = $objPHPExcel->getActiveSheet();
echo '<table border=1>' . "\n";
foreach ($objWorksheet->getRowIterator() as $row) {
echo '<tr>' . "\n";
$cellIterator = $row->getCellIterator();
// This loops all cells, even if it is not set.
// By default, only cells that are set will be iterated.
$cellIterator->setIterateOnlyExistingCells(false);
foreach ($cellIterator as $cell) {
echo '<td>' . $cell->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";
}
echo '</table>' . "\n";
回答by Tom
Ok...the names are deceiving. setActiveSheetIndex also does a get so the solution was this
好吧……名字是骗人的。setActiveSheetIndex 也做了一个 get 所以解决方案是这样的
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("cmt_school_data.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex(1);
//objWorksheet = $objPHPExcel->getActiveSheet();
echo '<table border=1>' . "\n";
foreach ($objWorksheet->getRowIterator() as $row) {
echo '<tr>' . "\n";
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
// even if it is not set.
// By default, only cells
// that are set will be
// iterated.
foreach ($cellIterator as $cell) {
echo '<td>' . $cell->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";
}
echo '</table>' . "\n";
回答by Thiwanka
<?php
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$inputFileType = 'Excel5';
// $inputFileType = 'Excel2007';
// $inputFileType = 'Excel2003XML';
// $inputFileType = 'OOCalc';
// $inputFileType = 'Gnumeric';
$inputFileName = './sampleData/example1.xls';
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
echo 'Loading all WorkSheets<br />';
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($inputFileName);
echo '<hr />';
echo $objPHPExcel->getSheetCount(),' worksheet',(($objPHPExcel->getSheetCount() == 1) ? '' : 's'),' loaded<br /><br />';
$loadedSheetNames = $objPHPExcel->getSheetNames();
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
echo **$sheetIndex**,' -> ',$loadedSheetName,'<br />';
$sheetData = $objPHPExcel->**getSheet**(**$sheetIndex**)->toArray(null,true,true,true);
var_dump($sheetData);
}?>
回答by Vir
I know it's been too late for the answer. But I find solution as below.
我知道答案已经太晚了。但我找到如下解决方案。
//load library - EXCEL
$this->load->library('excel');
$objPHPExcel = PHPExcel_IOFactory::load('./folder/exceldata.xls');
//Get How Many Sheets in your Excel file.
echo $objPHPExcel->getSheetCount();
Then, to access a sheet by its index, use the getSheet() method. Note that sheets are indexed from 0. e.g.
然后,要按索引访问工作表,请使用 getSheet() 方法。请注意,工作表从 0 开始索引。例如
//Retrieve the **1st 'tab' worksheet** e.g. called 'Sheet 1'
$worksheet = $objPHPExcel->getSheet(0);
So in your case, if you want to read only Sheet-2 then,
因此,在您的情况下,如果您只想阅读 Sheet-2,那么
$worksheet = $objPHPExcel->getSheet(1);
OR to read all Sheets from your Excel file, you have to use foreach loop as below.
或者要从 Excel 文件中读取所有工作表,您必须使用 foreach 循环,如下所示。
foreach($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
//echo $highestRow;
//echo $highestColumn;
//die();
for($row=2; $row<=$highestRow; $row++)
{
$column1 = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
$column2= $worksheet->getCellByColumnAndRow(1, $row)->getValue();
$finaldata[] = array(
'data1' => trim($column1),
'data2' => trim($column2),
);
}
}
/*echo "<pre>";
echo count($finaldata);
print_r($finaldata);
die();*/