PHP实用工具:实现Excel转Mysql工具自动字段长度

所得Mysql语句一般适合查立得万能搜等mysql只查不改的系统。 

$t = "Excel转Mysql工具";
$s = "Excel复制过来的二维结构表内容,第一行各列字段名(列标题),以后一行一条数据";
$yw = "chalide".date("YmdH");
$datedir = "";
function txttihuan($stext){
$stext = str_replace(array("\r\n","\r","\n"),"
",$stext);
$stext = str_replace(array("\t"),"
",$stext);
return addslashes($stext);//htmlspecialchars($stext);
}
function ss($Key){
 $html="";
 exit($html);
}
$base = trim($_POST['name']);
if (strlen($base)>10){
$files="S2".date("YmdHis");
$uptis = "上传失败：";
if(!stristr($base,"\t"))$uptis .= "制表符;
";
if(!stristr($base,"\n"))$uptis .= "回车符;
";
if($uptis<>"上传失败：") ss("提交内容由Excel复制过来:应当包含:".$uptis);
file_put_contents($datedir.$files.".csv", $base);
 
$file = fopen($datedir.$files.".csv", 'r');
$header = fgetcsv($file);
$max_lengths = array_fill(0, count($header), 12);
while (($line = fgetcsv($file,0,"\t")) !== false) {
  foreach ($line as $i => $value) {
    $max_lengths[$i] = max($max_lengths[$i], strlen($value));
  }
}
fclose($file);
 
$file = fopen($datedir.$files.".csv",'r') or ss("读取失败：读取上传文件内容失败！"); 
 $ii=-1; $ix1="|"; $ix0=0;
$xs1tao ="
-- -公共说明：本功能适合查立得php+mysql无后台查询！
-- -公共说明：你需要修改表名称`$yw`为实际(多处)！
";
 
$xs2tao ="
-- -建表结构说明：ID字段自动增加,请确保其他字段都不是ID,其他字段均为文本！
-- -字段长度说明：varchar(*)中*号自动为csv各列最高长度 请根据自己实际修改(中文得乘3)以免只写入部分值
-- -可选性能提升：请根据实际修改索引字段INDEX(),UNIQUE KEY(),FULLTEXT()等
-- -其他：数据库引擎InnoDB/Myisam 编码等
";
 
$px ="-- -如果表已存在则先清空表,不需要则删除忽略！\r\n";
$px .= "DROP TABLE IF EXISTS `".$yw."`;\r\n\r\n";
 
while ($data = fgetcsv($file,0,"\t")){
 $ii++; $io=0;
 $pt = "-- -以下是写入新数据Sql;\r\n";
 $pt .= "INSERT INTO `$yw` (";
 $pa = "-- -以下是建表Sql;建表用\r\n";
 $pa .= "CREATE TABLE `$yw` (\r\n";
 $pa .= "`id` int(10) unsigned NOT NULL AUTO_INCREMENT,\r\n";
 if($ii."a"=="0a"){
 $dd=array(); $par="\r\n-- - 如需索引自定义修改以下内容后加在PRIMARY KEY (`id`)前\r\n";
 foreach($data as $keyy=>$valy) { 
 $io++; $valy = txttihuan($valy); $dd[$io]=$valy; $ilen = $max_lengths[$keyy];
 $pa .= "`$valy` varchar($ilen) COMMENT '$valy' DEFAULT 'Null',\r\n";
 if($io."a"=="1a"){ $pt .= "`$valy`"; }else{ $pt .= ", `$valy`"; }
 }
if($dd[1]) $par .= "-- -普通索引参考: INDEX ".$dd[1]." (`".$dd[1]."`),\r\n";
if($dd[1]) $par .= "-- -全文索引参考: FULLTEXT (`".$dd[1]."`),\r\n";
if($dd[2]) $par .= "-- -唯一索引参考: UNIQUE KEY `".$dd[2]."` (`".$dd[2]."`),\r\n";
 $pa .= "PRIMARY KEY (`id`)\r\n";
 $pa .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
 $pt .= ") VALUES ";
 $lix = $io; $rst = "$xs1tao\r\n$px\r\n$pa\r\n$par\r\n\r\n$xs2tao\r\n$pt"; 
 }else{
 foreach($data as $keyy=>$valy) { 
 $io++;  $valy = txttihuan($valy);
 if($io."a"=="1a"){ $linex = "\r\n('$valy'";}else{$linex .= ", '$valy'"; }
 } 
 if(!stristr("-{$lix}-","-{$io}-")){ $ix1.="$ii|"; $ix0++;}
 $rst .= "$linex),";
 }
}
$rst = Trim($rst,",").";";
if (@unlink($datedir.$files.".csv")){ }
if($ix0>0) $tips = "
可能有{$ix0}行单元格包含制表符或者回车换行，不正常($ix1)";
}
?>"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
"http://www.w3.org/1999/xhtml">
"Content-Type" content="text/html; charset=utf-8" />
"X-UA-Compatible" content="IE=EmulateIE7" />
"author" content="yujianyue, admin@ewuyi.net">
"copyright" content="www.12391.net">
"main">
'onebyone'>
"md">
class="w">
<legend>php echo $t; ?>（<b>php echo $h; ?>b>）功能内测中legend>
<div id="tab02">
php if(strlen($rst)>1){?>
 转化结果 
 echo $tips; ?>
class="so_bax" id="t2">
<textarea name="name" class="txts" id="name" placeholder="转化后内容" onfocus="st('name',2);" onBlur="sta(2)" >
php echo $rst; ?>
textarea>
<b>继续转化b>请点下边一行。<br>
div>
ol>
php } ?>    
<h4> xls转Mysql h4>
<ol>
<form name="queryForm" method="post" action="?t=php echo date("YmdHis");?>" onsubmit="return sta(0);">
<div class="so_bax" id="t2">
<textarea name="name" class="txts" id="name" placeholder="php echo $s; ?>" onfocus="st('name',2);" onBlur="sta(2)" >php echo $s; ?>textarea>
div>
<div class="so_but">
<input type="submit" name="button" class="buts" id="sub" value="立即转化" />
div>
<div id="tishi2" style="display:none;">Excel复制过来的二维结构表内容div>
form> 
ol>
  
<h4> 功能说明h4>
<ol>
将Excel表格二维结构部分内容转为Mysql数据（去回车换行及制表符）。
ol>
  
  
<h4> 使用须知h4>
<ol>
1. 二维表：第一行列标题，以后一行一条数据。<br>
2. 无合并单元格：即每行一条数据，第一行每个字段（每格）对应列。<br>
3. 主动字段长度但均为文本格式，一般适合查立得万能搜等只查不改系统。<br>
ol>
div><script type="text/javascript"> 
 
script> 
fieldset>div>
div>
<div id='fooder' class="linkar">
div>
body>
html>