/**
     * 导出excel文件
     * @return mixed
     */
    public function downqiyeOp() {
        $list = model('company')->order('sort asc,id asc')->field('id,name,fullname')->with(['parameter'=>function($query){
            $query->field( 'sid,table,zuowei');
        }])->select()->toArray();

        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);

        //设置行高度
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);

        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);

        //set font size bold
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
        $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);


        $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //合并cell
        $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');

        // set table header content
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', date('Y').'年度CEO高峰论坛欢迎宴参会名单汇总 时间:'.date('Y-m-d H:i:s'))
            ->setCellValue('A2', '企业名称')
            ->setCellValue('B2', '参会姓名')
            ->setCellValue('C2', '选座');
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<count($list);$i++){
//
            $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $list[$i]['name']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['fullname']);
            //判断是否已选择座位
            if($list[$i]['parameter']['table']){    //已选择
                $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['parameter']['table'].'-'.$list[$i]['parameter']['zuowei']);
            }else{  //未选择
                //改变字体颜色为红色
                $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':C'.($i+3))->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_RED));
                //改变背景色为黄色
                $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':C'.($i+3))->getFill()->applyFromArray(array(
                    'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                    'startcolor' => array(
                        'rgb' => 'FFFF00'
                    )
                ));
                //设置单元格内容
                $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), '未选');
            }
//          $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//          $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
        }


        //  sheet命名
        $objPHPExcel->getActiveSheet()->setTitle('欢迎宴参会名单-'.date('Y').'年度CEO高峰论坛汇总表');


        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


        // excel头参数
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="欢迎宴参会名单-'.date('Y').'年度CEO高峰论坛汇总表('.date('Ymd-His').').xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式,excel2007为xlsx格式

        $objWriter->save('php://output');

    }




    //将excel数据转入数据表
    public function addexcelOp() {
        if(request()->ispost()){
            //获取上传文件信息
            $file = request()->file('addexcel');
            //1000b=1kb  大小不超过10mb
            $info = $file->validate(['size'=>10000000 ,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
            if($info){
                //获取文件名
                $excel_path=$info->getSaveName(); //'20200218\xinxi.xlsx'
                //获取上传文件地址--
                $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $excel_path;//D:\wamp\www\admin1\public\excel\20200218\xinxi.xlsx
                //获取上传文件后缀
                $name = $info->getExtension();
                //引入类
                vendor("PHPExcel.PHPExcel.IOFactory");
                if($name =='xlsx' ){
                    $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
                }else{
                    $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 
                }
                //加载文件内容 utf8geshi
                $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');
                // echo "<pre>";
                //转换为数组格式
                $excel_array=$obj_PHPExcel->getsheet(0)->toArray();
                //输出数组中的当前元素和下一个元素的值,然后把数组的内部指针重置到数组中的第一个元素
                $arr  = reset($excel_array);
                //销毁标题
                unset($excel_array[0]); 
                $data = [];  
                $i=0;  
                $Pinyin = new ChinesePinyin();
                foreach ($excel_array as $k => $v) {
                    $fullname = preg_replace('/\s/', '', $v[0]);  //姓名
                    $name = preg_replace('/\s/', '', $v[1]);  //企业名称
                    if($fullname && $fullname != '人员没有确定' && $fullname != '不参加'){  
                        //企业排序                
                        $sort = model('company')->where(['name'=>$name])->value('sort');
                        if(!$sort){
                            $sortmax = model('company')->order('sort desc')->value('sort');
                            $sort = $sortmax +1;
                        }
                        $fullname=preg_replace("/\\d+/",'', $fullname);
                        //取企业名称的首个字符的拼音大写
                        $first = mb_substr($name,0,1);
                        $letter = strtoupper($Pinyin->TransformUcwords($first));
                        $Arr = [
                            'fullname'=>$fullname,
                            'name'=>$name,
                            'sort'=>$sort,
                            'letter'=>$letter,
                        ];
                        $data[] = $Arr;
                        db('company')->insert($Arr);
                    }
                    $i++; 
                }              
                $this->success('添加成功,添加'.count($data).'条','qiye');
            }else{
                $this->error($file->getError());
            }
        }
        if(request()->isGet()){
            return $this->fetch();
        }      
    }
最后修改:2021 年 03 月 26 日
如果觉得我的文章对你有用,请随意赞赏