项目中的导出（jxl插件）

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_33743248

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量225

点赞数

文章标签：数据库

假如你现在还在为自己的技术担忧，假如你现在想提升自己的工资，假如你想在职场上获得更多的话语权，假如你想顺利的度过35岁这个魔咒，假如你想体验BAT的工作环境，那么现在请我们一起开启提升技术之旅吧，详情请点击http://106.12.206.16:8080/qingruihappy/index.html

第一步，获取要导出的参数，为导出做准备

 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<String, String> params = new HashMap<String, String>();
17         String[] flowIds = null;
18         List<ExcelShellProperty> list = new ArrayList<ExcelShellProperty>();
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<ExcelShellProperty> qryList = new ArrayList<ExcelShellProperty>();
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             }else
79             {
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 List<ExcelShellProperty> getDownLoadList(Map params) throws Exception {
 3         List<Map<String, Object>> openMPWMapList = getDetailListForDownLoad(params);
 4         
 5         if (CollectionUtils.isNotEmpty(openMPWMapList)) {
 6             for (Map<String, Object> openMPWMap : openMPWMapList) {
 7                 setAccountDefaultData(openMPWMap);
 8             }
 9         }
10         
11         List<ExcelShellProperty> list = new ArrayList<ExcelShellProperty>();
12         ExcelShellProperty excelShellProperty = new ExcelShellProperty();
13         excelShellProperty.setSheetName("CRM-无卡支付移动支付信息");
14         excelShellProperty.setColProperties(EXLPROPERTIES);
15         excelShellProperty.setColView(EXLCOLVIEW);
16         excelShellProperty.setDataList(openMPWMapList);
17 
18         list.add(excelShellProperty);
19         return list;
20     }

 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, List<ExcelShellProperty> sheets, 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         try
11         {
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         finally
62         {
63             try
64             {
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     }