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

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 

	

field_data();
    if ($query->num_rows() == 0) {
        echo '
The table appears to have no data.
';
    } 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";
    }
}
?>

本文地址：

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