1.本例开发了一个Excel 文档管理的插件,基于VS2012 和Excel2010,先上效果图
2.技术原理:使用Excel提供的自定义任务面板即CTP技术以及Ribbon菜单实现
1.先新增一个Ribbon 菜单,本人给的名字是UserRibbon,就是开始菜单的第一个文档管理的按钮,控制是否打开文档管理的那个自定义任务面板
2.增加一个UserControl,里面加上TreeView控件,来显示三种EXcel文档(当前打开文档,经常使用文档,最近使用文档)
3.加载任务面板代码段
- <pre name="code" class="csharp">using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Xml.Linq;
- using Excel = Microsoft.Office.Interop.Excel;
- using Office = Microsoft.Office.Core;
- using Microsoft.Office.Tools.Excel;
- using Microsoft.Office.Core;
- using System.Diagnostics;
- using System.Runtime.InteropServices;
- namespace OfficeDocM
- {
- public partial class ThisAddIn
- {
- // 定义一个任务窗体
- internal Microsoft.Office.Tools.CustomTaskPane DocManageTaskPane;
- internal DocExplorer docExplorer;
- internal Excel.Application ExcelApp;
- private void ThisAddIn_Startup(object sender, System.EventArgs e)
- {
- //SingleInstanceCheck(); 有点问题先不控制
- Init();
- }
- private void Init()
- {
- //初始化
- ExcelApp = Globals.ThisAddIn.Application;
- //实例化文档浏览器窗口
- docExplorer = new DocExplorer();
- //注册Excel打开事件
- Globals.ThisAddIn.Application.WorkbookOpen += Application_WorkbookOpen;
- //加载自定义面板
- DocManageTaskPane = Globals.ThisAddIn.CustomTaskPanes.Add(docExplorer, "文档浏览器");
- ShowDocManageTaskPane(MsoCTPDockPosition.msoCTPDockPositionLeft);
- }
- //处理Excel打开事件
- private void Application_WorkbookOpen(Excel.Workbook Wb)
- {
- docExplorer.SetFileList(Wb.Path, ExcelApp.ActiveWorkbook.Name);
- }
- private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
- {
- }
- public void ShowDocManageTaskPane(MsoCTPDockPosition postion = MsoCTPDockPosition.msoCTPDockPositionLeft)
- {
- // 使任务窗体可见
- DocManageTaskPane.Visible = true;
- DocManageTaskPane.DockPosition = postion;
- }
- public void HideDocManageTaskPane()
- {
- // 使任务窗体不可见
- DocManageTaskPane.Visible = false;
- }
- #region VSTO 生成的代码
- /// <summary>
- /// 设计器支持所需的方法 - 不要
- /// 使用代码编辑器修改此方法的内容。
- /// </summary>
- private void InternalStartup()
- {
- this.Startup += new System.EventHandler(ThisAddIn_Startup);
- this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
- }
- #endregion
- }
- //非托管代码部分(引用Win32API)
- public partial class ThisAddIn
- {
- [DllImport("User32.dll ")]
- private static extern bool ShowWindowAsync(IntPtr hWnd, int cmdShow);
- //单实例检查
- private void SingleInstanceCheck()
- {
- int Process_min_id;
- Process[] processes = Process.GetProcessesByName("EXCEL");
- Process_min_id = processes[0].Id;
- //杀死其他进程
- if (processes.Length > 1)
- {
- for (int i = 1; i < processes.Length; i++)
- {
- if (Process_min_id < processes[i].Id)
- processes[i].Kill();
- else
- {
- Process.GetProcessById(Process_min_id).Kill();
- Process_min_id = processes[i].Id;
- }
- }
- }
- //显示最开始的进程
- ShowWindowAsync(Process.GetProcessById(Process_min_id).MainWindowHandle, 1);
- }
- }
- }
4 操作文档管理代码段- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Drawing;
- using System.Data;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- using Excel=Microsoft.Office.Interop.Excel;
- using System.IO;
- using System.Xml;
- using System.Collections;
- using System.Reflection;
- using Microsoft.Win32;
- namespace OfficeDocM
- {
- public partial class DocExplorer : UserControl
- {
- Excel.Application ExcelApp;
- Excel.Workbooks ExcelWorkBooks;
- Hashtable ht_common = new Hashtable(); //保存常用的excel
- Hashtable ht_cuureet = new Hashtable();//保存当时的excel
- TreeNode root = new TreeNode();//根节点
- TreeNode common_root = new TreeNode();//常用根节点
- TreeNode current_root = new TreeNode();//当前根节点
- TreeNode recent_root = new TreeNode();//最近使用根节点
- public DocExplorer()
- {
- InitializeComponent();
- Init();
- }
- public void Init()
- {
- ExcelApp = Globals.ThisAddIn.Application;
- ExcelWorkBooks = ExcelApp.Workbooks;
- //树根初始化
- TreeViewInit();
- //加载XML常用文件配置
- LoadCommonFiles();
- //加载最近使用文件
- LoadRecentFiles();
- }
- public void SetFileList(string filePath, string fileName)
- {
- string fileFullPath;
- fileFullPath = filePath + @"\" + fileName;
- if (File.Exists(fileFullPath))
- {
- TreeNode chldNode = new TreeNode();
- chldNode.Name = fileFullPath;
- chldNode.Text = fileName;
- current_root.Nodes.Add(chldNode);
- chldNode.ForeColor = Color.Red;
- if (!current_root.IsExpanded)
- current_root.ExpandAll();
- }
- }
- //单击打开文件
- private void treeView_FileList_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
- {
- //创建Excel控件对象
- string Excelpath;
- Boolean docExists = false;
- if (e.Node.Level == 2 && e.Button==MouseButtons.Left)//左键单击叶子节点打开文件
- {
- Excelpath = treeView_FileList.SelectedNode.Name;
- foreach (Excel._Workbook wb in ExcelWorkBooks)
- {
- if (Excelpath == wb.FullName)
- {
- wb.Activate();
- docExists = true;
- break;
- }
- }
- if (!docExists)
- {
- if (File.Exists(Excelpath))
- {
- //监听了excel 打开事件,当前使用文档统一在打开事件里面处理
- ExcelWorkBooks.Open(Excelpath);
- if (!current_root.IsExpanded)
- current_root.ExpandAll();
- }
- else
- MessageBox.Show("文件不存在!");
- }
- }
- }
- //双击关闭文件
- private void treeView_FileList_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
- {
- if (e.Node.Parent == current_root && e.Node.Level==2)//双击打开文件则关闭
- {
- foreach (Excel._Workbook wb in ExcelWorkBooks)
- {
- if (e.Node.Name == wb.FullName)
- {
- wb.Close();
- current_root.Nodes.Remove(e.Node);
- break;
- }
- }
- }
- }
- private void TreeViewInit()
- {
- root.Text = "文档列表";
- current_root.Text = "当前打开文档";
- common_root.Text = "经常使用文档";
- recent_root.Text = "最近使用文档";
- treeView_FileList.Nodes.Add(root);
- root.Nodes.Add(current_root);
- root.Nodes.Add(common_root);
- root.Nodes.Add(recent_root);
- if (!root.IsExpanded)
- {
- root.Expand();
- }
- }
- private void LoadCommonFiles()
- {
- string xmlPath;
- int firstIndex, lastIndex;
- RegistryKey Key;
- Key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\Excel\Addins\OfficeDocM");
- xmlPath = Key.GetValue("Manifest").ToString();
- Key.Close();
- firstIndex=xmlPath.IndexOf(":")+4;
- lastIndex=xmlPath.LastIndexOf("/");
- xmlPath=xmlPath.Substring(firstIndex, lastIndex - firstIndex);
- xmlPath = xmlPath + @"\CustExcels.xml";
- //加载XML
- XmlDocument xmlDoc = new XmlDocument();
- if (File.Exists(xmlPath))
- {
- xmlDoc.Load(xmlPath);
- //忽略注释内容
- XmlReaderSettings settings = new XmlReaderSettings();
- settings.IgnoreComments = true;
- //得到根节点
- XmlNode ExcelsNode = xmlDoc.SelectSingleNode("Excels");
- // 得到根节点的所有子节点
- XmlNodeList ExcelsNodeList = ExcelsNode.ChildNodes;
- foreach (XmlNode xnitem in ExcelsNodeList)
- {
- TreeNode chldNode = new TreeNode();
- string docPath, docName;
- // 将节点转换为元素,便于得到节点的属性值
- XmlElement excelList = (XmlElement)xnitem;
- // 得到excel节点的所有子节点
- XmlNodeList xnl0 = excelList.ChildNodes;
- docName = xnl0.Item(0).InnerText;
- docPath = xnl0.Item(1).InnerText;
- chldNode.Name = docPath + @"\" + docName;
- chldNode.Text = docName;
- chldNode.ForeColor = Color.Purple;
- common_root.Nodes.Add(chldNode);
- }
- if (!common_root.IsExpanded)
- common_root.ExpandAll();
- }
- else
- {
- Log my = new Log(@"D:\log.txt", FileMode.Create);
- my.Writeln(DateTime.Now.ToLocalTime().ToString()+"["+xmlPath+"]");
- }
- }
- private void LoadRecentFiles()
- {
- string fileFullPath,fileName;
- for (int i = 1; i <= ExcelApp.RecentFiles.Count; i++)
- {
- fileFullPath=ExcelApp.RecentFiles.get_Item(i).Name;
- if (File.Exists(fileFullPath))
- {
- fileName = System.IO.Path.GetFileName(fileFullPath);
- TreeNode chldNode = new TreeNode();
- chldNode.Name = fileFullPath;
- chldNode.Text = fileName;
- recent_root.Nodes.Add(chldNode);
- chldNode.ForeColor = Color.SteelBlue;
- if (!recent_root.IsExpanded)
- recent_root.ExpandAll();
- }
- }
- }
- /*因为单击选中的还是上次的节点,那么在第一次加载上次的节点还是null的
- *所以使用单击打开这个事件是必不可少的*/
- private void treeView_FileList_MouseDown(object sender, MouseEventArgs e)
- {
- if ((sender as TreeView) != null)
- {
- treeView_FileList.SelectedNode = treeView_FileList.GetNodeAt(e.X, e.Y);
- }
- }
- }
- }
5关键点说明
1.本例中使用到了一个XML文件作为配置文件,保存经常使用的文档信息,有一个问题就是,没办法确定这个文件的路径,因为不知道用户会把这个插件安装在哪里,各种方法试就是不行,突然灵机一动,Excel 怎么加载插件的,原来在注册表,果然如此 就有了下面一段代码(路径里包含一些其他东西 就特别处理了一下)
- string xmlPath;
- int firstIndex, lastIndex;
- RegistryKey Key;
- Key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\Excel\Addins\OfficeDocM");
- xmlPath = Key.GetValue("Manifest").ToString();
- Key.Close();
- firstIndex=xmlPath.IndexOf(":")+4;
- lastIndex=xmlPath.LastIndexOf("/");
- xmlPath=xmlPath.Substring(firstIndex, lastIndex - firstIndex);
- xmlPath = xmlPath + @"\CustExcels.xml";
2.Ribbon 菜单位置的确定 图1 图2
2.1如果自己的菜单想独立(与office本生的菜单如开始,插入等 平级 Ribbon的ControlIDType 就选择Custom,若想其作为一个字菜单就选Office,并通过OfficeId指定其属于哪个菜单,如 图1 )
2.2 子菜单位置确定,如 图2 关键属性 Position
2.3 每个Office 菜单Id 见 office 菜单名称
联系客服