首先安装 composer 链接地址 https://www.phpcomposer.com

composer require phpoffice/phpexcel

一、导入excel

1.读取文件导入数据

public function upload(){
        if (request()->isPost()) {
            $file = input('post.importFile'); //获取表单上传文件
            $ext = input('post.ext'); //获取表单上传文件
            if ($file) {
                $path = $file;
                $file_name = ROOT_PATH . 'public' . $path;   //上传文件的地址
                if ($ext == 'xls') {
                    include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php";
                    $objReader = new \PHPExcel_Reader_Excel5();
                } else if ($ext == 'xlsx') {
                    include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel2007.php";
                    $objReader = new \PHPExcel_Reader_Excel2007();
                } else if ($ext == 'csv') {
                    include "../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/CSV.php";
                    $objReader = new \PHPExcel_Reader_CSV();
                } else {
                    die('文件格式不正确');
                }
                $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');//加载文件内容,编码utf-8
                $excel_array = $obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);
                $result = [];
                foreach($excel_array as $v){
                    //这里实现逻辑
                  	$res = $getData -> searchComponyali($v[0]);
                    array_push($result, $res);
                }
                return ['code' => 1, 'msg' => '查询成功'];
            }
        }else{
            return $this->fetch();
        }
    }

2.数据导出

/**
     * excel表格导出
     * @param string $fileName 文件名称
     * @param array $headArr 表头名称
     * @param array $data 要导出的数据
     * @author cyh
     */
    public function excelExport()
    {
        $fileName = '公司数据';
        $headArr = ['id', '公司名称', '法定代表人', '注册资本', '注册时间', '经营状态', '统一社会信用代码', '工商注册号', '纳税人识别号', '组织机构代码', '行业大类', '行业小类', '企业类型', '人员规模', '经营期限自', '经营期限至', '登记机关', '核准日期', '注册地址', '经营范围'];
        $redis = new Redis(config('cache.redis'));
        $data = $redis->get('companyResultData'.date('Y-m-d'));
        $data = json_decode($data, true);
        $fileName .= "_" . date("Y_m_d") . ".xls";
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties();
        $key = ord("A"); // 设置表头
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;
        }
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) { // 列写入
                $value = is_numeric($value) ? "'" . $value : $value;
                $objActSheet->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }
        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename= $fileName ");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); // 文件通过浏览器下载
        exit();
    }