先看图,后有完整代码
<?php
$t = "Excel数据转Sql查询系统字段半智能分析";
$s = "Excel复制过来的二维结构表内容,分析查询条件!";
$x = "字段|最大长度|长度有|数字字段|空值存在|纯姓名|英文用户名|科学计数|是否等长|是否唯一";
$xx= explode("|",$x);
function isname($str) {$pattern = '/^[\x{4e00}-\x{9fa5}]{2,8}$/u'; // Unicode编码范围内的汉字return preg_match($pattern, $str);
}
function isuser($str) {$pattern = '/^[a-zA-Z0-9_\-]{6,18}$/'; //return preg_match($pattern, $str);
}
$tt=array();
$tt["空值存在"]="查询条件列内容如果空值则查询不到";
$tt["纯汉字姓名特征"]="是(2-6汉字):适合查询条件";
$tt["英文用户名特征"]="是(6-16字母数字):适合查询条件";
$tt["科学计数"]="数值已科学计数,会影响数值精度和人眼识别;如果是查询条件则查询不到!";
$datedir = "";
if($_GET["x"] == "cha"){
$base = isset($_POST['tips']) ? $_POST['tips'] : "";
$files="S2".date("YmdHis");
$uptis = "上传失败:";
if(!stristr($base,"\t"))$uptis .= "制表符;";
if(!stristr($base,"\n"))$uptis .= "回车符;";
if($uptis<>"上传失败:") exit("提交内容由Excel复制过来:应当包含:".$uptis);
file_put_contents($datedir.$files.".csv", $base);$file = fopen($datedir.$files.".csv", 'r');
$header = fgetcsv($file, 0, "\t");$nozd = count($header); $jj=0;
$max_lengths = array_fill(0, $nozd, 0); //字段最长
$lengths = []; //字段长度
$strlist = []; //字段集合
$lies = []; //字段集合
$num_str = array_fill(0, $nozd, "是"); //是否全数字
$is_null = array_fill(0, $nozd, "No"); //是否空值
$is_user = array_fill(0, $nozd, "<b>是</b>");
$is_name = array_fill(0, $nozd, "<b>是</b>");
while (($line = fgetcsv($file,0,"\t")) !== false) {$jj ++; if(count($line)<>$nozd) $lies[] = $jj;foreach ($line as $i => $value) {$strlist[$i][] = $value;$lenx = strlen($value);$lengths[$i][] = strlen($value);if( Trim($value)=="" ) $is_null[$i] = "<b>是</b>";if( !is_numeric($value) ) $num_str[$i] = "否";if( !isuser($value) ) $is_user[$i] = "否";if( !isname($value) ) $is_name[$i] = "否";$max_lengths[$i] = max($max_lengths[$i], strlen($value));}
}
fclose($file);
echo "<h3>分析结果:<strong>机器识别,仅供参考</strong></h3>\r\n";
echo "<table cellspacing=\"0\" class=\"table\" cellpadding=\"0\">\r\n";
echo "<tr class='tt'><td>".join("</td><td>",$xx)."</td></tr>\r\n";
for($ii=0;$ii<$nozd;$ii++){$line = [];$uni = array_count_values($strlist[$ii]); $una = array_keys($uni); $unb = join(";",$una);$lilen = array_unique($lengths[$ii]); $listl = join(",",$lilen);$line["字段"] = $header[$ii];$line["最长长度"] = $max_lengths[$ii];$line["字段长度有"] = $listl;$line["字段属性"] = $num_str[$ii];$line["空值存在"] = $is_null[$ii];$line["汉字名字"] = $is_name[$ii];$line["用户名"] = $is_user[$ii];
preg_match_all("/[+-]?[\d]+([\.][\d]+)?[Ee][+-]?[\d]+/", $unb, $match);
$kes = count($match[0]);
if($kes>0){ $line["科学计数"] = "异常<b>$kes</b>处";}else{ $line["科学计数"] = "正常";} if(count($lilen)==1) $line["是否等长"] = "是"; else $line["是否等长"] = "否";$isc = array_diff($uni, [1]);if(count($isc)<1) $line["是否唯一"] = "是"; else $line["是否唯一"] = "否";echo "<tr><td>".join("</td><td>",$line)."</td></tr>\r\n";
}
echo "</table>\r\n";
echo "<p><b>请核对:</b>核对数据:{$jj}行{$nozd}列</p>\r\n";
if(count($lies)>0) echo "<p><b>有".count($lies)."行</b>(行:".join(",",$lies).")列数与标题列数({$nozd})不一致</p>\r\n";
foreach($tt as $ti=>$tv){ echo "<p><b>$ti</b>: $tv</p>";}exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<title><?php echo $t; ?></title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,minimum-scale=1.0,maximum-scale=1.0">
<meta name="apple-mobile-web-app-capable" content="yes" />
<script type="text/javascript">
console.log("问题反馈电话:","15058593138");
console.log("问题反馈邮件:","admin@12391.net");
function $(objId){
return document.getElementById(objId);
}
function loadcha(xid) {
var xmlhttp;
var Stxt= "nums=aa";
Stxt+="&tips="+ encodeURIComponent($("tips").value);
//$("tips").innerHTML = "正在加载...";
if (window.XMLHttpRequest) {
xmlhttp = new XMLHttpRequest();
} else {
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
var btxt = xmlhttp.response;
if(btxt == "err01"){ $("tipx").innerHTML = "!"; return false;}
$('tipx').innerHTML = xmlhttp.response;
}
}
xmlhttp.open("POST", "?x=cha&tt="+Math.random(), true);
xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xmlhttp.send(Stxt);
}</script>
<style>
div,#tipx{display:block;width:99.7%;border:0;margin-top:5px;}
textarea{display:block;width:99.7%;border:1px solid #ccc;height:160px;}
table{margin:20px auto;border-left:1px solid #a2c6d3;border-top:3px solid #0180CF;font-size:12px;width:99.7%;}
table td{border-right:1px solid #a2c6d3;border-bottom:1px solid #a2c6d3;padding:2px;word-wrap:break-word;word-break:break-all;}
td{min-width:30px;max-width:490px;}
.tt{background-color: #f2f2f2;}
b{color:red;}
#submit{ height:35px;}
</style>
</head>
<body><form class="form" id="form" method="POST" act="?act=cha" >
<p><?php echo $t; ?></p>
<textarea id="tips">
姓名 学号 身份证 科目1 科目2 科目3 科目4 科目5 科目. 科目N
李一一 10001001 90001001 87 84 75 91 83 76 87
李二 10001002 90001001 95 81 81 71 60 8299
李三 10001003 90001003 68 80 65 79 68 71 91
李四 10001004 90001004 82 80 75 90 87 1.23457E+15 81
李五 10001005 90001005 60 64 61 71 73 61
李六 10001006 90001006 62 69 77 73 96 6 82
李七 10001007 90001007 98 72 68 59 84 333 92
</textarea><input type="button" id="submit" value="提交分析" onclick="loadcha('xid')">
<div id="tipx"></div></form>
</body>
</html>
仅供娱乐!