symfony通过command实现Excel导入

symfony实现excel既可以通过command，也可以通过server来实现。

setName：设置命令名称

set parameter：设置命令参数 

 protected function configure()
    {
        $this->setName('add:projectunit')
            ->setDescription('import excel data')
            ->setHelp('set parameter', '-f <filepath> Relative path ', '-s <service>  data conversion server', '-t <type>     Excel5 excel2003 / Excel2007 excel2007  default')
            ->addOption('file', 'f', InputOption::VALUE_OPTIONAL, 'Relative path')
            ->addOption('row', 'r', InputOption::VALUE_OPTIONAL, 'start row')
            ->addOption('type', 't', InputOption::VALUE_OPTIONAL, 'Excel5 excel2003 / Excel2007 excel2007', 'Excel2007')
            ->setDescription('update bidding');
    }

以下为在导入时，执行的功能逻辑

 protected function execute(InputInterface $input, OutputInterface $output)
    {
        $output->writeln([
            'Start Command',
            '------------'
        ]);
        /*
         * ProjectUnitRepository $projectUnitRepo
         */
        $projectUnitRepo = $this->getEm()->getRepository('InfraFacilityBundle:ProjectUnit');
        $areaRepo = $this->getContainer()->get('doctrine')->getManager()->getRepository('AdministrationBundle:Area');
        $projectRepo = $this->getEm()->getRepository('InfraFacilityBundle:Project');
        //获取数据
        $fileData = $this->getFileData($input);
        foreach ($fileData as $key =>$val){
            $projectData = $projectRepo->findOneBy(['name'=>$val[0]]); // $val excel表中的列数
            $projectUnitData =$projectUnitRepo->findOneBy(['name'=>$val[1], 'unitType'=>4]);
            if (empty($projectUnitData)) {
                $projectUnitData = new ProjectUnit();
            }
            // 设置关联的工程ID
            if (!empty($projectData)) {
                //查询是否有和project相同的工程名称，有则赋予project_id值
                $projectUnitData->setProjectId($projectData->getId());
            }
            $projectUnitData->setName($val[1]);
            $projectUnitData->setUnitType(4);
//            $projectUnitData->setDirector($val[10]);
//            $projectUnitData->setLoc($val[3]);
//            $projectUnitData->setAddress($val[2]);
            $this->getEm()->persist($projectUnitData);
            $this->getEm()->flush();
            $output->writeln([$key.'添加成功']);
        }
        $output->writeln([
            'End import',
            '------------'
        ]);
    }

这些就是一些基本的内容了，主要功能逻辑上上面部分 

public function getEm()
    {
        /**
         * @var \Doctrine\Persistence\ObjectManager $em
         */
        $em = $this->getContainer()->get('doctrine')->getManager('infra_facility');
        return $em;
    }
    protected function getFileData($input)
    {
        $file = $input->getOption('file');
        $type = $input->getOption('type');
        $row = empty($input->getOption('row')) ? 0 : $input->getOption('row');
        $cont = $this->getContainer();
        $objReader = \PHPExcel_IOFactory::createReader($type); /* Excel5 for 2003 excel2007 for 2007 */
        // Excel 路径
        $objPHPExcel = $objReader->load($file);
        // 获取工作表的数目
        $sheetCount = $objPHPExcel->getSheetCount();
        for ($i = 0; $i < $sheetCount; $i++) {
            $objWorksheet = $objPHPExcel->getSheet($i);

            $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
            $highestColumn = $objWorksheet->getHighestColumn();
            $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); // 总列数
            $dataArr = [];
            for ($row; $row <= $highestRow; $row++) {
                $data = array();
                // 注意highestColumnIndex的列数索引从0开始
                for ($col = 0; $col < $highestColumnIndex; $col++) {
                    $str = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                    if (is_object($str))
                        $str = $str->__toString();
                    $data[$col] = str_replace(array(
                        "\r\n",
                        "\r",
                        "\n",
                        " ",
                        "'"
                    ), "", trim($str));

                }
                $data[] = $i;
                if (!empty($data[$i])) {
                    $dataArr[] = $data;
                }


            }
        }
        return $dataArr;
    }

最后在控制台输入命令即可自动导入

eg：php bin/console add:projectunit -f文件相对路径