PHPExcel 批量导入数据库(原生)

批量导入数据库

PHPExcel类下载地址:下载地址

//仅兼容xls
<?php
header('Content-type:text/html;charset=utf-8');
require_once('./PHPExcel/IOFactory.php');
require_once("PHPExcel.php");
require_once("./PHPExcel/Reader/Excel5.php");
require_once("../../include/config.php");
require_once("../checkuser.php");

class uploads{
    public function insertExcel(){
        $file=$_FILES['excel'];
        if(empty($file['name'])){
            echo "<script>alert('请选择文件！');history.go(-1);</script>";
        };
        $filename = time().substr($ex['name'],stripos($ex['name'],'.'));
        $path = './uploads/'.$filename;
        move_uploaded_file($file['tmp_name'],$path);
        $exfn = $this->_readExcel($path);
        array_shift($exfn);
        $data = [];
        
        foreach ($exfn as $k => $v ){
            // 查询媒体类型
            $class_id = $v[1];
            $sql = "SELECT id FROM media_class where SmallClass = '".$class_id."' ";
            mysql_select_db($dbname);
            $result=mysql_query($sql);
            $rs=mysql_fetch_array($result);
            $data[$k]['class_id'] = $rs[0];
            $data[$k]['medianame'] = $v[0];
            $data[$k]['price'] = $v[2];
            $data[$k]['dlprice'] = $v[3];
            $data[$k]['scprice'] = $v[4];
            $data[$k]['linkurl'] = $v[7];
            $data[$k]['xinwenyuan'] = $v[8];
            $data[$k]['content'] = $v[9];
            $data[$k]['weburl'] = $v[10];
            $data[$k]['anliurl'] = $v[10];
            $select = "SELECT medianame FROM media_info where medianame = '".$data[$k]['medianame']."' ";
            $sl = mysql_query($select);
            $res = mysql_fetch_array($sl);
            
            if($res){
                $update = "UPDATE media_info SET nav_id=46,class_id=".$data[$k]['class_id'].",medianame='".$data[$k]['medianame']."',price=".$data[$k]['price'].",dlprice=".$data[$k]['dlprice'].",scprice=".$data[$k]['scprice'].",linkurl='".$data[$k]['linkurl']."',xinwenyuan='".$data[$k]['xinwenyuan']."',content='".$data[$k]['content']."',weburl='".$data[$k]['weburl']."',anliurl='".$data[$k]['anliurl']."',sh=1 WHERE medianame='".$data[$k]['medianame']."' ";
                $up = mysql_query($update);
            }else{
                 $insert = "INSERT INTO media_info(nav_id,class_id,medianame,price,dlprice,scprice,linkurl,xinwenyuan,content,weburl,anliurl,sh) VALUE(46,".$data[$k]['class_id'].",'".$data[$k]['medianame']."',".$data[$k]['price'].",".$data[$k]['dlprice'].",".$data[$k]['scprice'].",'".$data[$k]['linkurl']."','".$data[$k]['xinwenyuan']."','".$data[$k]['content']."','".$data[$k]['weburl']."','".$data[$k]['anliurl']."',1) ";
                     $in = mysql_query($insert);
                }
            }
            
            if($in || $up){
                 echo "<script>alert('导入成功！');history.go(-1);</script>";
             }else{
                 echo $this->error();
                 
             }
        
    }
    
    public function _readExcel($path)
    {    
        //引用PHPexcel 类
        require_once("PHPExcel.php");
        require_once('./PHPExcel/IOFactory.php');//静态类
        $type = 'Excel5';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
        $xlsReader = PHPExcel_IOFactory::createReader($type);  
        $xlsReader->setReadDataOnly(true);
        $xlsReader->setLoadSheetsOnly(true);
        $Sheets = $xlsReader->load($path);
        //开始读取上传到服务器中的Excel文件，返回一个二维数组
        $dataArray = $Sheets->getSheet(0)->toArray();
        return $dataArray;
    }
}
$media = new uploads();
echo $media->insertExcel();

?>