防攀爬系统——EXCEL导入数据库

最近一直在改fpp,这是学长们之前做的“防攀爬系统”,现在需要修改一些BUG以及增加一些功能。现将一些经验记录一下,提醒自己在之后的项目中不要去犯类似的错误。

首先是添加模块的问题,没看到已经投入使用的服务器上的代码,便一股脑地从本地的代码找原因,甚至还找到了服务器上代码和数据库的访问权限上。后来实在走投无路了,想到服务器上的代码会不会和实验室SVN上的不一样。果真如此,投入使用的服务器上并不是最新的代码。其实造成添加不了的原因就是231这一行代码,在填写表单信息点击提交按钮后,需要插入到数据库中,else里的语句就是执行插入操作的,但刚进去就return 1;了,实际上并没执行下面的语句。那为什么web端还提示“新增成功”呢?这就又是因为这个return 1了,MVC里交互时自定义1是成功的意思,正确的办法是把return 1;放在else语句结束的地方。

其次就是添加描述的问题了,添加的页面里有描述的栏目,但表单传值时没有传到后台,如图在node_model文件里第133行缺少


修改时没有加入修改描述的栏目,修改edit_node.php页面如下部分

还有node_mode里的get_node()函数

完成之后,修改描述会提示“没有做任何修改”的错误,找到原因发现node_model如下页面,这一判断语句显然是有问题的,如果3G号码没有改变就认为没有做任何修改了,没考虑描述、心跳、自检时间等的修改,return语句去掉即可

到此,描述的增加、修改问题成功解决。

为减少一个个添加模块所带来的不必要麻烦,需要添加一个从EXCEL表导入数据库的功能。这一模块主要有两个大的方面,一个是上传EXCEL文件,另一个是读取EXCEL写入数据库。先考虑的是后面一个,因为这一功能之前还没有学习过,上传文件在“学生科研项目管理平台”上已经得到了应用。网上提供读取EXCEL的类库还是挺多的,PHPExcel是用得比较多的类库了,也扩展出许多功能。不过还是从网上找了个excel_read2.php的类库,首先需要新建一个Spreadsheet_Excel_Reader对象,接下来是读取EXCEL数据的一个编码方式,在这边又纠结了好久。因为数据库的编码已经统一设置成了UTF-8,而MS的EXCEL编码是CP936(之前还以为是GBK的,实际通过mb_detect_encoding()函数输出后发现是CP936格式的,百度之后发现GBK和CP936是有很大相关性的,微软的CP936通常等同为GBK,但实际上GBK比CP936多出95个字,详细介绍见http://zh.wikipedia.org/wiki/GBK),当我已UTF-8的格式将EXCEL表显示出来时是奇怪字符的,GBK则会是正确的。

获取表头信息,通过比较与数据库中的字段,相同的时候再插入,这也是为了防止不按模版格式随便导入EXCEL的情况。j接下来的就是mysql_connect()连接数据库,mysql_select_db()选择数据库和mysql_query()设置编码的格式。

刚开始时每读取一行就insert数据库一遍,测试了几遍300个数据,花费的时间惊呆了,几乎都是18s多。

这也是因为这不是仅仅的插入数据,而是需要先从EXCEL表中读取出来,然后才执行插入语句。之后选择将EXCEL表的数据一次性读取出来,拼接成一句insert语句,测了几遍后时间都在200ms左右,

这明显提高了很多很多。测了一遍单一的300条insert语句,时间也是要2s左右的,这也给自己一个启示,以后在插入大量数据的时候,不要用循环插入法,而是一条语句插入所有记录。

附上读取EXCEL并写入数据库的源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
/**
*读取excel文件并写入数据库
*/
public function read_excel($filename)
{
require_once ’/application/libraries/PHPExcel/excel_read2.php’;
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(’gbk’);
$data->read("./application/upload/node_excel/".$filename);
//获取表头
$name_title = $data->sheets[0][’cells’][1][1];
$SIM_title = $data->sheets[0][’cells’][1][2];
$desc_title = $data->sheets[0][’cells’][1][3];
$heart_title = $data->sheets[0][’cells’][1][4];
$IP_title = $data->sheets[0][’cells’][1][5];
$port_title = $data->sheets[0][’cells’][1][6];
//判断表头信息是否为模版里的表头,不是的话返回提示
if(!($name_title == "模块名称" && $SIM_title == "3G号码" && $desc_title == "模块描述" && $heart_title == "心跳间隔" && $IP_title == "服务器IP地址" && $port_title == "服务器port"))
{
return -1;
}
$con = mysql_connect("localhost","UID","PWD") or die("mysql_connect failed");
mysql_select_db(’fpp’) or die("mysql_select_db failed");
mysql_query("set names ’gbk’");//输出中文
//具体信息
$sql = "";
for ($i = 2; $i <= $data->sheets[0][’numRows’]; $i++)
{
$sql .= "(’".
$data->sheets[0][’cells’][$i][1]."’,’".
$data->sheets[0][’cells’][$i][2]."’,’".
$data->sheets[0][’cells’][$i][3]."’,’".
$data->sheets[0][’cells’][$i][4]."’,’".
$data->sheets[0][’cells’][$i][5]."’,’".
$data->sheets[0][’cells’][$i][6]."’),";
/*$sql = "INSERT INTO fpp_node(name,`desc`, ip) VALUES(’".
$data->sheets[0][’cells’][$i][1]."’,’".
$data->sheets[0][’cells’][$i][2]."’,’".
$data->sheets[0][’cells’][$i][3]."’)";
$sql = "INSERT INTO fpp_node(name,`desc`, ip) VALUES(’1’, ’2’, ’3’)";
if(mysql_query($sql,$con) == false)
return 0;
*/
}
$sql = substr($sql,0,strlen($sql)-1);//去除最后一个逗号
$sql = "insert into fpp_node(`name`, `SIMnumb`, `desc`, `heart_beat_interval`, `ip`, `server_port`) VALUES ".$sql;
if(mysql_query($sql,$con) == false)
{
mysql_close($con);
return 0;
}
return 1;
}

上传文件部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/**
*上传文件
*/
public function upload_excel()
{
if(($_FILES[’file’] !=""))
{
$tmp_filename = time().".xls";
if($_FILES["file"]["error"] == 1)
{
return "-1";//出错
}
elseif($_FILES["file"]["error"] == 7)
{
return "-7";//文件写入失败
}
elseif($_FILES["file"]["type"] != "application/kset")
{
return 2;//文件类型不符
}
elseif ($_FILES["file"]["size"] > 1024*1024)
{
return 3;//文件过大
}
elseif(file_exists("application/upload/node_excel/" . $tmp_filename))
{
return 4;//文件已存在
}
elseif(is_uploaded_file($_FILES[’file’][’tmp_name’]))
{
$address = "application/upload/node_excel/".$tmp_filename;//文件的保存目录
move_uploaded_file($_FILES["file"]["tmp_name"], $address);
if($this->read_excel($tmp_filename) == -1)
{
return -1;
}
return 1;//成功
}
else
{
return -1;
}
}
else
{
return 0;//未选择文件
}
}

下载模版部分则相对比较简单

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/**
*下载模版
*/
public function download_excel()
{
$filename = "./application/data/node/fpp.xls";
$out_filename = "fpp.xls";
if(!file_exists($filename))
{
return 0;
}
else
{
@header(’Accept-Ranges: bytes’);
@header(’Accept-Length: ’ . filesize($filename));
@header(’Content-Transfer-Encoding: binary’);
@header(’Content-type: application/octet-stream’);
@header(’Content-Disposition: attachment; filename=’ . $out_filename);
@header(’Content-Type: application/octet-stream; name=’ . $out_filename);
$file = @fopen($filename, "r");
echo @fread($file, @filesize($filename));
@fclose($file);
exit();
}
return 1;
}
Just a beginner.<br /><a href='https://github.com/yaoshanliang/about' target='_blank'>profile</a>