php 导入excel文件mysql数据库方法
应该说介绍了利用phpexcel插件来实现数据库的导入与导入功能,本文章主要是告诉你把excel导入到mysql数据库的方法.
先下载,下载phpexcel文件,地址：phpexcel.codeplex测试数据/
在reader.php文件中找到以下类似代码,第一行既是,改成正确的oleread.php路径即可:require_once 'oleread.php';然后新建一个php文件引入reader.php,代码如下:
<?php require_once 'Excel/reader.php' ; $data = new Spreadsheet_Excel_Reader(); $data ->setOutputEncoding( 'gbk' ); //此处设置编码，一般都是gbk模式 $data ->read( 'Book1.xls' ); //文件路径 error_reporting (E_ALL ^ E_NOTICE); //这里我就只循环输出excel文件的内容了，要入库，只要把输出的地方，写一段mysql语句即可~ for ( $i = 1; $i <= $data ->sheets[0][ 'numRows' ]; $i ++) { for ( $j = 1; $j <= $data ->sheets[0][ 'numCols' ]; $j ++) { echo "" ".$data->sheets[0]['cells'][$i][$j]." "," ; } echo "n" ; } ?>代码示例如下:
require_once 'phpexcel/Classes/PHPExcel.php' ; require_once 'phpexcel/Classes/PHPExcel/IOFactory.php' ; require_once 'phpexcel/Classes/PHPExcel/Reader/Excel5.php' ; $objReader = PHPExcel_IOFactory::createReader( 'Excel5' ); //use excel2007 for 2007 format $objPHPExcel = $objReader ->load( $filename ); //$filename可以是上传的文件，或者是指定的文件 $sheet = $objPHPExcel ->getSheet(0); $highestRow = $sheet ->getHighestRow(); // 取得总行数 $highestColumn = $sheet ->getHighestColumn(); // 取得总列数 $k = 0; //循环读取excel文件,读取一条,插入一条 for ( $j =2; $j <= $highestRow ; $j ++) { $a = $objPHPExcel ->getActiveSheet()->getCell( "A" . $j )->getValue(); //获取A列的值 $b = $objPHPExcel ->getActiveSheet()->getCell( "B" . $j )->getValue(); //获取B列的值 $sql = "INSERT INTO table VALUES(" . $a . "," . $b . ")" ; mysql_query( $sql ); }代码实例cvs导入到数据库,把csv导入到数据库,代码如下:
function getmicrotime(){ list( $usec , $sec ) = explode ( " " ,microtime()); return ((float) $usec + (float) $sec ); } $time_start = getmicrotime(); include ( "connectdb.php" ); function insert_data ( $id , $summary , $description , $additional_information , $category ) { $my_query1 = "insert into mantis_bug_text_table (id,description,additional_information) values ( '$id' , '$description' , '$additional_information' )"; $first = mysql_query( $my_query1 ); $my_query2 = "insert into mantis_bug_table (id,project_id,summary,bug_text_id) values ('$id','$category','$summary','$id')" ; $second = mysql_query( $my_query2 ); return ; } $fp = fopen ( "test.csv" , "r" ); while ( $data = fgetcsv ( $fp , '1000' , ',' )){ //开源代码phpfensi测试数据 insert_data ( $data [0], $data [1], $data [2], $data [3], $data [4]); echo "<font color = #ff0000 size = 20>数据导入成功！</font><br><br>" ; } fclose ( $fp ); $time_end = getmicrotime(); $time = $time_end - $time_start ; echo "程序执行时间：" . $time . "秒" ;查看更多关于php 导入excel文件mysql数据库方法 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did30449