java导出数据库数据到Excel,用户可以选择需要导出的字段，显示导入进度

项目中用到的导出数据到Excel记录下
需要包：poi-3.9.jar

页面代码：

 /*** 显示所有列，勾选需要导出的**/function exportModel(tableId){var tableParams = getLocalStorageModel(tableId, "tableParams");var totalHtml ='+tableId +'-edit"  aria-hidden="true">';totalHtml+='+tableId+'\',\'edit\')">';totalHtml+='×
+tableId+'-edit-header">'+$.i18n("exportData")+'
';totalHtml+='
+tableId+'-export-checkAllBtn" οnchange="checkAllShowExportItems(\''+tableId+'\')">'+$.i18n("check_all")+'
';totalHtml+='+tableId+'-export-RowPopId">';totalHtml+='+tableId+'\')">'+$.i18n("exportData");totalHtml+='+tableId+'\',\'edit\')">'+$.i18n("cancel")+'';$("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+= "
"; html+= ";if(!(selectItemsjson[i].show === false)){html+= " checked ";}var name = selectItemsjson[i].name;var title = $.i18n(i18nPrefix + name)||$.i18n("db.common." + name);html+= "name='"+selectRowsPopId+"' value='"+name+"' οnchange='changeShowExportItems(\""+tableId+"\")'>";html+= ""+title +"
"; } $("#"+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 = '+tableId +'-export-progress-modal"  aria-hidden="true">'+'';totalHtml += '+tableId+'\',\'export-progress-modal\')">×';totalHtml += '
+ tableId +'-edit-header">'+$.i18n("exportDetails")+'
';totalHtml += '';totalHtml += '';totalHtml += '';totalHtml += '';totalHtml += $.i18n("export_total_number")+' +tableId +'-export-totalCount">0, ';totalHtml += $.i18n("export_current_number")+' +tableId +'-export-curCount">0 ';totalHtml += '(+tableId +'-export-percent">0%)';totalHtml += '';totalHtml += '+tableId+'\',\'export-progress-modal\')">'+ $.i18n("cancel")+'';$("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.tbCdr.state.comData.str");String subtype = CommonUtils.lang("db.tbCdr.subtype")+":"+CommonUtils.lang("db.tbCdr.subtype.comData.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());}}

会显示导出总条数，当前导出条数。
上面只是一个思路，方便后面需要记录下，代码不全。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！