Codeigniter利用PHPExcel导出Excel文件

发布:smiling　来源: PHP粉丝网　添加日期：2014-07-26 14:43:14　浏览: 评论：0　

1.准备工作

下载PHPExcel：http://phpexcel.codeplex.com

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

2. 安装PHPExcel到Codeigniter

1）解压压缩包里的Classes文件夹中的内容到applicationlibraries目录下,目录结构如下:

-- applicationlibrariesPHPExcel.php

-- applicationlibrariesPHPExcel (文件夹)

2）修改applicationlibrariesPHPExcelIOFactory.php 文件

-- 将其类名从PHPExcel_IOFactory改为IOFactory,遵从CI类命名规则.

-- 将其构造函数改为public

3. 安装完毕,写一个导出excel的控制器(Controller),代码如下:

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');

    }



}

方法二,代码如下:

Excel Plugin

The following plugin will generate a tab-delimited file, and feed it to the client as an Excel file.

$this->load->plugin('to_excel');

$this->db->use_table('tablename');

$this->db->select('field1', 'field2');

// run joins, order by, where, or anything else here

$query = $this->db->get();

to_excel($query, ['filename']); // filename is optional, without it, the plugin will default to 'exceloutput'

So you could run:

to_excel($query, 'myfile'); // outputs myfile.xls

to_excel($query); // outputs exceloutput.xls

// you could also use a model here

to_excel($this->model_name->functioncall());

/system/plugins/to_excel_pi.php

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/*

* Excel library for Code Igniter applications

* Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006

*/

function to_excel($query, $filename='exceloutput')

{

     $headers = ''; // just creating the var for field headers to append to below

     $data = ''; // just creating the var for field data to append to below



     $obj =& get_instance();



     $fields = $query->field_data();

     if ($query->num_rows() == 0) {

          echo '<p>The table appears to have no data.</p>';

     } else {

          foreach ($fields as $field) {

             $headers .= $field->name . "t";

          }



          foreach ($query->result() as $row) {

               $line = '';

               foreach($row as $value) {

                    if ((!isset($value)) OR ($value == "")) {

                         $value = "t";

                    } else {

                         $value = str_replace('"', '""', $value);

                         $value = '"' . $value . '"' . "t";

                    }

                    $line .= $value;

               }

               $data .= trim($line)."n";

          }



          $data = str_replace("r","",$data);



          header("Content-type: application/x-msdownload");

          header("Content-Disposition: attachment; filename=$filename.xls");

          echo "$headersn$data";

     }

}

?>