用PHPExcel导出导入Excel

thinkPHP5.0框架

查询数据库调用Excel方法

public function exportlist(){
        $orderModel = new OrderModel();
        if($start_time = $this->request->param('start_time','')){
            $orderModel->where(function($query) use($start_time) {
                $query->where('created_at','>=',$start_time);
            });
        }
        if($end_time = $this->request->param('end_time','')){
            $orderModel->where(function($query) use($end_time) {
                $query->where('created_at','<=',$end_time);
            });
        }

        $expTitle = '详细营销单'.date('Y-m-d');
        //数据库的字段, '名字', '宽度
        $expCellName = [
            ['rec_code','推荐人',15],
            ['pro_code','产品',15],
            ['price','金额',8],
            ['sell_code','营销人',15],
            ['created_at','日期',20],
        ];
        $expTableData = $orderModel->order('id','desc')->select()->toArray();

        if($expTableData){
            $this->exportListExcel($expTitle,$expCellName,$expTableData);
        }
    }

Excel导出方法

public function exportListExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle;
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        Loader::import('PHPExcel.Classes.PHPExcel');
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');

        $objPHPExcel = new PHPExcel();
        // 居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal('center');
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical('center');
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

        // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
            $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

PHP导入Excel

public function excel(){
        if($this->request->isPost()){
            if (!empty($_FILES)) {
                if(!in_array(cmf_get_file_extension($_FILES['file']['name']),['xlsx','xls'])){
                    $this->error('请上传Excel格式的文件！', '');
                }
                $uploader = new Upload();
                $uploader->setFileType('file');
                $result = $uploader->upload();
                if ($result) {
                    Loader::import('PHPExcel.Classes.PHPExcel');
                    Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
                    Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');

                    $filename = ROOT_PATH .'public'. $result['preview_url'];  //文件路径
                    $extension = cmf_get_file_extension($result['name']);    //文件扩展名
                    if ($extension == 'xlsx') {
                        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
                        $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
                    } else if ($extension == 'xls') {
                        $objReader = PHPExcel_IOFactory::createReader('Excel5');
                        $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
                    } else {
                        $this->error('请上传Excel格式的文件！', '');
                    }
                    //$excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
                    $sheet =$objPHPExcel->getSheet(0);
                    $highestRow = $sheet->getHighestRow();//取得总行数
                    //$highestColumn =$sheet->getHighestColumn(); //取得总列数
                    for($i=2;$i<=$highestRow;$i++){
                        if($value = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue()){
                            if(!Db::name('sell')->where('sell_code',$value)->find()){
                                $data[] = ['sell_code'=>$value];
                            }
                        }
                    }

                    if (Db::name('sell')->insertAll ($data)) {
                        $this->success('导入数据成功！', url('AdminSell/index'));
                    } else {
                        $this->error('导入数据失败!', '');
                    }
                } else {
                    $this->error($uploader->getError(), '');
                }
            }
        }
    }

还有一种比较简单,快捷的方法, 不需要因为额外的文件或者composer, 就是直接输出excel文件.

只需要一个方法(适合菜鸟使用)

/**
 * Author  963087326@qq.com
 * 导出excel
 * @param  array $data 导出数据
 * @param  array $a    excel表头
 * @param  string $b   导出的文件名
 * @return file       
 */
function excel($data,$a,$b=null){
    header("Content-type:application/octet-stream");
    header("Accept-Ranges:bytes");
    header("Content-type:application/vnd.ms-excel");  
    header("Content-Disposition:attachment;filename=".$b.".xls");
    header("Pragma: no-cache");
     header("Expires: 0");

    //$a=['第一列','第二列','第三列'];
    foreach ($a as $k => $v) {
        $a[$k]=iconv("UTF-8", "GB2312//IGNORE",$v);
    }
    //输出表头
    echo implode(' ',$a)."
";
    foreach ($data as $key => $value) {
    $arr = array();
    foreach ($value as $kk => $vv) {
        if(empty($vv) || $vv=='无' ){
            $vv='--';
        }
        $j=iconv("UTF-8", "GB2312//IGNORE",$vv);
        $arr[]= trim($j);
    }
    //输出数据
    echo implode('  ',$arr)."
"; 
    }
    //$b=[1,2,3];
}