欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

laravel中利用PHPExcel读取excel数据存入数据库

程序员文章站 2022-05-17 09:28:34
...

文件的位置:

laravel中利用PHPExcel读取excel数据存入数据库

类的引入:

use IOFactory;
use PHPExcel_Cell;
use PHPExcel;

上传文件:

 /**
     * 文件上传
    */
    public function upload(Request $request){
        if($request->isMethod('post')){
            $file = $request->file('excel');
            if ($file->isValid()) {
                // 获取文件相关信息
                $originalName = $file->getClientOriginalName(); // 文件原名
                $ext = $file->getClientOriginalExtension();     // 扩展名
                $realPath = $file->getRealPath();   //临时文件的绝对路径
                $type = $file->getClientMimeType();     // image/jpeg
                if(!in_array($ext,['xls','xlsx','csv']) ){
                    return redirect('admin/index/index')->withInput()->with(with_err("文件类型错误!"));
                }
                // 上传文件
                $filename = date('YmdHis').'-' . uniqid() . '.' . $ext;
                // 使用我们新建的uploads本地存储空间(目录)
                //这里的uploads是配置文件的名称

                //把临时文件移动到指定的位置,并重命名
                $path = public_path().DIRECTORY_SEPARATOR.'uploads'.DIRECTORY_SEPARATOR.'excel'.DIRECTORY_SEPARATOR.date('Ym').DIRECTORY_SEPARATOR;


                $bool =  $file->move($path,$filename);

                if($bool){
                    $img_path =public_path() . '/uploads/excel/'.date('Ym').'/'.$filename;
                    $exfn = $this->_readExcel($img_path,$ext);
                    
                    return $img_path;
                }else{
                    return false;
                }

            }
        }else{

        }
    }

读取excel: 

    /**
     * 读取excel,返回数组
    */
  private function _readExcel($filename,$extension){
      include_once('../app/libs/PHPExcel/Classes/PHPExcel/IOFactory.php');

      //$this->load ->library('PHPExcel/IOFactory');

//      $objReader =IOFactory::createReader('Excel5');
      $objReader =\PHPExcel_IOFactory::createReader("Excel5");

      $objReader->setReadDataOnly(true);
      if ($extension =='xlsx') {
          $objReader = new \PHPExcel_Reader_Excel2007();
          $objPHPExcel = $objReader ->load($filename);
      } else if ($extension =='xls') {
          $objReader = new \PHPExcel_Reader_Excel5();
          $objPHPExcel = $objReader ->load($filename);
      } else if ($extension=='csv') {
          $PHPReader = new \PHPExcel_Reader_CSV();

          //默认输入字符集
          $PHPReader->setInputEncoding('GBK');

          //默认的分隔符
          $PHPReader->setDelimiter(',');

          //载入文件
          $objPHPExcel = $PHPReader->load($filename);
      }
//      $objPHPExcel= $objReader->load($filename);

      $objWorksheet= $objPHPExcel->getActiveSheet();

      $highestRow =$objWorksheet->getHighestRow();

      //echo$highestRow;die;

      $highestColumn = $objWorksheet->getHighestColumn();

      //echo$highestColumn;die;

      $highestColumnIndex =PHPExcel_Cell::columnIndexFromString($highestColumn);

      $excelData =array();

      for($row = 2;$row <= $highestRow; $row++) {

          for ($col= 0; $col < $highestColumnIndex; $col++) {

              $excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();

          }

      }

      return$excelData;
  }

结果:

laravel中利用PHPExcel读取excel数据存入数据库