Home php教程 php手册 Codeigniter利用PHPExcel导出Excel文件

Codeigniter利用PHPExcel导出Excel文件

May 25, 2016 pm 04:43 PM
codeigniter excel file phpexcel

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;);
    }
}
?>
Copy after login

方法二,代码如下:

Excel Plugin  
	The following plugin will generate a tab-delimited file, and feed it to the client as an Excel file. 
	 
	$this->load->plugin(&#39;to_excel&#39;); 
	$this->db->use_table(&#39;tablename&#39;); 
	$this->db->select(&#39;field1&#39;, &#39;field2&#39;); 
	// run joins, order by, where, or anything else here 
	$query = $this->db->get(); 
	to_excel($query, [&#39;filename&#39;]); // filename is optional, without it, the plugin will default to &#39;exceloutput&#39; 
	 
	So you could run: 
	 
	to_excel($query, &#39;myfile&#39;); // 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 

	
Copy after login
<?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";
    }
}
?>
Copy after login

                        

本文地址:

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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to implement custom middleware in CodeIgniter How to implement custom middleware in CodeIgniter Jul 29, 2023 am 10:53 AM

How to implement custom middleware in CodeIgniter

CodeIgniter middleware: Accelerate application responsiveness and page rendering CodeIgniter middleware: Accelerate application responsiveness and page rendering Jul 28, 2023 pm 06:51 PM

CodeIgniter middleware: Accelerate application responsiveness and page rendering

PHP development: Use PHPExcel to process Excel files PHP development: Use PHPExcel to process Excel files Jun 15, 2023 pm 03:45 PM

PHP development: Use PHPExcel to process Excel files

Complete Guide: How to process Excel files using php extension PHPExcel Complete Guide: How to process Excel files using php extension PHPExcel Jul 28, 2023 pm 10:01 PM

Complete Guide: How to process Excel files using php extension PHPExcel

PHP development: Using CodeIgniter to implement MVC pattern and RESTful API PHP development: Using CodeIgniter to implement MVC pattern and RESTful API Jun 16, 2023 am 08:09 AM

PHP development: Using CodeIgniter to implement MVC pattern and RESTful API

How to use the database query builder (Query Builder) in the CodeIgniter framework How to use the database query builder (Query Builder) in the CodeIgniter framework Jul 28, 2023 pm 11:13 PM

How to use the database query builder (Query Builder) in the CodeIgniter framework

How to use CodeIgniter5 framework in php? How to use CodeIgniter5 framework in php? Jun 01, 2023 am 11:21 AM

How to use CodeIgniter5 framework in php?

How to use PHP for Excel file processing? How to use PHP for Excel file processing? May 13, 2023 am 08:00 AM

How to use PHP for Excel file processing?

See all articles