关闭屏幕更新并重新打开(如果一开始是打开的)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace XLTimeTracker
{
    class API
    {
        public static void TurnAppSettingsOff()
        {
            AddinModule.CurrentInstance.ExcelApp.EnableEvents = false;
            AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = false;
        }
        public static void TurnAppSettingsOn()
        {
            if (AddinModule.CurrentInstance.ExcelApp == null) return;
            AddinModule.CurrentInstance.ExcelApp.EnableEvents = true;
            AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = true;
        }
    }
}

API.TurnAppSettingsOff();
// my code
API.TurnAppSettingsOn();

我只想创建一个内部管理器类来处理类似于以下的整个事情：

public sealed class ApplicationSettingsManager
{
    readonly object app;
    readonly Dictionary<string, object> appSettings;
    public ApplicationSettingsManager(object app)
    {
        this.app = app;
        appSettings = new Dictionary<string, object>();
    }
    public object Application { get { return app; } }
    public void SaveSetting(string settingName)
    {
        var propInfo = app.GetType().GetProperty(settingName);
        if (propInfo == null)
            throw new ArgumentException("Specified name is not a valid storable setting.", "setting");
        var value = propInfo.GetValue(app);
        if (appSettings.ContainsKey(settingName))
        {
            appSettings[settingName] = value;
        }
        else
        {
            appSettings.Add(settingName, value);
        }
    }
    public void SaveAllSettings()
    {
        var properties = app.GetType().GetProperties().Where(p => p.CanWrite &&
                                                             p.CanRead &&
                                                             p.SetMethod.IsPublic &&
                                                             p.GetMethod.IsPublic);
        foreach (var p in properties)
        {
            var value = p.GetValue(app);
            if (appSettings.ContainsKey(p.Name))
            {
                appSettings[p.Name] = value;
            }
            else
            {
                appSettings.Add(p.Name, value);
            }
        }
    }
    public void RestoreSetting(string settingName)
    {
        if (!appSettings.ContainsKey(settingName))
            throw new ArgumentException("Specified name does not correspond to a valid stored setting.", "settingName");
        var propInfo = app.GetType().GetProperty(settingName);
        propInfo.SetValue(app, appSettings[settingName]);
    }
    public void RestoreAllSettingas()
    {
        foreach (var p in appSettings)
        {
            RestoreSetting(p.Key);
        }
    }
}

这就行了。您可以按如下方式使用它；

var excelSettingsManager = new ApplicationSettingsManager(AddinModule.CurrentInstance.ExcelApp);
//store all settings you are going to tamper with...
excelSettingsManager.SaveSetting("EnableEvents");
excelSettingsManager.SaveSetting("ScreenUpdating");
//change excel setting and do your thing...
//...
//when done, restore settings
excelSettingsManager.RestoreAllSettings();

你完了！

这是我自己为解决这个问题而提出的解决方案。对我来说，这似乎比其他建议的解决方案更简单。如果你认为你有更好的解决方案，请告诉我！

(我下面的代码恰好在VB.NET中(

类来处理设置的更改和原始状态的存储：

Public Class ApplicationSettings
    Implements IDisposable
    Private ScreenUpdating As Boolean
    Private Events As Boolean
    Private Production As Boolean = True
    Public Sub New()
        MyBase.New()
        ScreenUpdating = AddinModule.ExcelApp.ScreenUpdating
        Events = AddinModule.ExcelApp.EnableEvents
    End Sub
    Public Sub Dispose() Implements IDisposable.Dispose
        AddinModule.ExcelApp.ScreenUpdating = ScreenUpdating
        AddinModule.ExcelApp.EnableEvents = Events
    End Sub
End Class

以下是我如何在代码中使用它：

Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick
    Using New ApplicationSettings
        'My code
    End Using
End Sub

我建议使用整数来表示状态，并使用布尔值隐藏它。这就是我的意思：

this.Working = true;
try
{
    // do something
}
finally
{
    this.Working = false;
}

然后实现Working属性，如下所示：

private int working;
public bool Working
{
    get { return working > 0; }
    set
    {
        if (value)
        {
             working++;
        }
        else
        {
             working--;
        }
    }
}

在内部，它只记得使用该整数设置的工作次数。如果working是0，那么您已恢复正常。

您可以根据需要多次设置Working。只要是> 0，它就会返回true。不要忘记将您的代码包装成try...catch，否则您将失去计数。

如果Working设置为false，则可以执行API调用。然后设置为true:

if (!this.Working)
{
    // do API call
    this.TurnAppSettingsOff();
}
this.Working = true;
try
{
    // do something
}
finally
{
    this.Working = false;
}
if (!this.Working)
{
    // do reset API call
    this.TurnAppSettingsOn();
}

您可以使用Stack<T>类来实现后进先出(LIFO(行为。

struct ExcelEventSettings
{
    public bool EnableEvents;
    public bool ScreenUpdating;
}
class Example
{
    private Stack<ExcelEventSettings> settingStack = new Stack<ExcelEventSettings>();
    // you can call this function as often as you called SaveAppSettings
    public void RestoreAppSettings()
    {
        if (settingStack.Count == 0)
            throw new Exception("There is no previous state!");
        ExcelEventSettings prevState = settingStack.Pop();
        setCurrentEnableEvents(prevState.EnableEvents);
        setCurrentScreenUpdating(prevState.ScreenUpdating);
    }
    public void SetAppSettings(bool enableEvents, bool screenUpdating)
    {
        ExcelEventSettings currentState;
        currentState.EnableEvents = getCurrentEnableEvents();
        currentState.ScreenUpdating = getCurrentScreenUpdating();
        settingStack.Push(currentState);
        setCurrentScreenUpdating(enableEvents);
        setCurrentEnableEvents(screenUpdating);
    }
    private bool getCurrentEnableEvents()
    {
       // Here you would call your Excel function
    }
    private bool getCurrentScreenUpdating()
    {
       // Here you would call your Excel function
    }
    private void setCurrentEnableEvents(bool value)
    {
        // Here you would call your Excel function
    }
    private void setCurrentScreenUpdating(bool value)
    {
        // Here you would call your Excel function
    }
}