喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(上版面)
喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(左版面)
首　　頁 最新消息 遊戲攻略 遊戲影片 遊戲圖片 下載區 學習分享 留言板 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 內自行建立資料表及欄位)
  • 這須要自行先在 MySQL 內建立資料表及欄位，並且要進程式碼內修改 Excel 檔名，及更改對應的資料表名、欄位。直接看程式碼吧！笨兔有附加說明，有興趣者自行參研唄！須修改或注意的為紅色字部份。
    • <?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

//須先在您的MySQL建立對應的資料表及欄位名
//改您要匯入的完整路徑檔案名稱 (放在同目錄內)
$file = "abc.xls";
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);
        }
			//匯入每筆資料,此例是四個欄位(由0開始)，若三欄位就留三行,
			//若五行則數字漸增加入如 $a4=addslashes($a[4]); 以此類推
			$a0=addslashes($a[0]);
			$a1=addslashes($a[1]);
			$a2=addslashes($a[2]);
			$a3=addslashes($a[3]);

			//INTO 後面改您的建立好的資料表名稱,() 內改您的資料表欄位名稱
			//VALUES() 內加入上面您抓取幾個欄位代號
			$sql =("INSERT INTO 資料表名稱 (`欄位名1`,`欄位名2`,`欄位名3`,`欄位名4`) VALUES ('$a0','$a1','$a2','$a3')");
			$result = mysql_query($sql) or die(mysql_error());	
		}
    }
?>
    笨兔隨手筆記於 2017/12/03

    喵喵笨兔 の 喵喵的家~遊戲天地
    http://vv0817.tk
    http://vvv.lionfree.net
    http://qo3op.asuscomm.com/vvv
    https://uoedgpkhyfwzmd7zua8myg-on.drv.tw/vvv