EXCEL C# 聚光灯

C# EXCEL版本聚光灯，开发框架利用EXCELDNA 开源库

public static class Extension
 {
     static Application _xlapp = (Application)ExcelDnaUtil.Application;
     public const string MyConditionRng = "$1:$1048576";
     [ExcelCommand(MenuName = "拓展", MenuText = nameof(开启聚光灯))]
     public static void 开启聚光灯()
     {
         try
         {
             _xlapp.SheetSelectionChange += _xlapp_SheetSelectionChange; ;
             _xlapp.WorkbookOpen += _xlapp_WorkbookOpen;
             _xlapp.WorkbookBeforeClose += _xlapp_WorkbookBeforeClose; ;
            foreach (Workbook item in _xlapp.Workbooks)
             {
                 foreach (Worksheet ws in item.Worksheets)
                 {
                     FormatCondition fc = null;
                     if (ws.Range[MyConditionRng].FormatConditions.Count == 0)
                     {
                         fc = ws.Range[MyConditionRng].FormatConditions.Add(XlFormatConditionType.xlExpression, XlFormatConditionOperator.xlEqual,
                             "=OR(CELL(\"ROW\")=ROW(),CELL(\"COL\")=COLUMN())");
                     }
                     else fc = ws.Range[MyConditionRng].FormatConditions.Item(1);
                     fc.SetFirstPriority();
                     fc.Interior.Color = ColorTranslator.ToOle(Color.LightPink);
                     fc.Font.Bold = true;
                 }
             }
         }
         catch (Exception ex)
         {
             Interaction.MsgBox(ex.Message);
         }
     }

     private static void _xlapp_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
     {
         try
         {
             foreach (Worksheet ws in Wb.Worksheets)
             {
                 if (ws.Range[MyConditionRng].FormatConditions.Count > 0)
                 {
                     ws.Range[MyConditionRng].FormatConditions.Item(1).Delete();
                 }
             }
         }
         catch (Exception ex)
         {
             Interaction.MsgBox(ex.Message);
         }
     }

     private static void _xlapp_WorkbookOpen(Workbook Wb)
     {
         try
         {
             Worksheet ws = Wb.ActiveSheet;
             if (ws == null) return;
             FormatCondition fc = null;
             if (ws.Range[MyConditionRng].FormatConditions.Count == 0)
             {
                 fc = ws.Range[MyConditionRng].FormatConditions.Add(XlFormatConditionType.xlExpression, XlFormatConditionOperator.xlEqual,
                     "=OR(CELL(\"ROW\")=ROW(),CELL(\"COL\")=COLUMN())");
             }
             else fc = ws.Range[MyConditionRng].FormatConditions.Item(1);
             fc.SetFirstPriority();
             fc.Interior.Color = ColorTranslator.ToOle(Color.LightPink);
             fc.Font.Bold = true;
         }
         catch (Exception ex)
         {
             Interaction.MsgBox(ex.Message);
         }
     }
     private static void _xlapp_SheetSelectionChange(object Sh, Range Target)
     {
         _xlapp.ScreenUpdating = false;
         _xlapp.ScreenUpdating = true;
     }


     [ExcelCommand(MenuName = "拓展", MenuText = nameof(关闭聚光灯))]
     public static void 关闭聚光灯()
     {
         try
         {
             _xlapp.SheetSelectionChange -= _xlapp_SheetSelectionChange; ;
             _xlapp.WorkbookOpen -= _xlapp_WorkbookOpen;
             _xlapp.WorkbookBeforeClose -= _xlapp_WorkbookBeforeClose; ;
             foreach (Workbook item in _xlapp.Workbooks)
             {
                 foreach (Worksheet ws in item.Worksheets)
                 {
                     if (ws.Range[MyConditionRng].FormatConditions.Count > 0)
                     {
                         ws.Range[MyConditionRng].FormatConditions.Item(1).Delete();
                     }
                 }
             }
         }
         catch (Exception ex)
         {
             Interaction.MsgBox(ex.Message);
         }
     }
 }