PHP, MYSQL, EXCEL 文档处理实例（变态级）

时间：2010-04-01 来源：kkboy_dyc

朋友手机同步出错，号码丢失。导出的EXCEL文件格式混乱，无法按映射关系导回手机。
先分析一下EXCEL文件：（截取部分数据样本，电话号码用11111替代）如下

C1 ID C2

全名:	1	爱尼特
名字:	2	爱尼特
	3
移动电话:	4	111111111
	5
全名:	6	PDA姜
名字:	7	PDA姜
	8
移动电话:	9	111111
	10
全名:	11	阿卡迪亚物业
姓氏:	12	阿卡迪亚物业
	13
移动电话:	14	11111111
	15
全名:	16	阿满
名字:	17	阿满
	18
移动电话:	19	1111111111

从上述数据块来看，导出的数据，每5行其实是一个“名片”，该EXCEL中一共有1300多个名片。
我们需要将其转化成如下格式的EXCEL才能重新导回OUTLOOK:

姓名	电话
张三	1111
李四	1111

如果我们手工来完成，是会死人的。

首先我们来将上述EXCEL导入MYSQL以便同PHP来加工，并重新部署数据。

1. 将原EXCEL按照以上3个字段(C1,ID,C2)分别保存为UTF-8的TXT文档。
1-1.txt 1-2.txt 1-3.txt

建立MYSQL 数据库 neil

建立表 test，字段为ID,C1,C2 （注意字段与变量的对应）

通过如下代码导入MYSQL

<?php
$id = file('c:\1-1.txt');
$C1 = file('c:\1-2.txt');
$C2 = file('c:\1-3.txt');
$i = 0;

mysql_connect('localhost','root','root');
mysql_query("set names utf8");

while (strlen($id[$i])>0)
{
$sql="insert into test values ('$id[$i]','$C1[$i]','$C2[$i]')";
$do=mysql_db_query("neil",$sql);
$i=$i+1;
echo ' ';
}
$s="select * from test";
$gg=mysql_db_query("neil",$s);
$n=mysql_num_rows($gg);
mysql_close();
echo ' ';
echo "共添加了".$n."个记录";

执行后以上数据就导入了MYSQL。

2. 开始剥离数据

意外：在EXCEL原文件中，最重要的事情有两个。

第一，确定有多少张名片，怎么来识别每一张名片？
第二，如何找到每张名片中的电话号码

通过分析数据段我们发现，每张名片都有一个“全名”是它独立的字符串，虽然我们也可以通过“名字”这个字符串来区分每张名片，但我们会发现，“名字”并不是唯一的，有的名片中用“姓氏”替代了“名字”，当更糟糕的事情是，随着深入的浏览原EXCEL表格，发现即便是“全名”也不是可以用来区分每张名片的唯一标识，有的“全名”使用了其他的名称来标识。

这就意味着我们无法计算这个EXCEL中到底有多少张名片。。

但通过分析，我们可以看到，ID是一个自然的递增数列。每5行代表一张名片。这样就给了我们一个思路。

我们可以通过ID和每张名片所占有的行数，来计算到底有多少张名片。

我们需要重新来导入数据到MYSQL，目的是清除空行。我们首先删除原文件ID列，保留C1,C2列，然后清除C1,C2列中的空行。最后重新插入ID列，删除多余的ID行数。保持ID,C1,C2数据对应。效果如下：

全名:	爱尼特候怡
名字:	爱尼特候怡
移动电话:	11111111
全名:	PDA姜
名字:	PDA姜
移动电话:	11111111
全名:	阿卡迪亚物业
姓氏:	阿卡迪亚物业
移动电话:	11111111
全名:	阿满
名字:	阿满
移动电话:	111111111

不难发现，每张名片占用了3行。
现在我们开始数据剥离，看如下代码：

<?php
$conn = mysql_connect('localhost','root','root');
mysql_select_db('neil',$conn);
mysql_query("set names utf8");

for($i=-2;$i<=4068;$i++){
 $n = $i;
 $n = $n+3;
 $i = $n-1;
 $num = $i+1;

 $sql_1 = "select * from test where ID=$num";
 $singel_res = mysql_query($sql_1);
 $row = mysql_fetch_array($singel_res);
 $C1 = $row['C1'];
 $C2 = $row['C2'];
 $sql_add = "insert into fullname (name) values ('$C2')"；
 $result = mysql_query($sql_add);
 if($result){
 echo "ok"." ";
 }else{
 echo "sorry"." ".mysql_error();
 }
 //}

}
?>

其中有一个算法，是专门用来计算从1-4068条记录中，挑出：
1，4，7，10，13.........
以上数列中的每一个数字，代表一张独立的名片。而其对应的C2列数据，正是我们需要整理出来的姓名，执行后效果如下：

			id	name
			1	爱尼特
			2	PDA姜
			3	阿卡迪亚物业
			4	阿满
			5	阿瓦山寨

现在我们从混乱的数据中剥离出来了我们需要的姓名列表。相信大家已经知道大概是什么意思了吧

接下来我们来做电话号码的剥离，看如下代码：

<?php
$conn = mysql_connect('localhost','root','root');
mysql_select_db('neil',$conn);
mysql_query("set names utf8");

for($i=1;$i<=4068;$i++){

 $sql_1 = "select * from test where ID=$i";
 $singel_res = mysql_query($sql_1);
 $row = mysql_fetch_array($singel_res);
 $C1 = $row['C1'];
 $C2 = $row['C2'];
 $sql_add = "insert into fullphone (phone) values ('$C2')";
 if(strstr($C1,'移动')!==false or strstr($C1,'商务')!==false){
 $result = mysql_query($sql_add);
 if($result){
 echo "ok"." ";
 }else{
 echo "sorry"." ".mysql_error();
 }
 }
}
?>

以上是从混乱数据中按照“移动电话”或者“商务”电话的映射关系，来剥离电话号码。效果如下：

			id	phone
			1	1391313131
			2	6131313131
			3	683113131
			4	113131313
			5	0313131313

相信到了这里，大家都知道接下来该怎么做了。

对，合并两个表。最后生成EXCEL，比较简单，我就不再讲了

看效果图：

姓名	电话
爱尼特候怡	3131313131
PDA姜	131318
阿卡迪亚物业	131313
阿满	31313131
阿瓦山寨	3131313131

现在的格式已经可以被OUTLOOK接受了，把它导回到联系人中去吧。

PHP, MYSQL, EXCEL 文档处理实例 （变态级）

PHP, MYSQL, EXCEL 文档处理实例（变态级）