项目中用到的导出数据到Excel记录下
需要包：poi-3.9.jar
页面代码：
/*** 显示所有列，勾选需要导出的**/function exportModel(tableId){var tableParams = getLocalStorageModel(tableId, "tableParams");var totalHtml ='<div class="modal fade modal-primary" id="'+tableId +'-edit" aria-hidden="true"><div class="modal-dialog" style="width: 200px;"><div class="modal-content">';totalHtml+='<div class="modal-header"><button type="button" class="close" οnclick="removeModal(\''+tableId+'\',\'edit\')">';totalHtml+='<span aria-hidden="true">×</span></button><h4 class="modal-title text-success" ng-class="text-success" id="'+tableId+'-edit-header"><i class="fa fa-plus"></i>'+$.i18n("exportData")+'</h4></div><div class="ng-scope" style="padding-top:15px;height: 290px;overflow-y: auto;">';totalHtml+='<li style="border-bottom: 1px solid #00A2E9;height:30px;"><div class="pull-left"><label style="margin: 0 0 0 20px;"><input type="checkbox" id="'+tableId+'-export-checkAllBtn" οnchange="checkAllShowExportItems(\''+tableId+'\')"><span class="text"></span></label>'+$.i18n("check_all")+'</div></li>';totalHtml+='<div id="'+tableId+'-export-RowPopId"></div>';totalHtml+='</div><div class="modal-footer " style="background-color:#FFFFFF;"><button type="button" class="btn btn-primary" οnclick="sbmtExport(\''+tableId+'\')">'+$.i18n("exportData");totalHtml+='</button><a class="btn btn-danger" οnclick="removeModal(\''+tableId+'\',\'edit\')">'+$.i18n("cancel")+'</a></div></div></div></div>';$("body").append(totalHtml);exportRowsModel(tableId,tableParams);var $editModal = $('#'+tableId + "-edit"); $editModal.modal({backdrop: 'static'});setTimeout(function () {$editModal.find("span.error").remove();$editModal.find("input.error").removeClass("error");}, 200);}function exportRowsModel(tableId, tableItems){var selectRowsPopId = tableId + "-export-RowPopId";//显示列下拉框IDvar allCheckBtnId = tableId + "-export-checkAllBtn";//全选框IDvar i18nPrefix = getLocalStorageModel(tableId, "tableParams").i18nPrefix;var html = "";var selectItemsjson = tableItems.trs;var len = selectItemsjson.length;for(var i=0; i<len; i++){if(selectItemsjson[i].lastShow === false || selectItemsjson[i].comType == "uploadFiles"){continue;}html+= "<li οnclick='event.stopPropagation()'><label style='margin: 0 0 0 20px;'>"; html+= "<input type='checkbox'";if(!(selectItemsjson[i].show === false)){html+= " checked ";}var name = selectItemsjson[i].name;var title = $.i18n(i18nPrefix + name)||$.i18n("mon." + name);html+= "name='"+selectRowsPopId+"' value='"+name+"' οnchange='changeShowExportItems(\""+tableId+"\")'>";html+= "<span class='text'></span></label>"+title +"</li>"; } $("#"+selectRowsPopId).html(html); //是否勾选全选框var chsub = $("input[name='"+selectRowsPopId+"']").length; //获取items的个数 var checkedsub = $("input[name='"+selectRowsPopId+"']:checked").length; //获取选中的items的个数 $("#"+allCheckBtnId).attr("checked", checkedsub == chsub); }function changeShowExportItems(tableId){var selectRowsPopId = tableId + "-export-RowPopId";//显示列下拉框IDvar itemsLen = $("input[name='"+selectRowsPopId+"']").length; //获取items的个数 var checkedItemsLen = $("input[name='"+selectRowsPopId+"']:checked").length; //获取选中的items的个数if (itemsLen == checkedItemsLen) {$("#" + tableId + "-export-checkAllBtn").prop("checked", true); }else{$("#" + tableId + "-export-checkAllBtn").prop("checked", false); }}function checkAllShowExportItems(tableId){var isChecked = $("#" + tableId + "-export-checkAllBtn").prop('checked'); $("input[name='"+tableId + "-export-RowPopId']").prop("checked", isChecked);}var exportProgressInterval;function getExportProgress(tableId){showExportProgressTip(tableId);//循环查看状态exportProgressInterval = setInterval(function(){$.ajax({url: window.PATH + '/exportProgressServlet',type: 'POST',dataType: 'json',success: function (res) {if(res.isLogin == false){clearInterval(exportProgressInterval);}else{if(res.ex_status){$("#"+tableId+"-export-percent").html(100);clearInterval(exportProgressInterval); removeModal(tableId,'export-progress-modal');}else{if(res.ex_curCount >0){$("#"+tableId+"-export-curCount").html(res.ex_curCount);$("#"+tableId+"-export-totalCount").html(res.ex_totalCount);if(res.ex_totalCount==0 || res.ex_curCount==0){$("#"+tableId+"-export-percent").html(0);}else{$("#"+tableId+"-export-percent").html((res.ex_curCount*100/res.ex_totalCount).toFixed(2));if(res.ex_totalCount == res.ex_curCount){clearInterval(exportProgressInterval); removeModal(tableId,'export-progress-modal');}}}}}},error: function(){console.log("error");clearInterval(exportProgressInterval);removeModal(tableId,'export-progress-modal');}});}, 100);}function sbmtExport(tableId){btnDisabledOrEnable(tableId+"-exportCsv",false,"darkorange");var selectRowsPopId = tableId + "-export-RowPopId";//显示列下拉框IDvar checkedItems = $("input[name='"+selectRowsPopId+"']:checked");var selectedItems='';for(var i=0;i<checkedItems.length;i++){if(i>0){selectedItems +=',';}selectedItems += $(checkedItems[i]).val();}var formData =new FormData();formData.append("cx_selectedItems",selectedItems);var queryParams = getSearchParams(tableId);for (var key in queryParams){formData.append(key,queryParams[key]);}if (selectedItems == '') {layer.msg($.i18n("select_fields_to_export"), {icon: 7, time: 1000 });return;}var localUrl = getLocalStorageModel(tableId, "requestUrl"); var url = window.PATH + localUrl + "export.ajax";var xhr = new XMLHttpRequest();//设置响应类型为blob类型xhr.responseType = "blob";xhr.onload = function () {if (this.status == "200") {//获取响应文件流var blob = this.response;var aElem = document.createElement('a');//将文件流保存到a标签aElem.href = window.URL.createObjectURL(blob);aElem.download = new Date().format("yyyyMMddHHmmss") + Math.floor(Math.random()*10)+".xlsx";aElem.onload = function (e) {window.URL.revokeObjectURL(aElem.href);};$("body").append(aElem); // 修复firefox中无法触发clickaElem.click();$(aElem).remove();clearInterval(exportProgressInterval);removeModal(tableId,'export-progress-modal');btnDisabledOrEnable(tableId+"-exportCsv",true,"darkorange");}}xhr.open("post", url, true);xhr.send(formData);removeModal(tableId,'edit');getExportProgress(tableId);}function showExportProgressTip(tableId){var totalHtml = '<div class="modal fade modal-primary" id="'+tableId +'-export-progress-modal" aria-hidden="true"><div class="modal-dialog">'+'<div class="modal-content">';totalHtml += '<div class="modal-header"><button type="button" class="close" οnclick="removeModal(\''+tableId+'\',\'export-progress-modal\')"><span aria-hidden="true">×</span></button>';totalHtml += '<h4 class="modal-title" ng-class="text-success" id="'+ tableId +'-edit-header"><i class="fa fa-list-alt"></i>'+$.i18n("exportDetails")+'</h4></div>';totalHtml += '<div class="ng-scope" style="padding-top:15px;">';totalHtml += '<div style="text-align: center;">';totalHtml += '<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12" style="height:40px;"><i class="fa fa-spinner fa-spin" aria-hidden="true" style="font-size:26px;color:#B0E2FF;"></i></div>';totalHtml += '<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">';totalHtml += $.i18n("export_total_number")+' <label id="'+tableId +'-export-totalCount" style="color:red;font-weight:bold;text-align:left;">0</label>, ';totalHtml += $.i18n("export_current_number")+' <label id="'+tableId +'-export-curCount" style="color:green;font-weight:bold;text-align:left;">0</label> ';totalHtml += '(<label id="'+tableId +'-export-percent" style="color:green;font-weight:bold;">0</label>%)</div>';totalHtml += '</div><div class="modal-footer " style="background-color:#FFFFFF;">';totalHtml += '<a class="btn btn-danger" οnclick="removeModal(\''+tableId+'\',\'export-progress-modal\')">'+ $.i18n("cancel")+'</a></div>';$("body").append(totalHtml);var $editModal = $('#'+tableId + "-export-progress-modal"); $editModal.modal({backdrop: 'static'});setTimeout(function () {$editModal.find("span.error").remove();$editModal.find("input.error").removeClass("error");}, 200);}
上面提交后到控制层
/*** ajax查询* @throws IOException **/@RequestMapping(value = EUConst.URI_EXPORT_AJAX, method = RequestMethod.POST, produces = MediaTypes.JSON_UTF_8)@ResponseBodypublic void exportData(HttpServletRequest request, HttpServletResponse response) throws IOException{String sid = request.getSession().getId();ProgressSingleton.put(sid + EUConst.EX_STATUS,false);String fileSaveName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+ new Random().nextInt(10);Map<String, Object> queryParam = ActionUtils.parseParameters(request, "cx_");List<String> headerList = Arrays.asList(queryParam.get("selectedItems").toString().split(",")); // 表格Titile queryParam.remove("selectedItems");ExportExcelUtils ee = new ExportExcelUtils(CommonUtils.lang("menu.cdr_cdrEtms"),"db.tbCdr.",headerList); if(!queryParam.isEmpty()){getQueryParam(queryParam);if(!isNotEmpty(queryParam.get("LIKE-|-dev_id"))){TbAdmUser user = UserUtils.getUserProfile().getTbAdmUser();Object[] devIds = devService.getDevIdsByAdmUserId(user.getUid());if(devIds.length>0){queryParam.put("IN-|-dev_id", StringUtils.join(devIds, ","));}} if(isNotEmpty(queryParam.get("LIKE-|-dev_id")) || isNotEmpty(queryParam.get("IN-|-dev_id"))){//初始化查询条件String conditions = cdrService.getConditions(queryParam);//根据条件获取需要查询的几张表 分表的时候根据时间来找出需要查询的表String queryTables = cdrService.getTableNames(queryParam);queryParam.remove("GT-|-endTime"); //中间的表不需要加上时间条件搜索queryParam.remove("LTE-|-endTime");String notTimeConditions = cdrService.getConditions(queryParam);notTimeConditions = notTimeConditions.split("ORDER BY")[0];//根据条件获取总数据条数 如：12万条数据int records = cdrService.getAllRows(conditions,queryTables);ProgressSingleton.put(sid + EUConst.EX_TOTAL_COUNT, records);//每次查询500条数据，在来根据总条数来计算需要查询的次数int pages = records%1000==0?records/1000:records/1000+1;int idx = 0;String state = CommonUtils.lang("db.tbCdr.state")+":"+CommonUtils.lang("db.Data.str");String subtype = CommonUtils.lang("db.tbCdr.subtype")+":"+CommonUtils.lang("db.Data.str");Row row1 = ee.addRow(); ee.addCell(row1,1,state);ee.addCell(row1,2,subtype);for (int i = 0; i < pages; i++) {List<TbCdr> list = cdrService.queryPageList(i+1,1000,conditions,notTimeConditions,queryTables);for (TbCdr tbCdr : list) {if(idx>0 && idx % 60000 ==0){ee.createSheetAndStyles();}int x = 0;Row row = ee.addRow(); for(int j = 0; j < headerList.size(); j++){String headerName = headerList.get(j);if("cause".equals(headerName)){ee.addCell(row,x++,tbCdr.getCause()+" "+tbCdr.getReason());}else if("gw_rc".equals(headerName)){ee.addCell(row,x++,tbCdr.getGw_cause());}else if("allPortName".equals(headerName)){ee.addCell(row,x++,CommonUtils.allPortName(tbCdr.getDev_name(),tbCdr.getPortno()));}else{ee.addCell(row,x++,CommonUtils.getDeclaredField(headerName, tbCdr));}}idx ++;ProgressSingleton.put(sid + EUConst.EX_CUR_COUNT, idx);}list.clear();}}}ProgressSingleton.remove(sid + EUConst.EX_TOTAL_COUNT);ProgressSingleton.remove(sid + EUConst.EX_CUR_COUNT);ProgressSingleton.put(sid + EUConst.EX_STATUS,true);ee.write(response,"Cdr_" + fileSaveName+".xlsx"); ee.dispose(); }
最前面有个异步到后台session中获取导出进度
import java.util.Hashtable;public class ProgressSingleton {//为了防止多用户并发，使用线程安全的Hashtableprivate static Hashtable<Object, Object> table = new Hashtable<Object, Object>();public static void put(Object key, Object value){table.put(key, value);}public static Object get(Object key){return table.get(key);}public static Object remove(Object key){return table.remove(key);}}import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.alibaba.fastjson.JSONObject;public class ProgressServlet extends HttpServlet {private static final long serialVersionUID = 1L;public void service(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{String id = request.getSession().getId();Object totalCount = ProgressSingleton.get(id + EUConst.EX_TOTAL_COUNT);Object curCount = ProgressSingleton.get(id + EUConst.EX_CUR_COUNT);Object status = ProgressSingleton.get(id + EUConst.EX_STATUS);JSONObject json = new JSONObject();json.put(EUConst.EX_TOTAL_COUNT, (totalCount !=null ? totalCount : 0));json.put(EUConst.EX_CUR_COUNT, (curCount !=null ? curCount : 0));json.put(EUConst.EX_STATUS, (status !=null ? status : false));response.getWriter().print(json.toString());}}
会显示导出总条数，当前导出条数。
上面只是一个思路，方便后面需要记录下，代码不全。
