phpexcel导入

/*
 * 亚马逊预留库存数据导入
 */
public function amazon_reserved_inventory_excel($params){
    $file=$params['file'];
    //获取文件后缀名
    $store=$params['store'];
    $date=$params['date'];
    $extension = $file->getClientOriginalExtension();
    if($extension=='csv'){
        $PHPExcel = new \PHPExcel_Reader_CSV();
    }elseif($extension=='xlsx'){
        $PHPExcel = new \PHPExcel_Reader_Excel2007();
    }else{
        $PHPExcel = new \PHPExcel_Reader_Excel5();
    }
    if (!$PHPExcel->canRead($file)) {
        return [
            'type' => 'fail',
            'msg' => '导入失败，Excel文件错误'
        ];
    }
    $PHPExcelLoad = $PHPExcel->load($file);
    $Sheet = $PHPExcelLoad->getSheet(0);

    /**取得一共有多少行*/
    $allRow = $Sheet->getHighestRow();

    //循环插入流量数据
    $a=0;$b=0;
    $time=time();
    for($j=2;$j<$allRow;$j++){
        $sku = $Sheet->getCellByColumnAndRow(0, $j)->getValue();
        $fnsku = $Sheet->getCellByColumnAndRow(1, $j)->getValue();
        $asin = $Sheet->getCellByColumnAndRow(2, $j)->getValue();
        $product_name = $Sheet->getCellByColumnAndRow(3, $j)->getValue();
        $product_name = str_replace("'"," ",$product_name);
        $reserved_fc_transfers = $Sheet->getCellByColumnAndRow(6, $j)->getValue();
        $reserved_fc_processing = $Sheet->getCellByColumnAndRow(7, $j)->getValue();

        $skuSql="select id from amazon_skulist where `sku`='$sku'";
        $checkSku=DB::select($skuSql);
        if($checkSku==null){
            $addSql="insert into amazon_skulist(`shop_id`,`sku`,`fnsku`,`asin`,`product_name`,`time`) values ('$store','$sku','$fnsku','$asin','$product_name','$time')";
            DB::insert($addSql);
        }else{
            $skuId=$checkSku[0]->id;
            $updateSql="update amazon_skulist set `fnsku`='$fnsku',`product_name`='$product_name',`time`='$time' where `id`='$skuId'";
            DB::update($updateSql);
        }
        $tonganSql="select good_num from saihe_inventory where `sku`='$sku' and warehouse_id='2'";
        $tongan=DB::select($tonganSql);
        if($tongan){
            $tongan_inventory=$tongan[0]['good_num'];
        }else{
            $tongan_inventory="";
        }
        $checksql="select id from amazon_inventory where `shop_id`='{$store}' and `sku`='{$sku}' and `fnsku`='{$fnsku}' and `asin`='{$asin}' and `date`='{$date}'";
        $check=DB::select($checksql);
        if($check==null){
            $sql="insert into amazon_inventory(`shop_id`,`sku`,`fnsku`,`asin`,`reserved_fc_transfers`,`reserved_fc_processing`,`tongan_inventory`,`date`,`time`) values ('$store','$sku','$fnsku','$asin','$reserved_fc_transfers','$reserved_fc_processing','$tongan_inventory','$date','$time')";
            DB::insert($sql);
            $a++;
        }else{
            $id=$check[0]->id;
            $updatesql="update amazon_inventory set `reserved_fc_transfers`='$reserved_fc_transfers',`reserved_fc_processing`='$reserved_fc_processing',`tongan_inventory`='$tongan_inventory',`time`='$time' where `id`='$id'";
            DB::update($updatesql);
            $b++;
        }
    }

    return [
        'type' => 'success',
        'msg' => '导入成功，本次共导入'.$a.'条数据,更新'.$b.'条数据'
    ];
}