【微信公众号开发】【13】批量导出公众号所有用户信息到Excel

前言：

1，一次拉取调用最多拉取10000个关注者的OpenID，当公众号关注者数量超过10000时，可通过填写next_openid的值，从而多次拉取列表的方式来满足需求

2，获取OpenID列表后，要批量获取用户微信信息，最多支持一次拉取100条

3，处理Excel的jar包用的是jxl

4，导出过程中不要输出内容到控制台（System.out.println，我开始是输出了分页页码）。会导致运行时间延长很多；而且控制台会遗漏打印，让人以为有部分数据没有获取到，但其实导出到Excel表的数据是没有问题的

正文：

1，获取所有的OpenID

//获取用户列表
public static String USER_LIST_URL = "https://api.weixin.qq.com/cgi-bin/user/get?access_token=%s&next_openid=%s";

//获取所有的openId
public static List<String> getAllOpenId(String appId)
{
    List<String> openIds = getOpenIds("", appId);
    logger.info(String.format("公众号人数：%s", openIds.size()));
    return openIds;
}
  
private static List<String> getOpenIds(String next_openid, String appId)
{
    List<String> openIdList=new ArrayList<String>();
      
    String accessToken = WeixinUtil.getAccessToken(appId);
    String requestUrl = String.format(WxConfig.USER_LIST_URL, accessToken, next_openid); //拼接请求地址          
    JSONObject jsonObject = HttpRequest.httpRequest(requestUrl, "GET", null); //获取用户列表
    
    if (null != jsonObject) {
        try {
            next_openid = jsonObject.getString("next_openid");
            int count = jsonObject.getInt("count");             
            JSONObject openIdObject = jsonObject.getJSONObject("data");
            
            if (count > 0) {
                JSONArray openids = openIdObject.getJSONArray("openid");
                List<String> temp_openIdList = JSONArray.toList(openids, new String(), new JsonConfig());    
                openIdList.addAll(temp_openIdList);
            }
            
            if (StringUtils.isNotEmpty(next_openid)) {
                List<String> list = getOpenIds(next_openid, appId);
                openIdList.addAll(list);
            }            
        } catch (Exception e) {
            logger.info(String.format("获取用户列表失败 errcode:%s;errmsg:%s", jsonObject.getInt("errcode"), jsonObject.getString("errmsg")));
        }
    }
    return openIdList;
}

2，根据openId列表获取用户信息

//批量获取用户基本信息
public static String USER_LIST_INFO_URL = "https://api.weixin.qq.com/cgi-bin/user/info/batchget?access_token=%s";

public static List<WxUserAll> getUserInfo(List<String> userOpenids, String appId)
{
    List<WxUserAll> user_info_list = new ArrayList<WxUserAll>();
            
    String accessToken = WeixinUtil.getAccessToken(appId);
    String requestUrl = String.format(WxConfig.USER_LIST_INFO_URL, accessToken); //拼接请求地址
    
    int total = userOpenids.size();
    int temp = 100;  //一次获取100
    int page = 0;  //当前页面
    int count = 0;  //总共获取多少次
    int index = 0;
    
    if(total != 0){
        if(total > temp){
            count = total/100 + ((total % 100 > 0) ? 1 : 0);
        }else{
            count = 1;
        }
        
        while (page < count) {
            System.out.println("page="+page);
            
            List<Map> userList = new ArrayList<Map>();
            index = (temp*(page+1)) > total ? total : (temp*(page+1));

            for (int i = page*temp; i <index; i++)
            {
                String openid = userOpenids.get(i);
                Map tUserMap = new HashMap<String, String>();
                tUserMap.put("openid", openid);
                tUserMap.put("lang", "zh_CN");
                userList.add(tUserMap);
            }
            Map requestMap = new HashMap<String, List>();
            requestMap.put("user_list", userList);
            String tUserJSON = JSONObject.fromObject(requestMap).toString();
            
            if(StringUtils.isNotEmpty(tUserJSON)){
                JSONObject jsonObject = HttpRequest.httpRequest(requestUrl, "POST", tUserJSON); //获取用户列表
                if(jsonObject != null){
                    JSONArray user_list = jsonObject.getJSONArray("user_info_list");
                    List<WxUserAll> temp_user_info_list = JSONArray.toList(user_list, new WxUserAll(), new JsonConfig());    
                    user_info_list.addAll(temp_user_info_list);
                }
                page++;    
            }else{
                break;
            }                    
        }
    }    

    return user_info_list;
}

3，jxl，jar包

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

4，导出数据到Excel，字段自己选择要哪些

import java.io.File;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.bf.base.utils.CalendarUtil;
import com.bf.wxChange.params.WxUserAll;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class SaveToExcel {
    public static File saveToExcel(List<WxUserAll> userInfos){
        File file=null;
        try {
          WritableWorkbook wwb = null;

          String fileName = "wxList.xls"; //创建可写入的Excel工作簿
          file = new File(fileName); //以fileName为文件名来创建一个Workbook          
          wwb = Workbook.createWorkbook(file);

          // 创建工作表
          WritableSheet ws = wwb.createSheet("wxList", 0);
          ws.setColumnView(0,35);
          ws.setColumnView(1,20);
          ws.setColumnView(2,20);
          ws.setColumnView(3,35);
          ws.setColumnView(4,15);
          ws.setColumnView(5,35);
          
          ws.mergeCells(0,0,12,0); //合并第一列第一行到第七列第一行的所有单元格 
          WritableFont font1= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD); 
          WritableCellFormat format1=new WritableCellFormat(font1); 
          format1.setAlignment(jxl.format.Alignment.CENTRE);
          Label top= new Label(0, 0, "all user info",format1);
          ws.addCell(top);
          
          //要插入到的Excel表格的行号，默认从0开始
          Label label0 = new Label(0, 1, "wxId");
          Label label1 = new Label(1, 1, "nickname");
          Label label2 = new Label(2, 1, "createTime");
          Label label3 = new Label(3, 1, "avatar");
          Label label4 = new Label(4, 1, "subscribe");
          Label label5 = new Label(5,1, "wxUnionId");
          ws.addCell(label0);
          ws.addCell(label1);
          ws.addCell(label2);
          ws.addCell(label3);
          ws.addCell(label4);
          ws.addCell(label5);

          for (int i = 0; i < userInfos.size(); i++) {
              Label user0 = new Label(0, i+2, userInfos.get(i).getOpenid());
              Label user1 = new Label(1, i+2, userInfos.get(i).getNickname());
              String subscribeTime = userInfos.get(i).getSubscribe_time();
              if (StringUtils.isNotEmpty(subscribeTime)) {
                  subscribeTime = CalendarUtil.DtoS(new Date(Long.parseLong(subscribeTime) * 1000L));
              }
              Label user2 = new Label(2, i+2, subscribeTime);
              Label user3 = new Label(3, i+2, userInfos.get(i).getHeadimgurl());
              Label user4 = new Label(4, i+2, userInfos.get(i).getSubscribe() == 0 ? "0" : "1");
              Label user5 = new Label(5, i+2, userInfos.get(i).getUnionid());

              ws.addCell(user0);
              ws.addCell(user1);
              ws.addCell(user2);
              ws.addCell(user3);
              ws.addCell(user4);
              ws.addCell(user5);             
          }

          wwb.write(); //写进文档          
          wwb.close(); //关闭Excel工作簿对象
        } catch (Exception e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        return file;
    }
}

5，测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class SaveToExcelTest {
    
    private static String APP_ID = Config.JOINT_CARE_APP_NAME;

    @Test
    public void ExcelTest() {
        UserUtil.getAllOpenId(APP_ID);
    }
    
    @Test
    public void AllExcelTest() {
        List<WxUserAll> allUserInfo = UserUtil.getUserInfo(UserUtil.getAllOpenId(APP_ID), APP_ID);
        
        if (!allUserInfo.isEmpty()) {
            SaveToExcel.saveToExcel(allUserInfo);
        }else {
          System.out.println("目前暂无用户...");
        }
    }    
}

6，其他代码

WxUserAll

public class WxUserAll {
    private Integer subscribe;
    
    private String openid;
    
    private String nickname;
    
    private Integer sex;
    
    private String language;
    
    private String city;
    
    private String province;
    
    private String country;
    
    private String headimgurl;
    
    private String subscribe_time;
    
    private String unionid;
    
    private String remark;
    
    private Integer groupid;
     
    private List<String> tagid_list;
    
    private String subscribe_scene;
    
    private Integer qr_scene;
    
    private String qr_scene_str;

    @Override
    public String toString() {
        return "WxUserAll [subscribe=" + subscribe + ", openid=" + openid + ", nickname=" + nickname + ", sex=" + sex
                + ", language=" + language + ", city=" + city + ", province=" + province + ", country=" + country
                + ", headimgurl=" + headimgurl + ", subscribe_time=" + subscribe_time + ", unionid=" + unionid
                + ", remark=" + remark + ", groupid=" + groupid + ", tagid_list=" + tagid_list + ", subscribe_scene="
                + subscribe_scene + ", qr_scene=" + qr_scene + ", qr_scene_str=" + qr_scene_str + "]";
    }

    public Integer getSubscribe() {
        return subscribe;
    }

    public void setSubscribe(Integer subscribe) {
        this.subscribe = subscribe;
    }

    public String getOpenid() {
        return openid;
    }

    public void setOpenid(String openid) {
        this.openid = openid;
    }

    public String getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public String getLanguage() {
        return language;
    }

    public void setLanguage(String language) {
        this.language = language;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public String getHeadimgurl() {
        return headimgurl;
    }

    public void setHeadimgurl(String headimgurl) {
        this.headimgurl = headimgurl;
    }

    public String getSubscribe_time() {
        return subscribe_time;
    }

    public void setSubscribe_time(String subscribe_time) {
        this.subscribe_time = subscribe_time;
    }

    public String getUnionid() {
        return unionid;
    }

    public void setUnionid(String unionid) {
        this.unionid = unionid;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public Integer getGroupid() {
        return groupid;
    }

    public void setGroupid(Integer groupid) {
        this.groupid = groupid;
    }

    public List<String> getTagid_list() {
        return tagid_list;
    }

    public void setTagid_list(List<String> tagid_list) {
        this.tagid_list = tagid_list;
    }

    public String getSubscribe_scene() {
        return subscribe_scene;
    }

    public void setSubscribe_scene(String subscribe_scene) {
        this.subscribe_scene = subscribe_scene;
    }

    public Integer getQr_scene() {
        return qr_scene;
    }

    public void setQr_scene(Integer qr_scene) {
        this.qr_scene = qr_scene;
    }

    public String getQr_scene_str() {
        return qr_scene_str;
    }

    public void setQr_scene_str(String qr_scene_str) {
        this.qr_scene_str = qr_scene_str;
    }    
}

参考博客：

1，微信公众号开发之如何一键导出微信所有用户信息到Excel - 酷玩时刻 - 博客园 https://www.cnblogs.com/zyw-205520/p/5958560.html