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）

<?php
class 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'); }