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;
}
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;
}