假如你现在还在为自己的技术担忧，假如你现在想提升自己的工资，假如你想在职场上获得更多的话语权，假如你想顺利的度过35岁这个魔咒，假如你想体验BAT的工作环境，那么现在请我们一起开启提升技术之旅吧，详情请点击
第一步，获取要导出的参数，为导出做准备
1 public ModelAndView downloadInfo(final HttpServletRequest request, final HttpServletResponse response) throws Exception { 2 3 List2ExcelHelper list2ExcelHelper = new List2ExcelHelper(); 4 String orderId = request.getParameter("orderId"); // 工单 5 String idContent = request.getParameter("idContent"); 6 String memcode = request.getParameter("memberCode"); 7 String merchantName = request.getParameter("merchantName"); 8 String flowNodeId = request.getParameter("flowNodeId"); // 流程+节点+产品编码 9 String orderType = request.getParameter("orderType"); //10 String merchantLevel = request.getParameter("merchantLevel"); // 产品11 String startDate = request.getParameter("startDate");12 String endDate = request.getParameter("endDate");13 String flowId = flowNodeId.split(";")[0];14 String nodeId = flowNodeId.split(";")[1];15 String productCode = flowNodeId.split(";")[2];16 Map params = new HashMap ();17 String[] flowIds = null;18 List list = new ArrayList ();19 if (!StringUtil.isEmpty(orderId)) {20 params.put("orderId", orderId);21 }22 if (!StringUtil.isEmpty(memcode)) {23 params.put("memcode", memcode);24 }25 if (!StringUtil.isEmpty(idContent)) {26 params.put("idContent", idContent);27 }28 if (!StringUtil.isEmpty(merchantName)) {29 params.put("merchantName", merchantName);30 }31 if (!StringUtil.isEmpty(merchantLevel)) {32 params.put("merchantLevel", merchantLevel);33 }34 params.put("flowNodeId", flowNodeId);35 params.put("orderType", orderType);36 flowIds = flowId.split(",");37 params.put("flowId", flowId);38 params.put("nodeId", nodeId);39 params.put("productCode", productCode);40 if (!StringUtil.isEmpty(startDate)) {41 params.put("startDate", startDate);42 }43 if (!StringUtil.isEmpty(endDate)) {44 params.put("endDate", endDate);45 }46 47 ManualOpenService manualOpenService = manualOpenFactory.getManualOpenService(params);48 if (manualOpenService == null) {49 return index(request, response);50 }51 52 for(String qryFlowId :flowIds)53 {54 params.put("flowId", qryFlowId);55 List qryList = new ArrayList ();56 qryList = manualOpenService.getDownLoadList(params);57 if(!list.isEmpty())58 {59 for(ExcelShellProperty espList:list)60 {61 for(ExcelShellProperty qryEspList:qryList)62 {63 List tempEspList = new ArrayList();64 if(espList.getSheetName().equals(qryEspList.getSheetName())){65 for(Object obj:qryEspList.getDataList()){66 if(!espList.getDataList().contains(obj)){67 tempEspList.add(obj);68 }69 }70 }71 if(!tempEspList.isEmpty())72 {73 espList.getDataList().addAll(tempEspList);74 }75 }76 }77 78 }else79 {80 list.addAll(qryList);81 }82 }83 84 String downLoadName = flowNodeId;85 86 NodeRole nodeRole = new NodeRole();87 nodeRole.setFlowId(flowId);88 nodeRole.setNodeId(nodeId);89 nodeRole = nodeRoleService.findNodeRoleByModel(nodeRole);90 if (nodeRole != null) {91 downLoadName = manualOpenService.generateDownloadName(request, nodeRole.getNodeDesc());92 }93 94 list2ExcelHelper.downLoad("商户信息", list, response, downLoadName);95 return null;96 } 其中最主要的就是这个qryList
qryList就是下面代码封装的东西。
封装信息一：EXLPROPERTIES
1 [工单详情ID, 订单编号, 客户名称, 快钱账户, 商户编号, SDK聚合支付, 微信APP支付%, 支付宝APP支付（标准版）%, 支付宝APP支付（定制版）%, 飞凡通APP支付%, 移动网关, 微信公众号支付, 公众号appid, 公众号appid费率, 微信WAP支付%, 支付宝服务窗支付%, 支付宝WAP支付（标准版）%, 支付宝WAP支付（定制版）%, 飞凡通H5支付%, 备注]
封装信息二：EXLCOLVIEW
1 [orderId, crmOrderId, merchantName, idContent, assetCode, SDKAggregationPayment, WXOrdinaryRate, ZhiFuBaoSOrdinaryRate, ZhiFuBaoCOrdinaryRate, SPAOrdinaryRate, MobileGateway, WeiXinPublicPayment, PublicNumberAppId, PNAOrdinaryRate, WXWPOrdinaryRate, ASWPOrdinaryRate, AWPSOrdinaryRate, AWPCOrdinaryRate, FFH5POrdinaryRate, Remark]
封装信息三：CRM-无卡支付移动支付信息
封装信息四：最主要的，从数据库动态获取的。下面是取了一个静态的作为案例的讲解
openMPWMapList
1 [{SPAOrdinaryRate=1, authorizeNum=null, merchantFinanceTmp=null, PublicNumberAppId=, creater=default_crm, bankProvince=null, WXWPOrdinaryRate=, city=澳门市, partnerid=null, saler=null, financeName=341321, PNAOrdinaryRate=, businessArea=, brandLicense=null, orgCode=null, merchantType=0, WXOrdinaryRate=, mainProductService=, SDKAggregationPayment=是, registCode=null, memberCode=10012400238, legalName=刘淑敏, statusLevel2=1, businessScope=null, unionPay=0, mainUsers=null, bankName=null, businessType=3, nullName=刘淑敏, postcode=null, manualDowngrade=0, channelShortName=春秋test01, ext3=null, callName=341321, salerDomainName=sunny.han, relationType=null, industryCode=301001, assetCode=812320047220019, externalId=15137639706423110, isBlack=0, merchantFinance={applyId=1712201755497668, billShareAmount=null, merchantsRegistratioId=null, billMailSendCont=2389472374@qq.com, contractIsExt=1, goalDescription=null, oppId=null, id=3385, discountReasonDescribe=null, crmOrderId=null, description=null, oppName=null, billAccountShareAmount=null, sendMailWay=1, createDate=1513763935000, merchantCode=3021729, fgpa=null, oldOppId=null, predictDate=null, payment=客户线下付款或返还, contractUniformSend=1, orderField=null, invoiceType=增值税普通发票, useDeposit=null, PayAfterReceive=null, goal=null, paymentLink=null, webphoneOrderNumber=null, contractNotstandOa=null, contractSuppleOa=null, discountReason=null, deliveryStatus=1, billContractEndDate=2018-12-19, bankAccount=null, billContractStartDate=2017-12-20}, businessTelephone=13721241234, mobilephone=13721241234, frontHelper=danny.huang, squareName=null, subjectType=, businessEmail=test0080201@qq.com, grade=普通, clueProvider=, statusLevel1=null, createDate=2017-12-20 18:04:37, changeList=, expectedAvgMonthlyAmount99Bill=, merchantCode=3021729, orderId=17698709, channelName=春秋航空公司test01, multiAssetTag=0, itemStatus=0, registFund=null, salerId=21705, foundDate=null, financeEmail=test0080201@qq.com, callTelephone=13721241234, authfileStatus=0, method=save, custBgIntroduction=, cardDesc=null, applyDetailId=7210226650, constraintBusiness=百货, telephone=null, bankBranch=null, isWdBusinessCircle=0, accessType=首选, nullEmail=201712081453test@163.com, merchantAlias=434, orgBelongto=null, productScope=营销产品[预付卡];金融产品[信用支付];支付产品[CP刷卡支付, 终端, 提现&定向付款, 电子付款, 无卡支付, 人民币网关, 移动支付, 账户支付(飞凡通), 静态码], businessName=341321, remark=, nullTelephone=13721241234, financeTelephone=13721241234, isUniformSocialCredit=null, ZhiFuBaoSOrdinaryRate=, ZhiFuBaoCOrdinaryRate=, bankNo=null, custIndustryStatus=, assetsSerial=DSCNMPW100115442027210226650, industryParentType=3, workOrderType=订单新增, shareMargin=, callEmail=test0080201@qq.com, province=澳门特别行政区, handleDate=null, applydetailId=7210226650, squareType=null, custSaleModel=, FFH5POrdinaryRate=2, capitalPaid=, ASWPOrdinaryRate=, bankCity=null, expectedAvgMonthlyAmount=, clueSource=, cardType=1, email=201712081453test@163.com, billAccountType=0, parentAssetsSerial=DSCNP100115442027210226648, websiteMerchantType=0, customerStatus=潜在, idContent=2389472374@qq.com, SpecialPassAPPPay=1, merchantSource=null, parentCustcode=812110053000029, operationTime=0.0, merchantShortName=null, childrenCustomer=0, registDate=null, crmOrderId=1712201755497668, area=null, cancelDate=null, merchantName=无卡支付00053, legalId=432477777444444, parentCustname=, expectedAvgTransactionValue=, FeiFanH5Pay=1, busiPropertyBelongto=1, cpAreaCode=11111111, AWPCOrdinaryRate=, legalSex=0, identityType=null, registAddress=null, industrySubType=19, custAnnualSales=, workOrderId=24319, productCode=DSCNMPW, website=null, batchOrder=null, otherInfo=null, MobileGateway=是, headquarters=账户转移商户A02, saleOrg=1, keyCustomer=0, categorys=null, isChannelCustomer=null, address=澳门特别行政区安徽省安庆市江苏无锡, businessRegno=45234523, clueConnSituation=, companyType=有限责任公司, businessFormat=null, Remark=, AWPSOrdinaryRate=, customerBrand=, employees=0, ffanDept=null}, {SPAOrdinaryRate=3, authorizeNum=null, merchantFinanceTmp=null, PublicNumberAppId=, creater=default_crm, bankProvince=null, WXWPOrdinaryRate=, city=澳门市, partnerid=null, saler=null, financeName=caiwu1, PNAOrdinaryRate=, businessArea=, brandLicense=null, orgCode=null, merchantType=0, WXOrdinaryRate=, mainProductService=, assetsNumToShow=, SDKAggregationPayment=是, registCode=null, memberCode=10012400246, legalName=刘淑敏, statusLevel2=1, businessScope=null, unionPay=0, mainUsers=null, bankName=null, businessType=3, nullName=hetong1, postcode=null, manualDowngrade=0, channelShortName=春秋test01, ext3=null, callName=tiaodan1, salerDomainName=sunny.han, relationType=null, industryCode=301002, assetCode=812320047220019, externalId=15138254313089911, isBlack=0, merchantFinance={applyId=1712211059427651, billShareAmount=null, merchantsRegistratioId=null, billMailSendCont=3872385792378@qq.com, contractIsExt=1, goalDescription=null, oppId=null, id=3394, discountReasonDescribe=null, crmOrderId=null, description=null, oppName=null, billAccountShareAmount=null, sendMailWay=1, createDate=1513825388000, merchantCode=3021737, fgpa=null, oldOppId=null, predictDate=null, payment=客户线下付款或返还, contractUniformSend=1, orderField=null, invoiceType=增值税普通发票, useDeposit=null, PayAfterReceive=null, goal=null, paymentLink=null, webphoneOrderNumber=null, contractNotstandOa=null, contractSuppleOa=null, discountReason=null, deliveryStatus=1, billContractEndDate=2018-12-20, bankAccount=null, billContractStartDate=2017-12-21}, businessTelephone=13721241234, mobilephone=13721241234, frontHelper=danny.huang, squareName=null, subjectType=, businessEmail=shangwu0080201@qq.com, grade=普通, clueProvider=, statusLevel1=null, createDate=2017-12-21 11:05:07, changeList=, expectedAvgMonthlyAmount99Bill=, merchantCode=3021737, orderId=17698730, channelName=春秋航空公司test01, multiAssetTag=0, itemStatus=0, registFund=null, parentAssetsNumToShow=812320047220019, salerId=21705, foundDate=null, financeEmail=caiwu0080201@qq.com, callTelephone=13721241234, authfileStatus=0, method=save, custBgIntroduction=, cardDesc=null, applyDetailId=7210226680, constraintBusiness=百货, telephone=null, bankBranch=null, isWdBusinessCircle=0, accessType=首选, nullEmail=test0080201@qq.com, merchantAlias=434, orgBelongto=null, productScope=营销产品[预付卡];金融产品[信用支付];支付产品[CP刷卡支付, 终端, 提现&定向付款, 电子付款, 无卡支付, 人民币网关, 移动支付, 账户支付(飞凡通), 静态码], businessName=shangwu1, remark=, nullTelephone=13721241234, financeTelephone=13721241234, isUniformSocialCredit=null, ZhiFuBaoSOrdinaryRate=, ZhiFuBaoCOrdinaryRate=, bankNo=null, custIndustryStatus=, assetsSerial=DSCNMPW100115442027210226680, industryParentType=3, workOrderType=订单新增, shareMargin=, callEmail=tiaodan0080201@qq.com, province=澳门特别行政区, handleDate=null, applydetailId=7210226680, squareType=null, custSaleModel=, FFH5POrdinaryRate=4, capitalPaid=, ASWPOrdinaryRate=, bankCity=null, expectedAvgMonthlyAmount=, clueSource=, cardType=1, email=201712081453test@163.com, parentAssetsId=4380639, billAccountType=0, parentAssetsSerial=DSCNP100115442027210226663, websiteMerchantType=0, customerStatus=正式, idContent=3872385792378@qq.com, SpecialPassAPPPay=1, merchantSource=null, parentCustcode=812110053000029, operationTime=0.0, merchantShortName=null, childrenCustomer=0, registDate=null, crmOrderId=1712211059427651, area=null, cancelDate=null, merchantName=无卡支付00054, legalId=2342342342, parentCustname=, expectedAvgTransactionValue=, FeiFanH5Pay=1, busiPropertyBelongto=1, cpAreaCode=11111111, AWPCOrdinaryRate=, legalSex=0, identityType=null, registAddress=null, industrySubType=20, custAnnualSales=, workOrderId=24332, productCode=DSCNMPW, website=null, batchOrder=1, otherInfo=null, MobileGateway=是, headquarters=账户转移商户A02, saleOrg=1, keyCustomer=0, categorys=null, isChannelCustomer=null, address=澳门特别行政区安徽省安庆市江苏无锡, businessRegno=342342, clueConnSituation=, companyType=有限责任公司, businessFormat=null, Remark=, AWPSOrdinaryRate=, customerBrand=, employees=0, ffanDept=null}]
1 @Override 2 public ListgetDownLoadList(Map params) throws Exception { 3 List
1 public class ExcelShellProperty 2 { 3 private String[] colProperties; 4 private String[] colView; 5 private String sheetName; 6 private List dataList; 7 public String[] getColProperties() { 8 return colProperties; 9 }10 public void setColProperties(String[] colProperties) {11 this.colProperties = colProperties;12 }13 public String[] getColView() {14 return colView;15 }16 public void setColView(String[] colView) {17 this.colView = colView;18 }19 public String getSheetName() {20 return sheetName;21 }22 public void setSheetName(String sheetName) {23 this.sheetName = sheetName;24 }25 public List getDataList() {26 return dataList;27 }28 public void setDataList(List dataList) {29 this.dataList = dataList;30 }31 32 33 } 1 private static final String[] EXLPROPERTIES = {"orderId", "crmOrderId", "merchantName", "idContent" , "assetCode","SDKAggregationPayment","WXOrdinaryRate",2 "ZhiFuBaoSOrdinaryRate", "ZhiFuBaoCOrdinaryRate", "SPAOrdinaryRate", "MobileGateway" ,"WeiXinPublicPayment", "PublicNumberAppId", 3 "PNAOrdinaryRate","WXWPOrdinaryRate","ASWPOrdinaryRate","AWPSOrdinaryRate","AWPCOrdinaryRate", "FFH5POrdinaryRate","Remark"};4 5 private static final String[] EXLCOLVIEW = { "工单详情ID", "订单编号", "客户名称", "快钱账户", "商户编号", "SDK聚合支付",6 "微信APP支付%", "支付宝APP支付（标准版）%", "支付宝APP支付（定制版）%", "飞凡通APP支付%", "移动网关", "微信公众号支付", "公众号appid","公众号appid费率", "微信WAP支付%",7 "支付宝服务窗支付%", "支付宝WAP支付（标准版）%", "支付宝WAP支付（定制版）%", "飞凡通H5支付%", "备注" };
第二步：插件的应用
1 public void downLoad(String dealType, Listsheets, HttpServletResponse response, String fileName) 2 { 3 WritableWorkbook book = null; 4 ByteArrayOutputStream baos = null; 5 ServletOutputStream out = null; 6 if (sheets.size() == 0) 7 { 8 return; 9 }10 try11 {12 baos = new ByteArrayOutputStream();13 // 打开文件，baos可以是一个字节流也可以是一个路径，baos一开始是一个null对象，下面通过book一直往里面添加东西，最后这个对象里面就有东西了。14 book = Workbook.createWorkbook(baos);15 int sheetNo = 1;16 for (ExcelShellProperty excelShellProperty : sheets)17 { //就是excel表格的sheet那里写的信息，默认的是sheet，sheet1等18 //sheetNo是第几个excel表格19 WritableSheet sheet = book.createSheet(excelShellProperty.getSheetName(), sheetNo);20 WritableCellFormat format = new WritableCellFormat(); //WritableCellFormat处代码使用了WritableCellFormat类，这个类非常重要，通过它可以指定单元格的各种属性，后面的单元格格式化中会有更多描述。 21 format.setAlignment(Alignment.CENTRE);22 23 format.setVerticalAlignment(VerticalAlignment.CENTRE);24 format.setBackground(Colour.BLUE);25 Label label = null;26 WritableFont wf = new WritableFont(WritableFont.TAHOMA);27 wf.setColour(Colour.BLUE);28 wf.setPointSize(12);29 WritableCellFormat wcfF = new WritableCellFormat(wf);30 //colView获取第一行的内容，并且是带有格式的31 String[] colView = excelShellProperty.getColView();32 int i = 0;33 for (int len = colView.length; i < len; ++i)34 {35 label = new Label(i, 0, colView[i], wcfF);36 sheet.setColumnView(i, 25);37 sheet.addCell(label);38 }39 40 setExcelBody(excelShellProperty.getDataList(), excelShellProperty.getColProperties(), label, sheet);41 sheetNo++;42 }43 book.write();44 book.close();45 book = null;46 response.setContentType("application/vnd.ms-excel;charset=gb2312");47 48 response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1" )+".xls");49 50 response.setContentLength(baos.size());51 52 out = response.getOutputStream();53 baos.writeTo(out);54 out.flush();55 out.close();56 }57 catch(Exception e)58 {59 log.error("List2ExcelHelper downLoad" , e);60 }61 finally62 {63 try64 {65 if (baos != null)66 baos.close();67 if (book != null)68 book.close();69 if (out != null)70 out.close();71 }72 catch(Exception e2)73 {74 e2.printStackTrace();75 }76 }77 }
1 public void setExcelBody(List massList, String[] colProperties, Label label, WritableSheet sheet1) 2 throws WriteException 3 { 4 int i = 0; 5 for (int len = massList.size(); i < len; ++i) 6 { //获取第2，3,4。。。。。行第一列的内容 7 Map listOrderMap = (Map) massList.get(i); 8 String cont = listOrderMap.get(colProperties[0])== null?"":listOrderMap.get(colProperties[0]).toString(); 9 label = new Label(0, i + 1, cont);10 sheet1.addCell(label);11 12 int j = 1;13 for (int length = colProperties.length; j < length; ++j)14 { //获取第2，3,4。。。。。行第二列以后的内容15 label = new Label(j, i + 1, getObjectStr(listOrderMap.get(colProperties[j])));16 sheet1.addCell(label);17 }18 }19 } 效果展示：
