使用 VSTO 和 C# 实现 Excel 2010 的自定义操作窗格和自定义功能区

• 下载源代码 - 213.1 KB

背景

由于我在金融行业从事软件开发工作，我需要与很多会计打交道。您可能知道，许多会计/财务协调员都喜欢使用 Microsoft Excel。因此，我最近使用 Microsoft Visual Studio Tools for Office (VSTO) 和 C# 为 Excel 2010 开发了一个小型实用程序。该实用程序使用户能够从 OLAP 数据库检索会计科目表维度详细信息，并将其显示在 Excel 工作表中。此外，它还使用户能够将这些数据导出为 XML 和 CSV 格式，以便与其他应用程序一起使用。在此实用程序中，我实现了自定义操作窗格和自定义功能区以进行用户交互。我认为本文及其包含的某些功能可能对其他用户也很有用。

介绍 

本文的目的是演示如何使用 Visual Studio Tools For Office (VSTO) 和 C# 为 Excel 2010 实现自定义操作窗格和自定义功能区。此外，我还将探讨 .NET Framework 的一些关键功能，如 Entity Framework，以及 C# 语言的特性，如 LINQ 和 Lambda 表达式。这些功能都使数据库交互变得非常轻松优雅。此外，它还可以将 Excel 工作簿转换为功能强大的应用程序，并提供美观的图形用户界面。我还使用了一个简单的轻量级日志记录类来演示众所周知的单例设计模式的应用。本文的场景是关于一家公司，其销售部门希望监控和跟踪客户订单以及产品详细信息，以了解其补货水平。它还允许他们以 CSV 和 XML 格式导出任何特定客户的订单。演示应用程序使用了 Northwind 数据库，该数据库可从 Microsoft 的 CodePlex 网站下载，其数据模型非常适合此应用程序。我为示例项目使用了 Visual Studio 2013 Professional。本文将涵盖以下关键点： 

• 实现自定义操作窗格。 
• 使用 Visual Designer 实现自定义功能区。 
• Entity Framework 
• LINQ 和 Lambda 表达式 
• Singleton 设计模式

设计概述

应用程序模型使用 Entity Framework 构建。日志记录是通过一个简单的 Logger 类实现的，该类被实现为单例。应用程序的类图如图 1 所示。

从图 1 中可以看出，CustomerPane 类是实现自定义操作窗格的 GUI 类。它继承自 UserControl 类。该类负责在下拉列表中显示客户详细信息供用户选择。它还负责将选定客户的订单详细信息填充到工作簿中，并负责将这些详细信息导出为 CSV 或 XML 格式。

CustomRibbon 是实现自定义功能区的类，它继承自 RibbonBase 类。Visual Studio 2013 中的可视化设计器提供了一个丰富的工具箱，您可以使用它将不同的控件拖放到功能区的设计界面上。您可以对控件进行分组，并选择各种控件，如按钮、切换按钮、单选或复选框按钮以及下拉列表。在此示例中，我使用一个简单的按钮来显示产品详细信息，使用切换按钮来显示和隐藏客户操作窗格。

业务层

BONorthWindFacade 类充当 GUI 层中的不同类与其他实体之间的外观（facade）。大多数实体都是由 Entity Framework 自动创建的。这些详细信息将在后面的部分中介绍。外观类为所有 GUI 类提供了一个统一的接口，并抽象了业务/数据层。这将使我们能够在未来修改业务和数据层的实现，而不会影响 GUI 类。

数据层

数据层使用 Entity Framework 5.0 实现，其中有一个关键类 NorthWindEntities，它继承自 DBContext 类。该类提供了对其他实体的访问，我们可以使用 LINQ 查询关键表的数据。图 2 显示了关键实体及其关系。

实现客户操作窗格

在 Visual Studio 解决方案资源管理器中，右键单击项目并选择“添加新项”。这将显示一个对话框，您可以在其中选择操作窗格控件。请参考图 3。

我已将此控件命名为 CustomerPane。该控件的用户界面如图 4 所示。

CustomerPane 类具有以下关键职责：

• 填充客户下拉列表。
• 用于将订单详细信息填充到工作簿中以供选定客户的事件处理程序。
• 用于将订单详细信息导出为 XML 或 CSV 格式的事件处理程序。

关键方法的实现细节如下所示：

// Below is private field in ThisWorkbook class
private CustomerPane cpane = new CustomerPane();

// This is start up method of ThisWorbook class
// See how the instance of customer pane control is added to ActionPane's controls collection.
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
    //Initialises the custom customer pane.
        this.ActionsPane.Controls.Add(cpane);
}

// Private field and methods in CustomerPane class
BONorthwindFacade _bs = new BONorthwindFacade();
       
private void InitDropdowns() {

    try
    {
        List<customer> customerList = _bs.GetCustomers();
        drpCustomer.DataSource = customerList;
        drpCustomer.DisplayMember = "CompanyName";
        drpExportFormat.DataSource = new string[] { "CSV", "XML" };
        drpExportFormat.SelectedIndex = 0;
        //Initialise_Customers(customerList);
    }
    catch (Exception ex)
    {
        string message = "Error occured while populating " + 
          "the schedule dropdown and error is " + ex.ToString();
        Logger.Log.Error(message);
    }
}

Thisworkbook 类在其启动事件处理程序中，将 CustomerPane 的实例添加到 ActionPane 的 Controls 集合中。CustomerPane 类创建 BONorthwindFacade 类的实例。InitDropdowns() 方法通过调用外观类的 GetCustomers() 方法来填充客户下拉列表。格式下拉列表由一个 string 数组填充。请注意 Logger 类的 Error() 方法用于记录异常。我将在后面的部分介绍此类。

private void AddOrdersToWorkbook(List<Order> orders,string customerID) {
    try
    {

        Logger.Log.Information(string.Format(
          "Adding Order of the customer {0} to the workbook", customerID));

        Excel1.Worksheet ws = null;
        foreach(Excel1.Worksheet ws1 in Globals.ThisWorkbook.Worksheets)
        {
            if(ws1.Name.Equals(customerID,StringComparison.InvariantCultureIgnoreCase))
            {
                ws = ws1;
                break;
            }
        }
        if (ws == null)
        {
            ws = Globals.ThisWorkbook.Worksheets.Add();
            ws.Name = customerID;
        }

        Excel1.Range range =ws.Range["A1"];

        range.Value2 = "Order ID";
        range.Offset[0, 1].Value2 = "Order Date";
        range.Offset[0, 2].Value2 = "Required Date";
        range.Offset[0, 3].Value2 = "Shipping Address";
        range.Offset[0, 4].Value2 = "Shipping City";
        range.Offset[0, 5].Value2 = "Shipping Country";
        range.Offset[0, 6].Value2 = "Shipping PostCode";
        range.Offset[0, 7].Value2 = "Shipped Date";
        range.Offset[0, 8].Value2 = "Order Amount";
        
        int rowIndex = 1;
        int colIndex = 0;

       foreach (Order od in orders)
       {
            range.Offset[rowIndex, colIndex].Value2 = od.OrderID;
            range.Offset[rowIndex, colIndex + 1].Value2 = od.OrderDate;
            range.Offset[rowIndex, colIndex + 1].NumberFormat = @"dd/mm/yyyy;@";
            range.Offset[rowIndex, colIndex + 2].Value2 = od.RequiredDate;
            range.Offset[rowIndex, colIndex + 2].NumberFormat = @"dd/mm/yyyy;@";
         
            range.Offset[rowIndex, colIndex + 3].Value2 = od.ShipAddress;
            range.Offset[rowIndex, colIndex + 4].Value2 = od.ShipCity;
            range.Offset[rowIndex, colIndex + 5].Value2 = od.ShipCountry;
            range.Offset[rowIndex, colIndex + 6].Value2 = od.ShipPostalCode;
            range.Offset[rowIndex, colIndex + 7].Value2 = od.ShippedDate;
            range.Offset[rowIndex, colIndex + 7].NumberFormat = @"dd/mm/yyyy;@";
         
            range.Offset[rowIndex, colIndex + 8].Value2 = 
              od.Order_Details.Sum(odet => odet.Quantity * odet.UnitPrice);
            range.Offset[rowIndex, colIndex + 8].NumberFormat = "$#,##0.00";
         
            rowIndex++;
        }
        range =ws.Range["A1:L1"];
        range.Font.Bold=true;
        range = ws.Range["A1:L1"];
        range.EntireColumn.AutoFit();
        Logger.Log.Information(string.Format(
          "Orders for the Customer {0} adding to the workbook completed!", customerID));
    }
    catch (Exception ex) { 
    
        string message = "Error occured while populating orders " + 
          "to the workbook and error is " + ex.ToString();
        Logger.Log.Error(message);
    }
}

上面的代码创建一个新的工作表，如果它不存在则添加到工作簿中，否则更新同一工作表。上面的大部分代码都是不言自明的。但请注意使用 lambda 表达式来计算订单金额。

两个导出方法如下所示。

private void SaveOrdersAsCSVFile(List<Order< orders, string customerID)
{
    string filePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + 
      FILE_PATH + customerID + "_Orders.csv";
    using (StreamWriter sw = new StreamWriter(filePath, false))
    {
        string header = "'Order ID','Order Date','Required Date'," + 
          "'Order Amount','Shipped Date', 'Address','City','Country','Zip'";
        sw.WriteLine(header);
        orders.ForEach(od=>sw.WriteLine(string.Format(
          "'{0}','{1}','{2}','{3:C}','{4}','{5}','{6}','{7}','{8}'", 
          od.OrderID,od.OrderDate,od.RequiredDate,
          od.Order_Details.Sum(odet=>odet.Quantity*odet.UnitPrice),
          od.ShippedDate,od.ShipAddress,od.ShipCity,od.ShipCountry,od.ShipPostalCode)));
      
    }
}

private void SaveOrderAsXMLFile(List<Order> orders, string customerID)
{
    try
    { 
        string filePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + 
          FILE_PATH + customerID + "_Orders.xml";
        XElement orderXML=
           new XElement("Orders",
               new XAttribute("CustomerID",customerID),
            from o in orders
            select 
              new XElement("Order",
                new XAttribute("OrderID",o.OrderID),
                new XElement("OrderDate",o.OrderDate),
                new XElement("RequiredDate",o.RequiredDate),
                new XElement("Amount",string.Format("{0:C}",
                     o.Order_Details.Sum(odet=>odet.UnitPrice*odet.Quantity))),
                new XElement("ShippingDetails",
                        new XElement("Address",o.ShipAddress),
                        new XElement("City",o.ShipCity),
                        new XElement("Country",o.ShipCountry),
                        new XElement("PostCode",o.ShipPostalCode)
                    )

              )
              );
        
        orderXML.Save(filePath);
    }catch(Exception ex){

        string message = "Error occured while saving the orders in xml format and error is " + ex.ToString();
        Logger.Log.Error(message);

    }
}

从上面的代码中，请注意使用 lambda 表达式写入 CSV 文件。此外，还展示了如何使用 LINQ 查询语法和新版本的 XElement 类生成 XML。另外，还展示了 XElement 和 XAttribute 类的嵌套构造函数的用法。LINQ 用于构建所有嵌套的订单元素。与早期 .NET Framework 版本相比，这是一种非常简洁简化的 XML 生成方法。

实现自定义功能区

在 Visual Studio 解决方案资源管理器中，您可以添加一个功能区控件（Visual Designer）。图 5 显示了 CustomRibbon 控件。它包含简单的按钮和切换按钮控件。您可以使用拖放方式从功能区工具箱中添加任何控件。此外，您可以设置这些控件的属性，使其显示为大按钮或小按钮。您还可以指定 Microsoft Office 图像库提供的 Office 图像 ID。我在代码下载中包含了该资源的链接。

CustomRibbon 控件类实现了两个任务：

1. 在对话框窗口中显示产品列表。
2. 处理显示或隐藏 CustomerPane 操作窗格的事件。

//This is method in Thisworkbook class
// And is invoked via show/hide handler by passing the checked status.
// of toggle button
public void ShowHide_ActionPane(bool flag)
{
    this.Application.DisplayDocumentActionTaskPane = flag;
    cpane.Visible = flag;

}

private void btnProduct_Click(object sender, RibbonControlEventArgs e)
{
    BONorthwindFacade bs = new BONorthwindFacade();
    List<ProductEntity> productList = bs.GetProducts();
    frmProducts form = new frmProducts(productList);
    form.ShowDialog();
}

上面的代码是不言自明的。frmProducts 是一个 WinForm，它填充 ProductEntity 集合并在 DataGridView 代码中显示。请参考代码以获取详细实现。

BONorthWindFacade 实现

此类在数据层和其他 GUI 类之间提供统一的接口。有关关键方法实现细节，请参阅下面的代码。

class BONorthwindFacade
{
    private NORTHWNDEntities _context = new NORTHWNDEntities();
    
    public BONorthwindFacade() {
       
    }

    /// <summary>
    /// Method:GetCustomers
    /// Purpose:Returns collection of Customers from NorthWind database
    /// </summary>
    /// <returns></returns>
    public List<Customer> GetCustomers() {

        var customers = from c in _context.Customers
                        select c;
        return customers.ToList<Customer>();
    }

    /// <summary>
    /// Method:GetOrders
    /// Purpose:Returns orders for the customer selected.
    /// </summary>
    /// <param name="customerID"></param>
    /// <returns></returns>
    public List<Order>GetOrders(string customerID)
    { 
        var orders= from  od in _context.Orders
                    where od.CustomerID.Equals(customerID)
                    select od;
        return orders.ToList<Order>();
    }

    /// <summary>
    /// Method:GetProducts
    /// Purpose:Returns products from NorthWind database
    /// and projects as collection of Custom class ProductEntity.
    /// </summary>
    /// <returns></returns>
    public List<ProductEntity> GetProducts()
    {
        var products =from p in _context.Products
                      select new ProductEntity {
                      
                        ProductID =p.ProductID,
                        ProductName=p.ProductName,
                        ProductCategory =p.Category.CategoryName,
                        QuantityPerUnit=p.QuantityPerUnit,
                        ReorderLevel=p.ReorderLevel,
                        UnitPrice=p.UnitPrice
                        
                      };
        return products.ToList<ProductEntity>();
    }
}

上面代码中实现的关键方法是：

• GetCustomers()：检索客户列表。
• GetOrders()：获取特定客户的订单。
• GetProducts()：检索产品及其类别。

在 GetProducts() 方法中，我将产品详细信息投影为自定义 ProductEntity 类的集合。

数据层实现

数据层使用 Entity Framework 实现。您可以使用 Visual Studio 添加 Entity Framework 数据模型 edmx 文件。然后，您可以定义连接字符串，并从底层数据库定义 Table 或 View。我仅选择了图 2 中所示的关键实体。NorthwindEntities 和其他实体类是自动生成的。有关详细信息，请参阅源代码。另请修改 App.Config 文件中的连接字符串。

<connectionStrings>
    <add name="NORTHWNDEntities" 
      connectionString="metadata=res://*/Models.NorthWindEntities.csdl|
        res://*/Models.NorthWindEntities.ssdl|res://*/Models.NorthWindEntities.msl;
        provider=System.Data.SqlClient;provider connection string="data source=servername;initial 
        catalog=NORTHWND;user id=northwind;password=pw here; 
      MultipleActiveResultSets=True;App=EntityFramework"" 
      providerName="System.Data.EntityClient" />
</connectionStrings>

Logger 类实现

此类仅用于演示单例设计模式的应用。您可以使用 Enterprise Library 提供的 Logging Application Block，该模块可从 Microsoft 的 Patterns and Practices 网站下载。

namespace DemoCustomActionPaneAndRibbon.Utilities
{
    public enum LogLevel
    { 
        DEBUG =1,
        INFORMATION,
        WARNING,
        ERROR,
        FATAL
       
    }
    // Logger class implemented as a Singleton.
    public class Logger
    {
        private static readonly Logger _instance = new Logger();
        private string _logFilePath = string.Empty;
        private const long MAX_FILESIZE = 5000 * 1000;
        private const string CATEGORY = "DemoCustomActionPaneAndRibbon";
        private Logger() {

            InitLogFilePath();
            this.CurrentLogLevel = LogLevel.DEBUG;
            this.IsEnabled = true;
        }

        public LogLevel CurrentLogLevel { get; set; }
        public bool IsEnabled { get; set; }
        private void InitLogFilePath()
        {
            string filePath = Environment.GetFolderPath(
              Environment.SpecialFolder.MyDocuments)+ @"\NorthWind\Logs\";
            filePath += string.Format("NorthWind_log_{0}_{1}_{2}_{3}_{4}.csv", 
              DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year, 
              DateTime.Now.Hour, DateTime.Now.Minute);
            _logFilePath = filePath;
        }

        public static Logger Log
        {
            get {
                return _instance;
            }
        }

        public void Debug(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.DEBUG) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.DEBUG);
            }

        }
        public void Warn(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.WARNING) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.WARNING);
            }
        }

        public void Information(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.INFORMATION) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.INFORMATION);
            }
        }

        public void Error(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.ERROR) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.ERROR);
            }
        }

        public void Fatal(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.FATAL) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.FATAL);
            }
        }

        private void SaveToLogFile(string category, string message, LogLevel curLevel)
        {
            try
            {
                if (_logFilePath.Length > MAX_FILESIZE)
                {
                    InitLogFilePath();
                }
                using (StreamWriter sw = new StreamWriter(_logFilePath,true))
                {
                    sw.WriteLine(string.Format("{0}-{1}\t{2}\t{3}\t{4}", 
                      DateTime.Now.ToShortDateString(), 
                      DateTime.Now.ToShortTimeString(),category, curLevel, message));
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}

Logger 类的构造函数是私有的，因此其他类无法实例化它。它有一个静态只读字段，用于分配 Logger 类的实例。此实例作为静态 Log 属性返回。默认情况下，LogLevel 设置为记录 DEBUG 级别。因此，它可以记录所有类型的消息。它在 MyDocuments 文件夹下的 logs 文件夹中创建日志文件。大多数用户不是其桌面的管理员，因此写入 Mydocuments 文件夹是一种避免权限问题的更好方法。

关注点

您可以使用 Office 图像 ID 为自定义功能区中使用的不同按钮选择合适的图像。您可以从 这里 下载此图像库的附加组件。如果在此库中找不到合适的图标，您也可以选择自定义图像图标。

结论

使用 Microsoft Visual Studio Tools for Office (VSTO)，我们现在可以使用 C#、VB.NET 或任何 .NET 兼容语言来开发和构建强大的 Office 解决方案。您可以利用最新 .NET Framework 和语言特性的强大功能。我希望本文中的示例应用程序能为您提供关于如何将 Excel 工作簿转换为强大应用程序的想法。此外，代码是编译好的，您不需要使用 VBA 编写宏。但我发现 Excel 的记录宏功能在我需要用 C# 实现特定 Excel 功能时非常有用。然后，我可以看到宏生成的代码，然后将其移植到 C#。请告诉我您的评论和建议。您也可以 发送电子邮件给我，以获取有关实现的任何疑问或澄清。谢谢。