wqy
2018-04-10 11:53:24
15130

 

引入

composer require phpoffice/phpexcel
 
/**   * 导入数据   */
public function importPost(){
    $user_id = get_current_admin_id();
    $file = $this->request->file('file');
    if(!$file){
        $this->error('文件不存在');
    }
    //保存文件
    $info = $file->move(ROOT_PATH . 'public' . DS . 'upload');
    if(!$info){
        $this->error($file->getError());
    }

    //文件路径

    $filepath =  ROOT_PATH.'public' . DS . 'upload'. DS .$info->getSaveName();
    $filename = "/public/upload/".str_replace('\\','/',$info->getSaveName());
    Loader::import('classes.PHPExcel',EXTEND_PATH,'.php');
    try{
        //$objReader = new \PHPExcel_Reader_Excel5();//注意和导出的类不一样哦
        $objReader = new \PHPExcel_Reader_Excel2007();//注意和导出的类不一样哦
    }catch (\Exception $e){
        $this->error($e->getMessage());
    }
    $sheet = $objPHPExcel->getSheet(0);

    $highestRow = $sheet->getHighestRow(); // 取得总行数
    if($highestRow < 2){
        $this->error('文件中没有数据');

    }
    $recharge = array();
    $res = [];
    for ($j=2;$j<=$highestRow;$j++){
        $res['id'] = (int)$objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取A列的值
        $day = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取B列的值
        $res['day'] =  date('Y-m-d ', ($day - 25569) * 24*60*60 ); //获得秒数;
        $res['ring'] = (string)$objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//获取C列的值
        $res['result'] = (string)$objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();//获取D列的值
        $res['user_id'] = $user_id;
        $recharge[] = $res;
    }
    $sheet1 = $objPHPExcel->getSheet(1);
    $highestRow1 = $sheet1->getHighestRow(); // 取得总行数
    if($highestRow1 < 2){
        $this->error('数据为空');
    }
    $recharge1 = array();
    $res1 = [];
    for ($j=2;$j<=$highestRow1;$j++){
        $res1['id'] = (int)$objPHPExcel->setActiveSheetIndex(1)->getCell("A".$j)->getValue();//获取A列的值
        $res1['content'] = (string)$objPHPExcel->setActiveSheetIndex(1)->getCell("B".$j)->getValue();//获取B列的值
        $recharge1[] = $res1;
    }
    //整合数据
    foreach ($recharge as $key=>$val){
        foreach ($recharge1 as $key1=>$val1){
            if($val['id'] == $val1['id'] ){
                $val['content'] = $val1['content'];
            }
        }
        $recharge[$key] = $val;
    }
    Db::startTrans();
    try{
        $ringTempModel = new RingTempModel();
        $ringTempModel->saveAll($recharge,false);
        $result = RingTempModel::where('user_id',$user_id)
            ->select()
            ->hidden(['id','user_id'])
            ->toArray();
        $ringModel = new RingModel();
        $ringModel->saveAll($result,false);
        //删除中转库中的数据
        RingTempModel::where('user_id',$user_id)->delete();
        //TODO 删除已上传的文件
        @unlink($filename);

        Db::commit();
        $this->success('导入成功',url('AdminRing/index'));
    }catch (Exception $e){
        @unlink($filename);
        Db::rollback();
        $this->error($e->getMessage());
    }
}