web页面中导出Excel (方法五) 纯 js 前端将table中数据导出Excel 使用 js-xlsx，数据在php服务器端整合成对像数组
这个示例使用 前端 html javascript 使用 js-xlsx 后端 php
数据在php服务器端整合成对像数组 转到js端 并整到 table 标签中，再将 table导出Excel 使用了 js-xlsx
js-xlsx 文件可自行搜索下载 ，参考 https://www.cnblogs.com/hailexuexi/p/12934054.html
优点：所有数据(包括列名)均在php整合，可以多次查询数据库，统一整合成二维数组，转到前端table标签中直接下载，效率高速度快。
缺点：在php端查询整合数据后，需要转到前端 ，虽然可以不显示，但数据量几十万行的话还是会影响效率的。
原理：就是上下两个 table 一个用于显示带分页，一个用于导出不分页（不显示）
html
<!-- GridView.tpl --> <script src="plugins/js-xlsx/xlsx.core.min.js"></script> <script src="jquery.min.js"></script> <!--若主程序已加载，可注掉 --> <script src="plugins/js-xlsx/excel.js"></script> <body> <div id='p' class='easyui-panel' title='任务列表' iconCls='icon-select' style='padding:10px;background:#fafafa;width:1250px;'> <div > <table> <tr> <td class='dt_select_label'>客户姓名:</td> <td class='dt_select_field'><input type='text' id='select_contact_name' name='select_contact_name' size='20' class="easyui-textbox" /></td> <td class='dt_select_label'>电话号码:</td> <td class='dt_select_field'><input type='text' id='select_contact_callerid' name='select_contact_callerid' size='20' class="easyui-textbox" /></td> <td class='dt_select_label'>客户来源:</td> <td class='dt_select_field'><select id="select_contact_source" name="select_contact_source" class="easyui-combobox" style="width:100px;" editable=false ><{$select_option_contact_source}></select></td> </tr> <tr> <td class='dt_select_label'>办事处:</td> <td class='dt_select_field'><select id="select_install_office" name="select_install_office" class="easyui-combobox" style="width:130px;" editable=false ><{$select_option_install_office}></select></td> <td width='67px'>负责人:</td> <td width="145px"><select id="select_assigned_user_name" name="select_assigned_user_name" class="easyui-combobox" style="width:130px;" ><{$select_option_install_worker}></select></td> <td class='dt_select_label'>工单状态:</td> <td class='dt_select_field'><select id="select_order_status" name="select_order_status" class="easyui-combobox" style="width:100px;" editable=false ><{$select_option_order_status}></select></td> </tr> </table> </div> <div> <input type="checkbox" id="checkbox_date_entered" name="checkbox_date_entered" style="vertical-align:middle;" value="" onclick="chose_date_entered()" /> <label style="vertical-align:middle;" >创建时间: </label> <td class='dt_select_field'><input type='text' id='start_datetime' name="start_datetime" pattern='datetime' class="datetime_date" value='<{$start_date}>' onFocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" alt='请填写一个日期' readonly/></td> <td>至</td> <td class='dt_select_field'><input type='text' id='stop_datetime' name="stop_datetime" pattern='datetime' class="datetime_date" value='<{$stop_date}>' onFocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" alt='请填写一个日期' readonly/></td> <input type="checkbox" id="checkbox_pay_date" name="checkbox_pay_date" style="vertical-align:middle;" value="" onclick="chose_checkbox_pay_date()" /> <label style="vertical-align:middle;" >实收款时间:</label> <td class='dt_select_field'><input type='text' id='start_datetime_pay_date' name='start_datetime_pay_date' pattern='datetime' class='datetime_date' value='<{$start_date_pay_date}>' onFocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" alt='请填写一个日期' readonly/></td> <td>至</td> <td class='dt_select_field'><input type='text' id='stop_datetime_pay_date' name='stop_datetime_pay_date' pattern='datetime' class='datetime_date' value='<{$stop_date_pay_date}>' onFocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" alt='请填写一个日期' readonly/></td> <a href='#' class='easyui-linkbutton' iconCls='icon-select' onclick='Select()'>查询</a> </div> </br> <table id='grid' class='easyui-datagrid' style='width:1220px;height:450px' title='列表' iconCls='icon-table' pagination='true' rownumbers='true' fitColumns='true' singleSelect='true' toolbar='#toolbar' > <thead> <tr> <th field='id' width='10' hidden='true'>编号</th> <th field='contact_id' width='10' hidden='true'> 客户编号</th> <th field='contact_name' width='15'align='center'>姓名</th> <th field='contact_callerid' width='15'align='center' >电话号码</th> <th field='date_entered' width='20'align='center'>创建时间</th> <th field='order_status' width='15' align='center' >工单状态</th> </tr> </thead> </table> <div id='toolbar'> <a href='javascript:void(0)' class='easyui-linkbutton' iconCls='icon-excel' plain='true' onclick='ToExcel_js()' <{$toexcel_disabled}> >导出</a> <a href='#' class='easyui-linkbutton' iconCls='icon-refresh' plain='true' onclick='Refresh()'>刷新</a> <a href='#' class='easyui-linkbutton' iconCls='icon-cancel' plain='true' onclick='parent.TabClose();'>关闭</a> </div> <!-- 用于导出的 table --> <table id="table_excel" style="text-align:center;" > <!-- block none display:none; --> </div> <script type='text/javascript'> function Refresh(){ $('#grid').datagrid('reload'); } //获取 查询 条件 参数 function Select_Parameter(){ var contact_name=$('#select_contact_name').val(); var assigned_user_id=$('#assigned_user_id').val(); var start_time=$('#start_datetime').val(); var stop_time=$('#stop_datetime').val(); var start_time_pay_date=$('#start_datetime_pay_date').val();//实际收款时间 var stop_time_pay_date=$('#stop_datetime_pay_date').val(); var checkbox_datetime="";//默认为 空 var node=document.getElementsByName("checkbox_date_entered"); for(var i=0;i<node.length;i++){ if(node[i].checked==true){ checkbox_datetime="checkbox_date_entered"; } } var node=document.getElementsByName("checkbox_pay_date");//实际收款时间 for(var i=0;i<node.length;i++){ if(node[i].checked==true){ checkbox_datetime="checkbox_pay_date"; } } var contact_callerid=$('#select_contact_callerid').val(); var contact_address="";//$('#select_contact_address').val(); //地址不用了 var contact_source=$('#select_contact_source').combobox('getValue');//客户来源 var contact_province=$('#select_contact_province').combobox('getValue');//省份 var contact_city=$('#select_contact_city').combobox('getValue');//城市 if(contact_city ==' --请选择-- '){ contact_city='0'; } var assigned_user_name=$('#select_assigned_user_name').combobox('getValue'); //负责人 var operator_user_name=""; //操作人 var install_office=$('#select_install_office').combobox('getValue');//办事处 var install_worker="";//$('#select_install_worker').combobox('getValue');// var order_status=$('#select_order_status').combobox('getValue'); //alert(repair_last_worker); var query={'checkbox_datetime':checkbox_datetime,'contact_name':contact_name,'assigned_user_id':assigned_user_id,'start_time':start_time,'stop_time':stop_time,'contact_callerid':contact_callerid ,'contact_address':contact_address,'contact_source':contact_source,'assigned_user_name':assigned_user_name,'operator_user_name':operator_user_name ,'contact_province':contact_province,'contact_city':contact_city,'install_office':install_office,'install_worker':install_worker ,'order_status':order_status ,'start_time_pay_date':start_time_pay_date,'stop_time_pay_date':stop_time_pay_date }; //,'repair_last_worker':repair_last_worker,'repair_num':repair_num,'start_repair_last_date':start_repair_last_date,'stop_repair_last_date':stop_repair_last_date return query; } function Select(){ var query=Select_Parameter();//获取 查询 条件 参数 $('#grid').datagrid('options').url='Ajax-index.php?module=<{$module_name}>&action=Ajax_GridView_Select_Rec&assigned_user_id=<{$assigned_user_id}>&start_time=<{$start_date}>&stop_time=<{$stop_date}>&install_office=<{$install_office}>' $('#grid').datagrid('options').queryParams=query; $('#grid').datagrid('reload'); } function ToExcel_js(){ //生成---table_excel------------------------------------------------------ var url='Ajax-index.php?module=<{$module_name}>&action=Ajax_GridView_Select_Rec_ToExcel&target=ToExcel<{$get_current_user}>' ; var query=Select_Parameter();// 查询条件; //alert(query); //请求数据------------------------------ $.ajax({ type: "POST", url: url, //后台php文件的地址 data: query, //查询参数 dataType: "json", success:function (data) { //alert("提交成功"+JSON.stringify(data)); //alert('data.length '+data.rows.length); //行数 var arrTable=data; columnCount = Object.keys(arrTable.rows[0]).length; //列数 //alert('data.length '+columnCount); //console.log(arrTable); for (i = 0; i < arrTable.rows.length; i++) { strRow="<tr>"; for(j=0; j<columnCount; j++){ if(arrTable.rows[i][j]==null){ strRow=strRow + '<td >' +"" + ""+"" + "</td>"; }else{ strRow=strRow + '<td >' +"" + arrTable.rows[i][j]+"" + "</td>"; } } strRow=strRow +"</tr>"; $("#table_excel").append(strRow); } //js-xlsx 方式 导出 Excel------------------------------------------------------ var table = document.querySelector("#table_excel"); var sheet = XLSX.utils.table_to_sheet(table); //将一个table对象转换成一个sheet对象 openDownloadDialog(sheet2blob(sheet), 'Book.xlsx'); }, error:function (data) { alert("导出失败 "+JSON.stringify(data)); } }); } </script> </body>
Ajax_GridView_Select_Rec_ToExcel.php
<?php $table_name='woke_order_repair'; $arr_result = array(); //返回值 $where='';//查询条件 if(!empty($_REQUEST['start_time'])){ $start_time=$_REQUEST['start_time']; $stop_time=$_REQUEST['stop_time']; } else{ $start_time=date("Y-m-d 0:0:0",strtotime("now"));//默认查询当天 $stop_time=date("Y-m-d 23:59:59",strtotime("now")); } if($_POST['checkbox_datetime']=="checkbox_date_entered"){ $where=" and date_entered >='{$start_time}' and date_entered <='{$stop_time}' "; // 时间 }else if($_POST['checkbox_datetime']=="checkbox_pay_date"){ $where=" and pay_date >='{$_POST['start_time_pay_date']}' and pay_date <='{$_POST['stop_time_pay_date']}' "; //实际收款时间 } //其它条件 if($_POST['contact_name']!=''){ $contact_name=$_POST['contact_name']; //iconv("UTF-8","GB2312",$_POST['name']); $where .=" and contact_name like '%{$contact_name}%' "; } if($_POST['contact_callerid']!=''){ $where .=" and ( contact_callerid = '{$_POST['contact_callerid']}' or contact_other_tel='{$_POST['contact_callerid']}' ) "; } if($_POST['contact_address']!=''){ $contact_address=$_POST['contact_address']; $where .=" and contact_address like '%{$contact_address}%' "; } if($_POST['assigned_user_name']!='' && $_POST['assigned_user_name']!='0' ){ //创建人 负责人 $where .=" and assigned_user_name like '%{$_POST['assigned_user_name']}%' "; } if($_REQUEST['install_office']!=''&& $_REQUEST['install_office']!='0'){ // 所属办公室 //$where .=" and install_office = '{$_REQUEST['install_office']}' "; $where .=" and install_office like '%{$_REQUEST['install_office']}%' "; } if($_POST['order_status']!=''&& $_POST['order_status']!='0'){ //工单状态 $where .=" and order_status = '{$_POST['order_status']}' "; } $row = array(); $row[]="编号";//0 $row[]="姓名"; $row[]="电话号码"; $row[]="创建时间"; $row[]="客户来源"; $row[]="媒体名称";//media_name 媒体名称 $row[]="媒体时间";//media_date 媒体时间 $row[]="省份"; $row[]="城市"; $row[]="地址"; $row[]="工单类型"; $row[]="工单状态";//11 $row[]="机器名称"; $row[]="安装时间"; //install_date $row[]="预约维修时间"; //repair_date_appointment $row[]="实际维修时间"; //维修时间 repair_date $row[]="办事处"; $row[]="维修人员"; //repair_worker $row[]="工单备注";//description $row[]="维修备注";//repair_description $row[]="客服";//20 $row[]="服务费";//serivce_money $row[]="配件金额";//product_parts_money $row[]="总金额";//sum_money $row[]="实收金额";//实收金额 received_money $row[]="收款时间";//收款时间 pay_date $row[]="收款备注";//收款备注 pay_description $row[]="出纳";//27 出纳 cashier_name //明细不显示 $row[]="配件名称";// $row[]="单价";// $row[]="数量";// $row[]="小计"; $items = array(); array_push($items, $row); //列 $col=" id,contact_name,contact_callerid, date_entered , contact_source,media_name,media_date,contact_province,contact_city,contact_address,order_type,order_status "; $col.=" ,product_name,install_date,repair_date_appointment,repair_date,install_office,repair_worker,description,repair_description,assigned_user_name "; $col.=" ,serivce_money,product_parts_money,sum_money,received_money,pay_date,pay_description,cashier_name "; $sql = "select {$col} from {$table_name} where ".$where .$order ;//." limit $offset,$rows "; deleted=0 //WriteLog($sql); $orderby=" order by date_entered desc "; //创建时间 排序 $where=" deleted=0 " . $where ; $sql = "select " . $col ." from {$table_name} where ".$where.$orderby ." "; $result=$db->query($sql); while($row_order=$db->fetch_array($result)){ /* 查明细 ，将 明细放在列上 多个明细，多个列 */ //查 _detail 表 根据 order_change_core_id 取 $col_detail=" part_name ,price ,number ,subtotal "; //实收金额 $order=" order by date_entered desc "; $sql_detail = "select {$col_detail} from woke_order_repair_detail where order_repair_id='".$row_order['id'] ."' " .$order; //WriteLog($sql_detail); $result_detail=$db->query($sql_detail); $i=27; while($row_detail=$db->fetch_array($result_detail)){ $i++; //WriteLog($i); $row_order[$i]=$row_detail[0]; $i++; $row_order[$i]=$row_detail[1]; $i++; $row_order[$i]=$row_detail[2]; $i++; $row_order[$i]=$row_detail[3]; } array_push($items, $row_order); } $arr_result['rows'] = $items; //WriteLog(json_encode($arr_result)); echo json_encode($arr_result); ?>
浙公网安备 33010602011771号