1.首先准备第三方类库PHPExecl;
安装命令:
composer require phpoffice/phpexcel
2.php代码(原生)
<?php
// 假定数据库用户名:root,密码:root,数据库:test_execl
$con = mysqli_connect("127.0.0.1","root","root","test_execl") or die(mysqli_connect_error($con));
mysqli_query($con,"set names 'utf8'") or die(mysqli_error($con));//设定mysql编码
header('Content-Type:text/html;charset=utf8');
$dir = dirname(__FILE__);
require_once './vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
require_once './vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';
require_once './vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php';
$filename = $dir.'/123.xlsx'; //本地文件
$objPHPExcel = PHPExcel_IOFactory::load($filename);//记载文件
$sheetCount = $objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
for($i=0; $i<$sheetCount; $i++){
$data = $objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据全部放到数组中
}
if (empty($data))
{
die('数据为空');
}
unset($data[0]);//去掉标题
foreach ($data as $key => $val)
{
//这里可以打印出数据,观察格式并导入数据库
}
mysqli_close($con);