PHPExcel导入导出

导入：

public function import(){
        $oldData=[];
        $info=$this->fileUplad();
        if($info['code']!=200){
            return false;
        }

        //获取对应表的全部字段并删除第一个id元素
        $title_to_field=array(
            '学生姓名'    =>  'name',
            '性别'        =>  'sex',
            '身份证号'    =>   'cardID',
            '民族'        =>  'nation',
            '政治面貌'    =>   'politics_id',
            '宗教信仰'    =>   'faith',
            '籍贯'        =>  'native_place',
            '毕业学校'    =>   'graduation_school',
            '专业'        =>  'major',
            '学历'        =>  'education',
            '是否独生子女' =>  'only_child',
            '电话'        =>  'tel',
            '家庭地址'     =>  'address',
            '标签'        =>  'tag_id',
            '备注'        =>  'remarks',
        );
        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        if ($info['exts'] == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        } else if ($info['exts'] == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else if($info['exts'] == 'csv'){
            $objReader = \PHPExcel_IOFactory::createReader('CSV')
                ->setDelimiter(',')
                ->setInputEncoding('GBK')
                ->setEnclosure('"')
                ->setSheetIndex(0);
        }

        //载入文件
        $objPHPExcel = $objReader->load($info['path'],$encode='utf-8');
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $objWorksheet=$objPHPExcel->getSheet(0);
        //获取总行数
        $highestRow = $objWorksheet->getHighestRow();
        //获取总列数
        $allColumn = $objWorksheet->getHighestColumn();

        $allColumn = \PHPExcel_Cell::columnIndexFromString($allColumn);
        //获取表头信息数组
        for ($currentColumn=0;$currentColumn<=$allColumn;$currentColumn++){
            $cell =$objWorksheet->getCellByColumnAndRow($currentColumn,1);
            $value=$cell->getCalculatedValue();
            if($value){
                $head_list[] = $value;
            }
        }
        
        //插入数据
        for ($row=2;$row<=$highestRow;++$row) {
            $result=[];
            for($i=0;$i<count($head_list);$i++){
                $cell =$objWorksheet->getCellByColumnAndRow($i,$row);
                $value=$cell->getCalculatedValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }
                if(strpos($value,'=')){
                    $value = "'".$value;
                }
                if(isset($title_to_field[$head_list[$i]])){
                    $key=$title_to_field[$head_list[$i]];
                    $result[$key]=$value;
                }

            }
            $res = self::insert($result);
            if(! $res){
                continue;
            }
        }
        return true;
    }

导出：

public static function export()
    {
        //查询数据库信息
        try {
            $xlsData = StudentModel::select()->append(['tag_name']);
        } catch (\Exception $e) {
            return $e->getMessage();
        }
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        vendor("PHPExcel.PHPExcel.IOFactory");
        // Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        // Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E,F,G,H,I,J,K,L,N,M,O,P");
        $arrHeader = array('学生姓名', '性别', '身份证号', '民族', '政治面貌', '宗教信仰', '籍贯', '毕业学校', '学历', '是否是独生子女', '电话', '专业', '家庭电话', '家庭地址', '标签', '备注');
        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($xlsData as $k => $v) {
            $k += 2;
            //表格内容
            $objActSheet->setCellValue('A' . $k, $v['name']);
            $objActSheet->setCellValue('B' . $k, ($v['sex'] == 1) ? '男' : '女');
            $objActSheet->setCellValue('C' . $k, ' '.$v['cardID']);
            $objActSheet->setCellValue('D' . $k, $v['nation']);
            $objActSheet->setCellValue('E' . $k, config('politics')[$v['politics_id']]);
            $objActSheet->setCellValue('F' . $k, $v['faith']);
            $objActSheet->setCellValue('G' . $k, $v['native_place']);
            $objActSheet->setCellValue('H' . $k, $v['graduation_school']);
            $objActSheet->setCellValue('I' . $k, $v['education']);
            $objActSheet->setCellValue('J' . $k, ($v['only_child'] == 1) ? '是' : '否');
            $objActSheet->setCellValue('K' . $k, ' '.$v['tel']);
            $objActSheet->setCellValue('L' . $k, $v['major']);
            $objActSheet->setCellValue('M' . $k, $v['family_tel']);
            $objActSheet->setCellValue('N' . $k, $v['address']);
            $objActSheet->setCellValue('O' . $k, $v['tag_name']);
            $objActSheet->setCellValue('P' . $k, $v['remarks']);
            // 图片生成
            //$objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            //$objDrawing[$k]->setPath(ROOT_PATH."public/static/image/playbtn.png");
            // 设置宽度高度
            //$objDrawing[$k]->setHeight(40);//照片高度
            //$objDrawing[$k]->setWidth(40); //照片宽度
            // 设置图片要插入的单元格
            //$objDrawing[$k]->setCoordinates('C' . $k);
            // 图片偏移距离
            //$objDrawing[$k]->setOffsetX(30);
            //$objDrawing[$k]->setOffsetY(12);
            //$objDrawing[$k]->setWorksheet($objExcel->getActiveSheet());
 
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }
 
        $width = array(20, 20, 15, 10, 10, 30, 10, 15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[5]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[0]);
        $objActSheet->getColumnDimension('D')->setWidth($width[5]);
        $objActSheet->getColumnDimension('E')->setWidth($width[5]);
 
        $outfile = md5("人员表" . time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }