下载PHPExcel包,解压放置third_party目录下:
third_party
PHPExcel
PHPExcel.php
libraries 增加excel.php 文件
<?php
if (! defined ( 'BASEPATH' ))
exit ( 'No direct script access allowed' );
//-------------------------------------
// 文件说明:excel导出
//-------------------------------------
require_once APPPATH.'/third_party/PHPExcel.php';
class Excel extends PHPExcel{
public function __construct() {
parent::__construct();
}
function convertUTF8($str)//可在需要字符转换时候使用
{
if(empty($str)) return "";
return iconv('gbk','utf-8',$str);
}
}
在使用的控制器构造函数中引入:
$this->load->library('excel');
function eth_export($stime,$etime){
$lsit = Array(
[0] => Array
(
['mid'] => 793
[13] => 131.96
['date'] => 20130504
['eth_in'] => 13
['eth_out'] => 14
[14] => 187.79
['count'] => 8
)
[1] => Array
(
['mid'] => 793
[13] => 146.97
['date'] => 20130505
['eth_in'] => 13
['eth_out'] => 14
[14] => 210.84
['count'] => 8
)
[2] => Array
(
['mid'] => 793
[13] => 141
['date'] => 20130506
['eth_in'] => 13
['eth_out'] => 14
[14] => 212.92
['count'] => 8
)
[3] => Array
(
['mid'] => 793
[13] => 108.69
['date'] => 20130507
['eth_in'] => 13
['eth_out'] => 14
[14] => 184.77
['count'] => 8
)
[4] => Array
(
['mid'] => 793
[13] => 140.36
['date'] => 20130508
['eth_in'] => 13
['eth_out'] => 14
[14] => 212.87
['count'] => 8
)
[5] => Array
(
['mid'] => 793
[13] => 132.4
['date'] => 20130509
['eth_in'] => 13
['eth_out'] => 14
[14] => 204.87
['count'] => 8
)
[6] => Array
(
['mid'] => 793
[13] => 204.93
['date'] => 20130510
['eth_in'] => 13
['eth_out'] => 14
[14] => 277.79
['count'] => 8
)
[7] => Array
(
['mid'] => 793
[13] => 136.68
['date'] => 20130511
['eth_in'] => 13
['eth_out'] => 14
[14] => 182.33
['count'] => 8
)
)
$ip = "122.13.xx.xx"
$ssh_desc = Array ( [13] => 'eth0_进流量' ,[14] => 'eth0_出流量' );
$this->excel->setActiveSheetIndex(0);
$this->excel->getActiveSheet()->setTitle($ip);
$this->excel->getActiveSheet()->setCellValue('A1', "{$ip}-{$stime}-{$etime}流量统计");
$this->excel->getActiveSheet()->mergeCells('A1:D1');
$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$rowTitle = array('IP',$ssh_desc[$list[0]['eth_in']]."(Gb)",$ssh_desc[$list[0]['eth_out']]."(Gb)",'日期');
foreach($rowTitle as $k=>$r){
$this->excel->getActiveSheet()->getStyleByColumnAndRow($k, 2)->getFont()->setBold(true);//字体加粗
$this->excel->getActiveSheet()->getStyleByColumnAndRow($k, 2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($k, 2, $r);
}
$i=3;
$j=1;
foreach($list as $k=>$v){
$this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $ip);
$this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $v[$v['eth_in']]);
$this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $v[$v['eth_out']]);
$this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $i, $v['date']);
$i++;
$j++;
}
$end_line = $i-1;
$this->excel->getActiveSheet()->setCellValue("A$i", "统计(Gb)");
$this->excel->getActiveSheet()->setCellValue("B$i", "=SUM(B3:B$end_line)");
$this->excel->getActiveSheet()->setCellValue("C$i", "=SUM(C3:C$end_line)");
$this->excel->getActiveSheet()->setCellValue("D$i", ($stime==$etime)?date('Y-m-d',strtotime($stime)):date('Y-m-d',strtotime($stime))."/".date('Y-m-d',strtotime($etime)));
$filename=$ip.'-'.$stime.'-'.$etime.'.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save('php://output');
}