安装库
composer require phpoffice/phpspreadsheet
经测试php7.4.3要安装1.28版本
composer require phpoffice/phpspreadsheet:1.28
引入对应的类
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Shared\Date;
本文隐藏内容 - 星球会员可见
编写方法
protected function readExcel($file)
{
// 类型
$allows = [
"application/vnd.ms-excel",
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
// 'text/csv'
];
if(!in_array($file["type"],$allows)){
return $file["type"].' 格式不允许';
}
try {
$filePath = $file["tmp_name"];
if($file["type"] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'){
$reader = new Xlsx();
}
else if($file["type"] == "application/vnd.ms-excel"){
$reader = new Xls();
}
$PHPExcel = $reader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
$timeIndex = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$cell = $currentSheet->getCellByColumnAndRow($currentColumn, 1);
$val = $cell->getValue();
if($val == '生日'){
$timeIndex[] = $currentColumn;
}
$fields[] = $val;
}
$datas = [];
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
if(in_array($currentColumn,$timeIndex)){
$dateTimeObject = Date::excelToDateTimeObject($val);
$val = $dateTimeObject->format('Y-m-d');
}
$values[] = $val;
}
$datas[] = array_combine($fields, $values);
}
return $datas;
} catch (\Exception $e) {
return "读取Excel文件时发生错误: " . $e->getMessage();
}
}
接收文件并写入数据库
public function uploadExcel()
{
if(isset($_FILES["file"]) && $_FILES["file"]["error"] == 0){
$file = $_FILES["file"];
$excelData = $this->readExcel($file);
if(is_string($excelData)){
return $this->error($excelData);
}
$datas = $excelData;
$saveDatas = [];
foreach ($datas as $value) {
$saveDatas[] = [
"number"=>$value["学号"],
"name"=>$value["姓名"],
"birthday"=>$value["生日"],
"stuclass_id"=>$value["班级id"],
"gender"=>$value["性别"]=="男"?1:2,
];
}
$res = Db::name("students")->insertAll($saveDatas);
if($res){
return $this->success('ok',$res);
}
else{
return $this->error('导入失败');
}
return $this->success('ok');
}
}
注意:
这个方法在新版本里弃用了:
->getCellByColumnAndRow($i,$j)
可换成下面这种方法:
->getCell(Coordinate::stringFromColumnIndex($i) .$j)
下面是借用layui的upload来对接
1.添加按钮
<button type="button" class="layui-btn demo-class-accept" lay-options="{accept: 'file',acceptMime:'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',exts: 'xlsx|xls'}">
<i class="layui-icon layui-icon-upload"></i>
上传文件
</button>
js方法:
var upload = layui.upload;
upload.render({
elem: '.demo-class-accept', // 绑定多个元素
url: '/api_admin/uploadExcel', // 此处配置你自己的上传接口即可
accept: 'file', // 普通文件
done: function(res){
layer.msg('上传成功');
console.log(res);
}
});
Comments