新竹網頁設計

Jul 12 Wed 2023 00:00
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> </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> </td>\n";
for( $j=0; $j<=$ws['max_col']; $j++ ) {
print "<td class=index> ";
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> </td>\n";
for( $j=0; $j<=$ws['max_col']; $j++ ) {
print "<td class=index> ";
if( $j>25 ) print chr((int)($j/26)+64);
print chr(($j % 26) + 65)." 列名</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 " ";
else
print $s;
break;
// integer number
case 1:
print "dt_int\"".$style."> ";
print $data['data'];
break;
// float number
case 2:
print "dt_float\"".$style."> ";
echo $data['data'];
break;
// date
case 3:
print "dt_date\"".$style."> ";
$ret = $data[data];//str_replace ( " 00:00:00", "", gmdate("d-m-Y H:i:s",$exc->xls2tstamp($data[data])) );
echo ( $ret );
break;
default:
print "dt_unknown\"".$style.">  ";
break;
}
print "</td>\n";
} else {
print "<td class=empty> </td>\n";
}
}
} else {
// print an empty row
for( $j=0; $j<=$ws['max_col']; $j++ )
print "<td class=empty> </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>";
}
*/
}

(繼續閱讀...)

marshabaopb 發表在痞客邦留言(0) 人氣()

個人分類：NetYea

▲top

Jul 11 Tue 2023 03:06
PHP燒毀函數ereg_replace()，若何用preg_replace()替換網頁設計

網頁設計

ereg_replace()是個利用正則的replacestr_replace()
ereg_replace ( string $pattern , string $replacement , string $string ) : string
preg_replace ( mixed $pattern , mixed $replacement , mixed $subject [, int $limit = -1 [, int &$count ]] ) : mixed
preg_replace()是今朝的保舉方案，必選參數3個，可選參數3個。根基上可以覆蓋只不外是參數的具體用法分歧。返回值是根基上一致的。ereg_replace()

文章出處