- A+
目的
开发一款可以同步Outlook邮件通讯录信息的插件。
方案
- VSTO 外接程序
- COM 加载项
VSTO 外接程序对Outlook的支持,是从2010版本之后开始的。
VSTO 4.0 支持Outlook 2010以后的版本,所以编写一次代码,就可以在不同的版本上运行。
COM 加载项十分依赖于.NET Framework框架和Office的版本,之后讲到的时候你就明白。
VSTO 外接程序
VSTO,全称是Visual Studio Tools for Office,在微软的Visual Studio平台中进行Office专业开发。VSTO是VBA的替代产品,使用该工具包使开发Office应用程序变得更简单,VSTO还能使用Visual Studio开发环境中的众多功能。
VSTO依赖于.NET Framework框架,并且不能在.net core或者.net 5+以上的平台运行。
创建VSTO程序
使用Visual Studio 2013的新建项目,如果你使用更新版本的话,那么你大概率找不到。因为被移除了。比如Visual Studio 2019最低创建的Outlook 2013 外接程序
Office/SharePoint -> .Net Framework 4 -> Outlook 2010 外接程序
之后我们会得到,这样的项目结构
打开ThisAddIn.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Outlook = Microsoft.Office.Interop.Outlook; using Office = Microsoft.Office.Core; using Microsoft.Office.Interop.Outlook; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.IO; using System.Threading; using System.Collections; namespace ContactsSynchronization { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { // Outlook启动时执行 MessageBox.Show("Hello VSTO!"); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { // Outlook关闭时执行 } #region VSTO 生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要 /// 使用代码编辑器修改此方法的内容。 /// </summary> private void InternalStartup() { // 绑定声明周期函数 this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } }
启动试试看
到这里我们就已经把项目搭建起来了,但在写代码之前不如再认识认识Outlook的个个对象吧。
认识VSTO中常用对象
常用类型
- MAPIFolder表示Outlook中的一个文件夹
- ContactItem 表示一个联系人
- DistListItem 表示一个联系人文件夹中的群组
- OlDefaultFolders 获取默认文件类型的枚举
- OlItemType 获取文件夹子项类型的枚举
全局实例Application
上挂载了我们用到大多数函数和属性。
Application.Session;// 会话实例 Application.Version;// DLL动态链接库版本 Application.Name;// 应用名称
Application.Session
会话实例,可以获取Outlook的大多数状态,数据。如文件夹、联系人、邮件等。
Outlook文件夹结构
Outlook 按照邮件账号区分用户数据,即每个邮件账号都有独立的收件箱,联系人等。
Outlook 默认情况下的文件夹结构
获取第一个邮箱账号的默认联系人文件夹
Application.Session.Stores.Cast<Outlook.Store()>.First().GetDefaultFolder(OlDefaultFolders.olFolderContacts);
获取Outlook的状态信息
获取联系人信息
MAPIFolder folder = Application.Session.GetDefaultFolder(OlDefaultFolders.olFolderContacts);//获取默认的通讯录文件夹 IEnumerable<ContactItem> contactItems = folder.Items.OfType<ContactItem>(); // 获取文件夹下的子项,OfType<ContactItem>只拿联系人的 foreach (ContactItem it in contactItems) { // 拿联系人的各种信息 string fullName = it.FullName; // 注意在此处修改联系人信息,再Save()是不生效的 }
添加联系人
MAPIFolder folder = Application.Session.GetDefaultFolder(OlDefaultFolders.olFolderContacts);// 获取默认的联系人文件夹 ContactItem contact = folder.Items.Add(OlItemType.olContactItem);// 新增联系人子项 // 设置各种信息 contact.FirstName = "三"; contact.LastName = "张"; contact.Email1Address = "zhangsan@163.com"; // 存储联系人 contact.Save();
删除联系人
Microsoft.Office.Interop.Outlook.MAPIFolder deletedFolder = application.Session.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);// 默认的联系人文件夹 int count = deletedFolder.Items.Count;// 获取子项数,包含联系人和群组 for (int i = count; i > 0; i--)// 遍历删除 { deletedFolder.Items.Remove(i); }
成品代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Outlook = Microsoft.Office.Interop.Outlook; using Office = Microsoft.Office.Core; using Microsoft.Office.Interop.Outlook; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.IO; using System.Threading; using System.Collections; namespace ContactsSynchronization { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { OperatorContact operatorInstance = new OperatorContact(this.Application); operatorInstance.Task(); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO 生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要 /// 使用代码编辑器修改此方法的内容。 /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } class OperatorContact { public OperatorContact(Microsoft.Office.Interop.Outlook.Application application) { this.application = application; } Microsoft.Office.Interop.Outlook.Application application = null; // outlook程序实例 private static string addressBookName = "汤石集团通讯录";// 通讯录名称 private Microsoft.Office.Interop.Outlook.MAPIFolder addressBookFolder = null; // 通讯录文件夹实例 public void Task() { new Thread(Run).Start(); } /// <summary> /// 开个新线程执行任务,不要堵塞原来的线程 /// </summary> private void Run() { try { if (NeedUpdate()) { addressBookFolder = getAddressBookFolder();// 覆盖式创建通讯录 List<Contact> remoteContacts = readRemoteContacts();// 读取远程邮箱通讯录 if (remoteContacts == null) return; Adjust(remoteContacts);// 调整联系人和群组 updateClientVersion();// 更新本地通讯录版本号 } } catch (System.Exception ex) { const string path = @"C:TONSemail-plugin-error.log"; FileInfo fileInfo = new FileInfo(path); long length = 0; if (fileInfo.Exists && fileInfo.Length != 0) length = fileInfo.Length / 1024 / 1024; if (length <= 3) File.AppendAllText(path, ex.Message + "rn"); else File.WriteAllText(path, ex.Message + "rn"); } } /// <summary> /// 覆盖式创建通讯录 /// </summary> /// <returns>通讯录文件夹实例</returns> private Microsoft.Office.Interop.Outlook.MAPIFolder getAddressBookFolder() { // 获取用户第一个PST档的通讯录文件夹的枚举器 IEnumerator en = application.Session.Stores.Cast<Outlook.Store>().First() .GetDefaultFolder(OlDefaultFolders.olFolderContacts) .Folders.GetEnumerator(); bool exits = false; Microsoft.Office.Interop.Outlook.MAPIFolder folder = null; // 遍历文件夹 while (en.MoveNext()) { Microsoft.Office.Interop.Outlook.MAPIFolder current = (Microsoft.Office.Interop.Outlook.MAPIFolder)en.Current; if (current.Name == addressBookName) { exits = true; folder = current; } } if (!exits) { // 创建汤石集团通讯录,并映射成通讯录格式 Microsoft.Office.Interop.Outlook.MAPIFolder newFolder = application.Session.Stores.Cast<Outlook.Store>().First() .GetDefaultFolder(OlDefaultFolders.olFolderContacts) .Folders.Add(addressBookName); newFolder.ShowAsOutlookAB = true;// 设置成“联系人”文件夹 return newFolder; } else { // 返回已经存在的同时集团通讯录文件夹,并删除里面的内容 int count = folder.Items.Count; for (int i = count; i > 0; i--) { folder.Items.Remove(i); } Microsoft.Office.Interop.Outlook.MAPIFolder deletedFolder = application.Session.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems); count = deletedFolder.Items.Count; for (int i = count; i > 0; i--) { deletedFolder.Items.Remove(i); } return folder; } } /// <summary> /// 更新本地的铜须录版本 /// </summary> private void updateClientVersion() { String path = @"C:TONSemail-plugin-version.conf"; string version = getRemoteVersion(); if (!File.Exists(path)) { File.WriteAllText(path,version); } else { File.WriteAllText(path, version); } } /// <summary> /// 判断是否需要更新 /// </summary> /// <returns>boolean值</returns> private bool NeedUpdate() { string remoteVersion = getRemoteVersion(); if (remoteVersion == null) return false; string clientVersion = getClientVersion(); return !(clientVersion == remoteVersion); } /// <summary> /// 读取服务器的通讯录版本 /// </summary> /// <returns>通讯录版本</returns> private string getRemoteVersion() { List<Dictionary<string, object>> items = SelectList( "SELECT TOP(1) [version] FROM TonsOfficeA..VersionControl WHERE applicationID = N'EmailContact'" , "Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc"); if (items == null) return null; return items[0]["version"].ToString(); } /// <summary> /// 获取本地的通讯录版本 /// </summary> /// <returns>通讯录版本</returns> private string getClientVersion() { String path = @"C:TONSemail-plugin-version.conf"; if (!File.Exists(path)) return null; return File.ReadAllText(path); } /// <summary> /// 读取远程的通讯录 /// </summary> /// <returns>联系人实例集合</returns> private List<Contact> readRemoteContacts() { List<Contact> remoteContacts = new List<Contact>(); List<Dictionary<string, object>> items = SelectList( "select [emailAddress],[firstName],[lastName],[companyName],[department],[_group] as 'group',[jobTitle] from [TonsOfficeA].[dbo].[EmailContacts]", "Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc"); items.ForEach(it => { Contact contact = new Contact(); contact.email1Address = it["emailAddress"].ToString(); contact.firstName = it["firstName"].ToString(); contact.lastName = it["lastName"].ToString(); contact.companyName = it["companyName"].ToString(); contact.department = it["department"].ToString(); if (it["jobTitle"] != null) contact.jobTitle = it["jobTitle"].ToString(); contact.groups = it["group"].ToString().Split(',').ToList(); remoteContacts.Add(contact); }); return remoteContacts; } /// <summary> /// 执行select语句 /// </summary> /// <param name="sql">select语句</param> /// <param name="connection">数据库链接语句</param> /// <returns>List<Dictionary<string, object>>结果</returns> /// <exception cref="System.Exception"></exception> public List<Dictionary<string, object>> SelectList(string sql, string connection) { if (sql == null || connection == null || sql == "" || connection == "") throw new System.Exception("未传入SQL语句或者Connection链接语句"); List<Dictionary<string, object>> list = new List<Dictionary<string, object>>(); SqlConnection conn = new SqlConnection(connection); SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (sqlDataReader == null) return null; while (sqlDataReader.Read()) { int count = sqlDataReader.FieldCount; if (count <= 0) continue; Dictionary<string, object> map = new Dictionary<string, object>(); for (int i = 0; i < count; i++) { string name = sqlDataReader.GetName(i); object value = sqlDataReader.GetValue(i); map.Add(name, value); } list.Add(map); } conn.Close(); return list; } catch (System.Exception) { conn.Close(); return null; } } /// <summary> /// 调整通讯录联系人 /// </summary> /// <param name="remoteContacts">数据库导入的联系人信息的源</param> private void Adjust(List<Contact> remoteContacts) { // copy一份以来做群组 List<Contact> distListItems = new List<Contact>(); Contact[] tempItems = new Contact[remoteContacts.Count]; remoteContacts.CopyTo(tempItems); tempItems.ToList().ForEach(it => { it.groups.ForEach(g => { Contact con = new Contact { firstName = it.firstName, lastName = it.lastName, email1Address = it.email1Address, companyName = it.companyName, department = it.department, group = g }; distListItems.Add(con); }); }); // 添加联系人 remoteContacts.ForEach(it => { ContactItem contact = addressBookFolder.Items.Add(); contact.FirstName = it.firstName; contact.LastName = it.lastName; contact.Email1Address = it.email1Address; contact.CompanyName = it.companyName; contact.Department = it.department; if (it.jobTitle != null) contact.JobTitle = it.jobTitle; contact.Save(); }); // 按群组分组,并创建群组保存 List<ContactStore> contactStores = distListItems .GroupBy(it => it.group) .Select(it => new ContactStore { group = it.Key, contacts = it.ToList() }) .ToList(); contactStores.ForEach(it => { DistListItem myItem = addressBookFolder.Items.Add(OlItemType.olDistributionListItem); it.contacts.ForEach(contact => { string id = String.Format("{0}{1}({2})", contact.lastName, contact.firstName, contact.email1Address); Recipient recipient = application.Session.CreateRecipient(id); recipient.Resolve(); myItem.AddMember(recipient); }); myItem.DLName = it.group; myItem.Save(); }); } struct Contact { public string email1Address; // 邮箱 public string firstName; // 姓氏 public string lastName; // 姓名 public string companyName; // 公司名称 public string department; // 部门名称 public List<string> groups; // 分组集合 public string group; // 分组 public string jobTitle; // 职称 } struct ContactStore { public string group; public List<Contact> contacts; } } }