PHP Excel類讀取excel檔案並且匯入資料庫_PHP教程

在一般情況下如果我們要把excel資料庫中的資料匯入到mysql資料庫我們沒什麼好辦法實現了，但有了PHP Excel失控這後一切都變得簡單了。

本代碼是在thinkphp中開始使用的

1.引入類

代碼如下

複製代碼

Vendor('PHPExcel.PHPExcel');//引入擴充類.就是/ Vendor('PHPExcel.PHPExcel.IOFactory'); Vendor('PHPExcel.PHPExcel.Reader.Excel5'); $excel_file= ROOT_PATH."/public/Uploads/".$publicity_bankdata_mod->where("id=".$data['id'])->getField('excel_file'); //dump($excel_file);exit; $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format $objPHPExcel = $objReader->load($excel_file);//$uploadfile $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得總行數 $highestColumn = $sheet->getHighestColumn(); // 取得總列數 $arr_result = array(); $strs=array(); $strs_datas=array(); $succ_result=0; $error_result=0;

上面看上去有點亂，下面我來寫一個完整的類

代碼如下

複製代碼

set_time_limit(20000); ini_set('memory_limit','-1'); require_once './PHPExcel.php'; require_once './PHPExcel/IOFactory.php'; require_once './PHPExcel/Reader/Excel5.php'; //使用pdo串連資料庫 $dsn = "mysql:host=localhost;dbname=alumni;"; $user = "root"; $password = ""; try{ $dbh = new PDO($dsn,$user,$password); $dbh->query('set names utf8;'); }catch(PDOException $e){ echo "串連失敗".$e->getMessage(); } //pdo綁定參數操作 $stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) "); $stmt->bindParam(":gid", $gid,PDO::PARAM_STR); $stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR); $stmt->bindParam(":name", $name,PDO::PARAM_STR); $objReader = new PHPExcel_Reader_Excel5(); //use excel2007 $objPHPExcel = $objReader->load('bks.xls'); //指定的檔案 $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得總行數 $highestColumn = $sheet->getHighestColumn(); // 取得總列數 for($j=1;$j<=10;$j++) { $student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列學號 $name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名 $gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid } //將擷取的excel內容插入到資料庫 $stmt->execute(); ?>

php-excel-reader操作excel中的兩個重要的方法：

1.dump(),它可以將excel內容以html格式輸出：

echo $data->dump(true,true);

2.將excel資料存入數組中，使用$data->sheets，列印下如下：

代碼如下

複製代碼

Array ( [0] => Array ( [maxrow] => 0 [maxcol] => 0 [numRows] => 5 [numCols] => 4 [cells] => Array ( [1] => Array ( [1] => 編號 [2] => 姓名 [3] => 年齡 [4] => 學號 ) [2] => Array ( [1] => 1 [2] => 小紅 [3] => 22 [4] => a1000 ) [3] => Array ( [1] => 2 [2] => 小王 [3] => 33 [4] => a1001 ) [4] => Array ( [1] => 3 [2] => 小黑 [3] => 44 [4] => a1002 ) [5] => Array ( [2] => by [3] => www.phpddt.com ) ) [cellsInfo] => Array ( [1] => Array ( [1] => Array ( [xfIndex] => 15 ) [2] => Array ( [xfIndex] => 15 ) [3] => Array ( [xfIndex] => 15 ) [4] => Array ( [xfIndex] => 15 ) ) [2] => Array ( [1] => Array ( [string] => 1 [raw] => 1 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15 ) [2] => Array ( [xfIndex] => 15 ) [3] => Array ( [string] => 22 [raw] => 22 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15 ) [4] => Array ( [xfIndex] => 15 ) ) [3] => Array ( [1] => Array ( [string] => 2 [raw] => 2 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 6 [formatColor] => [xfIndex] => 23 ) [2] => Array ( [xfIndex] => 23 ) [3] => Array ( [string] => 33 [raw] => 33 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 6 [formatColor] => [xfIndex] => 23 ) [4] => Array ( [xfIndex] => 23 ) ) [4] => Array ( [1] => Array ( [string] => 3 [raw] => 3 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15 ) [2] => Array ( [xfIndex] => 15 ) [3] => Array ( [string] => 44 [raw] => 44 [rectype] => unknown [format] => %s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15 ) [4] => Array ( [xfIndex] => 15 ) ) [5] => Array ( [2] => Array ( [xfIndex] => 15 ) [3] => Array ( [xfIndex] => 24 [hyperlink] => Array ( [flags] => 23 [desc] => www.bKjia.c0m [link] => http://www.phpddt.co ) ) ) ) ) [1] => Array ( [maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0 ) [2] => Array ( [maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0 ) )

http://www.bkjia.com/PHPjc/630697.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/630697.htmlTechArticle在一般情況下如果我們要把excel資料庫中的資料匯入到mysql資料庫我們沒什麼好辦法實現了，但有了PHP Excel失控這後一切都變得簡單了。 本代...

