2025年微信对账单转excel

大家好，我是讯享网，很高兴认识大家。

微信官方手册：一种是返回字符串，一种是返回压缩包

这里采用返回字符串再拼接导出excel

官方文档：

https://pay.weixin..com/wiki/doc/api/native.php?chapter=9_6

请求接口：

https://api.mch.weixin..com/pay/downloadbill

请求参数

<xml> <appid>wx2421b1c4370ec43b</appid> <bill_date></bill_date> <bill_type>ALL</bill_type> <mch_id></mch_id> <nonce_str>21df7dc9cd8616b56919f20d9f</nonce_str> <sign>332F17B766FCEBE9D6E40457A1</sign> </xml>

讯享网

详细：

页面需要传参数请求，最好是以表单请求方式；

传入的参数时间格式为：

这里采用两次请求：

讯享网 <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"> &times; </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>&nbsp;&nbsp;&nbsp; <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,`,`0,`1000,`034289,`,`085e9858e3ba5186aafcbaed1,`MICROPAY,`SUCCESS,`CFT,`CNY,`0.01,`0.0,`0,`0,`0,`0,`,`,`被扫支付测试,`订单额外描述,`0,`0.60% `2014-11-1016：46：14,`wx2421b1c4370ec43b,`,`0,`1000,`029794,`,`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/_/article/details/

2025年微信对账单转excel

相关推荐