当前位置：首页 > news >正文

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

news 来源：原创 2024/5/10 3:34:18

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

<?php
$t = "Excel转Mysql工具";
$s = "Excel复制过来的二维结构表内容,第一行各列字段名(列标题),以后一行一条数据";
$yw = "chalide".date("YmdH");
$datedir = "";
function txttihuan($stext){
$stext = str_replace(array("\r\n","\r","\n"),"<br />",$stext);
$stext = str_replace(array("\t"),"<br />",$stext);
return addslashes($stext);//htmlspecialchars($stext);
}
function ss($Key){$html="<script>\r\n";$html.="alert('".$Key."');\r\n";$html.="history.go(-1);\r\n";$html.="</script>";exit($html);
}
$base = trim($_POST['name']);
if (strlen($base)>10){
$files="S2".date("YmdHis");
$uptis = "上传失败：";
if(!stristr($base,"\t"))$uptis .= "制表符;<br>";
if(!stristr($base,"\n"))$uptis .= "回车符;<br>";
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 = "<br>可能有{$ix0}行单元格包含制表符或者回车换行，不正常($ix1)";
}
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />
<title><?php echo $t; ?> - <?php echo $h; ?></title>
<meta name="author" content="yujianyue, admin@ewuyi.net">
<meta name="copyright" content="www.12391.net">
<script type="text/javascript">
function $(Obj){
return document.getElementById(Obj);
}
function Trim(Str){
return  Str.replace(/(^\s*)|(\s*$)/g,"");
}
function menuFix(){var sfEls=$("menu").getElementsByTagName("li");for (var index=0;index<sfEls.length;index++){sfEls[index].onmouseover=function(){this.className+=(this.className.length > 0 ? " " : "") + "sfhover";}sfEls[index].onmouseout=function(){this.className=this.className.replace(new RegExp("( ?|^)sfhover\\b"),"");}}
}
function Pid(id,tag){if(!tag){return $(id);} else{return $(id).getElementsByTagName(tag);}
}
function tab_zzjs(id,hx,box,iClass,s,pr){var hxs=Pid(id,hx);var boxs=Pid(id,box);if(!iClass){ // 如果不指定class，则：boxsClass=boxs; // 直接使用box作为容器}else{var boxsClass = [];for(i=0;i<boxs.length;i++){if(boxs[i].className.match(/\btab\b/)){// 判断容器的class匹配boxsClass.push(boxs[i]);}}}if(!pr){ // 如果不指定预展开容器，则：go_to(0); // 默认展开序列yy();} else {go_to(pr);yy();}function yy(){for(var i=0;i<hxs.length;i++){hxs[i].temp=i;if(!s){// 如果不指定事件，则：s="onclick"; // onmouseoverhxs[i][s]=function(){go_to(this.temp);}} else{hxs[i][s]=function(){go_to(this.temp);}}}}function go_to(pr){for(var i=0;i<hxs.length;i++){if(!hxs[i].tmpClass){hxs[i].tmpClass=hxs[i].className+=" ";boxsClass[i].tmpClass=boxsClass[i].className+=" ";}if(pr==i){hxs[i].className+=" up"; // 展开状态：标题boxsClass[i].className+=" up"; // 展开状态：容器} else {hxs[i].className=hxs[i].tmpClass;boxsClass[i].className=boxsClass[i].tmpClass;}}}
}
</script>
<style type="text/css">
/*自定义css*/*{margin:0;padding:0;line-height:150%;}
body{font-family:"microsoft yahei",SimHei;background:#fff;font-size:14px;}
body{background:#fff;}
a{text-decoration:none;}
a:link{color:blue;}
a:visited{color:blue;}
a:hover,a:active{color:blue;}
b{color:red;font-weight:normal;}
.r{text-align:right;}
.l{text-align:left;}
.c{text-align:center;}
span{color:gray;}
.gray{color:gray;}
strong{color:red;}.w{ padding:30px;background-image:url(logo.png);background-position:bottom right;background-repeat:no-repeat;}
p{padding:5px;line-height:150%;}
p a:hover{padding:3px;color:green;}
h2{height:37px;line-height:36px;font-size:28px;padding:12px 0px;width:100%;color:blue;}
h3{font-size:14px;line-height:180%;margin:1px 2px;color:red;}
h4{font-size:14px;line-height:180%;font-weight:normal;margin:1px 2px;color:blue;}
.mabida {position:relative; }
.mabida .aiyaha { position: absolute; left: 0px; top: 20px; z-index:999;background-color:white; border:1px #006AA8 solid; display: none;}
.mabida .aiyaha img { width:138px; margin:12px; }
.mabida:hover .aiyaha { display: block;}
strong,b{color:red;margin:2px;line-height:130%;}
h5{font-size:14px;line-height:120%;font-weight:300;margin:1px 2px;color:blue;}
#tab02{position:relative;margin:0px;border:0px solid #ccc;} 
#tab02 h4 {line-height:150%;padding:5px 2px;font-size:16px;text-align:left;color:blue;} 
#tab02 h4 {cursor:pointer;border:solid #ccc;border-width:1px 0 0 0;} 
#tab02 h4.up {color:red;background:#f6f6f6;line-height:180%;padding:5px 2px;border-width:2px 0 0 0;font-weight:600;} 
#tab02 ol {display:none;border:0px solid #ccc;} 
#tab02 ol.up { display:block;padding:3px 0px; min-height:54px;} 
caption{padding:5px;margin:8px auto;margin-left:18px;}
table{border-top:2px solid #0180CF; margin:0 auto;font-size:12px;width:99%;}
table td{min-width:66px;border-bottom:1px solid #a2c6d3;padding:5px 0px;word-wrap:break-word;word-break:break-all;}
table tr:nth-child(even){background: #FCFCFC;}
.tt{background:#e5f2fa;line-height:18px;FONT-SIZE:12px;font-weight:600;}
.red{background:red;color:white;}
#header,#main,#footer{margin:0px auto;width:99%;min-width:888px;max-width:1188px;display:block;}
#header{background:#fff;height:38px;line-height:38px;border-bottom:2px solid #ccc;z-index:3;}
#header .logo{font-style:italic;padding:0 0 0 8px;font-size:12px;font-weight:300;color:green;}
#header .logo b{height:38px;line-height:38px;font-weight:900;font-size:20px;color:#0180CF;}
#menu{float:right;height:36px;line-height:36px;margin:0 auto;padding:0 8px 0 0;}
#menu ul{list-style:none;margin:0;padding:0;}
#menu ul li{float:left;margin-left:2px;}
#menu ul li a{display:block;border:1px solid white;padding:0 3px;min-width:88px;height:36px;line-height:36px;text-align:center;}
#menu ul li a:hover{border:1px solid #ccc;background:#0180CF;color:white;}
#menu ul li ul{background:white;z-index:5;display:none;top:37px;border:1px solid #ccc;position:absolute;}
#menu ul li ul li{float:none;background:#eee;margin:0;}
#menu ul li ul li a{background:white;border-bottom:1px solid #ccc;}
#menu ul li ul li a:hover{background:#0180CF;color:white;border-bottom:1px solid #ccc;}
#menu ul li:hover ul{display:block;}
#onebyone{margin:0px auto;width:99%;min-width:888px;max-width:1188px;display:block;}textarea{margin:0 auto;width:99.5%;height:300px;}
.select select{width:100%;padding:5px;border:1px #CACACA solid;height:40px;-webkit-appearance:none;}
.select option{height:40px;}
.select{margin:0 auto;width:92%;height:40px;overflow:hidden;}
.so_but{margin:15px auto;width:92%;text-align:center;margin-top:12px;border:0px;}
.so_bus{margin:15px auto;width:92%;text-align:left;margin-top:12px;border:0px;}
.so_but .buts{line-height:200%;padding:5px 25px;background:#0180CF url(line_bg.jpg) repeat-x;border-radius:25px;color:white;border:0px;}
fieldset{margin:5px auto;border:0;border-top:1px solid #ccc;list-style-type:none;table-layout:fixed;white-space: normal;word-break:break-all; }
.w{width:100%;padding:0; }
legend{margin:0px 18px;padding:0px;text-align:center;font-size:20px;color:green;font-weight:600;text-overflow:ellipsis;white-space:nowrap;}
fieldset {word-wrap:break-word;}
.none {border:1px solid green;text-align:center;}
#fooder{display:none;}
#footer{text-align:center;margin-top:0px;padding-top:5px;border-top:1px solid #ccc;}
</style>
</head>
<body>
<div id="main">
<div id='onebyone'>
<!-- table!sta -->
<div id="md">
<fieldset class="w">
<legend><?php echo $t; ?>（<b><?php echo $h; ?></b>）功能内测中</legend>
<div id="tab02">
<?php if(strlen($rst)>1){?>
<h4> 转化结果 </h4>
<ol>
<?php echo $tips; ?>
<div 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"> 
<!-- 
tab_zzjs("tab02","h4","ol");
//--> 
</script> 
</fieldset></div>
<!-- table!old -->
</div>
<div id='fooder' class="linkar">
</div>
</body>
</html>