php 在 codeigniter 中读取 .xlsx 和 .xls 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32305410/
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
Read .xlsx and .xls data in codeigniter
提问by David Coder
I want to read the data of .xlsx or .xls file in codeigniter. I have read the other questions related it but nothing works. I have used phpexcel, reader but with no luck. In my project i give the option to upload excel file then i want to read the data and insert it in the database.
我想在 codeigniter 中读取 .xlsx 或 .xls 文件的数据。我已经阅读了与它相关的其他问题，但没有任何效果。我使用过 phpexcel, reader 但没有运气。在我的项目中，我提供了上传 excel 文件的选项，然后我想读取数据并将其插入到数据库中。
Now i am using phpExcel library I have wrote:
现在我正在使用我写过的 phpExcel 库：
$this->load->library('excel');
$reader= PHPExcel_IOFactory::createReader('Excel2007');
$reader->setReadDataOnly(true);
$path=(FCPATH.'uploads/productfile/'.$_FILES['upload_file']['name']);
$excel=$reader->load($path);
$sheet=$excel->setActiveSheetIndex(0);
for($i=0;$i<=1000;$i++)
{
$col1= $sheet->getCellByColumnAndRow(0,$i)->getValue();
$col2= $sheet->getCellByColumnAndRow(1,$i)->getValue();
$col3= $sheet->getCellByColumnAndRow(2,$i)->getValue();
var_dump($col1);
}
but it display :
但它显示：
Uncaught exception 'PHPExcel_Exception' with message 'You tried to set a sheet active by the out of bounds index: 0. The actual number of sheets is 0 Please give me some example code.
未捕获的异常“PHPExcel_Exception”带有消息“您试图通过越界索引将工作表设置为活动状态：0。实际工作表数为 0 请给我一些示例代码。
The error
Please give me some example code:
错误请给我一些示例代码：
采纳答案by David Coder
Thanks to allfor your suggestion: I got the solution :
感谢大家的建议： 我得到了解决方案：
$file_data = $this->upload->data();
$file_path = './uploads/productfile/'.$file_data['file_name'];
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = $file_path;
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet); // Here get total count of row in that Excel sheet
for($i=2;$i<=$arrayCount;$i++)
{
'product'=$allDataInSheet[$i]["C"],
'brand'=$allDataInSheet[$i]["I"],
'standard'=$allDataInSheet[$i]["J"],
}
回答by KSP
Try this :
尝试这个 ：
$sheet = $excel->getActiveSheet()->toArray(null,true,true,true);
This will return you an array of the current active sheet.Hope this helps.
这将返回当前活动工作表的数组。希望这会有所帮助。
回答by Lelio Faieta
This error is caused by a wrong initialization of the PHPexcel reader functionalities I suppose. I usually do something like this to get the data from an excel:
我想这个错误是由 PHPexcel 阅读器功能的错误初始化引起的。我通常会做这样的事情来从 excel 中获取数据：
require_once '../Classes/PHPExcel/IOFactory.php';
$filename = '../uploads/product/abc.xls';
$objPHPExcel = PHPExcel_IOFactory::load($filename);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$total_rows = $highestRow-1;
for ($row = 2; $row <= $highestRow; ++ $row) {
//id
$cell = $worksheet->getCellByColumnAndRow(0,$row);
$id = $cell->getValue();
if(is_null($id)){$id = '#';}
}
回答by Lovepreet Singh
This extension of PHPExcel_IOFactory has issues with symbols like ? Trademark, Copy Right, Degree etc.
PHPExcel_IOFactory 的这个扩展有符号问题，比如？商标、版权、学位等
You can't read a particular block if it contains such special characters.
如果包含此类特殊字符，则无法读取特定块。
回答by user3444748
This code segment works for me,
这个代码段对我有用，
$this->load->library('excel');
$reader= PHPExcel_IOFactory::createReader('Excel2007');
$reader->setReadDataOnly(true);
$path= "./media/customer_exports/data-1558003506.xlsx";
$excel=$reader->load($path);
$sheet = $excel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($sheet);
for($i=2;$i<=$arrayCount;$i++)
{
echo $sheet[$i]["A"].$sheet[$i]["B"].$sheet[$i]["C"];
}
