Codeigniter集成PHPExcel

来源：互联网发布：python 模块编辑：程序博客网时间：2024/06/04 18:03

先下载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;}

0 0