phpexcel导出导入

1. 准备工作
   下载PHPExcel：http://phpexcel.codeplex.com/

这是个强大的Excel库，这里只演示导出Excel文件的功能，其中的大部分功能可能都用不着。

1. 安装PHPExcel到Codeigniter
   1） 解压压缩包里的PHPExcel_1.8.0_odt / Classes文件夹中的内容到application/libraries目录下，目录结构如下：

   – application/libraries/PHPExcel.php

   – application/libraries/PHPExcel (文件夹)

2）修改application/libraries/PHPExcel/IOFactory.php 文件

– 将其类名从PHPExcel_IOFactory改为IOFactory，遵从CI类命名规则。

– 将其构造函数改为public

1. 安装完毕，写一个导出excel的控制器（Controller）

<?phpclass Table_export extends CI_Controller{    function __construct()    {        parent :: __construct();         // Here you should add some sort of user validation        // to prevent strangers from pulling your table data    }     function index($table_name)    {        $query = $this -> db -> get($table_name);        if (!$query)            return false;         // Starting the PHPExcel library        $this -> load -> library('PHPExcel');        $this -> load -> library('PHPExcel/IOFactory');        $objPHPExcel = new PHPExcel();        $objPHPExcel -> getProperties() -> setTitle("export") -> setDescription("none");        $objPHPExcel -> setActiveSheetIndex(0);         // Field names in the first row        $fields = $query -> list_fields();        $col = 0;        foreach ($fields as $field)        {            $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($col, 1, $field);            $col++;        }         // Fetching the table data        $row = 2;        foreach($query -> result() as $data)        {            $col = 0;            foreach ($fields as $field)            {                $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($col, $row, $data -> $field);                $col++;            }             $row++;        }         $objPHPExcel -> setActiveSheetIndex(0);        $objWriter = IOFactory :: createWriter($objPHPExcel, 'Excel5');         // Sending headers to force the user to download the file        header('Content-Type: application/vnd.ms-excel');        header('Content-Disposition: attachment;filename="Products_' . date('dMy') . '.xls"');        header('Cache-Control: max-age=0');        $objWriter -> save('php://output');    } }

phpexcel导入

CI框架 利用phpexcel类库 导入excel文件并入库

//从excel导入到数据表
——-上传文件

public function excel_put(){        //先做一个文件上传，保存文件        $path=$_FILES['file'];        $filePath = "uploads/".$path["name"];        move_uploaded_file($path["tmp_name"],$filePath);        //$data=array('B'=>'name','C'=>'pwd','D'=>'money1','E'=>'salt');        $data=array('B'=>'name','C'=>'pid');        $tablename='city2';//表名字        $this->excel_fileput($filePath,$data,$tablename);}

———–处理文件，上传并入库

private function excel_fileput($filePath,$data,$tablename){       //ci框架中引入excel类$this -> load -> library('PHPExcel');       $this -> load -> library('PHPExcel/IOFactory');        $PHPExcel = new PHPExcel();        $PHPReader = new PHPExcel_Reader_Excel2007();        if(!$PHPReader->canRead($filePath)){            $PHPReader = new PHPExcel_Reader_Excel5();            if(!$PHPReader->canRead($filePath)){                echo 'no Excel';                return ;            }        }        // 加载excel文件        $PHPExcel = $PHPReader->load($filePath);        // 读取excel文件中的第一个工作表        $currentSheet = $PHPExcel->getSheet(0);        // 取得最大的列号        $allColumn = $currentSheet->getHighestColumn();        // 取得一共有多少行        $allRow = $currentSheet->getHighestRow();        // 从第二行开始输出，因为excel表中第一行为列名        for($currentRow = 2;$currentRow <= $allRow;$currentRow++){            /**从第A列开始输出*/            //echo $allColumn;            for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){                $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();                //print_r($val);                //die;                if($currentColumn == 'A')                {                    //echo $val."\t";                }else if($currentColumn <= $allColumn){                    $data1[$currentColumn]=$val;                }            }            foreach($data as $key=>$val){                $data2[$val]=$data1[$key];            }            $this->db->insert($tablename,$data2);            //print_r($data2);            //echo "</br>";        }        //echo "\n";        echo "导入成功";    }public function   excel(){    #查询需要导出的数据定义为标量: $arr    $arr=array();    #导入类    $this->load->library('PHPExcel/PHPExcel');    #导入类    $this->load->library('PHPExcel/IOFactory');    #实例化导入类    $excel=new PHPExcel();    #设置当前本的值    $excel->setActiveSheetIndex(0);/*可有可无*/    #设置当前行    $row=1;    #遍历数据    $excel->setActiveSheetIndex()->setCellValueByColumnAndRow(0,$row,'username');    $excel->setActiveSheetIndex()->setCellValueByColumnAndRow(1,$row,'password');    $row++;    foreach($arr as $k=>$v)    {        $excel->setActiveSheetIndex()->setCellValueByColumnAndRow(0,$row,$v['username']);        $excel->setActiveSheetIndex()->setCellValueByColumnAndRow(1,$row,$v['password']);        $row++;    }    $excel = IOFactory :: createWriter($excel, 'Excel5');    // Sending headers to force the user to download the file    header('Content-Type: application/vnd.ms-excel');    header('Content-Disposition: attachment;filename="Products_' . date('dMy') . '.xls"');    header('Cache-Control: max-age=0');    $excel -> save('php://output');}