微信官方手册：一种是返回字符串，一种是返回压缩包
这里采用返回字符串再拼接导出excel
官方文档：
https://pay.weixin.qq.com/wiki/doc/api/native.php?chapter=9_6
请求接口：
https://api.mch.weixin.qq.com/pay/downloadbill
请求参数
<xml>
<appid>wx2421b1c4370ec43b</appid>
<bill_date>20141110</bill_date>
<bill_type>ALL</bill_type>
<mch_id>10000100</mch_id>
<nonce_str>21df7dc9cd8616b56919f20d9f679233</nonce_str>
<sign>332F17B766FC787203EBE9D6E40457A1</sign>
</xml>详细：
页面需要传参数请求，最好是以表单请求方式；
传入的参数时间格式为：20180906
这里采用两次请求：
<div class="text-left" style="padding: 30px 0px;">
<label>请输入日期</label>
<input placeholder="查询的账单日期" id="wechatTime" name="wechatTime" class="form-control layer-date" onclick="laydate({istime: false, format: 'YYYY-MM-DD'})">
</div>
<div>
<button class="btn btn-primary btn-sm" onclick="notarize('weixin')">确认</button>
</div>
<div class="modal fade" id="myModal" method="get"tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<form id="downloadBillData" method="get">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">
×
</button>
<h4 class="modal-title"> 下载文件 </h4>
</div>
<div class="modal-body">
<div class="row">
<h4 class="modal-title" style="padding: 20px;">点击下载按钮开始下载文件</h4>
<input type="hidden" id="downloadType" name="downloadType"/>
<input type="hidden" id="downloadData" name="downloadData"/>
</div>
<div class="row">
<button type="button" class="btn btn-default pull-right" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary pull-right" onclick="downloadFile()">下载</button>
</div>
</div>
</div>
</form>
</div>js:
function notarize(e){
var billType = "ALL";
var biilDate=$("#wechatTime").val();
var biilDate = biilDate.replace(/-/g,"");
if(biilDate ==""){
return;
}
$.post("/a/bill/getBill",{billType:billType,biilDate:biilDate}).success(function(data) {
if (data.success) {
layer.closeAll('loading');
$("#myModal").modal('show');
$("#downloadType").val(data.downloadType);
$("#downloadData").val(data.data);
} else {
layer.closeAll('loading');
layer.alert(data.msg, {
icon : 2
});
}
}).fail(function(data) {
layer.alert("网络错误！", {
icon : 2
});
layer.closeAll('loading');
});
}
function downloadFile(){
var downloadData = $("#downloadData").val();
$("#downloadBillData").attr("action", "downloadBillData");
$("#downloadBillData").submit();
}
java接口
@RequestMapping("getBill")
@ResponseBody
public Object getBill(String biilDate, String billType, HttpServletRequest request, HttpServletResponse response)
throws Exception {
Map<String, Object> map = new HashMap<String, Object>();
ResultDTO resultDTO = tradeInfoFacade.getBillURL(biilDate, billType);
map.put("downloadType", tradeWay.getValue());
map.put("data", resultDTO.getData());
map.put("success", true);
return map;
}
public ResultDTO getBillURL(String biilDate, String billType) {
String appid = WECHAT_APPID;// 替换为自己的ID
String mch_id = WECHAT_MCH_ID;// 商户号-->替换为自己的商户号
String nonce_str = UUIDHexGenerator.generate();// 随机字符串
String tarType = "GZIP";
PaymentPo paymentPo = new PaymentPo();
paymentPo.setAppid(appid);
paymentPo.setMch_id(mch_id);
paymentPo.setNonce_str(nonce_str);
paymentPo.setBill_date(biilDate);
paymentPo.setBill_type(billType);
// paymentPo.setTar_type(tarType);
Map<String, String> sParaTemp = new HashMap<String, String>();
sParaTemp.put("appid", appid);
sParaTemp.put("mch_id", mch_id);
sParaTemp.put("nonce_str", nonce_str);
sParaTemp.put("bill_date", biilDate);
sParaTemp.put("bill_type", billType);
// sParaTemp.put("tar_type", tarType);
// 除去数组中的空值和签名参数
Map<String, String> sPara = PayUtil.paraFilter(sParaTemp);
String prestr = PayUtil.createLinkString(sPara); // 把数组所有元素，按照“参数=参数值”的模式用“&”字符拼接成字符串
// MD5运算生成签名
String mysign = PayUtil.sign(prestr, "&key=" + WECHAT_MERC_KEY, "utf-8").toUpperCase();
sParaTemp.put("sign", mysign);
paymentPo.setSign(mysign);
String respXml = MessageUtil.messageToXML(paymentPo);
// 打印respXml发现，得到的xml中有“__”不对，应该替换成“_”
respXml = respXml.replace("__", "_");
String result = PayUtil.httpRequest(loadBillUrl, "POST", respXml);
Map<String, String> retMap = readStringXmlOut(result);
ResultDTO payResult = new ResultDTO();
payResult.setData(new Date());
if (("SUCCESS").equals(retMap.get("return_code"))) {
payResult.setResultCode(40000);
payResult.setMessage(retMap.get("return_msg"));
return payResult;
}
payResult.setData(result);
payResult.setResultCode(10000);
payResult.setMessage("请求成功！");
return payResult;
}返回的字符串：
交易时间,公众账号ID,商户号,子商户号,设备号,微信订单号,商户订单号,用户标识,交易类型,交易状态,付款银行,货币种类,总金额,代金券或立减优惠金额,微信退款单号,商户退款单号,退款金额,代金券或立减优惠退款金额,退款类型,退款状态,商品名称,商户数据包,手续费,费率
`2014-11-1016：33：45,`wx2421b1c4370ec43b,`10000100,`0,`1000,`1001690740201411100005734289,`1415640626,`085e9858e3ba5186aafcbaed1,`MICROPAY,`SUCCESS,`CFT,`CNY,`0.01,`0.0,`0,`0,`0,`0,`,`,`被扫支付测试,`订单额外描述,`0,`0.60%
`2014-11-1016：46：14,`wx2421b1c4370ec43b,`10000100,`0,`1000,`1002780740201411100005729794,`1415635270,`085e9858e90ca40c0b5aee463,`MICROPAY,`SUCCESS,`CFT,`CNY,`0.01,`0.0,`0,`0,`0,`0,`,`,`被扫支付测试,`订单额外描述,`0,`0.60%
总交易单数,总交易额,总退款金额,总代金券或立减优惠退款金额,手续费总金额
`2,`0.02,`0.0,`0.0,`0将字符串第二次请求然后转换下载：页面调用看上面的js
接口第二次请求下载：
@RequestMapping(value = "downloadBillData")
public void downloadBillData(String downloadData, HttpServletRequest request, HttpServletResponse response) throws IOException {
String fileName = "微信对账_" + DateUtils.getDate("yyyy-MM-dd") + ".xlsx";
response.setContentType("application/octet-stream; charset=utf-8");
// 设置下载文件名
response.addHeader("Content-Disposition", "attachment; filename=\"" + Encodes.urlEncode(fileName));
this.exportWechatExcel(downloadData, response.getOutputStream());
}
private Map<String, Object> spirtString(String result) {
Map<String, Object> map = new HashMap<String, Object>();
String headerInfo = result.substring(0, result.indexOf("`"));
String tradeMsg = result.substring(result.indexOf("`"));
String tradeInfo = tradeMsg.substring(0, tradeMsg.indexOf("总")).replace("`", "");
String totalMsg = tradeMsg.substring(tradeMsg.indexOf("总"));
String totalHeader = totalMsg.substring(0, totalMsg.indexOf("`"));
String totalInfo = totalMsg.substring(totalMsg.indexOf("`")).replace("`", "");
map.put("headerInfo", headerInfo);
map.put("tradeInfo", tradeInfo);
map.put("totalHeader", totalHeader);
map.put("totalInfo", totalInfo);
return map;
}
public void exportWechatExcel(String data, OutputStream out) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
// 遍历物料类型 一个map对象为一个sheet页
Sheet sheet = null;
// 创建sheet页
sheet = workbook.createSheet("微信账单统计");
// 字体
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 11);// 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
// 头背景色
XSSFCellStyle styleHeader = workbook.createCellStyle();
styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
styleHeader.setFont(font);
// 创建标题
Row row = sheet.createRow(0);
row.setHeightInPoints(30);
// 设置excel头
Cell cell = row.createCell(0);
cell.setCellValue(
"微信账单统计" + " 导出日期： " + DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(styleHeader);
if (StringUtils.isEmpty(data)) {
workbook.write(out);
}
Map<String, Object> map = spirtString(data);
// 创建标题
row = sheet.createRow(1);
row.setHeightInPoints(20);
String[] headerInfo = map.get("headerInfo").toString().split(",");
// 设置excel头
if (null != headerInfo && headerInfo.length > 0) {
for (int i = 0; i < headerInfo.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerInfo[i]);
cell.setCellStyle(styleHeader);
}
}
int len = 0;
// 内容背景色
XSSFCellStyle styleContent = workbook.createCellStyle();
styleContent.setFont(font);
XSSFDataFormat format = workbook.createDataFormat();
styleContent.setDataFormat(format.getFormat("@"));
int titleCount = 1;
int rowNum = 2;
String[] tradeArray = map.get("tradeInfo").toString().split("%");
if (null != tradeArray && tradeArray.length > 0) {
for (int j = 0; j < tradeArray.length; j++) {
row = sheet.createRow(rowNum);
String[] infoStringList = tradeArray[j].split(",");
for (int k = 0; k < infoStringList.length; k++) {
String value = infoStringList[k];
if (null == infoStringList[k] || infoStringList[k].equals("")) {
value = "-";
}
cell = row.createCell(k);
cell.setCellValue(value);
cell.setCellStyle(styleContent);
}
rowNum = ++rowNum;
titleCount++;
}
}
rowNum = ++rowNum;
String[] totalHeader = map.get("totalHeader").toString().split(",");
row = sheet.createRow(rowNum);
for (int g = 0; g < totalHeader.length; g++) {
cell = row.createCell(g);
cell.setCellValue(totalHeader[g]);
cell.setCellStyle(styleContent);
}
rowNum = ++rowNum;
row = sheet.createRow(rowNum);
String[] totalInfo = map.get("totalInfo").toString().split(",");
for (int g = 0; g < totalInfo.length; g++) {
cell = row.createCell(g);
cell.setCellValue(totalInfo[g]);
cell.setCellStyle(styleContent);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}结果：
用到工具类：
public class PaymentPo {
private String appid;// 小程序ID
private String mch_appid;// ID
private String mch_id;// 商户号
private String device_info;// 设备号
private String nonce_str;// 随机字符串
private String sign;// 签名
private String body;// 商品描述
private String detail;// 商品详情
private String attach;// 附加数据
private String out_trade_no;// 商户订单号
private String partner_trade_no;// 商户订单号2
private String fee_type;// 货币类型
private String spbill_create_ip;// 终端IP
private String time_start;// 交易起始时间
private String time_expire;// 交易结束时间
private String goods_tag;// 商品标记
private String total_fee;// 总金额
private String notify_url;// 通知地址
private String trade_type;// 交易类型
private String limit_pay;// 指定支付方式
private String openid;// 用户标识
private String check_name;//校验用户姓名选项
private String auth_code;//授权码
private String transaction_id;//微信订单号
private String refund_fee;//退款費用
private String out_refund_no;//商户退款单号
}MessageUtil.java
public class MessageUtil {
public static HashMap<String, String> parseXML(HttpServletRequest request) throws Exception, IOException {
HashMap<String, String> map = new HashMap<String, String>();
// 通过IO获得Document
SAXReader reader = new SAXReader();
Document doc = reader.read(request.getInputStream());
// 得到xml的根节点
Element root = doc.getRootElement();
recursiveParseXML(root, map);
return map;
}
private static void recursiveParseXML(Element root, HashMap<String, String> map) {
// 得到根节点的子节点列表
List<Element> elementList = root.elements();
// 判断有没有子元素列表
if (elementList.size() == 0) {
map.put(root.getName(), root.getTextTrim());
} else {
// 遍历
for (Element e : elementList) {
recursiveParseXML(e, map);
}
}
}
private static XStream xstream = new XStream(new XppDriver() {
@Override
public HierarchicalStreamWriter createWriter(Writer out) {
return new PrettyPrintWriter(out) {
// 对所有xml节点都增加CDATA标记
boolean cdata = true;
@Override
public void startNode(String name, Class clazz) {
super.startNode(name, clazz);
}
@Override
protected void writeText(QuickWriter writer, String text) {
if (cdata) {
writer.write("<![CDATA[");
writer.write(text);
writer.write("]]>");
} else {
writer.write(text);
}
}
};
}
});
public static String messageToXML(PaymentPo paymentPo) {
xstream.alias("xml", PaymentPo.class);
return xstream.toXML(paymentPo);
}
}PayUtil.java
public class PayUtil {
private static Logger logger = LoggerFactory.getLogger(PayUtil.class);
// HTTP请求器
@Autowired
private static CloseableHttpClient httpClient;
// 请求器的配置
@Autowired
private static RequestConfig requestConfig;
// 连接超时时间，默认10秒
private static int socketTimeout = 10000;
// 传输超时时间，默认30秒
private static int connectTimeout = 30000;
/**
*
* @param text
* @param key
* @param input_charset
* @return
*/
public static String sign(String text, String key, String input_charset) {
text = text + key;
return DigestUtils.md5Hex(getContentBytes(text, input_charset));
}
/**
*
* @param text
* @param sign
* @param key
* @param input_charset
* @return
*/
public static boolean verify(String text, String sign, String key, String input_charset) {
text = text + key;
String mysign = DigestUtils.md5Hex(getContentBytes(text, input_charset));
if (mysign.equals(sign)) {
return true;
} else {
return false;
}
}
/**
*
* @param content
* @param charset
* @return
*/
public static byte[] getContentBytes(String content, String charset) {
if (charset == null || "".equals(charset)) {
return content.getBytes();
}
try {
return content.getBytes(charset);
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("MD5签名过程中出现错误,指定的编码集不对,您目前指定的编码集是:" + charset);
}
}
/**
*
* @param codeLength
* @return
*/
public static String createCode(int codeLength) {
String code = "";
for (int i = 0; i < codeLength; i++) {
code += (int) (Math.random() * 9);
}
return code;
}
/**
*
* @param sArray
* @return
*/
public static Map<String, String> paraFilter(Map<String, String> sArray) {
Map<String, String> result = new HashMap<String, String>();
if (sArray == null || sArray.size() <= 0) {
return result;
}
for (String key : sArray.keySet()) {
String value = sArray.get(key);
if (value == null || value.equals("") || key.equalsIgnoreCase("sign") || key.equalsIgnoreCase("sign_type")) {
continue;
}
result.put(key, value);
}
return result;
}
/**
*
*
* @param params
* 需要排序并参与字符拼接的参数组
* @return 拼接后字符串
*/
public static String createLinkString(Map<String, String> params) {
List<String> keys = new ArrayList<String>(params.keySet());
Collections.sort(keys);
String prestr = "";
for (int i = 0; i < keys.size(); i++) {
String key = keys.get(i);
String value = params.get(key);
if (i == keys.size() - 1) {// 拼接时，不包括最后一个&字符
prestr = prestr + key + "=" + value;
} else {
prestr = prestr + key + "=" + value + "&";
}
}
return prestr;
}
/**
*
* @param requestUrl
* 请求地址
* @param requestMethod
* 请求方法
* @param outputStr
* 参数
*/
public static String httpRequest(String requestUrl, String requestMethod, String outputStr) {
// 创建SSLContext
StringBuffer buffer = null;
try {
URL url = new URL(requestUrl);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod(requestMethod);
conn.setDoOutput(true);
conn.setDoInput(true);
conn.connect();
// 往服务器端写内容
if (null != outputStr) {
OutputStream os = conn.getOutputStream();
os.write(outputStr.getBytes("utf-8"));
os.close();
}
// 读取服务器端返回的内容
InputStream is = conn.getInputStream();
InputStreamReader isr = new InputStreamReader(is, "utf-8");
BufferedReader br = new BufferedReader(isr);
buffer = new StringBuffer();
String line = null;
while ((line = br.readLine()) != null) {
buffer.append(line);
}
} catch (Exception e) {
e.printStackTrace();
}
if (null != buffer) {
return buffer.toString();
} else {
return "";
}
}
public static String urlEncodeUTF8(String source) {
String result = source;
try {
result = java.net.URLEncoder.encode(source, "UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
/**
* 通过Https往API post xml数据
*
* @param url
* @param xmlObj
* @param path
* @param mchId
* @return
* @throws Exception
*/
public static String httpsRequest(String url, String path, String xmlObj, String mchId) throws Exception {
// 加载证书
initCert(path, mchId);
String result = null;
HttpPost httpPost = new HttpPost(url);
// 得指明使用UTF-8编码，否则到API服务器XML的中文不能被成功识别
StringEntity postEntity = new StringEntity(xmlObj, "UTF-8");
httpPost.addHeader("Content-Type", "text/xml");
httpPost.setEntity(postEntity);
// 设置请求器的配置
httpPost.setConfig(requestConfig);
try {
HttpResponse response = httpClient.execute(httpPost);
HttpEntity entity = response.getEntity();
result = EntityUtils.toString(entity, "UTF-8");
} catch (ConnectionPoolTimeoutException e) {
logger.debug("http get throw ConnectionPoolTimeoutException(wait time out)");
} catch (ConnectTimeoutException e) {
logger.debug("http get throw ConnectTimeoutException");
} catch (SocketTimeoutException e) {
logger.debug("http get throw SocketTimeoutException");
} catch (Exception e) {
logger.debug("http get throw Exception");
} finally {
httpPost.abort();
}
return result;
}
/**
* 加载证书
*
* @param path
* @param mchId
* @throws IOException
*/
private static void initCert(String path, String mchId) throws Exception {
KeyStore keyStore = KeyStore.getInstance("PKCS12");
InputStream instream = PayUtil.class.getResourceAsStream(path);
try {
keyStore.load(instream, mchId.toCharArray()); // 加载证书密码，默认为商户ID
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
} finally {
instream.close();
}
// Trust own CA and all self-signed certs
SSLContext sslcontext = SSLContexts.custom().loadKeyMaterial(keyStore, mchId.toCharArray()) // 加载证书密码，默认为商户ID
.build();
// Allow TLSv1 protocol only
SSLConnectionSocketFactory sslsf = new SSLConnectionSocketFactory(sslcontext, new String[] { "TLSv1" }, null,
SSLConnectionSocketFactory.BROWSER_COMPATIBLE_HOSTNAME_VERIFIER);
httpClient = HttpClients.custom().setSSLSocketFactory(sslsf).build();
// 根据默认超时限制初始化requestConfig
requestConfig = RequestConfig.custom().setSocketTimeout(socketTimeout).setConnectTimeout(connectTimeout).build();
}
}XMLUtil.java
public class XMLUtil {
public static Map<String, String> xmlAnalysis(HttpServletRequest request) throws DocumentException {
Map<String, String> reMap = new HashMap<String, String>();
BufferedReader reader = null;
Document doc = null;
StringBuilder sb = new StringBuilder();
try {
ServletInputStream in = request.getInputStream();
reader = new BufferedReader(new InputStreamReader(request.getInputStream(), "utf-8"));
String line = null;
while ((line = reader.readLine()) != null) {
sb.append(line);
}
doc = DocumentHelper.parseText(sb.toString());
Element rootElt = doc.getRootElement();
Iterator iter = rootElt.elementIterator("out_trade_no"); // 获取根节点下的子节点
Iterator timeIter = rootElt.elementIterator("time_end");// 时间
Iterator transaction = rootElt.elementIterator("transaction_id");// 交易流水
Iterator resultCode = rootElt.elementIterator("result_code");// 交易流水
// 遍历节点--商户订单
while (iter.hasNext()) {
Element recordEle = (Element) iter.next();
String name = recordEle.getName();
String values = recordEle.getStringValue();
reMap.put(name, values);
}
while (timeIter.hasNext()) {
Element recordEle = (Element) timeIter.next();
String name = recordEle.getName();
String values = recordEle.getStringValue();
reMap.put(name, values);
}
while (transaction.hasNext()) {
Element recordEle = (Element) transaction.next();
String name = recordEle.getName();
String values = recordEle.getStringValue();
reMap.put(name, values);
}
while (resultCode.hasNext()) {
Element recordEle = (Element) resultCode.next();
String name = recordEle.getName();
String values = recordEle.getStringValue();
reMap.put(name, values);
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return reMap;
}
}UUIDHexGenerator.java
public class UUIDHexGenerator {
private static String sep = "";
private static final int IP;
private static short counter = (short) 0;
private static final int JVM = (int) (System.currentTimeMillis() >>> 8);
private static UUIDHexGenerator uuidgen = new UUIDHexGenerator();
static {
int ipadd;
try {
ipadd = toInt(InetAddress.getLocalHost().getAddress());
} catch (Exception e) {
ipadd = 0;
}
IP = ipadd;
}
public static UUIDHexGenerator getInstance() {
return uuidgen;
}
public static int toInt(byte[] bytes) {
int result = 0;
for (int i = 0; i < 4; i++) {
result = (result << 8) - Byte.MIN_VALUE + bytes[i];
}
return result;
}
protected static String format(int intval) {
String formatted = Integer.toHexString(intval);
StringBuffer buf = new StringBuffer("00000000");
buf.replace(8 - formatted.length(), 8, formatted);
return buf.toString();
}
protected static String format(short shortval) {
String formatted = Integer.toHexString(shortval);
StringBuffer buf = new StringBuffer("0000");
buf.replace(4 - formatted.length(), 4, formatted);
return buf.toString();
}
protected static int getJVM() {
return JVM;
}
protected synchronized static short getCount() {
if (counter < 0) {
counter = 0;
}
return counter++;
}
protected static int getIP() {
return IP;
}
protected static short getHiTime() {
return (short) (System.currentTimeMillis() >>> 32);
}
protected static int getLoTime() {
return (int) System.currentTimeMillis();
}
public static String generate() {
return new StringBuffer(36).append(format(getIP())).append(sep).append(format(getJVM())).append(sep).append(format(getHiTime()))
.append(sep).append(format(getLoTime())).append(sep).append(format(getCount())).toString();
}
/**
* @param args
*/
public static void main(String[] args) {
String id = "";
UUIDHexGenerator uuid = UUIDHexGenerator.getInstance();
/*
* for (int i = 0; i < 100; i++) { id = uuid.generate(); }
*/
id = uuid.generate();
System.out.println(id);
}
}工具类可以参考：https://blog.csdn.net/qq_30641447/article/details/73222648
扫一扫
5601
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
