php导入excel到mysql的方法

1、下载PHPExcel，解压到目录PHPExcel。

2、引用连接数据库文件和PHPExcel文件，在php文件上面加上代码include("conn.php"); require_once './PHPExcel/PHPExcel.php'; require_once './PHPExcel/PHPExcel/IOFactory.php'; require_once './PHPExcel/PHPExcel/Reader/Excel5.php';

3、这里是导入excel2007 的xlsx格式，如果是2003格式可以把“excel2007”换成“Excel5",$excelpath是你的excel路径。$objReader = PHPExcel_IOFactory::createReader('excel2007'); //use Excel5 for 2003 format $excelpath='myexcel.xlsx'; $objPHPExcel = $objReader->load($excelpath); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); //取得总行数 $highestColumn = $sheet->getHighestColumn(); //取得总列数

4、逐行循环读取excel，并加入分隔腿发滏呸符。for($j=2;$j<=$highestRow;$j++) //从第二行开始读取数据 { $str=&鳎溻趄酃quot;"; for($k='A';$k<=$highestColumn;$k++) //从A列读取数据 { $str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'|*|';//读取单元格 } $str=mb_convert_encoding($str,'GBK','auto');//根据自己编码修改 $strs = explode("|*|",$str); // echo $str . "<br />"; $sql = "insert into test (title,content,sn,num) values ('{$strs[0]}','{$strs[1]}','{$strs[2]}','{$strs[3]}')"; //echo $sql; if(!mysql_query($sql,$conn)) { echo 'excel err'; } }

5、数据库连接文件conn.php，根据自己数据库填写<?php$conn=mysql_connect("localhost","root","123456") or die("数据库服务器连接错误".mysql_error()); mysql_select_db("temp",$conn) or die("数据库访问错误".mysql_error()); mysql_query("set character set gb2312"); mysql_query("set names gb2312");?>

6、导入效果图

7、全部代码，注释部分可以调试<?php include("conn.php"); require_once './PHPExce造婷用痃l/PHPExcel.php'; require_once './PHPExcel/PHPExcel/IOFactory.php'; require_once './PHPExcel/PHPExcel/Reader/Excel5.php'; $objReader = PHPExcel_IOFactory::createReader('excel2007'); //use Excel5 for 2003 format $excelpath='myexcel.xlsx'; $objPHPExcel = $objReader->load($excelpath); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); //取得总行数 $highestColumn = $sheet->getHighestColumn(); //取得总列数 for($j=2;$j<=$highestRow;$j++) //从第二行开始读取数据 { $str=""; for($k='A';$k<=$highestColumn;$k++) //从A列读取数据 { $str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'|*|';//读取单元格 } $str=mb_convert_encoding($str,'GBK','auto');//根据自己编码修改 $strs = explode("|*|",$str); //echo $str . "<br />"; //exit; $sql = "insert into test (title,content,sn,num) values ('{$strs[0]}','{$strs[1]}','{$strs[2]}','{$strs[3]}')"; //echo $sql; //exit; if(!mysql_query($sql,$conn)) { echo 'excel err'; } } ?>