Codeigniter+PHPExcel implements exporting data to Excel file

Codeigniter+PHPExcel implements exporting data to Excel file_PHP tutorial

WBOY

Release： 2016-07-13 10:28:24

Original

901 people have browsed it

PHPExcel is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats. And Codeigniter is a powerful PHP framework. Combining the two can produce great results!

1. Preparation

Download PHPExcel: http://phpexcel.codeplex.com
This is a powerful Excel library, here we only demonstrate exporting Excel files features, most of which may not be used.

2. Install PHPExcel to Codeigniter

1) Unzip the contents of the Classes folder in the compressed package to the applicationlibraries directory. The directory structure is as follows:
- -applicationlibrariesPHPExcel.php
--applicationlibrariesPHPExcel (folder)
2) Modify the applicationlibrariesPHPExcelIOFactory.php file
--Change its class name from PHPExcel_IOFactory to IOFactory, following the CI class naming rules.
--Change its constructor to public

3. After installation, write a controller (Controller) that exports excel

The code is as follows:

Copy code The code is as follows:

classTable_exportextendsCI_Controller{
    function__construct()
    {
        parent :: __construct();
        // Hereyoushouldaddsomesortofuservalidation
        // topreventstrangersfrompullingyourtabledata
    }
    functionindex($table_name)
    {
        $query = $this -> db -> get($table_name);
        if(!$query)
            returnfalse;
        // StartingthePHPExcellibrary
        $this -> load -> library('PHPExcel');
        $this -> load -> library('PHPExcel/IOFactory');
        $objPHPExcel = newPHPExcel();
        $objPHPExcel -> getProperties() -> setTitle("export") -> setDescription("none");
        $objPHPExcel -> setActiveSheetIndex(0);
        // Fieldnamesinthefirstrow
        $fields = $query -> list_fields();
        $col = 0;
        foreach($fieldsas$field)
        {
            $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($col, 1, $field);
            $col++;
            }
        // Fetchingthetabledata
        $row = 2;
        foreach($query -> result()as$data)
        {
            $col = 0;
            foreach($fieldsas$field)
            {
                $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($col, $row, $data -> $field);
                $col++;
                }
            $row++;
            }
        $objPHPExcel -> setActiveSheetIndex(0);
        $objWriter = IOFactory :: createWriter($objPHPExcel, 'Excel5');
        // Sendingheaderstoforcetheusertodownloadthefile
        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');
        }
    }

4.测试

加入数据库有表名为products，此时可以访问http://www.yoursite.com/table_export/index/products导出Excel文件了。