Excel插件開發(fā)

vbavsto 2021-04-29

展開全文

1.本例開發(fā)了一個Excel 文檔管理的插件，基于VS2012 和Excel2010,先上效果圖

2.技術(shù)原理：使用Excel提供的自定義任務(wù)面板即CTP技術(shù)以及Ribbon菜單實現(xiàn)

1.先新增一個Ribbon 菜單，本人給的名字是UserRibbon,就是開始菜單的第一個文檔管理的按鈕，控制是否打開文檔管理的那個自定義任務(wù)面板

2.增加一個UserControl，里面加上TreeView控件，來顯示三種EXcel文檔(當(dāng)前打開文檔，經(jīng)常使用文檔，最近使用文檔)

3.加載任務(wù)面板代碼段

<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
    {
        // 定義一個任務(wù)窗體 
        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)
        {
            // 使任務(wù)窗體可見 
            DocManageTaskPane.Visible = true;
            DocManageTaskPane.DockPosition = postion;
        }
        public void HideDocManageTaskPane()
        {
            // 使任務(wù)窗體不可見 
            DocManageTaskPane.Visible = false;
        }
        #region VSTO 生成的代碼
        /// <summary>
        /// 設(shè)計器支持所需的方法 - 不要
        /// 使用代碼編輯器修改此方法的內(nèi)容。
        /// </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;
            //殺死其他進(jìn)程
            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;
                    }
                }
            }
            //顯示最開始的進(jìn)程
            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();//保存當(dāng)時的excel
        TreeNode root = new TreeNode();//根節(jié)點
        TreeNode common_root = new TreeNode();//常用根節(jié)點
        TreeNode current_root = new TreeNode();//當(dāng)前根節(jié)點
        TreeNode recent_root = new TreeNode();//最近使用根節(jié)點
        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)
        {
            //創(chuàng)建Excel控件對象
            string Excelpath;
            Boolean docExists = false;
            if (e.Node.Level == 2 && e.Button==MouseButtons.Left)//左鍵單擊葉子節(jié)點打開文件
            {
                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))
                    {
                       //監(jiān)聽了excel 打開事件，當(dāng)前使用文檔統(tǒng)一在打開事件里面處理
                        ExcelWorkBooks.Open(Excelpath);
                        if (!current_root.IsExpanded)
                            current_root.ExpandAll();
                    }
                    else
                        MessageBox.Show("文件不存在！");
                }
            }
        }
        //雙擊關(guān)閉文件
        private void treeView_FileList_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            if (e.Node.Parent == current_root && e.Node.Level==2)//雙擊打開文件則關(guān)閉
            {
                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 = "當(dāng)前打開文檔";
            common_root.Text = "經(jīng)常使用文檔";
            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);
                //忽略注釋內(nèi)容
                XmlReaderSettings settings = new XmlReaderSettings();
                settings.IgnoreComments = true;
                //得到根節(jié)點
                XmlNode ExcelsNode = xmlDoc.SelectSingleNode("Excels");
                // 得到根節(jié)點的所有子節(jié)點
                XmlNodeList ExcelsNodeList = ExcelsNode.ChildNodes;
                foreach (XmlNode xnitem in ExcelsNodeList)
                {
                    TreeNode chldNode = new TreeNode();
                    string docPath, docName;
                    // 將節(jié)點轉(zhuǎn)換為元素，便于得到節(jié)點的屬性值
                    XmlElement excelList = (XmlElement)xnitem;
                    // 得到excel節(jié)點的所有子節(jié)點
                    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();
                }
            }
        }
        /*因為單擊選中的還是上次的節(jié)點，那么在第一次加載上次的節(jié)點還是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關(guān)鍵點說明

1.本例中使用到了一個XML文件作為配置文件，保存經(jīng)常使用的文檔信息，有一個問題就是，沒辦法確定這個文件的路徑，因為不知道用戶會把這個插件安裝在哪里，各種方法試就是不行，突然靈機(jī)一動，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";