php ci 导出excel,CI 框架下 php 数据库数据导入excel

view层(JS)：

$("#download_excel_archieve_btn").click(function (e) {

var url = "<?php echo site_url('errorlog/clientreport/download_excel_all')?>";

var xhr = new XMLHttpRequest();

var params = $("#form").serialize();

xhr.open('POST', url, true);

xhr.responseType = 'arraybuffer';

xhr.onload = function () {

if (this.status === 200) {

var filename = "";

var disposition = xhr.getResponseHeader('Content-Disposition');

if (disposition && disposition.indexOf('attachment') !== -1) {

var filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;

var matches = filenameRegex.exec(disposition);

if (matches != null && matches[1]) filename = matches[1].replace(/['"]/g, '');

}

var type = xhr.getResponseHeader('Content-Type');

var blob = new Blob([this.response], {

type: type

});

if (typeof window.navigator.msSaveBlob !== 'undefined') {

window.navigator.msSaveBlob(blob, filename);

} else {

var URL = window.URL || window.webkitURL;

var downloadUrl = URL.createObjectURL(blob);

if (filename) {

var a = document.createElement("a");

if (typeof a.download === 'undefined') {

window.location = downloadUrl;

} else {

a.href = downloadUrl;

a.download = filename;

document.body.appendChild(a);

a.click();

}

} else {

window.location = downloadUrl;

}

setTimeout(function () {

URL.revokeObjectURL(downloadUrl);

}, 100);

}

}

};

xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");

xhr.send(params);

e.stopPropagation();

return false;

});

controller层：

public function download_excel_all()

{

//获取前段传递的数据

$begin_time = $this->input->post('begin_time');

$end_time = $this->input->post('end_time');

$client_type = $this->input->post('client_type');

$app_version = $this->input->post('appversion');

$uid = $this->input->post('uid');

$event_type = $this->input->post('event_type');

$limit = 3000;

//判断数据

if (empty($begin_time ))

{

$begin_time =NULL;

}

if (empty($end_time))

{

$end_time = NULL;

}

if (empty($client_type))

{

$client_type = NULL;

}

if (empty($appversion))

{

$app_version = NULL;

}

if (empty($uid))

{

$uid = NULL;

}

if (empty($event_type))

{

$event_type = NULL;

}

//加载类库

$this->load->library('PHPExcel');

//查询加载数据

$this->load->model('errorlog/Report_model');

$exceldata = $this->Report_model->get($begin_time,$end_time,$this->partner_id,$uid,$event_type,$app_version,$client_type,$offset, $limit);

if($exceldata['data'] == FALSE)

{

$this->load->vars('error','没有统计数据');

}

array_unshift($exceldata['data'], array_keys($exceldata['data'][0]));

$row = 1;

$this->phpexcel = new PHPExcel();

do

{

$col = 0;

$row_data = current($exceldata['data']);

foreach($row_data as $value)

{

$this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);

$col++;

}

$row++;

}while(next($exceldata['data']));

$objWriter = new PHPExcel_Writer_Excel2007($this->phpexcel);

header('Content-type: application/vnd.ms-excel');

header('Content-Disposition: attachment; filename="error_report.xlsx"');

$objWriter->save('php://output');

}

model层：

/*

获取错误报告数据

$client_type是客户端的类型：1，PC端；2，苹果手机；16，Android手机

*/

public function get($begin_time = NULL, $end_time = NULL, $partner_id = NULL, $uid = NULL, $event_type = NULL, $app_version = NULL, $client_type = 1, $offset = 0, $limit = 10)

{

$conditions = array('1' => '1');

if(isset($begin_time))

{

$conditions['postime >= '] = $begin_time;

}

if(isset($end_time))

{

$conditions['postime <= '] = $end_time;

}

if(isset($partner_id))

{

$conditions['parnerid'] = $partner_id;

}

if(isset($uid))

{

$conditions['uid'] = $uid;

}

if(isset($event_type))

{

$conditions['eventtype'] = $event_type;

}

if(isset($app_version))

{

$conditions['appver'] = $app_version;

}

if(isset($client_type))

{

$conditions['clienttype'] = $client_type;

}

$query = $this->db->select('count(*) as total', FALSE)

->from('client_exception_report')

->where($conditions)

->get();

if($query == FALSE)

{

return FALSE;

}

$row = $query->first_row('array');

$query->free_result();

$query = $this->db->select('*, module, eventtype', FALSE)

->from('client_exception_report')

->where($conditions)

->order_by('postime', 'desc')

->limit($limit, $offset)

->get();

if($query == FALSE)

{

return FALSE;

}

$rs['data'] = $query->result_array();

$rs['total'] = $row['total'];

$query->free_result();

//查出module_description(模块描述)和event_description(事件描述)

if($rs['total'] != 0)

{

$eventtypes = $modules = array();

foreach($rs['data'] as $row)

{

if(!isset($modules[$row['module']]))

$modules[$row['module']] = $row['module'];

if(!isset($eventtypes[$row['module']]))

$eventtypes[$row['eventtype']] = $row['eventtype'];

}

$query = $this->db->select('description as module_description, module_id', FALSE)

->from('event_list')

->where_in('module_id', $modules)

->get();

if($query == FALSE)

{

return FALSE;

}

$_rs = $query->result_array();

$module_array = array();

foreach($_rs as $key => $row)

{

$module_array[$row['module_id']] = $row['module_description'];

}

$query->free_result();

foreach($rs['data'] as $key => $row)

{

$rs['data'][$key]['module_description'] = $module_array[$rs['data'][$key]['module']];

}

$query = $this->db->select('description as event_description, event_id', FALSE)

->from('event_list')

->where_in('event_id', $eventtypes)

->get();

if($query == FALSE)

{

return FALSE;

}

$_rs = $query->result_array();

$event_array = array();

foreach($_rs as $key => $row)

{

$event_array[$row['event_id']] = $row['event_description'];

}

$query->free_result();

foreach($rs['data'] as $key => $row)

{

$rs['data'][$key]['event_description'] = $event_array[$rs['data'][$key]['eventtype']];

}

}

return $rs;

}