文章中心

php导入excel表到mysql数据库方法

类别:网站建设 看过:636

我们使用PhpSpreadsheet来实现导入execl表到mysql数据库,注意:PhpSpreadsheet使用要求php的版本在7.1以上。

PhpSpreadsheet来实现导入execl表到mysql数据库代码如下:

一、数据表结构

-- 表的结构 `t_student`

CREATE TABLE IF NOT EXISTS `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  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

二、数据库连接(conn.php)

<?php 
define('DBHOST', 'localhost');
define('DBNAME', 'demo_execl');
define('DBUSER', 'root');
define('DBPWD', '');
define('DBPREFIX', 'hw_');
define('DBCHARSET', 'utf8');
define('CONN', '');
define('TIMEZONE', 'Asia/Shanghai');

try{
    $db = new PDO('mysql:host='.DBHOST.';dbname='.DBNAME, DBUSER, DBPWD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->query('SET NAMES utf8;');
}catch(PDOException  $e ){
    echo '{"result":"failed", "msg":"连接数据库失败!"}';
    exit;
}
?>
三、执行导入数据库(import.php)
<?php 
require 'vendor/autoload.php';

include('conn.php');

$reader = PhpOfficePhpSpreadsheetIOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('students.xlsx');

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = PhpOfficePhpSpreadsheetCellCoordinate::columnIndexFromString($highestColumn); 

$lines = $highestRow - 2; 
if ($lines <= 0) {
    exit('Excel表数据为空');
}

$sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES ";

for ($row = 3; $row <= $highestRow; ++$row) {
    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
    $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文
    $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学
    $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语

    $sql .= "('$name','$chinese','$maths','$english'),";
}
$sql = rtrim($sql, ","); //去掉最后一个,号
try {
    $db->query($sql);
    echo 'OK';
} catch (Exception $e) {
    echo $e->getMessage();
}
?>

上篇:什么是锚文本?百度如何判断锚文本的质量?

下篇:php导出excel表方法

请长按下方链接进行复制发给好友~
http://www.yidongseo.com/mobile/article_view_2_146.html
确定