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 = PhpOfficePhpSpreadsheetCellCoordinate::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);
}
四.运行成功返回如下
===今天学习到此===
