tp3.2.3运用phpexcel将excel文件导入mysql数据库

1,下载PHPExcel
2,配置将下载好的PHPExcel文件与PHPExcel.php 放到thinkphp 根目录 include/Library/Org/Util/下面
3,同时将PHPExcel.php 改名为 PHPExcel.class.php
以上是将PHPexcel扩展添加到tp里面(本人tp版本为3.2.3)

4,html代码
tp标签
<!-- $Id: brand_list.htm 15898 2009-05-04 07:25:41Z liuhui $ -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>零件管理</title>
<meta name="robots" content="noindex, nofollow">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="__PUBLIC__/css/page.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/styles/general.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/styles/main.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/css/style.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="__PUBLIC__/js/jquery.js"></script>
</head>
<body>
<form method="post" action="" name="theForm" id="uploadForm" enctype="multipart/form-data">
      <input type="file" class="se2" name="file" id="file" size="45" onchange="checkField(this.value)" style="height: 30px; width: 300px;"/>
      <label for="f_file">
          <input class="se1" type="button" value="导入" />
      </label>
</form>
</body>
</html>
<script>
function checkField(val)
{
    UpladFile();
    changev();
}
function UpladFile() {
    var fileObj = document.getElementById("file").files[0]; // 获取文件对象
    var FileController = "__URL__/doleadin";     // 接收上传文件的后台地址
    // FormData 对象
    var form = new FormData();
    form.append("author", "hooyes");      // 可以增加表单数据
    form.append("file", fileObj);       // 文件对象
    // XMLHttpRequest 对象
    var xhr = new XMLHttpRequest();
    xhr.open("post", FileController, true);
    xhr.onload = function () {
        alert('上传完成!');
    };
    xhr.send(form);
}
function changev(){
    $('#file').attr('value','');
}
</script>

5,tp后台接受并处理上传文件代码
//处理文件上传 并导入mysql
    public function doleadin()
    {
        $upload = new \Think\Upload();// 实例化上传类
        $upload->maxSize   =     3145728 ;// 设置附件上传大小
        $upload->exts      =     array('xls', 'xlsx');// 设置附件上传类
        $upload->savePath  =      '/'; // 设置附件上传目录
        // 上传文件
        $info   =   $upload->uploadOne($_FILES['file']);
        $filename = './Uploads/'.$info['savepath'].$info['savename'];
        $exts = $info['ext'];

        if(!$info) {// 上传错误提示错误信息
              $this->error($upload->getError());
          }else{// 上传成功
                  $this->import_excel($filename, $exts);
        }
    }

    /**
     * 导入excel文件
     * @param  string $file excel文件路径
     * @return array        excel文件内容数组
     */
    public function import_excel($filename, $exts='xls')
    {
        //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.IOFactory");
        //创建PHPExcel对象,注意,不能少了\
        $PHPExcel=new \PHPExcel();
        //如果excel文件后缀名为.xls,导入这个类
        if($exts == 'xls'){
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader=new \PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader=new \PHPExcel_Reader_Excel2007();
        }


        //载入文件
        $PHPExcel=$PHPReader->load($filename);
        //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
       $data = array();
       for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始,列从A开始
                for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
                         $addr = $colIndex.$rowIndex;
                         $cell = $currentSheet->getCell($addr)->getValue();
                       if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                                $cell = $cell->__toString();
                        }
                        $data[$rowIndex][$colIndex] = $cell;
                }
        }
        if(is_file($filename)){
            unlink($filename);
        }
        $this->save_import($data);
    }

    //保存数据到数据库
    public function save_import($data)
    {
        //零件配置表
        $obj_dlaccess=new \Model\Dl_accessModel();
        //零件系列表
        $obj_info = new \Model\Dl_infoModel();
        //城市MOdel
        $city_info = new \Model\CityModel();
        $result_msg = array();

        foreach ($data as $k=>$v){
           // $v['C'] = (String)$data[$k]['C'];

            if($k >= 2){
                //$v['C'] = (String)$data[$k]['C'];

                $data = '';
                //系列id
                $info_name = (String)htmlspecialchars(trim($v['B']));
                $info_id = $obj_info->file_exist($info_name);
                if($info_id){
                    $data['info_id'] = $info_id;
                    $data['access_name'] = htmlspecialchars(trim($v['C']));
                    $city_name = htmlspecialchars(trim($v['D']));
                    $data['city_id'] = $city_info->city_id($city_name);
                    $data['access_mark'] = htmlspecialchars(trim($v['E']));
                    $access_num = (int)htmlspecialchars(trim($v['F']));
                    if(is_int($access_num)){
                        $data['access_num'] = $access_num;
                        $access_price = $v['G'];
                        $arr = explode('/',$access_price);
                        if(is_array($arr)){
                               $data['access_price']=$arr[0];
                               if($arr[1]=='元'){
                                   $data['price_stu'] = 1;
                               }else{
                                   $data['price_stu'] = 2;
                               }
                               $data['access_dealer'] = htmlspecialchars(trim($v['H']));
                               $data['access_phone'] = htmlspecialchars(trim($v['I']));

                               $arr1 = htmlspecialchars(trim($v['J']));
                               $arr1 = explode('/',$arr1);
                               if(is_array($arr1)){

                                   $data['inventory']=$arr[0];
                                   if($arr[1]=='个'){
                                       $data['inventory_stu'] = 1;
                                   }else{
                                       $data['inventory_stu'] = 2;
                                   }
                                   //查询是否已存在该零件
                                   $return = $obj_dlaccess->filed_exsit($data);

                                   if(empty($return)){
                                       $data['up_time'] = time();
                                       $result = $obj_dlaccess->to_add($data);
                                       if($result){
                                           array_push($result_msg,'第'.($k-1).'条导入成功!<br/>');
                                       }
                                   }else{
                                           array_push($result_msg,'第'.($k-1).'条导入失败!<br/>');
                                   }
                               }

                        }else{
                            return 'excel格式错误';
                        }
                    }else{
                        return 'excel格式错误';
                    }
                }else{
                    return 'excel格式错误';
                }
            }
        }
   // return $result_msg;
    }
注意:如遇到例如:
object(PHPExcel_RichText)[98]
private '_richTextElements' =>
 array
   0 =>
     object(PHPExcel_RichText_TextElement)[99]
       private '_text' => string '96018270' (length=8)
   1 =>
     object(PHPExcel_RichText_Run)[608]
       private '_font' =>
         object(PHPExcel_Style_Font)[164]
           ...
       private '_text' (PHPExcel_RichText_TextElement) => string '/96018290' (length=9)
 此类型的数据  则将该数据 强制转换为(string)string字符串即可!

posted on 2017-08-31 15:37  好好耕耘  阅读(549)  评论(0编辑  收藏

导航

统计