Example #1
0
  private String test() {

    // 读取excel文件
    String realPath = "C:/Users/wchao/Desktop/上品214快捷日获奖名单.xls";
    Workbook book = null;

    WritableWorkbook book2 = null;
    WritableSheet sheet2 = null;
    Connection conn = null;
    conn =
        JDBCUtil.getConnection(
            JDBCUtil.getUrl("192.168.1.21", "1521", "danpin1", 0), "dev_user", "dev_user");
    try {
      try {
        book2 = Workbook.createWorkbook(new File("C:/Users/wchao/Desktop/上品214快捷日获奖名单111.xls"));
        sheet2 = book2.createSheet("第1页", 0);

      } catch (Exception e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }

      book = Workbook.getWorkbook(new File(realPath));

      Sheet sheet = book.getSheet(0);
      int count = sheet.getRows();
      System.out.println("--------------------------------------");
      for (int i = 1; i < count; i++) { // 最后一行是合计				
        Cell[] cells = sheet.getRow(i);
        String tradeNo = cells[2].getContents().trim();

        StringBuffer result = new StringBuffer();
        URL U = null;
        BufferedReader in = null;
        String sign = null;
        Map<String, String> sParaTemp = new HashMap();
        sParaTemp.put("out_trade_no", null);
        sParaTemp.put("trade_no", tradeNo);
        sParaTemp.put("service", "single_trade_query");
        sParaTemp.put("partner", "2088002692486430");
        sParaTemp.put("_input_charset", "utf-8");
        Map<String, String> sPara = AlipayCore.paraFilter(sParaTemp);
        // 生成签名结果
        sign = AlipayCore.buildMysign(sPara); // 752cc62e72503d01c4cc93b654906918

        try {
          String url =
              "https://www.alipay.com/cooperate/gateway.do?_input_charset=utf-8"
                  + "&sign="
                  + sign
                  + "&_input_charset=utf-8"
                  + "&sign_type=MD5"
                  + "&service=single_trade_query"
                  + "&partner=2088002692486430"
                  + "&trade_no="
                  + tradeNo;
          U = new URL(url);
          URLConnection connection = U.openConnection();
          connection.connect();
          in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
          String line;
          while ((line = in.readLine()) != null) {
            result.append(line);
            // System.out.println("-------------------\n" + new String(line.getBytes("gbk"),
            // "utf-8"));
          }

          StringReader sr =
              new StringReader(new String(result.toString().getBytes("gbk"), "utf-8"));
          BufferedReader br = new BufferedReader(sr);
          try {
            JAXBContext jaxbContext = JAXBContext.newInstance("net.shopin.alipay.entity");
            Unmarshaller unMarshaller = jaxbContext.createUnmarshaller();
            SchemaFactory schemaFactory =
                SchemaFactory.newInstance("http://www.w3.org/2001/XMLSchema");
            Schema schema =
                schemaFactory.newSchema(
                    new File(
                        "E:/上品/支付宝批量退货/java/refund_fastpay_by_platform_nopwd_jsp_utf8/src/net/shopin/alipay/entity/singleTradeQuery.xsd"));
            unMarshaller.setSchema(schema);
            Alipay alipay = (Alipay) unMarshaller.unmarshal(U);
            String outTradeNo = alipay.getResponse().getTrade().getOutTradeNo();
            // 根据单品订单号查询收货人姓名	详细邮寄地址	联系电话	省份	城市	邮编
            System.out.println(
                "SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
                    + "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
                    + outTradeNo
                    + "')");

            // 循环结果集
            Statement stmt = conn.createStatement();
            ResultSet rs =
                stmt.executeQuery(
                    "SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
                        + "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
                        + outTradeNo
                        + "')");
            while (rs.next()) {
              Label cell0 = new Label(0, i, tradeNo);
              Label cell1 = new Label(1, i, outTradeNo);
              Label cell2 = new Label(2, i, rs.getString("RECEPT_NAME"));
              Label cell3 = new Label(3, i, rs.getString("RECEPT_ADDRESS"));
              Label cell4 = new Label(4, i, rs.getString("RECEPT_PHONE"));
              Label cell5 = new Label(5, i, rs.getString("INCEPT_PROVINCE"));
              Label cell6 = new Label(6, i, rs.getString("INCEPT_CITY"));
              Label cell7 = new Label(7, i, rs.getString("INCEPT_POSTCODE"));

              sheet2.addCell(cell0);
              sheet2.addCell(cell1);
              sheet2.addCell(cell2);
              sheet2.addCell(cell3);
              sheet2.addCell(cell4);
              sheet2.addCell(cell5);
              sheet2.addCell(cell6);
              sheet2.addCell(cell7);
            }
            JDBCUtil.close(rs, stmt, null);

          } catch (Exception e) {
            e.printStackTrace();
          }

        } catch (Exception e) {
          e.printStackTrace();
        }
      }
      try {
        book2.write();
        book2.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    } catch (BiffException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IndexOutOfBoundsException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      try {
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    return null;
  }
Example #2
0
  private String getTradeNoByOutTradeNo(String outTradeNo, BigDecimal refundMoney) {
    StringBuffer result = new StringBuffer();
    URL U = null;
    BufferedReader in = null;
    String tradeNo = null;
    String sign = null;
    Map<String, String> sParaTemp = new HashMap();
    sParaTemp.put("out_trade_no", outTradeNo);
    sParaTemp.put("trade_no", null);
    sParaTemp.put("service", "single_trade_query");
    sParaTemp.put("partner", AlipayConfig.partner);
    sParaTemp.put("_input_charset", AlipayConfig.input_charset);
    Map<String, String> sPara = AlipayCore.paraFilter(sParaTemp);
    // 生成签名结果
    sign = AlipayCore.buildMysign(sPara);

    try {
      //			String url = "https://www.alipay.com/cooperate/gateway.do?" +
      String url =
          AlipayService.ALIPAY_GATEWAY_NEW
              + "_input_charset=utf-8"
              + "&sign="
              + sign
              + "&_input_charset=utf-8"
              + "&sign_type=MD5"
              + "&service=single_trade_query"
              + "&partner="
              + AlipayConfig.partner
              + "&out_trade_no="
              + outTradeNo;
      U = new URL(url);
      URLConnection connection = U.openConnection();
      connection.connect();
      in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
      String line;
      while ((line = in.readLine()) != null) {
        result.append(line);
        // System.out.println("-------------------\n" + new String(line.getBytes("gbk"), "utf-8"));
      }

      StringReader sr = new StringReader(new String(result.toString().getBytes("gbk"), "utf-8"));
      BufferedReader br = new BufferedReader(sr);
      try {
        JAXBContext jaxbContext = JAXBContext.newInstance("net.shopin.alipay.entity");
        Unmarshaller unMarshaller = jaxbContext.createUnmarshaller();
        SchemaFactory schemaFactory = SchemaFactory.newInstance("http://www.w3.org/2001/XMLSchema");
        Schema schema =
            schemaFactory.newSchema(
                new File(
                    PropertiesUtil.getProperties(
                        "alipay.schema.refundfastpay.path",
                        "E:/上品/支付宝批量退货/java/refund_fastpay_by_platform_nopwd_jsp_utf8/src/net/shopin/alipay/entity/singleTradeQuery.xsd")));
        unMarshaller.setSchema(schema);
        Alipay alipay = (Alipay) unMarshaller.unmarshal(U);

        if (alipay.getIsSuccess().equals("T")) {
          // tradeNo = alipay.getResponse().get(0).getTrade().get(0).getTradeNo();
          tradeNo = alipay.getResponse().getTrade().getTradeNo();
          // 校验是否超过三个月

          // 校验Money
          BigDecimal totalFee =
              new BigDecimal(alipay.getResponse().getTrade().getTotalFee()); // 总金额
          BigDecimal toBuyerFee =
              new BigDecimal(
                  StringUtils.isNotEmpty(alipay.getResponse().getTrade().getToBuyerFee())
                      ? alipay.getResponse().getTrade().getToBuyerFee()
                      : "0"); // 退款金额
          BigDecimal leftMoney = totalFee.subtract(toBuyerFee);
          if (leftMoney.compareTo(refundMoney) == -1) { // 剩下的钱小于退款金额
            tradeNo =
                PropertiesUtil.getProperties(
                        "alipay.interface.returncode.outoftotalfee", "OUTOFTOTALFEE")
                    + tradeNo;
          }
        }
        return tradeNo;

      } catch (JAXBException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (SAXException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (RuntimeException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } finally {
        // br.close();
      }
    } catch (MalformedURLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      try {
        in.close();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    return null;
  }