关于thinkphp中使用phpexcel

最新推荐文章于 2024-03-12 16:33:01 发布

上善若水zyp

最新推荐文章于 2024-03-12 16:33:01 发布

阅读量377

点赞数

CC 4.0 BY-SA版权

文章标签： phpexcel thinkphp表格导入导出

本文链接：https://blog.youkuaiyun.com/ZhaoYanping0803/article/details/78949480

本文介绍如何利用PHPExcel库在ThinkPHP框架下实现Excel表格的数据导入与导出功能。涵盖PHPExcel的安装配置、控制器中引用PHPExcel的方法、导入导出的具体实现步骤及代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一步:下载phpexcel并引入到thinkphp3.2.3

下载的phpexcel放在如下目录中

第二步:控制器中引用phpexcel

第三步:表格导入

具体代码如下

public function upexcel() {
		// var_dump ($_FILES ['peopleexcel'] ['name']);exit;
		ini_set('memory_limit','10M');  //设置内存限制
		if (!empty($_FILES)) {
			$config = array(
				'exts' => array('xlsx','xls'),
				'maxSize' => 3145728000,
				'rootPath' =>"./Public/",
				'savePath' => 'Uploadexcel/',
				'subName' => array('date','Ymd'),
			);
			$upload = new \Think\Upload($config);
			if (!$info = $upload->upload()) {
				$this->error($upload->getError());
			}
			vendor("PHPExcel.PHPExcel");
			$file_name=$upload->rootPath.$info['peopleexcel']['savepath'].$info['peopleexcel']['savename'];
			$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//
			// 判断导入表格后缀格式
			if ($extension == 'xlsx') {
				$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
				$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
			} else if ($extension == 'xls'){
				$objReader =\PHPExcel_IOFactory::createReader('Excel5');
				$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
				// var_dump($objPHPExcel) ;
				// exit;
			}
			$sheet =$objPHPExcel->getSheet(0);
			$highestRow = $sheet->getHighestRow();//取得总行数
			$highestColumn =$sheet->getHighestColumn(); //取得总列数
			$table='people';
			D('people')->execute('truncate table pro info'); //清除数据库数据
			// var_dump($highestColumn);exit;
			for ($i = 2; $i <= $highestRow; $i++) {
				//看这里看这里,前面小写的a是表中的字段名，后面的大写A是excel中位置
				$data['res_name'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
				$data['sex'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
				$data['birth'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
				$data['nation'] =$objPHPExcel->getActiveSheet()->getCell("D" .$i)->getValue();
				$data['id_number'] =$objPHPExcel->getActiveSheet()->getCell("E" .$i)->getValue();
				$data['type'] =$objPHPExcel->getActiveSheet()->getCell("F" .$i)->getValue();
				$data['residence'] =$objPHPExcel->getActiveSheet()->getCell("G" .$i)->getValue();
				$data['education'] =$objPHPExcel->getActiveSheet()->getCell("H" .$i)->getValue();
				$data['political'] =$objPHPExcel->getActiveSheet()->getCell("I" .$i)->getValue();
				$data['health'] =$objPHPExcel->getActiveSheet()->getCell("J" .$i)->getValue();
				$data['marriage'] =$objPHPExcel->getActiveSheet()->getCell("K" .$i)->getValue();
				$data['word'] =$objPHPExcel->getActiveSheet()->getCell("L" .$i)->getValue();
				$data['native_place'] =$objPHPExcel->getActiveSheet()->getCell("M" .$i)->getValue();
				$data['address'] =$objPHPExcel->getActiveSheet()->getCell("N" .$i)->getValue();
				$data['word_home'] =$objPHPExcel->getActiveSheet()->getCell("O" .$i)->getValue();
				$data['is_jh'] =$objPHPExcel->getActiveSheet()->getCell("P" .$i)->getValue();
				$data['is_sb'] =$objPHPExcel->getActiveSheet()->getCell("Q" .$i)->getValue();
				$data['is_ms'] =$objPHPExcel->getActiveSheet()->getCell("R" .$i)->getValue();
				$data['ms'] =$objPHPExcel->getActiveSheet()->getCell("S" .$i)->getValue();
				$data['is_pk'] =$objPHPExcel->getActiveSheet()->getCell("T" .$i)->getValue();
				$data['is_lx'] =$objPHPExcel->getActiveSheet()->getCell("U" .$i)->getValue();
				$data['pk_people'] =$objPHPExcel->getActiveSheet()->getCell("V" .$i)->getValue();
				$data['special_people'] =$objPHPExcel->getActiveSheet()->getCell("W" .$i)->getValue();
				$data['ylbx'] =$objPHPExcel->getActiveSheet()->getCell("X" .$i)->getValue();
				$data['xlby'] =$objPHPExcel->getActiveSheet()->getCell("Y" .$i)->getValue();
				$data['family_type'] =$objPHPExcel->getActiveSheet()->getCell("Z" .$i)->getValue();
				$data['house_con'] =$objPHPExcel->getActiveSheet()->getCell("AA" .$i)->getValue();
				$data['affairs_con'] =$objPHPExcel->getActiveSheet()->getCell("AB" .$i)->getValue();
				$data['info'] =$objPHPExcel->getActiveSheet()->getCell("AC" .$i)->getValue();
				$data['village_id'] =$objPHPExcel->getActiveSheet()->getCell("AD" .$i)->getValue();
				$data['other'] =$objPHPExcel->getActiveSheet()->getCell("AE" .$i)->getValue();
				//看这里看这里,这个位置写数据库中的表名
				$type='showList';
				D($table)->add($data);
			}
			$this->jsonReply('200' , '导入成功');
		} else {
			$this->jsonReply('500' , '请选择上传的文件');


		}
	}

第四步:表格导出

参数分别为表名,字段名,导出的数据

具体代码如下:

//导出people表
    function explodMail(){
        $xlsName  = "通讯录表";
        $xlsCell  = array(     
        array('mailname','姓名'),
        array('mailphone','联系电话'),   
        array('mailvillage','所属村组'),        
        );
        $mailname=I('param.mailname');
        $mailvillage=I('param.mailvillage');
        if($mailname){
            $con['res_name']  = array('like',"%$mailname%");
        }
        if($id_number){
            $con['mailvillage']  = array('like',"%$mailvillage%");
        }
        $xlsData=M('Maillist')->where($con)->order('id asc')->select();
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

  /**
     *
     * 导出Excel
     */
    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");
       
        $objPHPExcel = new PHPExcel();
        $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].'2', $expCellName[$i][1]); 
        } 
          // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
          for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $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;   
    }