喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(上版面)
www.000webhost.com
喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(左版面)
首  頁 最新消息 遊戲攻略 遊戲影片 遊戲圖片 下載區 學習分享 留言板 FB粉絲團
 喵喵笨兔 の 喵喵的家~遊戲天地 - Excel 檔直接匯入 MySQL 資料庫:不用轉檔、不會中文和日韓文亂碼...

Excel 檔直接匯入 MySQL 資料庫:不用轉檔、不會中文和日韓文亂碼...

Excel 檔直接匯入 MySQL 資料庫 

  • 一般 Excel 轉 CSV 檔再匯入MySQL 若發生中文亂碼,可用筆記本 (notepad) 開啟 CSV 另存時,編碼選 UTF-8 即可解決。但若有日文、韓文....等其它國語言,則一樣是亂碼。笨兔編修的此轉換程式,只要是 Excel 內可看到的語言(日文、韓文....),全都可完整的匯入資料庫。
  • 此是配合 PHPExcel 來修改編寫的。
    phpexcel 官網:https://phpexcel.codeplex.com/
    phpexcel 下載:https://github.com/PHPOffice/PHPExcel
    本小站下載 PHPExcel

  • 本站下載內含笨兔寫好的二個轉換程式 test.php (自動建立資料表及欄位)、test1.php (須先自行建立資料表及欄位) 和 phpexcel 目錄,把它放在您本機架站目錄內 (若要放在雲端伺服器使用,那就自行去修改程式碼囉!)。 如笨兔放在本機 MySQL 架站的 C:\AppServ\www\MyCollections\ 內。

  • 輸入 http://localhost/mycollectionphp/test.php 和http://localhost/mycollectionphp/test1.php 即可開啟使用。
test.php 範例程式碼:(自動建立資料表及欄位)
  • Excel 檔名即為 MySQL 資料庫內的資料表名稱。請勿用中文檔名。
  • Excel 內第一行各欄位名稱,即為資料表的欄位名稱,預設皆為文字 (text) 型態。若須更改再自行進 MySQL 更改。
  • 高手所寫的 PHPExcel 主要是以 office 2007 為主,所以儘量用 .xls 來匯入。其實用.xlsx 也可以完整的成功匯入,只是會出現幾行錯誤警告訊息。
  • 資料表若存在,會出現警告訊息。但只要欄位名稱都相符,也可成功匯入。
  • 同檔案重覆匯入,或不同檔案但欄位名稱皆相同匯入,則會在最後一筆之後匯入,筆數因此會累增。
  • 詳情看程式碼內說明,須修改或注意的為紅色字部份,只要更改您的資料庫資料即可。範例設定的 MySQL 相關名稱如下。MySQL 連線部份,可參考笨兔另一篇隨手筆記【db_002】PHP 連接 MySQL 資料庫
    • 伺服器名稱: localhost
    • 使用者帳號: root
    • 使用者密碼: 12345678
    • 資料庫名稱: my_collection
<?php 
$dbhost = "localhost"; //改成您的資料庫資料 $dbuser = "root"; $dbpass = "12345678"; $dbname = "my_collection"; $conn=mysql_connect($dbhost,$dbuser,$dbpass) or die('Error with MySQL connect'); mysql_query("SET NAMES 'UTF8'"); mysql_select_db($dbname); require_once 'PHPExcel/Classes/PHPExcel.php'; //必導入的PHPExel if($_GET['excelfile']!=NULL){ $file=$_GET['excelfile']; //完整路徑檔名 $pos=mb_strrpos($file,"\\"); //路徑最後出現\的位置 $pos2=mb_strrpos($file,"."); //路徑最後出現.的位置 $len=mb_strlen($file); $fileName=mb_substr($file,$pos+1,($pos2-$pos-1)); //擷取檔案名(不含副檔名) $fileName1=mb_substr($file,$pos+1); //擷取檔案名(含副檔名) try { $objPHPExcel = PHPExcel_IOFactory::load($file); } catch(Exception $e) { die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage()); } $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); foreach($sheetData as $key => $col) { if ($key != 0){ //0表示從Excel第一行開始讀取,1從第二行 $a = array(); foreach ($col as $colkey => $colvalue) { array_push($a,$colvalue); } //讀取第1行,把它拿來當資料表的欄位名,預設都為文字text型態 if($key=='1'){ $i=count($a); //計算有幾欄 for($j=0;$i>$j;$j++){ if($j==0){ $table_col="`".$a[$j]."` text"; //第1欄不加,分隔 $table_col1="`".$a[$j]."`"; }else{ $table_col=$table_col.",`".$a[$j]."` text"; //第2欄後都加,分隔 $table_col1=$table_col1.",`".$a[$j]."`"; //此是用來寫入資料用的欄位 } } //建資料表 $sql = 'CREATE TABLE `'.$fileName.'` ('.$table_col.')'; //若資料表已存在也沒關係,只是每個欄位名稱要和您設在EXCEL第一行的一樣,才能成功寫入資料 if (mysql_query($sql)){ echo '資料表 '.$fileName.' 建立成功'; }else { echo '<font color="red">資料表 '.$fileName.' 建立失敗 (可能已存在了!)</font>'; } }else{ $i=count($a); for($j=0;$i>$j;$j++){ //讀取每行各欄位資料 if($j==0){ $table_data="'".addslashes($a[$j])."'"; //第1欄不加,分隔 }else{ $table_data=$table_data.",'".addslashes($a[$j])."'"; //第2欄後都加,分隔 } } $sql ="INSERT INTO `".$fileName."` (".$table_col1.") VALUES (".$table_data.")"; $result = mysql_query($sql) or die(mysql_error()); } } } } ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>喵喵笨兔 - Excel 檔直接匯入 MySQL </title> </head> <body> <h3 align="center">喵喵笨兔 - Excel 檔直接匯入 MySQL (自動建立資料表及欄位)</h3> <div> <form method="get"> <label for="fileField">要轉換的 EXCEL 檔 (勿用中文檔名):</label> <input type="file" name="excelfile" id="excelfile"> <input type="submit" name="submit" id="submit" value="開始匯入"> </form> </div> </body> </html>

【範例結果】

  • 以有日韓文內容的 abc.xls 為例,第一行先輸入要成為資料表的欄位名。
  • 直接將路徑指向您電腦內要匯入 MySQL 的 Excel 檔,再按開始匯入即可完成。


test1.php 範例程式碼:(須先在 MySQL 內自行建立資料表及欄位)