phpexcel导入并且有错时标记出文件错位位置

    public function actionImport()//excel导入
    {
        $model = new CrmCustomerImportForm();
        $ok = "";
        if (Yii::$app->request->isPost) {
//        if ($model->load(Yii::$app->request->post())) {
            $file = UploadedFile::getInstance($model, 'file');  //获取上传的文件实例
            if ($file) {
                $filename = time().'.'.$file->extension;
                $filename = time();
                $file->saveAs($filename.'.'.$file->extension);//保存文件
                /*exit;
                $format = $file->extension;*/
                if(in_array($file->extension,array('xls','xlsx','csv'))){
                    if($file->extension =='xlsx')
                    {
                        $objReader = new \PHPExcel_Reader_Excel2007();
                        $objPHPExcel = $objReader ->load($file);
                    }
                    else if ($file->extension =='xls')
                    {
                        $objReader = new \PHPExcel_Reader_Excel5();
                        $objPHPExcel = $objReader ->load($file);
                    }
                    else if ($file->extension=='csv')
                    {
                        $PHPReader = new \PHPExcel_Reader_CSV();
                        $PHPReader->setInputEncoding('GBK');//默认输入字符集
                        $PHPReader->setDelimiter(',');//默认的分隔符
                        $objPHPExcel = $PHPReader->load($file);//载入文件
                    }
                    else
                    {
                        die('文件格式不对!');
                    }
                    $objWorksheet = $objPHPExcel->getSheet(0);//载入文件并获取第一个sheet
                    $highestRow = $objWorksheet->getHighestRow();//总行数
                    $highestColumn = $objWorksheet->getHighestColumn();//总列数
//                    $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //将字母变为数字
                    if($highestRow > 1){






                        $transaction=Yii::$app->db->beginTransaction();
                        try {
                            $tableData = [];
                            $err = [];
                            for($row = 2; $row <= $highestRow; $row++){
                            $data = [];
                            for($col = 'A'; $col <= $highestColumn; $col++){
                                $data[] = trim($objWorksheet->getCell($col.$row)->getValue());//列字母不转换为数字时的写法
                            }
                            $tableData[] = $data;

                            $customer = new CrmCustomerImportForm();
                            $customer->name = $data[0];
                            $customer->gender = $data[1];
                            $customer->get_way = $data[2];
                            $customer->phone = $data[3];
                            $customer->tel = $data[4];
                            $customer->wechat = $data[5];
                            $customer->email = $data[6];
                            $customer->qq = $data[7];
                            $customer->birthday = str_replace('/', '-', $data[8]);
                            $customer->caller = $data[9];
                            $customer->street = $data[10];
                            $customer->remark = $data[11];
                                //判断是否达到上限
//                                if (!$this->checkmaxuser((new OrgSearch())->getOrg())){
//                                    throw new Exception('达到上限了!');
//                                };
                                if(!$customer->validate())
                                {
                                    throw new Exception(reset($customer->getFirstErrors()));
                                }
                                //判断用户是否已存在
                                $existUser = $customer->getCrmCustomer();
                                if ($existUser) {
//                                    $objectPHPExcel = new PHPExcel();
//                                    $objectPHPExcel->setActiveSheetIndex(0);
//                                    header('Content-Type : application/vnd.ms-excel');
//                                    header('Content-Disposition:attachment;filename="'.$filename.'"');
//                                    if($file->extension =='xlsx')
//                                    {
////                                        $objReader = new \PHPExcel_Reader_Excel2007();
////                                        $objPHPExcel = $objReader ->load($file);
//                                        $objWriter= PHPExcel_IOFactory::createWriter($filename,'Excel2007');
//                                        $objWriter->save('php://output');
//
//                                    }
//                                    else if ($file->extension =='xls')
//                                    {
////                                        $objReader = new \PHPExcel_Reader_Excel5();
////                                        $objPHPExcel = $objReader ->load($file);
//                                        $objWriter= PHPExcel_IOFactory::createWriter($filename,'Excel5');
//                                        $objWriter->save('php://output');
//
//                                    }
//                                    else if ($file->extension=='csv')
//                                    {
////                                        $PHPReader = new \PHPExcel_Reader_CSV();
////                                        $PHPReader->setInputEncoding('GBK');//默认输入字符集
////                                        $PHPReader->setDelimiter(',');//默认的分隔符
////                                        $objPHPExcel = $PHPReader->load($file);//载入文件
//                                        $objWriter= PHPExcel_IOFactory::createWriter($filename,'CSV');
//                                        $objWriter->save('php://output');
//                                    }
//                                    throw new Exception('用户已存在'.$existUser->phone);


                                    //保存可保不同文件名,格式不变
//                                    $objWriter = new PHPExcel_Writer_Excel2007($PHPExcel,'PDF');
//                                    $objWriter->save('./sndemo1.xlsx');
//                                    dump($PHPExcel);

//                                    var_dump($row);die;
                                    if($file->extension =='xlsx')
                                    {
                                        $objReader = new \PHPExcel_Reader_Excel2007();
                                        $objPHPExcel = $objReader ->load($filename.'.'.$file->extension);
                                        $currentSheet = $objPHPExcel->getSheet(0);
                                        $currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                                            ->getStartColor()->setARGB('D1E17E'); //设置标题背景颜色

                                        /** 输出到指定目录 */
                                        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                                        $objWriter->save($filename.'.'.$file->extension);//文件保存路径

                                        /** 输出到浏览器直接下载打开 */
                                        header('Content-Type:application/vnd.ms-excel'); //指定下载文件类型
                                        header('Content-Disposition: attachment; filename="'.$filename.'.xlsx"'); //指定下载文件的描述
                                        header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下载文件的大小

                                        /**  将文件内容读取出来并直接输出，以便下载 */
                                        readfile($filename.'.'.$file->extension);
                                    }
                                    else if ($file->extension =='xls')
                                    {
                                        $objReader = new \PHPExcel_Reader_Excel5();
                                        $objPHPExcel = $objReader ->load($filename.'.'.$file->extension);
                                        $currentSheet = $objPHPExcel->getSheet(0);
                                        $currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                                            ->getStartColor()->setARGB('D1E17E'); //设置标题背景颜色

                                        /** 输出到指定目录 */
                                        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                                        $objWriter->save($filename.'.'.$file->extension);//文件保存路径

                                        /** 输出到浏览器直接下载打开 */
                                        header('Content-Type:application/vnd.ms-excel'); //指定下载文件类型
                                        header('Content-Disposition: attachment; filename="'.$filename.'.xls"'); //指定下载文件的描述
                                        header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下载文件的大小

                                        /**  将文件内容读取出来并直接输出，以便下载 */
                                        readfile($filename.'.'.$file->extension);
                                    }
                                    else if ($file->extension=='csv')
                                    {
                                        $PHPReader = new \PHPExcel_Reader_CSV();
//                                        $PHPReader->setInputEncoding('GBK');//默认输入字符集
//                                        $PHPReader->setDelimiter(',');//默认的分隔符
                                        $objPHPExcel = $PHPReader->load($filename.'.'.$file->extension);//载入文件
                                        $currentSheet = $objPHPExcel->getSheet(0);
                                        $currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                                            ->getStartColor()->setARGB('D1E17E'); //设置标题背景颜色
//                                        $currentSheet->setCellValue($col.$row,'CPU使用率:');//表头赋值//

                                        /** 输出到指定目录 */
                                        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
                                        $objWriter->save($filename.'.'.$file->extension);//文件保存路径


                                        /** 输出到浏览器直接下载打开 */
                                        header('Content-Type:application/vnd.ms-excel'); //指定下载文件类型
                                        header('Content-Disposition: attachment; filename="'.$filename.'.csv"'); //指定下载文件的描述
                                        header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下载文件的大小

                                        /**  将文件内容读取出来并直接输出，以便下载 */
                                        readfile($filename.'.'.$file->extension);

//                                        $objPHPExcel->getActiveSheet()->setTitle('User');
//                                        $objPHPExcel->setActiveSheetIndex(0);
//                                        header('Content-Type: application/vnd.ms-excel');
//                                        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
//                                        header('Cache-Control: max-age=0');
//                                        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//                                        $objWriter->save('php://output');
//
                                    }

//                                    unlink($filename.'.'.$file->extension);
//                                    $objWriter = new PHPExcel_Writer_Excel2007($PHPExcel,'PDF');
//                                    $objWriter->save('./sndemo1.xlsx');
//                                    dump($PHPExcel);







//                                    $objPHPExcel = PHPExcel_IOFactory::load($filename);
//                                    $sheet = $objPHPExcel->getSheet(0); // 读取第一個工作表
//                                    $highestColumm = $sheet->getHighestColumn(); // 取得总列数
//                                    $highestRow = $sheet->getHighestRow(); // 取得总行数
//
                                    /** 循环读取每个单元格的数据 */
//                                    $i = 2;
//                                    foreach ($list as $key => $value) {
//                                        $objPHPExcel->setActiveSheetIndex(0)
//                                            ->setCellValue('Z'.$i, $value['demo'])
//                                            ->setCellValue('AA'.$i, $value['demo']);
//                                        $i++;
//                                    }

//                                    $objPHPExcel->getActiveSheet()->setTitle('Simple');
//                                    $objPHPExcel->setActiveSheetIndex(0);

                                    /** 输出到指定目录 */
//                                    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//                                    $objWriter->save('simple.xlsx');//文件保存路径


//                                    /** 输出到浏览器直接下载打开 */
//                                    header('Content-Type:application/vnd.ms-excel'); //指定下载文件类型
//                                    header('Content-Disposition: attachment; filename="'.$filename.'"'); //指定下载文件的描述
//                                    header('Content-Length:'.filesize($filename)); //指定下载文件的大小
//
//                                    /**  将文件内容读取出来并直接输出，以便下载 */
//                                    readfile($filename);

                                }
                                //crm_customer表添加客户
                                if(!$customer->saveCustomer()){
                                    throw new Exception('客户导入失败');
                                };
                        }
                            $transaction->commit();
                        } catch (Exception $e) {
                            $transaction->rollBack();
//                                throw $e;
                            $err= $e->getMessage();
                        }







                    }
//                    unlink($filename);
                    if ($ok == 1){
//                        $this->redirect(array('index'));
                    } else{
                        Yii::$app->session->setFlash('error', $err);
                    }
                }
            }
            else
            {
                Yii::$app->session->setFlash('error', '请上传文件!');
            }
        }else{
            return $this->render('import',[
                'model'=>$model
            ]);
        }
        return $this->render('import',[
            'model'=>$model
        ]);
    }

发表回复取消回复

要发表评论，您必须先登录。