-
1. 下载依赖:https://github.com/PHPOffice/PHPExcel ;
或使用命令安装:
composer require phpoffice/phpexcel
2. 下载完成后,将PHPExcel文件夹放在laravel的vendor目录下;
更改支持PHP7.3语法:
将continue改成break
3. 在你需要使用的控制器引用:
use PHPExcel_IOFactory;
use PHPExcel;
4. 例子:读取Excel:
封装(为了防止Class 'PHPExcel_IOFactory' not found 报错,以下使用两种引入):
<?php
namespace App\Http\Kit;
use PHPExcel_IOFactory;
use PHPExcel;
use Exception;
if (is_file(path_info()['base_path'].'/vendor/phpexcel/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php')){
require_once path_info()['base_path'].'/vendor/phpexcel/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
}else{
exit('文件不存在。');
}
class Excel {
// 读取excel
// 最多只能读10w+1行
// $excel_file是excel文件的绝对路径;$start_row是起始行,默认2;$end_row是截至行,默认最大行
public function excel_to_array($excel_file=null, $start_row = null, $end_row = null){
$test_data = [
'excel_file'=> $excel_file,
'start_row'=> $start_row,
'end_row'=> $end_row,
];
if (!$excel_file){
$state = 0;
$msg = 'Excel路径参数不全';
$content = [];
}else{
//加载excel文件
$filename = $excel_file;
$objPHPExcelReader = PHPExcel_IOFactory::load($filename);
$sheet = $objPHPExcelReader->getSheet(0); // 读取第一个工作表(编号从 0 开始,sheet)
if (!$end_row){
$highestRow = $sheet->getHighestRow(); // 取得最大行数
}else{
$highestRow = $end_row;
}
$highestColumn = $sheet->getHighestColumn(); // 取得最大列数
if ($highestColumn > 100001){
$state = 0;
$msg = '最大行数100001行,如有超过,请切割文件';
$content = [];
}else{
$arr = ['A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z']; // 目标列标号
if (!$start_row){ // 默认从第二行读
$start_row = 2;
}
$res_arr = [];
for ($row = $start_row; $row <= $highestRow; $row++) { // 一次读取一列
$row_arr = [];
for ($column = 0; $arr[$column] != 'F'; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
$row_arr[] = $val;
}
$res_arr[] = $row_arr;
}
$state = 1;
$msg = 'Excel读取完成';
$content = $res_arr;
}
}
return [
'state'=> $state,
'msg'=> $msg,
'test_data'=> $test_data,
'content'=> $content,
];
}
public function __call($func_name, $args){
$txt = "class:".__CLASS__." ,函数不存在:$func_name ,参数:$args ";
exit($txt);
}
}
调用excel_to_array($excel_file)即可,例如:
$excel_file = '/home/wwwroot/default/xxxxxx/vendor/phpexcel/Excel/1.xlsx';
$array = $excel->excel_to_array($excel_file);
var_dump($array);
结果:
-
-