Codeigniter集成PHPExcel

先下载PHPExcel

1） 压缩包里的Classes文件夹放到application\libraries\目录下，目录结构如下： 
-- application\libraries\PHPExcel.php 
-- application\libraries\PHPExcel (文件夹) 
2）修改application\libraries\PHPExcel\IOFactory.php 文件 
-- 将其类名从PHPExcel_IOFactory改为IOFactory，遵从CI类命名规则。 
-- 将其构造函数改为public 

控制器里的方法

/*
    * 生成下载excel文件
    * $filename="生成的excel名称";
    * $headArr=array("表头","名称");
    * $data 要显示的数据
    * 	array(array('username'=>1,'pwd'=>2),array(...)..);
    * 调用方法：
    * 	$this->getExcel($filename,$headArr,$data);
    * 
    */
  public function getExcel($fileName = "test", $headArr = array("username","pwd"), $data = array(array('username'=>1,'pwd'=>2))) {
		date_default_timezone_set('Asia/Shanghai');
		//对数据进行检验
		if (empty($data) || !is_array($data)) {
			die("data must be a array");
		}
		//检查文件名
		if (empty($fileName)) {
			exit;
		}

		$date = date("Y_m_d", time());
		$fileName .= "_{$date}.xls";



		$this->load->library('PHPExcel');
		$this->load->library('PHPExcel/IOFactory');

		//创建PHPExcel对象
		$objPHPExcel = new PHPExcel();

		$objPHPExcel->getProperties();

		//设置表头
		$key = ord("A");
		foreach ($headArr as $v) {
			$colum = chr($key);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
			$key += 1;
		}


		$column = 2;
		$objActSheet = $objPHPExcel->getActiveSheet();
		foreach ($data as $key => $rows) { //行写入
			$span = ord("A");
			foreach ($rows as $keyName => $value) { // 列写入
				$j = chr($span);
				$objActSheet->setCellValue($j . $column, $value);
				$span++;
			}
			$column++;
		}

		$fileName = iconv("utf-8", "gb2312", $fileName);
		//重命名表

		//设置活动单指数到第一个表,所以Excel打开这是第一个表
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean(); //清除缓冲区,避免乱码
		header('Content-Type: application/vnd.ms-excel');
		header("Content-Disposition: attachment;filename=\"$fileName\"");
		header('Cache-Control: max-age=0');

		$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); //文件通过浏览器下载
		exit;

	}

</pre><pre name="code" class="php">

<pre name="code" class="php">/*
	 * 导入execl文件获取表中数据
	 * @param  [file] $file [excel文件存放路径]
	 * @return [array]       [返回表中数据]
	 */
	public function execExcel() {
		date_default_timezone_set('Asia/Shanghai');

		$file = "./uploads/test.xls";
		//检查文件
		if (empty($file)) {
			exit;
		}

		$this->load->library('PHPExcel');
		$this->load->library('PHPExcel/IOFactory');
		// $CI->load->library('PHPExcel/Reader/Excel5');

		// 创建对象
		$objPHPExcel = new IOFactory();

		$readerType = $objPHPExcel::identify($file);
		$objReader = $objPHPExcel::createReader($readerType);
		// 读文件
		$objPHPExcel = $objReader->load($file);

		$objWorksheet = $objPHPExcel->getActiveSheet(0);

		// 总行数
		$highestRow = $objWorksheet->getHighestRow();
		// 总列数
		$highestColumn = $objWorksheet->getHighestColumn();

		$highestColumnIndex = range('A', $highestColumn);
		$data = array();
		// 从第二行开始，第一行一般是表头
		for ($row = 2; $row <= $highestRow; $row++) {
			$array = array();
			foreach ($highestColumnIndex as $value) {
				$address = $value . $row;
				$array[] = $objWorksheet->getCell($address)->getFormattedValue();
			}
			array_push($data, $array);
		}
		return $data;
	}