Card list用excellentexport前端导出excel
收录、记录、整理PHP 相关知识和技术文档
介绍后台如何通过excellentexport 库在前端导出excel
main.php
-
增加js引入
<script src="/js/excellentexport.js"></script>
card.list
-
修改html
<div class="psr tr mt10">
<a class="btw" color="black" onclick="open_export_panel()"><span icon="cloud_download">Export</span></a>
<div class="cards_export_wrp" id="psr_<?=$order_id;?>" style="display:none;">
<label class="text_wrp w200"><span>Password</span><input type="password" id="chpwd" placeholder="password"></label>
<label class="btw"><span icon="cloud_download">Export</span><input type="button" class="pwd_confirm" value="Confirm"></label>
<a id="export_anchor" class="dn">export_anchor</a>
</div>
</div> -
修改js
<script type="text/javascript">
function open_export_panel() {
$('#psr_<?=$order_id?>').show();
}
$('#psr_<?=$order_id?> .pwd_confirm').click(function () {
var $ch_pwd_input = $('#chpwd');
var $filter_form = $('form.filter');
var params = $filter_form.serializeArray();
params.push({name:'order_id',value: '<?=$order_id?>'});
params.push({name:'password',value: $.trim($ch_pwd_input.val())});
$.post('/card/export-data', params, function (data) {
if (data.code > 0) {
ExcellentExport.convert(data.options, data.sheets);
$ch_pwd_input.val("");
$('#psr_<?=$order_id?>').hide();
document.getElementById("export_anchor").click();
} else {
alert(data.message);
}
}, 'json');
});
//$('#psr_<?//=$order_id?>// .pwd_confirm').click(function () {
// var password = $.trim($('#chpwd').val());
// $.post('/?r=card/chpwd', {password: password}, function (data) {
// if (data.code > 0) {
// var $filter_form = $('form.filter');
// var is_filter_date = $filter_form.find('input[name="filter_sold_time"]').eq(0).attr('checked');
// var date_range = $filter_form.find('input[name="date_range"]').eq(0).val();
// var category_id = $filter_form.find('select[name="category_id"]').eq(0).val();
// var category_type_id = $filter_form.find('select[name="type_id"]').eq(0).val();
// var status = $filter_form.find('select[name="status"]').eq(0).val();
// var keyword = $.trim($filter_form.find('input[name="keyword"]').eq(0).val());
//
// var filter_sold_time = 0;
// if (is_filter_date == 'checked') {
// filter_sold_time = 1;
// }
//
// var param = '&order_id=' + <?//=$order_id?>// +'&date_range=' + date_range + '&status=' + status + '&keyword=' + keyword
// + '&category_id=' + category_id + '&type_id=' + category_type_id + '&filter_sold_time=' + filter_sold_time;
// if (<?//=count($card_list)?>//) {
// <?//if ($order_id == 0): ?>
// window.location.href = '/?r=card/export-excel' + param;
// <?//else: ?>
// window.location.href = '/?r=card/export-order-cards&order_id=' + <?//=$order_id?>//;
// <?//endif?>
// } else {
// alert('<?php //echo _("Export data can not be empty"); ?>//');
// return false;
// }
// } else {
// alert('<?php //echo _("Password wrong") ?>//');
// }
// }, 'json');
//});
cardController
-
增加action
//点卡数据导出
public function exportDataAction()
{
set_time_limit(120);
ini_set('memory_limit', '1024M');
//验证密码
$password = $this->in->post('password', '');
empty($password) && throwActException('Please input password');
!check_passwd($password,$this->user['password']) && throwActException('Invalid User password');
//获取过滤条件
$order_id = $this->in->post('order_id', 0);
$type_id = $this->in->post('type_id', 0);
$status = $this->in->post('status', 0);
$category_id = $this->in->post('category_id', 0);
$keyword = $this->in->post('keyword', '');
$category_id = $this->in->post('category_id', 0);
$filter_sold_time = $this->in->post('filter_sold_time', '0');
$date_range = $this->in->post('date_range', '2007/01/01' . ' - ' . date('Y/m/d', $this->in->server('request_time')));
$begin_date = $end_date = '';
if ($order_id > 0) { // orders中的card列表
} else {
// cards列表
$date_range_p = explode(' - ', $date_range);
$begin_date = $date_range_p[0];
$end_date = $date_range_p[1];
}
// order
if ($order_id > 0) {
$order = OrderModel::getInstance()->getById($order_id);
if (0 == $category_id) {
$category_id = $order['category_id'];
$type_id = $order['product_id'];
}
} else {
$category_pairs = CardCategoryModel::getInstance()->getPairs();
if (0 == $category_id) {
$category_id = key($category_pairs);
}
$type_pairs = CardTypeModel::getInstance()->getCardTypes(array(
'category_id' => $category_id,
));
$new_type_pairs = array();
$type_status = 'Open'; // 默认状态
if (count($type_pairs)) {
foreach ($type_pairs as $tp) {
if ($tp['status'] != 1) {
$type_status = 'Closed';
} else {
$type_status = 'Open';
}
$new_type_pairs[$type_status][] = array(
$tp['id'] => $tp['name'],
);
}
}
$i = 0;
foreach ($new_type_pairs as $newtp_key => $newtp) {
foreach ($newtp as $key => $value) {
$i++;
if (array_key_exists($type_id, $value)) {
// 有该类型
$i = 0;
break;
}
}
if ($i == 0) {
break;
}
}
if ($i > 0) {
// 没有该类型
foreach ($new_type_pairs as $newtp_key => $newtp) {
foreach ($newtp as $key => $value) {
$type_id = key($value); // 当前类型id
}
}
}
}
$params = array(
'type_id' => $type_id,
'status' => $status,
'order_id' => $order_id,
'keyword' => $keyword,
);
if ($filter_sold_time) {
$params['filter_sold_time'] = $filter_sold_time;
}
if (isset($begin_date) && $begin_date) {
$params['begin_time'] = strtotime($begin_date . ' 00:00:00');
}
if (isset($end_date) && $end_date) {
$params['end_time'] = strtotime($end_date . ' 23:59:59');
}
$card_list = CardModel::getInstance()->getExportList($params);
$data_count = count($card_list);
// 规定每一张表中的数量为2000
$sheet_maxnum = 2000;
$division = floor($data_count / $sheet_maxnum);
$mode = $data_count % $sheet_maxnum;
$sheet_index = ($mode != 0) ? $division : ($division - 1); // 表的最大索引下标(从0开始计算的)
$sheet_options = array(
'anchor'=>'export_anchor',
'format'=>'xlsx', //'xlsx'/'xls'/'csv',
'filename'=>'cards_'.REQUEST_TIME.'xlsx',
);
$sheets = array();
$sheet_entity_form = array(
// 'table'=>'card_table',
'array'=>[],
'arrayHasHeader'=>true,
'removeColumns'=>[],
'filterRowFn'=>'function(row) {return true}' // Return true to keep
);
$sheet_header = ['Type','SN','PIN'];
$sheet_entity_form['array'][] = $sheet_header;
foreach ($card_list as $item){
$type = $item['type_name'];
$sn = str_rot13($card_list[$i]['card_number']);
$crypt = new Tuki_Crypt('ciX]wQfZXJ*ggLbGV^#7A,.ZeHF~9-[dnq7jaa8Xwuv$HwFxVQ', MCRYPT_DES, MCRYPT_MODE_CFB, $item['created']);
$pin = $crypt->decrypt($item['card_pin']);
$sheet_entity_form['array'][] = [$type,$sn,$pin];
}
$sheet_entity = array(
'name'=>'sheet1',
'from'=>$sheet_entity_form,
);
$sheets[]=$sheet_entity;
return $this->json->code(1)->options($sheet_options)->sheets($sheets);
}