thinkcmf+PHPExcel实现导入导出功能

thinkphp3.2+PHPExcel实现导入导出功能：

以下是我自己的实现方法，可能存在很多不足，欢迎大家提出改进...
实现步骤：

一：去官网http://phpexcel.codeplex.com/下载最新PHPExcel放到Vendor下，注意位置：ThinkPHP\Core\Library\Vendor

二：在StampController.class.php中添加如下的方法：

1、实现导出功能：

1.1、导出PHP代码:

/**
	 *  导出数据库中数据表的数据
     * @param $expTitle 名称
     * @param $expCellName 参数
     * @param $expTableData 内容
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function exportExcel($expTitle,$expCellName,$expTableData){

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = 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');
		
		$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()->getColumnDimension('D')->setWidth(10);
//        $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]);
        }
        // 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');//Excel5为xls格式，excel2007为xlsx格式
        $objWriter->save('php://output');   
        exit;
    }
	
    /**
     * 导出Stamp的数据
     */
    function export(){//导出Excel
		//数据库中的数据表
        $xlsName  = "Stamp";
        $xlsCell  = array(
            array('id','编号'),
            array('stamp_title','艺术品名称'),
            array('stamp_uuid','证书编号'),
            array('stamp_src','证书图片'),
            array('art_name','作者'),
            array('stamp_type','艺术品类型'),
			array('stamp_describe','艺术品描述'),
			array('stamp_spec','艺术品规格（尺寸）'),
			array('stamp_material','艺术品材质（例如：纸质）'),
			array('stamp_age','创作年份'),
        );
		$data['is_deleted'] = 0;
        $xlsModel = M('Stamp');
            //导出所有的内容
            $xlsData  = $xlsModel->where($data)
				->Field('id,stamp_title,stamp_uuid,stamp_src,
				art_name,stamp_type,stamp_describe,stamp_spec,
				stamp_material,stamp_age')
				//->order('id desc')
				->select();
				
		/**
        foreach ($xlsData as $k => $v)
        {
            $xlsData[$k]['objtype']= $v['objtype'] == 1 ?'捐资助学':'不定项捐款';
        }
		*/

		/* 调用导出方法 */
        $this->exportExcel($xlsName,$xlsCell,$xlsData);

    }

1.2、导出导入的html页面代码：

<form class="well form-search" method="get" action="{:U('Stamp/index')}">
			证书名称：
			<input type="text" name="stamp_title" style="width: 200px;" value="{$option['stamp_title']}" placeholder="请输入姓名关键字...">
			证书编号： 
			<input type="text" name="stamp_uuid" style="width: 200px;" value="{$option['stamp_uuid']}" placeholder="请输入证书编号...">
			<input type="submit" class="btn btn-primary" value="搜索"/>
			<a href="{:U('Stamp/export')}" target="_blank"  class="btn btn-primary">导出</a>
			<a href="{:U('Stamp/importStamp')}" target="_blank"  class="btn btn-primary">导入</a>
		</form>

1.3、导出的效果图：

2、导入功能：

2.1、php代码：

/**导入数据页面*/
    public function importStamp()
    {
        $this->display('exportstamp');
    }

public function exportstamp() {
		ini_set('memory_limit','1024M');
		if (!empty($_FILES)) {
		$config = array(
			'exts' => array('xlsx','xls'),
			'maxSize' => 3145728000,
			'rootPath' =>"./Data/",
			'savePath' => 'upload/Excle/',
			'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['excelData']['savepath'].$info['excelData']['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');
		}
		$sheet =$objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();//取得总行数
		$highestColumn =$sheet->getHighestColumn(); //取得总列数
		//D('pro_info')->execute('truncate table pro_info'); //每次导入清空原有数据
		for ($i = 2; $i <= $highestRow; $i++) {
			
			
			
			$data['stamp_title']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
            $data['stamp_uuid'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
            $data['stamp_src']= $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
            $data['art_name']= $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
			$data['stamp_type']= $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
			$data['stamp_describe']= $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
			$data['stamp_spec']= $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
			$data['stamp_material']= $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
			$data['stamp_age']= $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
			//看这里看这里,这个位置写数据库中的表名

			if(M("Stamp")->where("stamp_uuid='".$data['stamp_uuid']."' and is_deleted = 0")->find()){
                   //上面注释的代码是用艺术品名称/证书编号判断
                }else{
                    M("Stamp")->add($data);
                    $j++;
                }
			}
			$this->success('导入成功！本次导入数量：'.$j, U('Stamp/index'));
		} else {
			$this->error("请选择上传的文件");
		}
	}

2.2、html页面代码：

<body>
	<form method="post" class="form-horizontal js-ajax-forms" action="{:U('Stamp/exportstamp')}" enctype="multipart/form-data">
		<fieldset>
			<div class="control-group">
				<label class="control-label">导入excel文件:</label>
					<div class="controls">
						<input type="file" name="excelData">
						<span class="form-required">*</span>
					</div>
				</div>
		</fieldset>
		<div class="form-actions">
			<button type="submit" class="btn btn-primary js-ajax-submit">导入</button>
		</div>
	</form>
</body>

2.3、效果展示