Tp6之PhpspreadSheet学习（3）导入Excel导入mysql数据库

Thinkphp6之PhpspreadSheet学习（3）导入Excel导入mysql数据库

使用PhpSpreadsheet将Excel导入到MySQL数据库

一.在数据库中建立数据表

首先我们需要准备一张MySQL表，表名t_student，表结构如下：

CREATE TABLE `t_student` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(32) NOT NULL COMMENT '姓名',

`chinese` int(6) NOT NULL DEFAULT '0' COMMENT '语文',

`maths` int(6) NOT NULL DEFAULT '0' COMMENT '数学',

`english` int(6) NOT NULL DEFAULT '0' COMMENT '外语',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

二. 准备一个Excel文件表，放在public目录中

三.控制器中的代码如下：

public function Exceltomysql(){

$obj = IOFactory::createReader('Xlsx');

$obj -> setReadDataOnly(true);

$spreadsheet = $obj -> load('student.xlsx');//

$sheet = $spreadsheet->getActiveSheet();

# 获取总列数

$highestColumn = $sheet->getHighestColumn();

# 获取总行数

$highestRow = $sheet->getHighestRow();

# 列数 改为数字显示

$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

//echo "行数".$highestRow."列数".$highestColumnIndex;

$log = [];

for($a=2;$a<$highestRow;$a++){

$name = $sheet->getCellByColumnAndRow(1,$a)->getValue();

$chinese = $sheet->getCellByColumnAndRow(2,$a)->getValue();

$maths = $sheet->getCellByColumnAndRow(3,$a)->getValue();

$english = $sheet->getCellByColumnAndRow(4,$a)->getValue();

$data = [

'name' => $name,

'chinese' => $chinese,

'maths' => $maths,

'english' => $english

];

$res = Db::table('t_student')->insert($data);

if($res){

$log[] = '第'.$a.'条，插入成功';

}else{

$log[] = '第'.$a.'条，插入失败';

}

}

//echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);

dump($log);

}

四.运行成功返回如下

===今天学习到此===