package com.goafanti.order.service.impl; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import com.goafanti.common.bo.Error; import com.goafanti.common.constant.AFTConstants; import com.goafanti.common.dao.TOrderBillNewMapper; import com.goafanti.common.dao.TOrderBonusMapper; import com.goafanti.common.dao.TOrderLogMapper; import com.goafanti.common.dao.TOrderMidMapper; import com.goafanti.common.dao.TOrderNewMapper; import com.goafanti.common.dao.TOrderTaskMapper; import com.goafanti.common.dao.TemporaryReceivablesMapper; import com.goafanti.common.enums.OrderLogProcess; import com.goafanti.common.error.BusinessException; import com.goafanti.common.model.TOrderBillNew; import com.goafanti.common.model.TOrderBonus; import com.goafanti.common.model.TOrderLog; import com.goafanti.common.model.TOrderNew; import com.goafanti.common.model.TOrderTask; import com.goafanti.common.model.TemporaryReceivables; import com.goafanti.common.utils.DateUtils; import com.goafanti.common.utils.ExportExcelUtil; import com.goafanti.common.utils.LoggerUtils; import com.goafanti.core.mybatis.BaseMybatisDao; import com.goafanti.core.mybatis.page.Pagination; import com.goafanti.core.shiro.token.TokenManager; import com.goafanti.order.bo.OrderListBo; import com.goafanti.order.bo.ReceivablesListBo; import com.goafanti.order.bo.TOrderInvoiceBo; import com.goafanti.order.bo.TOrderNewBo; import com.goafanti.order.bo.TemporaryReceivablesBo; import com.goafanti.order.enums.ApprovalNewState; import com.goafanti.order.enums.BonusSubject; import com.goafanti.order.enums.LiquidationNewState; import com.goafanti.order.enums.ProcessStatus; import com.goafanti.order.enums.ReceivablesStatus; import com.goafanti.order.enums.TransactionSubject; import com.goafanti.order.service.FundManageOrderService; import com.goafanti.order.service.OrderBillService; import com.goafanti.order.service.OrderInvoiceService; import com.goafanti.order.service.OrderReceivablesService; @Service public class OrderReceivablesServiceImpl extends BaseMybatisDao implements OrderReceivablesService{ @Autowired private TemporaryReceivablesMapper temporaryReceivablesMapper; @Autowired private TOrderBillNewMapper tOrderBillNewMapper; @Autowired private TOrderNewMapper tOrderNewMapper; @Autowired private TOrderBonusMapper tOrderBonusMapper; @Autowired private TOrderLogMapper tOrderLogMapper; @Autowired private TOrderTaskMapper tOrderTaskMapper; @Autowired private TOrderMidMapper tOrderMidMapper; @Resource private FundManageOrderService fundManageOrderService; @Resource private OrderInvoiceService orderInvoiceService; @Resource private OrderBillService orderBillService; @Override public int addReceivables(TemporaryReceivablesBo t) { t.setAid(TokenManager.getAdminId()); if(t.getCorporateName()==null)t.setCorporateName(""); t.setCreateTime(new Date()); t.setStatus(0); if(t.getReceivablesTimes()!=null) t.setReceivablesTime(DateUtils.StringToDate(t.getReceivablesTimes(), AFTConstants.YYYYMMDD)); return temporaryReceivablesMapper.insertSelective(t); } @Override public int updateReceivables(TemporaryReceivablesBo t) { t.setOrderNo(t.getOrderNo().trim()); t.setStatus(ReceivablesStatus.ZC.getCode()); if(t.getReceivablesTimes()!=null) { t.setReceivablesTime(DateUtils.StringToDate(t.getReceivablesTimes(), AFTConstants.YYYYMMDD)); } return temporaryReceivablesMapper.updateByPrimaryKeySelective(t); } @Override public int deleteReceivables(String ids) { List delList = new ArrayList(); String[] strs = ids.split(","); for (String str : strs) { delList.add(str); } return temporaryReceivablesMapper.deleteByids(delList); } @Override public int deleteAllReceivables() { return temporaryReceivablesMapper.deleteByaid(TokenManager.getAdminId()); } @Override public int batchImport() { List list=temporaryReceivablesMapper.selectByaid(TokenManager.getAdminId()); if(list.size()>99)throw new BusinessException(new Error("数量过大,建议不超过99。")); List listBull=new ArrayList<>(); for (TemporaryReceivables tr : list) { TOrderBillNew tbn=new TOrderBillNew(); //获取已收款 BigDecimal bill = tOrderBillNewMapper.getAmountByOid(tr.getOrderNo()); //获取总金额 TOrderNewBo t=tOrderNewMapper.getOrderNewDetail(tr.getOrderNo()); //订单匹配不到 if(t==null||t.getOrderNo()==null) { tr.setStatus(ReceivablesStatus.DDYC.getCode()); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); continue; }else if(t.getDeleteSign()==2||t.getDeleteSign()==3) { tr.setStatus(ReceivablesStatus.BGSD.getCode()); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); continue; }else if(!tr.getCorporateName().equals(t.getUserName())) { tr.setStatus(ReceivablesStatus.MCYC.getCode()); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); continue; }else if(temporaryReceivablesMapper.selectReceivablesNumberRepeats(tr)>0) { tr.setStatus(ReceivablesStatus.YSK.getCode()); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); continue; } //计算收款结果 BigDecimal bd = bill.add(tr.getAmount()); //判断表中数据 BigDecimal count = new BigDecimal(0).add(bd); for (TOrderBillNew tBill : listBull) { if (tBill.getOrderNo().equals(tr.getOrderNo())) { count=count.add(tBill.getTransactionAmount()); continue; } } TOrderBonus bonus = new TOrderBonus(); bonus.setId(UUID.randomUUID().toString()); bonus.setOrderNo(tr.getOrderNo()); bonus.setGrantBy(TokenManager.getAdminId()); int lstaus = 0;//如果订单流水小于首付 lstaus 0 if(count.compareTo(t.getTotalAmount()) == 1){//如果订单流水总额大于总金额 return -1 tr.setStatus(ReceivablesStatus.JEYC.getCode()); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); continue; }else if (count.compareTo(t.getTotalAmount()) == 0) {//等于总额 //付完款//如果订单流水等于于总金额 lstaus 2 lstaus = 2; bonus.setBonusSubject(BonusSubject.WK.getCode());//12 //发放给技术员如果付完款所有参与这个项目任务的人都会有结算奖 //获得所有的任务负责人 List l = tOrderTaskMapper.getReceiverByOid(tr.getOrderNo()); for (TOrderTask tOrderTask : l) { if(null != tOrderTask &&null != tOrderTask.getTaskReceiver()){ bonus.setTaskId(tOrderTask.getId());//任务id bonus.setGrantTarget(tOrderTask.getTaskReceiver()); bonus.setGrantType(1); int c = tOrderBonusMapper.checkByOnoAndSub(bonus); //不存在就设置 if(c==0){ bonus.setId(UUID.randomUUID().toString()); tOrderBonusMapper.insertSelective(bonus); } } } if(tr.getStatus()==2) { tr.setStatus(0); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); } addOrderLog(tr.getOrderNo(), OrderLogProcess.CWQK.getCode()); }else if (count.compareTo(t.getTotalAmount()) == -1) {//小于总额 //如果订单流水小于总金额 if(bd.compareTo(t.getFirstAmount()) == 0 || bd.compareTo(t.getFirstAmount()) == 1){//首付完成 //查看首付奖是否存在 TOrderBonus b = new TOrderBonus(); b.setOrderNo(tr.getOrderNo()); b.setBonusSubject(11); b.setGrantType(0); b.setGrantTarget(t.getSalesmanId()); int c = tOrderBonusMapper.checkByOnoAndSub(b); //不存在就设置 if(c==0){ bonus.setBonusSubject(BonusSubject.SF.getCode());//11 } lstaus = 1; addOrderLog(tr.getOrderNo(), OrderLogProcess.CWSF.getCode()); } } if(tr.getStatus()==2) { tr.setStatus(0); temporaryReceivablesMapper.updateByPrimaryKeySelective(tr); } //否则新增流水信息 Date date=new Date(); tbn.setBillNo(UUID.randomUUID().toString().replaceAll("-", "")); tbn.setCreater(TokenManager.getAdminId()); tbn.setType(1);//批量 tbn.setOrderNo(t.getOrderNo()); tbn.setCreateTime(date); tbn.setPayeeId("0");//默认设置0 tbn.setPayerId(t.getBuyerId()); tbn.setTransactionSubject(0); tbn.setTransactionChannel(2); tbn.setConfirmSign(0); tbn.setTransactionAmount(tr.getAmount()); tbn.setDeleteSign((new Byte("0"))); tbn.setFinancialPayTime(tr.getReceivablesTime()); tbn.setFinancialPayNo(tr.getFinancialPayNo()); tbn.setRemarks(tr.getRemarks()); tOrderMidMapper.updatefinalReceivables(t.getOrderNo(),tr.getAmount(),date); //修改订单结算金额和结算状态 //如果已经首付并且流程状态是没有派给咨询师的修改流程状态为4 TOrderNew orderNew = new TOrderNew(); orderNew.setOrderNo(tr.getOrderNo()); //这里的已收金额是以前收的所有金额加上本次收的金额 orderNew.setSettlementAmount(count); orderNew.setLiquidationStatus(lstaus); tOrderNewMapper.updateByPrimaryKeySelective(orderNew); //如果达到奖金发放要求新增奖金信息 if(null != bonus.getBonusSubject()){ bonus.setId(UUID.randomUUID().toString()); bonus.setGrantTarget(t.getSalesmanId()); //设置发放人类型 bonus.setGrantType(0); tOrderBonusMapper.insertSelective(bonus); } listBull.add(tbn); } if(!listBull.isEmpty()) { tOrderBillNewMapper.insertBatch(listBull); temporaryReceivablesMapper.deleteNormalByaid(TokenManager.getAdminId()); }else { return -1; } LoggerUtils.debug(getClass(), "=========================批量导入结束==================="); return 1; } private void addOrderLog(String orderNo, Integer code) { TOrderLog tl=new TOrderLog(); tl.setOrderNo(orderNo); tl.setProcess(code); tl.setAid(TokenManager.getAdminId()); tOrderLogMapper.insertSelective(tl); } @SuppressWarnings("unchecked") @Override public Pagination listReceivables(ReceivablesListBo rl,Integer status,String adminName, String adminDepId, String orderDepId, Integer pageSize, Integer pageNo) { Map parameter=new HashMap(); if(pageSize==null||pageSize<1)pageSize=10; if(pageNo==null||pageNo<1)pageNo=1; if(StringUtils.isNotBlank(rl.getContractNo()))parameter.put("contractNo", rl.getContractNo()); if(StringUtils.isNotBlank(rl.getOrderNo()))parameter.put("orderNo", rl.getOrderNo()); if(StringUtils.isNotBlank(rl.getNickname()))parameter.put("nickname", rl.getNickname()); if(StringUtils.isNotBlank(rl.getCorporateName()))parameter.put("corporateName", rl.getCorporateName()); if(StringUtils.isNotBlank(adminName))parameter.put("adminName", adminName); if(StringUtils.isNotBlank(adminDepId))parameter.put("adminDepId", adminDepId); if(StringUtils.isNotBlank(orderDepId))parameter.put("orderDepId", orderDepId); if(null!=status)parameter.put("status", status); if(StringUtils.isNotBlank(TokenManager.getAdminId()))parameter.put("aid", TokenManager.getAdminId()); Pagination p=(Pagination) findPage("selectReceivablesList", "selectReceivablesCount", parameter, pageNo, pageSize); List list=(List) p.getList(); for (ReceivablesListBo r : list) { //设置导入信息数据是否有问题判断 // InptStatus 0 正常 1内容不完整 2订单未能匹配 3名称不匹配 if (StringUtils.isBlank(r.getAmount())||StringUtils.isBlank(r.getOrderNo()) ||StringUtils.isBlank(r.getCorporateName())||r.getReceivablesTimes()==null) { r.setInptStatus(1); }else if(r.getTotalAmount()==null) { r.setInptStatus(2); }else if(!r.getCorporateName().equals(r.getNickname())) { r.setInptStatus(3); }else { r.setInptStatus(0); } } return p; } @Override public Map getCountlistReceivables(ReceivablesListBo rl, Integer status, String adminName, String adminDepId, String orderDepId) { Map parameter=new HashMap(); if(StringUtils.isNotBlank(rl.getContractNo()))parameter.put("contractNo", rl.getContractNo()); if(StringUtils.isNotBlank(rl.getOrderNo()))parameter.put("orderNo", rl.getOrderNo()); if(StringUtils.isNotBlank(rl.getNickname()))parameter.put("nickname", rl.getNickname()); if(StringUtils.isNotBlank(rl.getCorporateName()))parameter.put("corporateName", rl.getCorporateName()); if(StringUtils.isNotBlank(adminName))parameter.put("adminName", adminName); if(StringUtils.isNotBlank(adminDepId))parameter.put("adminDepId", adminDepId); if(StringUtils.isNotBlank(orderDepId))parameter.put("orderDepId", orderDepId); if(null!=status)parameter.put("status", status); if(StringUtils.isNotBlank(TokenManager.getAdminId()))parameter.put("aid", TokenManager.getAdminId()); //计算回收表的订单总金额与当前总收款额。 Map map=new HashMap(); map.put("totalAmount", new BigDecimal(temporaryReceivablesMapper.getCountlistReceivablesTotalAmount(parameter)).stripTrailingZeros() ); map.put("amount",new BigDecimal(temporaryReceivablesMapper.getCountlistReceivablesAmount(parameter)).stripTrailingZeros() ); return map; } @SuppressWarnings("deprecation") @Override public void batchListReceivables(MultipartFile file) { //获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); //创建一个对象,用来存储数据 List list=new ArrayList<>(); if(workbook != null){ //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(0); if(sheet == null){ return; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //处理空白行 for (int i = firstRowNum; i <= sheet.getLastRowNum();i++) { Row r = sheet.getRow(i); if(r == null){ // 如果是空行(即没有任何数据、格式),直接把它以下的数据往上移动 sheet.shiftRows(i+1, sheet.getLastRowNum(),-1); continue; } boolean flag = false; for(Cell c:r){ if(c.getCellType() != Cell.CELL_TYPE_BLANK){ flag = true; break; } } if(flag){ i++; continue; } else{//如果是空白行(即可能没有数据,但是有一定格式) if(i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉 sheet.removeRow(r); else//如果还没到最后一行,则数据往上移一行 sheet.shiftRows(i+1, sheet.getLastRowNum(),-1); } } lastRowNum=sheet.getLastRowNum(); if (lastRowNum<2) { throw new BusinessException(new Error("未找到正确的参数。")); } //循环除了第2行的所有行 for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){ //获得当前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getLastCellNum(); //循环当前行 cellNum = (firstCellNum+1) 则是除开第一例 TemporaryReceivablesBo trb=new TemporaryReceivablesBo(); for(int cellNum = (firstCellNum); cellNum < lastCellNum;cellNum++){ Cell cell = row.getCell(cellNum); try { switch (cellNum) { case 0: trb.setFinancialPayNo(getCellValue(cell)); break; case 1: trb.setOrderNo(getCellValue(cell).trim()); break; case 2: trb.setCorporateName(getCellValue(cell)); break; case 3: trb.setAmount(new BigDecimal((getCellValue(cell).equals("")?"0":getCellValue(cell)))); break; case 4: trb.setReceivablesTime(DateUtils.StringToDate(getCellValue(cell), AFTConstants.YYYYMMDD)); break; case 5: trb.setRemarks(getCellValue(cell)); break; } } catch (Exception e) { throw new BusinessException(new Error("表格第"+(rowNum+1)+"行输入内容不正确。")); } } trb.setAid(TokenManager.getAdminId()); trb.setCreateTime(new Date()); trb.setStatus(0); list.add(trb); } } temporaryReceivablesMapper.insertBatch(list); //计算重复的单 temporaryReceivablesMapper.updateRepeatOrderNo(TokenManager.getAdminId()); } public static Workbook getWorkBook(MultipartFile file) { //创建Workbook工作薄对象,表示整个excel Workbook workbook = null; try { //获取excel文件的io流 InputStream is = file.getInputStream(); //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook if(file.getOriginalFilename().matches("^.+\\.(?i)(xls)$"))workbook = new HSSFWorkbook(is); if(file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$"))workbook = new XSSFWorkbook(is); } catch (IOException e) { } return workbook; } @SuppressWarnings("deprecation") public static String getCellValue(Cell cell){ String cellValue = ""; if(cell == null){ return cellValue; } //把数字当成String来读,避免出现1读成1.0的情况 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ cell.setCellType(Cell.CELL_TYPE_STRING); } //判断数据的类型 switch (cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC: //数字 cellValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: //字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: //Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: //公式 cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: //空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: //故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } @SuppressWarnings("unchecked") @Override public void exportReceivables(HttpServletResponse response,OrderListBo order, Integer pageSize, Integer pageNo) throws Exception { OutputStream out = response.getOutputStream(); // 财务专员自己的id order.setFinanceId(TokenManager.getAdminId()); //order.setProcessStatus(3);//分配给自己 改为前端 传3未分配 4已分配 order.setOrderStatus(2);//营销管理员审核通过 if(pageSize==null||pageSize<0)pageSize=9999; if(pageNo==null||pageNo<0)pageNo=1; Pagination p=(Pagination) fundManageOrderService.financeList(order, pageNo, pageSize).get("pagination"); List list=(List)p.getList() ; if(list.isEmpty())throw new BusinessException(new Error("数据是空的。","数据是空的。")); try { //设计表头 String[] comment = {"订单编号", "合同编号","客户名称","订单部门","订单负责人","签单日期","下单日期", "签单金额(万元)", "已开票金额(万元)", "已收金额(万元)","最近收款(万元)","最近收款时间","结算状态","订单流程","是否特批"}; String fileName = "收款结算列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls"; XSSFWorkbook wb =exportTemplateInfoS(comment,"收款结算",list,0); //根据列表编写文档 //返回名称及参数 response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1")); response.setContentType("application/octet-stream;charset=utf-8"); // 返回数据流 wb.write(out); out.flush(); out.close(); } catch (Exception e) { throw new BusinessException(new Error("表格生成错误。")); }finally { out.flush(); out.close(); } } @SuppressWarnings("unchecked") @Override public void exportInvoice(HttpServletResponse response, String orderNo, Integer pageSize, Integer pageNo)throws Exception { OutputStream out = response.getOutputStream(); if(pageSize==null||pageSize<0)pageSize=9999; if(pageNo==null||pageNo<0)pageNo=1; Listlist=(List) orderInvoiceService.salesmanOrderInvoiceList( orderNo, pageNo,pageSize).getList(); // if(list.isEmpty())throw new BusinessException(new Error("数据是空的。")); try { //设计表头 String[] comment = {"编号","订单编号","开票金额(万元)","申请时间","开票状态"}; String fileName = orderNo+"开票记录列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls"; //type 0 收款 1开票 2流水 XSSFWorkbook wb =exportTemplateInfoS(comment,"开票记录",list,1); //根据列表编写文档 //返回名称及参数 response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1")); response.setContentType("application/octet-stream;charset=utf-8"); // 返回数据流 wb.write(out); out.flush(); out.close(); } catch (Exception e) { throw new BusinessException(new Error("表格生成错误。")); }finally { out.flush(); out.close(); } } @SuppressWarnings("unchecked") @Override public void exportMyBill(HttpServletResponse response, TOrderBillNew billNew, Integer pageSize, Integer pageNo)throws Exception { OutputStream out = response.getOutputStream(); if(pageSize==null||pageSize<0)pageSize=9999; if(pageNo==null||pageNo<0)pageNo=1; Listlist=(List) orderBillService.myBillList(billNew,pageNo, pageSize).getList(); try { //设计表头 String[] comment = {"订单编号","合同编号","收款时间","财务流水时间","财务流水号","金额(万元)","收款方","付款方","流水科目","平台流水号","收款类型","备注"}; String fileName = (billNew.getOrderNo()==null?"":billNew.getOrderNo())+"流水记录列表" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()) + ".xls"; //type 0 收款 1开票 2流水 XSSFWorkbook wb =exportTemplateInfoS(comment,"流水记录",list,2); //根据列表编写文档 //返回名称及参数 response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"iso-8859-1")); response.setContentType("application/octet-stream;charset=utf-8"); // 返回数据流 wb.write(out); out.flush(); out.close(); } catch (Exception e) { throw new BusinessException(new Error("表格生成错误。")); }finally { out.flush(); out.close(); } } /** * * @param comment * @param fileName * @param list * @param type 0收款 1开票 2流水 * @return */ public static XSSFWorkbook exportTemplateInfoS(String[] comment,String fileName,List list,Integer type){ //map 数据 ,comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //遍历map XSSFSheet sheet = wb.createSheet(fileName); //3。创建一个row XSSFRow row = sheet.createRow(0); //4.创建一个cell XSSFCell cell = row.createCell(0); cell.setCellValue(fileName+"列表"); XSSFCellStyle style1 = ExportExcelUtil.setCellStyle1(wb);//标题 XSSFCellStyle style2 = ExportExcelUtil.setCellStyle2(wb);//正文 cell.setCellStyle(style1); //合并列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格 //设置其他单位格式 //创建表头 XSSFRow row2 = sheet.createRow(1); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row2.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style2); } switch (type) { case 0: addSettlementReceivablesList(comment, list, sheet); break; case 1: addSettlementInvoiceList(comment, list, sheet); break; case 2: addSettlementMyBillList(comment, list, sheet); break; } return wb; } @SuppressWarnings("unchecked") private static void addSettlementMyBillList(String[] comment, List list, XSSFSheet sheet) { for (TOrderBillNew ob : (List)list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { // String[] comment = {"订单编号","合同编号","收款时间","财务流水时间","财务流水号","金额(万元)","收款方","付款方","流水科目","平台流水号","收款类型","备注"}; case "订单编号": o = ob.getOrderNo(); break; case "合同编号": o = ob.getContractNo(); break; case "收款时间": o = ob.getCreateTimes(); break; case "财务流水时间": o = ob.getFinancialPayTimes(); break; case "财务流水号": o = ob.getFinancialPayNo(); break; case "金额(万元)": o = ob.getTransactionAmount().toString(); break; case "收款方": o = ob.getPayeeName(); break; case "付款方": o = ob.getPayerName(); break; case "流水科目": o = TransactionSubject.getValueByCode(ob.getTransactionSubject()); break; case "平台流水号": o = ob.getBillNo(); break; case "收款类型": if(ob.getType()==0) { o = "手工收款"; break; }else if(ob.getType()==1) { o = "批量收款"; break; } case "备注": o = ob.getRemarks(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } } @SuppressWarnings("unchecked") private static void addSettlementInvoiceList(String[] comment, List list, XSSFSheet sheet) { for (TOrderInvoiceBo ob : (List)list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { // String[] comment = {"编号","订单编号","开票金额(万元)","申请时间","开票状态"}; case "编号": o = ob.getId().toString(); break; case "订单编号": o = ob.getOrderno(); break; case "开票金额(万元)": o = ob.getAmount().toString(); break; case "申请时间": o = ob.getCreateTime(); break; case "开票状态": // 状态 0未提交 1处理中 2通过 3拒绝 4 撤销 switch(ob.getStatus()) { case 1 :o = "处理中"; break; case 2 :o = "通过"; break; } break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } } @SuppressWarnings("unchecked") private static void addSettlementReceivablesList(String[] comment, List list, XSSFSheet sheet) { for (OrderListBo ob : (List)list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { // "订单编号", "合同编号","客户名称","订单部门","订单负责人","签单日期","下单日期", // "签单金额(万元)", "已开票金额(万元)", "已收金额(万元)","最近收款(万元)","结算状态","订单流程","是否特批"} case "订单编号": o = ob.getOrderNo().toString(); break; case "合同编号": o = ob.getContractNo(); break; case "客户名称": o = ob.getBuyerName(); break; case "订单部门": o = ob.getDepartmentName(); break; case "订单负责人": o = ob.getSellerName(); break; case "签单日期": o = ob.getSignTime(); break; case "下单日期": o = ob.getCreateTime(); break; case "签单金额(万元)":o=ob.getSignTotalAmount();break; case "已开票金额(万元)":o=ob.getInvoiceAmount();break; case "已收金额(万元)":o=ob.getActuallyTotalAmount();break; case "最近收款(万元)":o=ob.getFinalReceivables();break; case "最近收款时间":o=ob.getFinalReceivablesTime();break; case "结算状态":o=LiquidationNewState.getValueByCode(ob.getLiquidationStatus());break; case "订单流程":o=ProcessStatus.getValueByCode(ob.getProcessStatus());break; case "是否特批":o=ApprovalNewState.getValueByCode(ob.getApproval());break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } } }