Modal模态框+Ajax+PHPExcel 带进度条上传Excel至mysql

研究了很久怎么在模态框中上传Excel，并且显示进度条，最后还是采用迂回战术。。利用iframe…测试在chorme可以运行。。

1index.html

<section class="content"> <div class="container-fluid"> <div class="card"> <!--<div class="card-header">--> <div class="card-body"> <div class="row"> <div class="col-md-12"> <div id="toolbar"> <form class="form-inline"> <div class="form-group"> <div class="input-group input-group-sm" id="query_items"> <div class="input-group-prepend"> <span class="input-group-text">Month</span> <span class="input-group-text"> <i class="far fa-calendar-alt"></i></span> </div> <input type="text" class="form-control" id="mth_choose" size="25" /> </div> <div class="input-group input-group-sm p-1" id="slic_q"> <div class="input-group-prepend"> <span class="input-group-text">SLIC</span> </div> <div class="input-group-prepend"> <select id="q_slic" class="form-control"> </select> </div> </div> <div class="input-group input-group-sm mr-2" id="btn_q"> <span class="input-group-append"> <button type="button" class="btn btn-info btn-flat btn-sm" id="query_base"> <i class="fas fa-search"></i></button> </span> </div> <div class="btn-group btn-group-sm p-1" id="btn_grp"> <button id="u_wgt" class="btn btn-success btn-sm mr-2" data-toggle="modal" data-target="#upload_wgt" type="button" disabled><i class="fas fa-upload"></i>Weight</button> <button id="u_inf" class="btn btn-info btn-sm mr-2" data-remote="child_page/test.php" data-toggle="modal" data-target="#upload_info" type="button"><i class="fas fa-upload"></i>Basic Info</button> <button id="d_exp" class="btn btn-primary btn-sm mr-2" data-toggle="modal" data-target="#dowload_exp"><i class="fas fa-download"></i>Export</button> </div> </div> </form> </div> </div><!-- /.toolbar --> </div> <!-- /.row --> <!--</div> /.card header --> <!-- <div class="card-body"> --> <div class="row"> <div class="col-md-12"> <table id="incentive_table" class="table table-bordered table-striped table-sm"></table> </div> </div> </div><!-- /.card body --> </div><!-- /.card --> </div><!-- /.container-fluid --></section>

2点击Weight按钮后弹出模态框

<!--weight upload Modal--><div class="modal fade" id="upload_wgt" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" data-backdrop="static"> <div class="modal-dialog modal-lg"> <div class="modal-content"> <div class="modal-header"> <h4 class="modal-title" id="myModalLabel">Upload Weight</h4> <button type="button btn-sm" class="close" data-dismiss="modal" aria-hidden="true"> × </button> </div> <div class="modal-body"> <span id="message"></span> <form id="frm_upload_wgt" method="POST" enctype="multipart/form-data" class="form-horizontal"> <div class="form-group" align="center"> <label class="col-md-4 control-label">Select Upload File</label> <input type="file" name="file" id="file" /> </div> <div id='load_frm' class="form-group"> <iframe id="wgt_load_frm" src="" width="100" height="50" frameborder='no' scrolling="no"></iframe> </div> <div class="form-group" align="center"> <input type="hidden" name="hidden_field" value="1" /> <button type="submit" class="btn btn-info" id="wgt_import" disabled> <i class="fa fa-upload p-1"></i> Upload </button> <button type="button" class="btn btn-danger" id="close_list" data-dismiss="modal"> <i class="fa fa-times p-1"></i> Close </button> </div> </form> <span id="message"></span> </div> </div> </div></div><!--weight upload Modal end-->

3选择文件后点击上传，然后在iframe中显示进度条

3.1 index.html中的Jquery

$("#u_wgt").on("click", function () { $("#frm_upload_wgt")[0].reset(); document.getElementById("wgt_load_frm").src = ""; $("#wgt_import").removeAttr("disabled", "true"); $('#frm_upload_wgt').on('submit', function (event) { $('#message').html(''); event.preventDefault(); $.ajax({ url: "child_page/upload_files/upload_excel_file.php", method: "POST", data: new FormData(this), dataType: "json", contentType: false, cache: false, processData: false, beforeSend: function () { var file = document.getElementById('file').files[0]; var fileName = file.name; if (fileName.endsWith("xlsx")) { $("#wgt_import").attr("disabled", "true"); } else { modal_js.fail({ 'msg': 'Just Accept .xlsx file', 'icon': 2 }); $('#file').val(''); return false; } }, success: function (data) { if (data.success) { var file_name = data.file_name; var main = document.getElementById("load_frm"); var iframe = document.getElementById("wgt_load_frm"); var width = main.offsetWidth; var height = main.offsetHeight; iframe.style.width = width "px"; iframe.style.height = (height - 100) "px"; document.getElementById("wgt_load_frm").src = "child_page/upload_files/upload_pu_weight.php?file=" file_name; } if (data.error) { $('#message').html('<div class="alert alert-danger">' data.error '</div>'); $("#wgt_import").removeAttr("disabled", "true"); } } }) }); });

3.2 upload_excel_file.php

<?php$file = $_FILES['file']['name'];$filetempname = $_FILES['file']['tmp_name'];$filePath = '../../upFile/';require_once('../../plugins/PHPExcel/PHPExcel.php');require_once('../../plugins/PHPExcel/PHPExcel/IOFactory.php');require_once('../../plugins/PHPExcel/PHPExcel/Reader/Excel2007.php');//require_once ('../PHPExcel/PHPExcel/Reader/CSV.php');$filename = explode(".", $file); //把上传的文件名以“.”好为准做一个数组。$time = date("y-m-d-H-i-s"); //去当前上传的时间$filename[0] = $time; //取文件名t替换$name = implode(".", $filename); //上传后的文件名$uploadfile = $filePath . $name; //上传后的文件名地址$result = move_uploaded_file($filetempname, $uploadfile); //假如上传到当前目录下if ($result) //如果上传文件成功，就执行导入 excel操作{ $output = array( 'success' => true, 'file_name' => $uploadfile ); echo json_encode($output);}

3.3 upload_pu_weight.php

<!DOCTYPE html><html lang="en"><head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <meta http-equiv="x-ua-compatible" content="ie=edge" /> <title>Upload</title></head><body> <div id="progress" class="inline" style="width:700px;border:1px solid #ccc;"></div> <div id="information" class="inline"></span></body></html><?phpset_time_limit(0);require_once("../../connections/ie_mysql_li_db.php");$sqls = new Mysqldb();if (isset($_GET['file'])) { //下面的路径按照你PHPExcel的路径来修改 require_once('../../plugins/PHPExcel/PHPExcel.php'); require_once('../../plugins/PHPExcel/PHPExcel/IOFactory.php'); require_once('../../plugins/PHPExcel/PHPExcel/Reader/Excel2007.php'); //require_once ('../PHPExcel/PHPExcel/Reader/CSV.php'); $uploadfile = $_GET['file']; $str = ""; // $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2003 $objReader = PHPExcel_IOFactory::createReader('Excel2007'); //use excel2003 和 2007 format //$objReader = PHPExcel_IOFactory::createReader('CSV');//use CSV $objPHPExcel = PHPExcel_IOFactory::load($uploadfile); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $rownum = $highestRow - 1; $highestColumn = $sheet->getHighestColumn(); // 取得总列数 for ($j = 2; $j <= $highestRow; $j ) { $percent = intval($j / $highestRow * 100) . "%"; echo '<script language="javascript"> document.getElementById("progress").innerHTML="<div style="width:' . $percent . ';background-image:url(../../img/pbar-ani.gif);"> </div>"; document.getElementById("information").innerHTML="' . $percent . ' - ' . $j . ' in TTL ' . $highestRow . ' processed."; </script>'; echo str_repeat(' ', 1024 * 8); flush(); sleep(0.8); for ($k = 'A'; $k <= $highestColumn; $k ) { $str .= iconv('utf-8', 'gbk', $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue()) . '||'; //读 取单元格 } $strs = explode("||", $str); $w_slic = $strs[0]; $w_trk = $strs[1]; $w_billing_weight = $strs[2]; $w_update = date('Y-m-d'); $sql = "replace into tbl_pu_weight( w_slic, w_trk, w_billing_weight, w_update ) values ( '" . $w_slic . "', '" . $w_trk . "', '" . $w_billing_weight . "', '" . $w_update . "' )"; $sqls->simple_query($sql); $str = ""; } unlink($uploadfile); //删除上传的excel文件 echo '<script language="javascript"> document.getElementById("information").innerHTML="Upload Successful!"; </script>';}else { echo '<script language="javascript"> document.getElementById("information").innerHTML="Upload Faile!"; </script>';}?>

3.4 进度条图片。。pbar-ani.gif