C# 读取excel用户列表过滤一个月内未收到外部邮件已离职的员工
1、通过aspose.cells读取excel中的数据并添加到list中
//存储从excel中读取出来的数据 List<UserInfo> lst_userinfo = new List<UserInfo>(); string excel_path = @"G:\zhyue\backup\2018-08-01 读取腾讯邮箱接口-获取一个月内未接收到外部邮件且已离职的邮箱\腾讯邮箱\上海邮箱整理(1).xlsx"; //打开excel文件 Workbook curr_wb = File.Exists(excel_path) ? new Workbook(excel_path) : new Workbook(); //打开第一个sheet Worksheet sheet_first = curr_wb.Worksheets[0]; Cells Cells = sheet_first.Cells; //一共多少行数据 int rows = sheet_first.Cells.MaxDataRow + 1; //循环读取excel for (int i = 1; i < rows; i++) {//从第二行开始读 if (Cells[i, 3].StringValue.Contains("离职")) { lst_userinfo.Add(new UserInfo() { username = Cells[i, 0].StringValue, email = Cells[i, 2].StringValue, department = Cells[i, 5].StringValue, phone = Cells[i, 7].StringValue, sex = Cells[i, 8].StringValue, position = Cells[i, 9].StringValue }); } }
class UserInfo { /// <summary> /// 姓名 /// </summary> public string username { get; set; } /// <summary> /// 电子邮件 /// </summary> public string email { get; set; } /// <summary> /// 所属部门 /// </summary> public string department { get; set; } /// <summary> /// 性别 /// </summary> public string sex { get; set; } /// <summary> /// 手机 /// </summary> public string phone { get; set; } /// <summary> /// 职务 /// </summary> public string position { get; set; } }
2、创建excel对象并循环上一步得到的list，给腾讯接口发送请求获取日志记录过滤一个月内没有收到外部邮件的已离职员工
string excel_path = @"G:\zhyue\backup\2018-08-01 读取腾讯邮箱接口-获取一个月内未接收到外部邮件且已离职的邮箱\腾讯邮箱\过滤没收到外部邮件的用户.xlsx"; //打开excel文件 Workbook curr_wb = File.Exists(excel_path) ? new Workbook(excel_path) : new Workbook(); //打开第一个sheet Worksheet sheet_first = curr_wb.Worksheets[0]; Cells Cells = sheet_first.Cells; //表头 Cells[0, 0].PutValue("姓名"); Cells[0, 1].PutValue("电子邮件"); Cells[0, 2].PutValue("所属部门"); Cells[0, 3].PutValue("性别"); Cells[0, 4].PutValue("手机"); Cells[0, 5].PutValue("职务"); int curr_row = 1;//当前在第几行 //循环判断指定用户是否收到过来自指定邮箱以外的邮件 lst_userinfo.ForEach(userinfo => { bool is_received = CheckIsReceiveEmailEndWithTargetList(userinfo.email); if (!is_received) { Cells[curr_row, 0].PutValue(userinfo.username); Cells[curr_row, 1].PutValue(userinfo.email); Cells[curr_row, 2].PutValue(userinfo.department); Cells[curr_row, 3].PutValue(userinfo.sex); Cells[curr_row, 4].PutValue(userinfo.phone); Cells[curr_row, 5].PutValue(userinfo.position); curr_row++; } }); //保存文件 curr_wb.Save(excel_path, SaveFormat.Xlsx);
/// <summary> /// 检查指定邮箱是否收到除指定邮箱以外的邮件 /// </summary> private static bool CheckIsReceiveEmailEndWithTargetList(string user_mail_url) { //获取ACCESS_TOKEN ACCESS_TOKEN ACCESS_TOKEN = Get_ACCESS_TOKEN(); //post查看一个月内邮件 https://api.exmail.qq.com/cgi-bin/log/mail?access_token=ACCESS_TOKEN StringBuilder jsonData = new StringBuilder(); jsonData.Append("{"); jsonData.AppendFormat("\"begin_date\":\"{0}\",", DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd"));//开始日期。格式 为2016-10-01 jsonData.AppendFormat("\"end_date\":\"{0}\",", DateTime.Now.ToString("yyyy-MM-dd"));//开始日期。格式为2016-10-07 jsonData.AppendFormat("\"userid\":\"{0}\",", user_mail_url);//筛选条件：指定成员帐号 jsonData.AppendFormat("\"subject\":\"{0}\",", "");//筛选条件：包含指定主题内容 jsonData.AppendFormat("\"mailtype\":{0}", 2);//邮件类型。0:收信+发信 1:发信 2:收信 jsonData.Append("}"); string post_url = string.Format("https://api.exmail.qq.com/cgi-bin/log/mail?access_token={0}", ACCESS_TOKEN.access_token); string response = WebRequestExt.PostData(jsonData.ToString(), post_url); MailList MailList = JsonConvert.DeserializeObject<MailList>(response); //当前邮件是否收到过不是来自except_email的邮件 bool result = false; if (MailList != null && MailList.errcode == 0) { //挑选出发送人不是来自except_email的Email的所有邮件 List<string> except_email = new List<string>() { "joannarealestate.com.cn","jrecorporate.com","jremobility.com","jrechina.com" }; result = MailList.list.Exists(mail => { return !except_email.Exists(x => mail.sender.Contains(x)); }); } return result; }
/// <summary> /// 获取有效ACCESS_TOKEN /// </summary> /// <returns></returns> private static ACCESS_TOKEN Get_ACCESS_TOKEN() { string file_access_token = string.Empty;//从文件中读取的access_token string txtFileUrl = Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).Parent.Parent.FullName + "\\" + "ACCESS_TOKEN.txt"; using (FileStream fs = File.Open(txtFileUrl, FileMode.OpenOrCreate)) { byte[] bytes = new byte[fs.Length]; fs.Read(bytes, 0, bytes.Length); file_access_token = Encoding.Default.GetString(bytes); } ACCESS_TOKEN ACCESS_TOKEN = new ACCESS_TOKEN(); ACCESS_TOKEN = JsonConvert.DeserializeObject<ACCESS_TOKEN>(file_access_token); if (ACCESS_TOKEN != null && ACCESS_TOKEN.expires_date > DateTime.Now) {//还没过期 } else {//已经过期或不存在ACCESS_TOKEN 向腾讯发起请求获取ACCESS_TOKEN string access_token_url = string.Format(@"https://api.exmail.qq.com/cgi-bin/gettoken?corpid={0}&corpsecret={1}", "你的corpid", "你的corpsecret"); string response = WebRequestExt.GetData(access_token_url); ACCESS_TOKEN = JsonConvert.DeserializeObject<ACCESS_TOKEN>(response); if (ACCESS_TOKEN != null && ACCESS_TOKEN.expires_in == 7200) {//获取成功 ACCESS_TOKEN.expires_date = DateTime.Now.AddSeconds(ACCESS_TOKEN.expires_in); //写入到文件 File.WriteAllText(txtFileUrl, JsonConvert.SerializeObject(ACCESS_TOKEN)); } else {//获取失败 MessageBox.Show(response); } } return ACCESS_TOKEN; }
class ACCESS_TOKEN { /// <summary> /// 获取到的凭证。长度为64至512个字节 /// </summary> public string access_token { get; set; } /// <summary> /// 凭证的有效时间（秒） /// </summary> public int expires_in { get; set; } /// <summary> /// 凭证的有效时间-当前时间加上expires_in /// </summary> public DateTime expires_date { get; set; } public int errcode { get; set; } public string errmsg { get; set; } }
class MailList { /// <summary> /// 返回码 /// </summary> public int errcode { get; set; } /// <summary> /// 对返回码的文本描述内容 /// </summary> public string errmsg { get; set; } /// <summary> /// 列表数据 /// </summary> public List<Mail> list { get; set; } }
class Mail { /// <summary> /// 邮件类型。1:发信 2:收信 /// </summary> public int mailtype { get; set; } public string subject { get; set; } /// <summary> /// 发信者 /// </summary> public string sender { get; set; } /// <summary> /// 收信者 /// </summary> public string receiver { get; set; } /// <summary> /// 时间（时间戳格式） /// </summary> public int time { get; set; } /// <summary> /// 邮件状态 /// 0: 其他状态 /// 1: 发信中 /// 2: 被退信 /// 3: 发信成功 /// 4: 发信失败 /// 11: 收信被拦截 /// 12: 收信，邮件进入垃圾箱 /// 13: 收信成功，邮件在收件箱 /// 14: 收信成功，邮件在个人文件夹 /// </summary> public int status { get; set; } }
