说明:在Linux下把excel数据导入到mssql数据库中,这实在是很搞笑的一个活动!几乎很少有程序员研究过这个问题吧?如此变态的问题,估计我是第一个研究的!呵呵,完全是自找苦吃~~,但是今天完了这项太有挑战性的工作!
先清理一下思路先,~~
首先:需要把文件上传到服务器上
然后:读取excel数据列显示出来
然后:让用户选择字段的对应关系
然后:提交数据,读取字段的对应关系
最后:批量导入数据,删除临时文件
一共是以上五步骤!我们一步步分析~~~
第一步:下载附件中的phpexcelparser4.rar ,这个文件是上传excel盗服务器上并以web形式展示出来的!这个一般没有问题的!问题是程序的做法是把表存为临时表而没有真正保存下来,所以首先要更改程序代码为
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='fkhxm'>客户姓名</option>");
echo("\n<option?value='fsfzh'>身份证号</option>");
echo("\n<option?value='fyddh'>移动电话</option>");
echo("\n<option?value='ftxdz'>通信地址</option>");
echo("\n<option?value='femail'>Email</option>");
echo("\n<option?value='flxdh'>联系电话</option>");
echo("\n<option?value='fkhah'>客户爱好</option>");
echo("\n<option?value='fbzxx'>备注信息</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>";
}
*/
}
运行效果如下:[attach=1][attach=1]
第二步是要读取数据出来,代码如下:
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");
}
//以及读取完毕,如果没有错误的话就可以循环往MSSQL中增加数据了!
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> </td>\n";
$fkhxmnum=0;
$fsfzhnum=0;
$fyddhnum=0;
$ftxdznum=0;
$femailnum=0;
$flxdhnum=0;
$fkhahnum=0;
$fbzxxnum=0;
for(?$j=0;?$j<=$ws['max_col'];?$j++?)?{
//print?"<td?class=index> ";
//if(?$j>25?)?print?chr((int)($j/26)+64);
//先读取列名
$tmpcolum=trim($_POST["$j"]);
//echo($tmpcolum."|");
if?($tmpcolum=="fkhxm")?$fkhxmnum=$j;
if?($tmpcolum=="fsfzh")?$fsfzhnum=$j;
if?($tmpcolum=="fyddh")?$fyddhnum=$j;
if?($tmpcolum=="ftxdz")?$ftxdznum=$j;
if?($tmpcolum=="femail")?$femailnum=$j;
if?($tmpcolum=="flxdh")?$flxdhnum=$j;
if?($tmpcolum=="fkhah")?$fkhahnum=$j;
if?($tmpcolum=="fbzxx")?$fbzxxnum=$j;
}
for(?$i=0;?$i<=$ws['max_row'];?$i++?)?{
//$fkhxm=
//echo($fkhxmnum.$fsfzhnum.$fyddhnum.$ftxdznum.$femailnum.$flxdhnum.$fkhahnum.$fbzxxnum);
//print?"<tr><td?class=index>".($i+1)."</td>\n";
if(isset($ws['cell'][$i])?&&?is_array($ws['cell'][$i])?)?{
if?($fkhxmnum!=0&&$ftxdznum!=0&&($fyddhnum!=0||$flxdhnum!=0))//请在这里指定必须的不为空的字段
{
$sql="insert?into?k_qlkhxx(fkhxm,fsfzh,fyddh,ftxdz,femail,flxdh,$fkhah,fbzxx,fglry,fglryxm,fdjry,ffzdm)?values('".uc2html($exc->sst['data'][$ws['cell'][$i][$fkhxmnum]['data']])."','".$exc->sst['data'][$ws['cell'][$i][$fsfzhnum]['data']]."','".$exc->sst['data'][$ws['cell'][$i][$fyddhnum]['data']]."','".uc2html($exc->sst['data'][$ws['cell'][$i][$ftxdznum]['data']])."','".uc2html($exc->sst['data'][$ws['cell'][$i][$femailnum]['data']])."','".$exc->sst['data'][$ws['cell'][$i][$flxdhnum]['data']]."','".uc2html($exc->sst['data'][$ws['cell'][$i][$fkhahnum]['data']])."','".uc2html($exc->sst['data'][$ws['cell'][$i][$fbzxxnum]['data']])."','".$_SESSION["uyhmc"]."','".$_SESSION["uyhxm"]."','".$_SESSION["uyhmc"]."','".$_SESSION["ubm"]."')";
echo($sql."<br>");
}
//$conn->Query($sql);
}
}
}
//导入完成删除文件
unlink($filename);
}
你注意没有,我把执行的那一行注释掉的,只要去掉注释就可以正确执行了!
粤ICP备09177306号
0 Comments.