Codeigniter利用PHPExcel导出Excel文件

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),代码如下:

<?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(&#39;PHPExcel&#39;);
        $this->load->library(&#39;PHPExcel/IOFactory&#39;);
        $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, &#39;Excel5&#39;);
        // Sending headers to force the user to download the file
        header(&#39;Content-Type: application/vnd.ms-excel&#39;);
        header(&#39;Content-Disposition: attachment;filename="Products_&#39; . date(&#39;dMy&#39;) . &#39;.xls"&#39;);
        header(&#39;Cache-Control: max-age=0&#39;);
        $objWriter->save(&#39;php://output&#39;);
    }
}
?>

方法二,代码如下:

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(&#39;BASEPATH&#39;)) exit(&#39;No direct script access allowed&#39;);
/*
 * Excel library for Code Igniter applications
 * Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006
*/
function to_excel($query, $filename = &#39;exceloutput&#39;) {
    $headers = &#39;&#39;; // just creating the var for field headers to append to below
    $data = &#39;&#39;; // just creating the var for field data to append to below
    $obj = & get_instance();
    $fields = $query->field_data();
    if ($query->num_rows() == 0) {
        echo &#39;<p>The table appears to have no data.</p>&#39;;
    } else {
        foreach ($fields as $field) {
            $headers.= $field->name . "t";
        }
        foreach ($query->result() as $row) {
            $line = &#39;&#39;;
            foreach ($row as $value) {
                if ((!isset($value)) OR ($value == "")) {
                    $value = "t";
                } else {
                    $value = str_replace(&#39;"&#39;, &#39;""&#39;, $value);
                    $value = &#39;"&#39; . $value . &#39;"&#39; . "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";
    }
}
?>

                        

本文地址：

转载随意，但请附上文章地址:-)