Linux下把excel數據導入到mysql數據庫(亂碼問題已解決)

woff

說明:在Linux下把excel數據導入到mysql數據庫中,這實在是很搞笑的一個活動!幾乎很少有程序員研究過這個問題吧?如此變態的問題,估計我是第一個研究的!呵呵,完全是自找苦吃~~,但是今天完了這項太有挑戰性的工作!
先清理一下思路先,~~
首先:需要把文件上傳到服務器上
然後:讀取excel數據列顯示出來
然後:讓用戶選擇字段的對應關係
然後:提交數據,讀取字段的對應關係
最後:批量導入數據,刪除臨時文件
一共是以上五步驟!我們一步步分析~~~
第一步:下載附件中的phpexcelparser4.rar ,這個文件是上傳excel盜服務器上並以web形式展示出來的!這個一般沒有問題的!問題是程序的做法是把表存為臨時表而沒有真正保存下來,所以首先要更改程序代碼為

1. 
   
2. //uc轉換成html
   
3. function uc2html($str) {
   
4.    $ret = '';
   
5.    for( $i=0; $i<strlen($str)/2; $i++ ) {
   
6.        $charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);
   
7.        // $ret .= '&#'.$charcode;
   
8.        if($charcode<127)
   
9.          $ret .=chr($charcode);
   
10.        else
    
11.            $ret .= iconv("utf-8","utf-8",u2utf8($charcode));
    
12.    }
    
13.        return $ret;
    
14. }
    
15. //html轉成utf8
    
16. function u2utf8($c) {
    
17. $str="";
    
18. if ($c < 0x80) {
    
19.   $str.=$c;
    
20. } else if ($c < 0x800) {
    
21.   $str.=chr(0xC0 | $c>>6);
    
22.   $str.=chr(0x80 | $c & 0x3F);
    
23. } else if ($c < 0x10000) {
    
24.   $str.=chr(0xE0 | $c>>12);
    
25.   $str.=chr(0x80 | $c>>6 & 0x3F);
    
26.   $str.=chr(0x80 | $c & 0x3F);
    
27. } else if ($c < 0x200000) {
    
28.   $str.=chr(0xF0 | $c>>18);
    
29.   $str.=chr(0x80 | $c>>12 & 0x3F);
    
30.   $str.=chr(0x80 | $c>>6 & 0x3F);
    
31.   $str.=chr(0x80 | $c & 0x3F);
    
32. }
    
33. return $str;
    
34. }
    
35. 
    
36. if (trim($_POST["cmd"])=="upload")
    
37. {
    
38. $err_corr = "Unsupported format or file corrupted";
    
39. $excel_file_size;
    
40. $excel_file = $_FILES['excel_file'];
    
41. $uploadservername=$UploadAbsPath."tmpexcel/".$_FILES['excel_file']['name'];
    
42. echo($uploadservername);
    
43.      if (!is_writeable($UploadAbsPath."tmpexcel/"))
    
44.      {
    
45.      echo "目錄不可寫!"; exit;
    
46.      }
    
47.      else
    
48.      {
    
49.      echo "目錄可寫!";
    
50.      }
    
51. if (move_uploaded_file($_FILES['excel_file']['tmp_name'], $uploadservername))
    
52. {
    
53.     echo("上傳成功");
    
54. }
    
55. else
    
56. {
    
57.     echo("上傳失敗");
    
58. }
    
59. $excel_file=$uploadservername;
    
60. //if( $excel_file )
    
61. //    $excel_file = $_FILES['excel_file']['tmp_name'];
    
62. 
    
63. 
    
64. if( $excel_file == '' ) fatal("No file uploaded");
    
65. 
    
66. 
    
67. $exc = new ExcelFileParser("debug.log", ABC_NO_LOG);//ABC_NO_LOG ABC_VAR_DUMP);
    
68.     //echo($excel_file."|");
    
69.    
    
70. $style = $_POST['style'];
    
71. if( $style == 'old' )
    
72. {
    
73.     $fh = @fopen ($excel_file,'rb');
    
74.     if( !$fh ) fatal("No file uploaded");
    
75.     if( filesize($excel_file)==0 ) fatal("No file uploaded");
    
76.     $fc = fread( $fh, filesize($excel_file) );
    
77.     @fclose($fh);
    
78.     if( strlen($fc) < filesize($excel_file) )
    
79.     fatal("Cannot read file");
    
80.    
    
81.     $time_start = getmicrotime();
    
82.     $res = $exc->ParseFromString($fc);
    
83.     $time_end = getmicrotime();
    
84. }
    
85. elseif( $style == 'segment' )
    
86. {
    
87.     $time_start = getmicrotime();
    
88.     $res = $exc->ParseFromFile($excel_file);
    
89.     $time_end = getmicrotime();
    
90. }
    
91. 
    
92. 
    
93. switch ($res) {
    
94.     case 0: break;
    
95.     case 1: fatal("Can't open file");
    
96.     case 2: fatal("File too small to be an Excel file");
    
97.     case 3: fatal("Error reading file header");
    
98.     case 4: fatal("Error reading file");
    
99.     case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
    
100.     case 6: fatal("File corrupted");
     
101.     case 7: fatal("No Excel data found in file");
     
102.     case 8: fatal("Unsupported file version");
     
103. 
     
104. 
     
105.     default:
     
106.     fatal("Unknown error");
     
107. }
     
108. 
     
109. 
     
110. /*
     
111. print '<pre>';
     
112. print_r( $exc );
     
113. print '</pre>';
     
114. exit;
     
115. */
     
116. 
     
117. 
     
118. show_time();
     
119. 
     
120. 
     
121. echo <<<LEG
     
122. <b>Legend:</b><br><br>
     
123. <form name='doform' action='' method='post'>
     
124. <input type='hidden' name='action' value='do'>
     
125. <input type='hidden' name='excel_file' value=$excel_file>
     
126. <input type='hidden' name='style' value=$style>
     
127. <table border=1 cellspacing=0 cellpadding=0>
     
128. <tr><td>Data type</td><td>Description</td></tr>
     
129. <tr><td class=empty>&nbsp;</td><td class=index>An empty cell</td></tr>
     
130. <tr><td class=dt_string>ABCabc</td><td class=index>String</td></tr>
     
131. <tr><td class=dt_int>12345</td><td class=index>Integer</td></tr>
     
132. <tr><td class=dt_float>123.45</td><td class=index>Float</td></tr>
     
133. <tr><td class=dt_date>123.45</td><td class=index>Date</td></tr>
     
134. <table>
     
135. <br><br>
     
136. 
     
137. 
     
138. LEG;
     
139. /*
     
140. print "<pre>";
     
141. print_r ($exc->worksheet);
     
142. print_r($exc->sst);
     
143. print "</pre>";
     
144. */
     
145.     for( $ws_num=0; $ws_num<count($exc->worksheet['name']); $ws_num++ )
     
146.     {
     
147.     print "<b>Worksheet: \"";
     
148.     if( $exc->worksheet['unicode'][$ws_num] ) {
     
149.     print uc2html($exc->worksheet['name'][$ws_num]);
     
150.     } else
     
151.     print $exc->worksheet['name'][$ws_num];
     
152. 
     
153. 
     
154.     print "\"</b>";
     
155.     $ws = $exc->worksheet['data'][$ws_num];
     
156. 
     
157. 
     
158.     if( is_array($ws) &&
     
159.          isset($ws['max_row']) && isset($ws['max_col']) ) {
     
160.      echo "\n<br><br><table border=1 cellspacing=0 cellpadding=2>\n";
     
161. 
     
162. 
     
163.      print "<tr><td>&nbsp;</td>\n";
     
164.      for( $j=0; $j<=$ws['max_col']; $j++ ) {
     
165.     print "<td class=index>&nbsp;";
     
166.     if( $j>25 ) print chr((int)($j/26)+64);
     
167.     //這裡要顯示一個下拉列表來顯示數據
     
168.     //注意是循環數據<br />
     
169.     echo("\n<select name='".$j."'>");
     
170.     echo("\n<option value='0'>不選擇</option>");
     
171.    echo("\n<option value='costomernum'>客戶編號</option>");
     
172.    echo("\n<option value='name'>客戶姓名</option>");
     
173.    echo("\n<option value='phone1'>電話1</option>");
     
174.    echo("\n<option value='phone2'>電話2</option>");
     
175.    echo("\n<option value='address1'>地址1</option>");
     
176.    echo("\n<option value='address2'>地址2</option>");  
     
177.    echo("\n<option value='company'>公司</option>");
     
178.    echo("\n<option value='levelc'>職稱</option>");   
     
179.    echo("\n<option value='ps'>備註</option>");
     
180.    
     
181.     echo("</select>");
     
182.     print "</td>";
     
183.      }
     
184. 
     
185. 
     
186.      print "<tr><td>&nbsp;</td>\n";
     
187.      for( $j=0; $j<=$ws['max_col']; $j++ ) {
     
188.     print "<td class=index>&nbsp;";
     
189.     if( $j>25 ) print chr((int)($j/26)+64);
     
190.     print chr(($j % 26) + 65)."&nbsp;列名</td>";
     
191.      }
     
192. 
     
193. 
     
194. 
     
195. //表頭輸出完畢
     
196.     if ($ws['max_row']>9)
     
197.     {
     
198.     $shownum=9;
     
199.     }
     
200.     else
     
201.     {
     
202.     $shownum=$ws['max_row'];//只輸出前10條數據
     
203.     }
     
204.      for( $i=0; $i<=$shownum; $i++ ) {
     
205.      print "<tr><td class=index>".($i+1)."</td>\n";
     
206.      if(isset($ws['cell'][$i]) && is_array($ws['cell'][$i]) ) {
     
207.          for( $j=0; $j<=$ws['max_col']; $j++ ) {
     
208. 
     
209. 
     
210.     if( ( is_array($ws['cell'][$i]) ) &&
     
211.          ( isset($ws['cell'][$i][$j]) )
     
212.          ){
     
213. 
     
214. 
     
215.      // print cell data
     
216.      print "<td class=\"";
     
217.      $data = $ws['cell'][$i][$j];
     
218. 
     
219. 
     
220.      $font = $ws['cell'][$i][$j]['font'];
     
221.      $style = " style ='".ExcelFont::ExcelToCSS($exc->fonts[$font])."'";
     
222. 
     
223. 
     
224.          switch ($data['type']) {
     
225.     // string
     
226.     case 0:
     
227.         print "dt_string\"".$style.">";
     
228.         $ind = $data['data'];
     
229.         if( $exc->sst['unicode'][$ind] ) {
     
230.         $s = uc2html($exc->sst['data'][$ind]);
     
231.         } else
     
232.         $s = $exc->sst['data'][$ind];
     
233.         if( strlen(trim($s))==0 )
     
234.         print "&nbsp;";
     
235.         else
     
236.         print $s;
     
237.         break;
     
238.     // integer number
     
239.     case 1:
     
240.         print "dt_int\"".$style.">&nbsp;";
     
241.         print $data['data'];
     
242.         break;
     
243.     // float number
     
244.     case 2:
     
245.         print "dt_float\"".$style.">&nbsp;";
     
246.         echo $data['data'];
     
247.         break;
     
248.     // date
     
249.     case 3:
     
250.         print "dt_date\"".$style.">&nbsp;";
     
251. 
     
252. 
     
253.         $ret = $data[data];//str_replace ( " 00:00:00", "", gmdate("d-m-Y H:i:s",$exc->xls2tstamp($data[data])) );
     
254.         echo ( $ret );
     
255.         break;
     
256.     default:
     
257.         print "dt_unknown\"".$style."> &nbsp;";
     
258.         break;
     
259.          }
     
260.      print "</td>\n";
     
261.     } else {
     
262.         print "<td class=empty>&nbsp;</td>\n";
     
263.     }
     
264.          }
     
265.      } else {
     
266.     // print an empty row
     
267.     for( $j=0; $j<=$ws['max_col']; $j++ )
     
268.         print "<td class=empty>&nbsp;</td>";
     
269.     print "\n";
     
270.      }
     
271.      print "</tr>\n";
     
272.      }
     
273. 
     
274. 
     
275.      echo "</table><br>\n";
     
276.     } else {
     
277.     // emtpty worksheet
     
278.     print "<b> - empty</b><br>\n";
     
279.     }
     
280.     print "<br>";
     
281. 
     
282. 
     
283.     }
     
284.     echo("<input type='submit' name='Submit' value='轉換' />");
     
285.     echo("</form>");
     
286. /*    print "Formats<br>";
     
287.     foreach($exc->format as $value) {
     
288.     printf("( %x )",array_search($value,$exc->format));
     
289.     print htmlentities($value,ENT_QUOTES);
     
290.     print "<br>";
     
291.     }
     
292. 
     
293. 
     
294.      print "XFs<br>";
     
295.     for( $i=0;$i<count($exc->xf['format']);$i++) {
     
296.     printf ("(%x)",$i);
     
297.     printf (" format (%x) font (%x)",$exc->xf['format'][$i],$exc->xf['font'][$i]);
     
298. 
     
299. 
     
300.     print "<br>";
     
301.     }
     
302. */
     
303. }

複製代碼

運行效果如下:
第二步是要讀取數據出來,代碼如下:

1. if ($_POST["action"]=="do")
   
2. {
   
3.     //處理數據
   
4.     //先讀取表頭記錄
   
5.    
   
6.     $excel_file=$_POST["excel_file"];
   
7.     $fh = @fopen ($excel_file,'rb');
   
8.     $fc = fread( $fh, filesize($excel_file) );
   
9.     @fclose($fh);
   
10.     //echo("執行".$excel_file);
    
11.     $exc = new ExcelFileParser("debug.log", ABC_NO_LOG);//ABC_NO_LOG ABC_VAR_DUMP);
    
12.     //echo($excel_file."|");
    
13.    
    
14. $style = $_POST['style'];
    
15. if( $style == 'old' )
    
16. {
    
17.     $fh = @fopen ($excel_file,'rb');
    
18.     if( !$fh ) fatal("No file uploaded");
    
19.     if( filesize($excel_file)==0 ) fatal("No file uploaded");
    
20.     $fc = fread( $fh, filesize($excel_file) );
    
21.     @fclose($fh);
    
22.     if( strlen($fc) < filesize($excel_file) )
    
23.     fatal("Cannot read file");
    
24.    
    
25.     $time_start = getmicrotime();
    
26.     $res = $exc->ParseFromString($fc);
    
27.     $time_end = getmicrotime();
    
28. }
    
29. elseif( $style == 'segment' )
    
30. {
    
31.     $time_start = getmicrotime();
    
32.     $res = $exc->ParseFromFile($excel_file);
    
33.     $time_end = getmicrotime();
    
34. }
    
35. 
    
36. 
    
37. switch ($res) {
    
38.     case 0: break;
    
39.     case 1: fatal("Can't open file");
    
40.     case 2: fatal("File too small to be an Excel file");
    
41.     case 3: fatal("Error reading file header");
    
42.     case 4: fatal("Error reading file");
    
43.     case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
    
44.     case 6: fatal("File corrupted");
    
45.     case 7: fatal("No Excel data found in file");
    
46.     case 8: fatal("Unsupported file version");
    
47. 
    
48. 
    
49.     default:
    
50.     fatal("Unknown error");
    
51. }
    
52. //以及讀取完畢,如果沒有錯誤的話就可以循環往MySQL中增加數據了!
    
53.     for( $ws_num=0; $ws_num<count($exc->worksheet['name']); $ws_num++ )
    
54.     {
    
55. //    print "<b>Worksheet: \"";
    
56. //    if( $exc->worksheet['unicode'][$ws_num] ) {
    
57. //    print uc2html($exc->worksheet['name'][$ws_num]);
    
58. //    } else
    
59. //    print $exc->worksheet['name'][$ws_num];
    
60. //
    
61. //    print "\"</b>";
    
62.     $ws = $exc->worksheet['data'][$ws_num];
    
63. //
    
64. //
    
65. //     print "<tr><td>&nbsp;</td>\n";
    
66.         $namenum=0;
    
67.    $costomernumnum=0;
    
68.    $phone1num=0;
    
69.    $phone2num=0;   
    
70.    $address1num=0;
    
71.    $address2num=0;
    
72.    $companynum=0;
    
73.    $levelcnum=0;
    
74.    $psnum=0;   
    
75.      for( $j=0; $j<=$ws['max_col']; $j++ ) {
    
76.     //print "<td class=index>&nbsp;";
    
77.     //if( $j>25 ) print chr((int)($j/26)+64);
    
78.     //先讀取列名
    
79. 
    
80. 
    
81.     $tmpcolum=trim($_POST["$j"]);
    
82.     //echo($tmpcolum."|");
    
83.     if ($tmpcolum=="name") $namenum=$j;
    
84.    if ($tmpcolum=="costomernum") $costomernumnum=$j;
    
85.    if ($tmpcolum=="phone1") $phone1num=$j;
    
86.    if ($tmpcolum=="phone2") $phone2num=$j;   
    
87.    if ($tmpcolum=="address1") $address1num=$j;
    
88.    if ($tmpcolum=="address2") $address2num=$j;
    
89.    if ($tmpcolum=="company") $companynum=$j;
    
90.    if ($tmpcolum=="levelc") $levelcnum=$j;
    
91.    if ($tmpcolum=="ps") $psnum=$j;      
    
92.      }
    
93.      for( $i=0; $i<=$ws['max_row']; $i++ ) {
    
94.      //print "<tr><td class=index>".($i+1)."</td>\n";
    
95.      if(isset($ws['cell'][$i]) && is_array($ws['cell'][$i]) ) {
    
96.      if ($namenum!=0&&$phone1num!=0)//請在這裡指定必須的不為空的字段
    
97.      {
    
98.         $sql="insert into net_costomer(costomernum,name,company,levelc,phone1,phone2,address1,address2,ps)
    
99.    values('".$exc->sst['data'][$ws['cell'][$i][$costomernumnum]['data']]."',
    
100.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$namenum]['data']])."',
     
101.    '".uc2html($exc->sst['data'][$ws['cell'][$i][companynum]['data']])."',
     
102.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$levelcnum]['data']])."',
     
103.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$phone1num]['data']])."',
     
104.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$phone2num]['data']])."',
     
105.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$address1num]['data']])."',
     
106.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$address2num]['data']])."',
     
107.    '".uc2html($exc->sst['data'][$ws['cell'][$i][$psnum]['data']])."')";
     
108.      echo($sql."<br>");
     
109.      }
     
110.      //$conn->Query($sql);
     
111.          }
     
112.          }
     
113.          }
     
114. //導入完成刪除文件
     
115. unlink($filename);
     
116. 
     
117. 
     
118. }

複製代碼

你注意沒有,我把執行的那一行註釋掉的,只要去掉註釋就可以正確執行了!
如下圖片:
詳細的補充整理代碼在附件中!
MSN:woff5678@hotmail.com

axasko

不回不行了，因为楼猪太有才了。