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

woff 發表於 2011-11-6 13:38:10

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

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


//uc轉換成html
function uc2html($str) {
   $ret = '';
   for( $i=0; $i<strlen($str)/2; $i++ ) {
       $charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);
       // $ret .= '&#'.$charcode;
       if($charcode<127)
         $ret .=chr($charcode);
       else
         $ret .= iconv("utf-8","utf-8",u2utf8($charcode));
   }
       return $ret;
}
//html轉成utf8
function u2utf8($c) {
$str="";
if ($c < 0x80) {
$str.=$c;
} else if ($c < 0x800) {
$str.=chr(0xC0 | $c>>6);
$str.=chr(0x80 | $c & 0x3F);
} else if ($c < 0x10000) {
$str.=chr(0xE0 | $c>>12);
$str.=chr(0x80 | $c>>6 & 0x3F);
$str.=chr(0x80 | $c & 0x3F);
} else if ($c < 0x200000) {
$str.=chr(0xF0 | $c>>18);
$str.=chr(0x80 | $c>>12 & 0x3F);
$str.=chr(0x80 | $c>>6 & 0x3F);
$str.=chr(0x80 | $c & 0x3F);
}
return $str;
}

if (trim($_POST["cmd"])=="upload")
{
$err_corr = "Unsupported format or file corrupted";
$excel_file_size;
$excel_file = $_FILES['excel_file'];
$uploadservername=$UploadAbsPath."tmpexcel/".$_FILES['excel_file']['name'];
echo($uploadservername);
   if (!is_writeable($UploadAbsPath."tmpexcel/"))
   {
   echo "目錄不可寫!"; exit;
   }
   else
   {
   echo "目錄可寫!";
   }
if (move_uploaded_file($_FILES['excel_file']['tmp_name'], $uploadservername))
{
    echo("上傳成功");
}
else
{
    echo("上傳失敗");
}
$excel_file=$uploadservername;
//if( $excel_file )
//    $excel_file = $_FILES['excel_file']['tmp_name'];


if( $excel_file == '' ) fatal("No file uploaded");


$exc = new ExcelFileParser("debug.log", ABC_NO_LOG);//ABC_NO_LOG ABC_VAR_DUMP);
    //echo($excel_file."|");
   
$style = $_POST['style'];
if( $style == 'old' )
{
    $fh = @fopen ($excel_file,'rb');
    if( !$fh ) fatal("No file uploaded");
    if( filesize($excel_file)==0 ) fatal("No file uploaded");
    $fc = fread( $fh, filesize($excel_file) );
    @fclose($fh);
    if( strlen($fc) < filesize($excel_file) )
    fatal("Cannot read file");
   
    $time_start = getmicrotime();
    $res = $exc->ParseFromString($fc);
    $time_end = getmicrotime();
}
elseif( $style == 'segment' )
{
    $time_start = getmicrotime();
    $res = $exc->ParseFromFile($excel_file);
    $time_end = getmicrotime();
}


switch ($res) {
    case 0: break;
    case 1: fatal("Can't open file");
    case 2: fatal("File too small to be an Excel file");
    case 3: fatal("Error reading file header");
    case 4: fatal("Error reading file");
    case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
    case 6: fatal("File corrupted");
    case 7: fatal("No Excel data found in file");
    case 8: fatal("Unsupported file version");


    default:
    fatal("Unknown error");
}


/*
print '<pre>';
print_r( $exc );
print '</pre>';
exit;
*/


show_time();


echo <<<LEG
<b>Legend:</b><br><br>
<form name='doform' action='' method='post'>
<input type='hidden' name='action' value='do'>
<input type='hidden' name='excel_file' value=$excel_file>
<input type='hidden' name='style' value=$style>
<table border=1 cellspacing=0 cellpadding=0>
<tr><td>Data type</td><td>Description</td></tr>
<tr><td class=empty>&nbsp;</td><td class=index>An empty cell</td></tr>
<tr><td class=dt_string>ABCabc</td><td class=index>String</td></tr>
<tr><td class=dt_int>12345</td><td class=index>Integer</td></tr>
<tr><td class=dt_float>123.45</td><td class=index>Float</td></tr>
<tr><td class=dt_date>123.45</td><td class=index>Date</td></tr>
<table>
<br><br>


LEG;
/*
print "<pre>";
print_r ($exc->worksheet);
print_r($exc->sst);
print "</pre>";
*/
    for( $ws_num=0; $ws_num<count($exc->worksheet['name']); $ws_num++ )
    {
    print "<b>Worksheet: \"";
    if( $exc->worksheet['unicode'][$ws_num] ) {
    print uc2html($exc->worksheet['name'][$ws_num]);
    } else
    print $exc->worksheet['name'][$ws_num];


    print "\"</b>";
    $ws = $exc->worksheet['data'][$ws_num];


    if( is_array($ws) &&
         isset($ws['max_row']) && isset($ws['max_col']) ) {
   echo "\n<br><br><table border=1 cellspacing=0 cellpadding=2>\n";


   print "<tr><td>&nbsp;</td>\n";
   for( $j=0; $j<=$ws['max_col']; $j++ ) {
    print "<td class=index>&nbsp;";
    if( $j>25 ) print chr((int)($j/26)+64);
    //這裡要顯示一個下拉列表來顯示數據
    //注意是循環數據<br />
    echo("\n<select name='".$j."'>");
    echo("\n<option value='0'>不選擇</option>");
   echo("\n<option value='costomernum'>客戶編號</option>");
   echo("\n<option value='name'>客戶姓名</option>");
   echo("\n<option value='phone1'>電話1</option>");
   echo("\n<option value='phone2'>電話2</option>");
   echo("\n<option value='address1'>地址1</option>");
   echo("\n<option value='address2'>地址2</option>");
   echo("\n<option value='company'>公司</option>");
   echo("\n<option value='levelc'>職稱</option>");   
   echo("\n<option value='ps'>備註</option>");
   
    echo("</select>");
    print "</td>";
   }


   print "<tr><td>&nbsp;</td>\n";
   for( $j=0; $j<=$ws['max_col']; $j++ ) {
    print "<td class=index>&nbsp;";
    if( $j>25 ) print chr((int)($j/26)+64);
    print chr(($j % 26) + 65)."&nbsp;列名</td>";
   }



//表頭輸出完畢
    if ($ws['max_row']>9)
    {
    $shownum=9;
    }
    else
    {
    $shownum=$ws['max_row'];//只輸出前10條數據
    }
   for( $i=0; $i<=$shownum; $i++ ) {
   print "<tr><td class=index>".($i+1)."</td>\n";
   if(isset($ws['cell'][$i]) && is_array($ws['cell'][$i]) ) {
         for( $j=0; $j<=$ws['max_col']; $j++ ) {


    if( ( is_array($ws['cell'][$i]) ) &&
         ( isset($ws['cell'][$i][$j]) )
         ){


   // print cell data
   print "<td class=\"";
   $data = $ws['cell'][$i][$j];


   $font = $ws['cell'][$i][$j]['font'];
   $style = " style ='".ExcelFont::ExcelToCSS($exc->fonts[$font])."'";


         switch ($data['type']) {
    // string
    case 0:
      print "dt_string\"".$style.">";
      $ind = $data['data'];
      if( $exc->sst['unicode'][$ind] ) {
      $s = uc2html($exc->sst['data'][$ind]);
      } else
      $s = $exc->sst['data'][$ind];
      if( strlen(trim($s))==0 )
      print "&nbsp;";
      else
      print $s;
      break;
    // integer number
    case 1:
      print "dt_int\"".$style.">&nbsp;";
      print $data['data'];
      break;
    // float number
    case 2:
      print "dt_float\"".$style.">&nbsp;";
      echo $data['data'];
      break;
    // date
    case 3:
      print "dt_date\"".$style.">&nbsp;";


      $ret = $data;//str_replace ( " 00:00:00", "", gmdate("d-m-Y H:i:s",$exc->xls2tstamp($data)) );
      echo ( $ret );
      break;
    default:
      print "dt_unknown\"".$style."> &nbsp;";
      break;
         }
   print "</td>\n";
    } else {
      print "<td class=empty>&nbsp;</td>\n";
    }
         }
   } else {
    // print an empty row
    for( $j=0; $j<=$ws['max_col']; $j++ )
      print "<td class=empty>&nbsp;</td>";
    print "\n";
   }
   print "</tr>\n";
   }


   echo "</table><br>\n";
    } else {
    // emtpty worksheet
    print "<b> - empty</b><br>\n";
    }
    print "<br>";


    }
    echo("<input type='submit' name='Submit' value='轉換' />");
    echo("</form>");
/*    print "Formats<br>";
    foreach($exc->format as $value) {
    printf("( %x )",array_search($value,$exc->format));
    print htmlentities($value,ENT_QUOTES);
    print "<br>";
    }


   print "XFs<br>";
    for( $i=0;$i<count($exc->xf['format']);$i++) {
    printf ("(%x)",$i);
    printf (" format (%x) font (%x)",$exc->xf['format'][$i],$exc->xf['font'][$i]);


    print "<br>";
    }
*/
}
運行效果如下:
第二步是要讀取數據出來,代碼如下:

if ($_POST["action"]=="do")
{
    //處理數據
    //先讀取表頭記錄
   
    $excel_file=$_POST["excel_file"];
    $fh = @fopen ($excel_file,'rb');
    $fc = fread( $fh, filesize($excel_file) );
    @fclose($fh);
    //echo("執行".$excel_file);
    $exc = new ExcelFileParser("debug.log", ABC_NO_LOG);//ABC_NO_LOG ABC_VAR_DUMP);
    //echo($excel_file."|");
   
$style = $_POST['style'];
if( $style == 'old' )
{
    $fh = @fopen ($excel_file,'rb');
    if( !$fh ) fatal("No file uploaded");
    if( filesize($excel_file)==0 ) fatal("No file uploaded");
    $fc = fread( $fh, filesize($excel_file) );
    @fclose($fh);
    if( strlen($fc) < filesize($excel_file) )
    fatal("Cannot read file");
   
    $time_start = getmicrotime();
    $res = $exc->ParseFromString($fc);
    $time_end = getmicrotime();
}
elseif( $style == 'segment' )
{
    $time_start = getmicrotime();
    $res = $exc->ParseFromFile($excel_file);
    $time_end = getmicrotime();
}


switch ($res) {
    case 0: break;
    case 1: fatal("Can't open file");
    case 2: fatal("File too small to be an Excel file");
    case 3: fatal("Error reading file header");
    case 4: fatal("Error reading file");
    case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
    case 6: fatal("File corrupted");
    case 7: fatal("No Excel data found in file");
    case 8: fatal("Unsupported file version");


    default:
    fatal("Unknown error");
}
//以及讀取完畢,如果沒有錯誤的話就可以循環往MySQL中增加數據了!
    for( $ws_num=0; $ws_num<count($exc->worksheet['name']); $ws_num++ )
    {
//    print "<b>Worksheet: \"";
//    if( $exc->worksheet['unicode'][$ws_num] ) {
//    print uc2html($exc->worksheet['name'][$ws_num]);
//    } else
//    print $exc->worksheet['name'][$ws_num];
//
//    print "\"</b>";
    $ws = $exc->worksheet['data'][$ws_num];
//
//
//   print "<tr><td>&nbsp;</td>\n";
      $namenum=0;
   $costomernumnum=0;
   $phone1num=0;
   $phone2num=0;   
   $address1num=0;
   $address2num=0;
   $companynum=0;
   $levelcnum=0;
   $psnum=0;   
   for( $j=0; $j<=$ws['max_col']; $j++ ) {
    //print "<td class=index>&nbsp;";
    //if( $j>25 ) print chr((int)($j/26)+64);
    //先讀取列名


    $tmpcolum=trim($_POST["$j"]);
    //echo($tmpcolum."|");
    if ($tmpcolum=="name") $namenum=$j;
   if ($tmpcolum=="costomernum") $costomernumnum=$j;
   if ($tmpcolum=="phone1") $phone1num=$j;
   if ($tmpcolum=="phone2") $phone2num=$j;   
   if ($tmpcolum=="address1") $address1num=$j;
   if ($tmpcolum=="address2") $address2num=$j;
   if ($tmpcolum=="company") $companynum=$j;
   if ($tmpcolum=="levelc") $levelcnum=$j;
   if ($tmpcolum=="ps") $psnum=$j;      
   }
   for( $i=0; $i<=$ws['max_row']; $i++ ) {
   //print "<tr><td class=index>".($i+1)."</td>\n";
   if(isset($ws['cell'][$i]) && is_array($ws['cell'][$i]) ) {
   if ($namenum!=0&&$phone1num!=0)//請在這裡指定必須的不為空的字段
   {
      $sql="insert into net_costomer(costomernum,name,company,levelc,phone1,phone2,address1,address2,ps)
   values('".$exc->sst['data'][$ws['cell'][$i][$costomernumnum]['data']]."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$namenum]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$levelcnum]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$phone1num]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$phone2num]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$address1num]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$address2num]['data']])."',
   '".uc2html($exc->sst['data'][$ws['cell'][$i][$psnum]['data']])."')";
   echo($sql."<br>");
   }
   //$conn->Query($sql);
         }
         }
         }
//導入完成刪除文件
unlink($filename);


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

axasko 發表於 2012-9-23 16:38:18

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

查看完整版本: Linux下把excel數據導入到mysql數據庫(亂碼問題已解決)