防攀爬系统——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://about.iat.net.cn' target='_blank'>profile</a>