How to import Sword and Spirit pinch data? PHP imports Execl table into database

PHP imports Execl table into database

/**
     * 上传文件
     */
    function uploadFileforExcel()
    {

        // 允许上传的图片后缀
        //$allowedExts = array("gif", "jpeg", "jpg", "png","xls");
        $allowedExts = array("xls", "xlsx");
        $temp = explode(".", $_FILES["file"]["name"]);
        echo $_FILES["file"]["size"];
        $extension = end($temp);     // 获取文件后缀名
        if ($_FILES["file"]["size"] < 204800 && in_array($extension, $allowedExts)) {   // 小于 200 kb
            if ($_FILES["file"]["error"] > 0) {
                echo "错误：: " . $_FILES["file"]["error"] . "<br>";
                return "";
            } else {
                // 判断当期目录下的 upload 目录是否存在该文件
                // 如果没有 upload 目录，你需要创建它，upload 目录权限为 777
                if (file_exists(dirname(dirname(dirname(dirname(__FILE__)))) . DIRECTORY_SEPARATOR . "uploadfile" . DIRECTORY_SEPARATOR . $_FILES["file"]["name"])) {
                    echo $_FILES["file"]["name"] . " 文件已经存在。 ";
                } else {
                    // 如果 upload 目录不存在该文件则将文件上传到 upload 目录下
                    move_uploaded_file($_FILES["file"]["tmp_name"], dirname(dirname(dirname(dirname(__FILE__)))) . DIRECTORY_SEPARATOR . "uploadfile" . DIRECTORY_SEPARATOR . $_FILES["file"]["name"]);
                    return dirname(dirname(dirname(dirname(__FILE__)))) . DIRECTORY_SEPARATOR . "uploadfile" . DIRECTORY_SEPARATOR . $_FILES["file"]["name"];
                }
            }
        } else {
            echo "非法的文件格式";
            return "";//非法的文件格式
        }
    }

    /**
     * 获取Execl表格数据
     */
    function getExeclData()
    {
        //首先导入PhPExcel
        require_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/api/PHPExcel/Classes/PHPExcel.php');
        $filePath = $this->uploadFileforExcel();
        if ($filePath == null || $filePath == '') {
            return;
        }
        //建立reader对象
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                echo 'no Excel';
                return;
            }
        }
        //建立excel对象，此时你即可以通过excel对象读取文件，也可以通过它写入文件
        $PHPExcel = $PHPReader->load($filePath);

        /**读取excel文件中的第一个工作表*/
        $currentSheet = $PHPExcel->getSheet(0);
        /**取得最大的列号*/
        $allColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $allRow = $currentSheet->getHighestRow();

        echo $allColumn . " -- " . $allRow . "<br />";

        //循环读取每个单元格的内容。注意行从1开始，列从A开始
        for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {
            $data = array();
            for ($colIndex = &#39;A&#39;; $colIndex <= &#39;N&#39;; $colIndex++) {
                $addr = $colIndex . $rowIndex;
                $cell = $currentSheet->getCell($addr)->getValue();
                array_push($data, $cell);
            }
            var_dump($data);
            $this->updataForExcel($data);
        }
        unlink($filePath);
    }

    /**
     * 根据Execl数据更新数据库
     * @param array $data
     * $data[0] --> name    客户姓名
     * $data[1] --> sex     性别
     * $data[2] --> cellphone   联系方式
     * $data[3] --> knowchannel 认知途径
     * $data[4] --> intent_size 需求面积
     * $data[5] --> intent_huxing   需求户型
     * $data[6] --> prices_reflect  价格反映
     * $data[7] --> intent_desc     置业目的
     * $data[8] --> focus_desc      关注点
     * $data[9] --> nofocus_desc    不认可点
     * $data[10] --> buytime        置业次数
     * $data[11] --> locdesc        居住区域
     * $data[12] --> intent_level   意向级别
     * $data[13] --> note           备注
     */
    function updataForExcel($data = array())
    {
        if (count($data) == 0) {
            return;
        }
        $cellphone = $data[2];
        if (isset($cellphone)) {

            $info = $this->useinfo_tag_db->get_one("cellphone = $cellphone");
            $settime = time();

            if (null != $info) {//原数据存在，修改
                $sql = "update useinfo_tag set name='$data[0]',sex='$data[1]',knowchannel='$data[3]',";
                $sql .= "intent_size='$data[4]',intent_huxing='$data[5]',prices_reflect='$data[6]',";
                $sql .= "intent_desc='$data[7]',focus_desc='$data[8]',nofocus_desc='$data[9]',";
                $sql .= "buytime='$data[10]',locdesc='$data[11]',intent_level='$data[12]',";
                $sql .= "note='$data[13]',settime=$settime";
                $sql .= " where cellphone = '$cellphone'";
                $result = $this->useinfo_tag_db->query($sql);
                if ($result) {
                    echo "修改成功";
                } else {
                    echo "修改失败";
                }
            } else {//没有当前数据，插入新数据

                $sql = "insert into useinfo_tag(name,sex,cellphone,knowchannel,intent_size,intent_huxing,";
                $sql .= "prices_reflect,intent_desc,focus_desc,nofocus_desc,buytime,locdesc,intent_level,note,settime)";
                $sql .= " values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]',";
                $sql .= "'$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]',$settime)";
                $result = $this->useinfo_tag_db->query($sql);
                if ($result) {
                    echo "插入成功";
                } else {
                    echo "插入失败";
                }
            }

            echo "<br />";

        }
    }

html part of the code:

<span><script </span><span>language=</span><span>"javascript" </span><span>type=</span><span>"text/javascript" </span><span>src=</span><span>"</span><span><?php echo </span><span><em>JS_PATH </em></span><span>?></span><span>jquery.form.js"</span><span>></script></span>

<span><form </span><span>id=</span><span>"form_file" </span><span>action=</span><span>"?m=kfqapp&c=useinfo_tag&a=getExeclData" </span><span>method=</span><span>"post"
</span><span>enctype=</span><span>"multipart/form-data"</span><span>>
</span><span>    <label </span><span>for=</span><span>"file"</span><span>></span><span>导入</span>Excel<span>表：</span><span></label>
</span><span>    <input </span><span>type=</span><span>"file" </span><span>name=</span><span>"file" </span><span>id=</span><span>"file"</span><span>/>
</span><span>    <input </span><span>type=</span><span>"button" </span><span>id=</span><span>"upfileSubmit" </span><span>name=</span><span>"upfileSubmit" </span><span>value=</span><span>"</span><span>提交</span><span>"</span><span>/>
</span><span></form></span>

<span>$</span>(<span>"#upfileSubmit"</span>).<span>click</span>(<span>function </span>() {

    <span>var </span>options = {
        <span>beforeSend</span>: <span>function </span>() {
            <span>//console.log("</span><span>开始</span><span>");
</span><span>$</span>(<span>'#container'</span>).<span>css</span>(<span>"display"</span><span>, </span><span>"block"</span>)<span>;
</span>}<span>,
</span><span>success</span>: <span>function </span>(data) {
            <span>//console.log("</span><span>结束</span><span>");
</span><span>$</span>(<span>'#container'</span>).<span>css</span>(<span>"display"</span><span>, </span><span>"none"</span>)<span>;
</span><span>window</span>.<span>location</span>.<span>reload</span>()<span>;
</span>}
    }

    <span>$</span>(<span>"#form_file"</span>).<span>ajaxSubmit</span>(options)<span>;
</span>})<span>;</span>

The above introduces how to import Sword and Soul pinch data and PHP to import the Execl table into the database, including how to import Sword and Soul pinch data. I hope it will be helpful to friends who are interested in PHP tutorials.