這個增益集是多年前自己練習增益集設計與PHP程式的作品，以前要將Excel資料轉移至MySql資料庫，我最常用的方式是把Excel資料表儲存成CSV格式，然後上載至網頁伺服器，交由phpMyAdmin這個PHP語言寫成的資料庫管理程式處理，將資料轉入Mysql資料庫。
後來剛好開始學習Excel增益集與PHP程式的設計，就把這兩個結合在一起練習，將Excel工作表加上一些Mysql資料庫欄位定義，將Excel工作表資料寫成PHP陣列，結合一些資料庫常進行的建立、新增、更新與刪除的動作，產生PHP的網頁程式。
這個增益集我放在增益集工坊，使用的方法我會陸續補上!
以下示範將一個學生的Excel成績資料表建置為Mysql主機localhost，資料庫名稱為scoredb的資料表score。
操作步驟如下:
<?
/*
ExcelToPHP.--2011年2月25日
修正中文字元含有\符號
無法正確顯示問題
建立新資料表程式
作者:Moore.Yang
*/
function PrintErrMsg($ErrorMsg)
{
printf ("<br><hr> %s <br><hr>\n", $ErrorMsg);
}
function SqlRunErrMsgFun ($SQL,$LINK) {
if (! mysql_query($SQL, $LINK)){
PrintErrMsg(sprintf("執行sql語法: %s 語法錯誤!",$SQL));
PrintErrMsg(sprintf("錯誤：%d %s", mysql_errno($LINK) , mysql_error($LINK)));
exit();}}
$Table=Array(
Array(10001,2,1,1,"姚倫庭",68)
,Array(10002,2,1,2,"王明弘",78)
,Array(10003,2,1,3,"陳琬誠",48)
,Array(10004,2,1,4,"郭婷堉",40)
,Array(10005,2,1,5,"謝皓奇",58)
,Array(10006,2,1,6,"胡慧元",60)
,Array(10007,2,1,7,"周雅怡",48)
,Array(10008,2,1,8,"黃善瑋",68)
,Array(10009,2,1,9,"蔡琪捷",46)
,Array(10010,2,1,10,"王鈞璘",30)
,Array(10011,2,1,11,"林彬睿",70)
,Array(10012,2,1,12,"黃姍慶",56)
,Array(10013,2,1,13,"蔡惠妏",42)
,Array(10014,2,1,14,"李怡青",54)
,Array(10015,2,1,15,"田 品",60)
,Array(10016,2,1,16,"李儀瑩",53)
,Array(10017,2,1,17,"張騏湄",62)
,Array(10018,2,1,18,"劉翊軒",56)
,Array(10019,2,1,19,"陳琳怡",40)
,Array(10020,2,1,20,"田婷庭",54)
,Array(10021,2,1,21,"徐建茵",34)
,Array(10022,2,1,22,"廖薇依",53)
,Array(10023,2,1,23,"潘亭翊",52)
,Array(10024,2,1,24,"婁沛",56)
,Array(10025,2,1,25,"李尚瀚",68)
,Array(10026,2,1,26,"劉怡炳",50)
,Array(10027,2,1,27,"林均雅",56)
,Array(10028,2,1,28,"邱煒吟",38)
,Array(10029,2,1,29,"洪宛予",60)
,Array(10030,2,1,30,"王峰萱",46)
,Array(10031,2,1,31,"張 榕",54)
,Array(10032,2,1,32,"劉涵逸",36)
,Array(10033,2,1,33,"張珮洁",28)
,Array(10034,2,1,34,"鄭巧超",34)
,Array(10035,2,1,35,"周俞文",34)
,Array(10036,2,1,36,"蔡宜誼",80)
,Array(10037,2,1,37,"洪 子",30)
,Array(10038,2,2,1,"蘇廷宗",74)
,Array(10039,2,2,2,"簡如日",68)
,Array(10040,2,2,3,"周伊芯",60)
,Array(10041,2,2,4,"張憲仲",54)
,Array(10042,2,2,5,"陳柏晏",42)
,Array(10043,2,2,6,"萬臻原",60)
,Array(10044,2,2,7,"簡茹宛",54)
,Array(10045,2,2,8,"李維元",68)
,Array(10046,2,2,9,"蘇鳳雪",54)
,Array(10047,2,2,10,"劉勝軒",54)
,Array(10048,2,2,11,"楊哲嫻",48)
,Array(10049,2,2,12,"游妤凱",35)
,Array(10050,2,2,13,"黃芬雯",18)
,Array(10051,2,2,14,"林涵以",38)
,Array(10052,2,2,15,"許安闐",65)
,Array(10053,2,2,16,"楊念婷",78)
,Array(10054,2,2,17,"羅閏于",70)
,Array(10055,2,2,18,"許如儀",64)
,Array(10056,2,2,19,"高泓雯",64)
,Array(10057,2,2,20,"周吟正",60)
,Array(10058,2,2,21,"黃綺祐",61)
,Array(10059,2,2,22,"黃 佩",72)
,Array(10060,2,2,23,"吳佳鈺",60)
,Array(10061,2,2,24,"林右筱",46)
,Array(10062,2,2,25,"張瑜莘",58)
,Array(10063,2,2,26,"陳紫德",64)
,Array(10064,2,2,27,"郭倢碧",66)
,Array(10065,2,2,28,"鐘晶逸",48)
,Array(10066,2,2,29,"邱銓隆",52)
,Array(10067,2,2,30,"詹庭雅",74)
,Array(10068,2,2,31,"張濬婉",52)
,Array(10069,2,2,32,"夏瑾雯",48)
,Array(10070,2,2,33,"林益容",66)
,Array(10071,2,2,34,"張華宏",48)
,Array(10072,2,2,35,"陳蓉涵",44)
,Array(10073,2,2,36,"陳岱煜",46)
,Array(10074,2,3,1,"王祐婷",66)
,Array(10075,2,3,2,"簡美鈞",62)
,Array(10076,2,3,3,"鐘至彧",64)
,Array(10077,2,3,5,"徐崇雯",48)
,Array(10078,2,3,6,"徐傳郁",58)
,Array(10079,2,3,7,"林瑞倫",54)
,Array(10080,2,3,8,"黃舒翔",43)
,Array(10081,2,3,9,"陳瑩芷",58)
,Array(10082,2,3,10,"沈如豪",52)
,Array(10083,2,3,11,"林力安",72)
,Array(10084,2,3,12,"陳均瑄",80)
,Array(10085,2,3,13,"陳瑀坤",70)
,Array(10086,2,3,14,"王宇郁",66)
,Array(10087,2,3,15,"郭煒寶",46)
,Array(10088,2,3,16,"拾燁蓁",72)
,Array(10089,2,3,17,"翁廷萱",57)
,Array(10090,2,3,18,"蔡仁于",63)
,Array(10091,2,3,19,"高達品",42)
,Array(10092,2,3,20,"邱修承",64)
,Array(10093,2,3,21,"姚婕涵",82)
,Array(10094,2,3,22,"江又家",42)
,Array(10095,2,3,23,"葉婉璇",66)
,Array(10096,2,3,24,"黃廷慧",52)
,Array(10097,2,3,25,"吳玉琪",56)
,Array(10098,2,3,26,"鄭上軒",58)
,Array(10099,2,3,27,"黃郁琳",54)
,Array(10100,2,3,28,"王亭鈞",66)
,Array(10101,2,3,29,"侯貞密",50)
,Array(10102,2,3,30,"簡雨柏",42)
,Array(10103,2,3,31,"陳辰新",58)
,Array(10104,2,3,32,"吳岱之",58)
,Array(10105,2,3,33,"陳宜慈",56)
,Array(10106,2,3,35,"簡育易",74)
,Array(10107,2,3,36,"劉昇珮",50)
,Array(10108,2,3,37,"塗育儒",50));
$Fds= Array("F1" , "F2" , "F3" , "F4" , "F5" , "F6" );
if (! ($link=mysql_connect ( "localhost" , "root" , "12233113311313" ))) {
PrintErrMsg(sprintf("使用者:root 無法連接上主機:localhost。請與管理者聯絡!"));
exit();}
if (! ($selectdb=mysql_select_db("scoredb" , $link))){
if (! ($resultcrdb=mysql_create_db("scoredb" , $link))){
PrintErrMsg(sprintf("使用者:root 無法建立資料庫。請與管理者聯絡!"));
PrintErrMsg(sprintf("錯誤：%d %s", mysql_errno($link) , mysql_error($link)));
exit();}
$selectdb=mysql_select_db("scoredb" , $link); }
$rows=mysql_query( "Select * From score");
if (! $rows) {
$sql="Create Table score ( sn INT(6) NOT NULL , INDEX (sn) , sy INT(2) , cn INT(2) , seat INT(2) , name CHAR(6) , score INT(3) )" ;
SqlRunErrMsgFun ($sql,$link);
for ($I=0; $I<count($Table); $I++) {
$sql = "Insert Into score Values (" ;
for ($K=0; $K<count($Table[0]); $K++) {
$$Fds[$K] = $Table[$I][$K];
if ( is_string($Table[$I][$K]) )
$sql = $sql ."'". addslashes($$Fds[$K]) . "'";
else
$sql = $sql . $$Fds[$K];
if ( $K <> count($Table[0])-1 ) $sql = $sql . ", ";
}
$sql = $sql . ");";
SqlRunErrMsgFun ($sql,$link);
echo $sql . "<br><hr>";
}}
else echo "score資料表已經存在!";
mysql_close ($link);
?>
十年磨一劍 