EXCEL C# 聚光灯

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

public static class Extension
    {
        static Application _xlapp = (Application)ExcelDnaUtil.Application;
        static AppEvents_Event AppEvents = (AppEvents_Event)ExcelDnaUtil.Application;
        public const string MyConditionRng = "$1:$1048576";
        public static Color HighLightColor = Color.LightBlue;
        [ExcelCommand(MenuName = "拓展", MenuText = nameof(开启聚光灯))]
        public static void 开启聚光灯()
        {
            try
            {
                _xlapp.SheetSelectionChange += _xlapp_SheetSelectionChange; ;
                _xlapp.WorkbookOpen += _xlapp_WorkbookOpen;
                _xlapp.WorkbookBeforeClose += _xlapp_WorkbookBeforeClose; ;
                _xlapp.WorkbookNewSheet += _xlapp_WorkbookNewSheet;
                AppEvents.NewWorkbook += AppEvents_NewWorkbook;
                if (_xlapp.Workbooks.Count == 0) return;
                foreach (Workbook item in _xlapp.Workbooks)
                {
                    foreach (Worksheet ws in item.Worksheets)
                    {
                        ws.AddSheetFormatCondition(HighLightColor);
                    }
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }

        private static void AppEvents_NewWorkbook(Workbook Wb)
        {
            try
            {
                foreach (Workbook item in _xlapp.Workbooks)
                {
                    foreach (Worksheet ws in item.Worksheets)
                    {
                        ws.AddSheetFormatCondition(HighLightColor);
                    }
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }

        private static void _xlapp_WorkbookNewSheet(Workbook Wb, object Sh)
        {
            try
            {
                Worksheet ws = Sh as Worksheet;
                ws?.AddSheetFormatCondition(HighLightColor);
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }

        private static void _xlapp_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
        {
            try
            {
                foreach (Worksheet ws in Wb.Worksheets)
                {
                    ws.CancelSheetFormatCondition();
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }

        private static void _xlapp_WorkbookOpen(Workbook Wb)
        {
            try
            {
                foreach (Worksheet ws in Wb.Worksheets)
                {
                    ws.AddSheetFormatCondition(HighLightColor);
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }
        private static void _xlapp_SheetSelectionChange(object Sh, Range Target)
        {
            _xlapp.ScreenUpdating = false;
            _xlapp.ScreenUpdating = true;
        }

        private static void AddSheetFormatCondition(this Worksheet ws, Color _color)
        {
            try
            {
                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);
                fc.Font.Bold = true;
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }

        private static void CancelSheetFormatCondition(this Worksheet ws)
        {
            try
            {
                if (ws == null) return;
                var rng = ws.Range[MyConditionRng];
                if (rng != null)
                {
                    if (rng.FormatConditions.Count > 0)
                        rng.FormatConditions.Item(1).Delete();
                }
                else return;
            }
            catch (Exception )
            {
                
            }
        }

        [ExcelCommand(MenuName = "拓展", MenuText = nameof(关闭聚光灯))]
        public static void 关闭聚光灯()
        {
            try
            {
                _xlapp.SheetSelectionChange -= _xlapp_SheetSelectionChange; ;
                _xlapp.WorkbookOpen -= _xlapp_WorkbookOpen;
                _xlapp.WorkbookBeforeClose -= _xlapp_WorkbookBeforeClose;
                _xlapp.WorkbookNewSheet -= _xlapp_WorkbookNewSheet;
                AppEvents.NewWorkbook -= AppEvents_NewWorkbook;
                foreach (Workbook item in _xlapp.Workbooks)
                {
                    foreach (Worksheet ws in item.Worksheets)
                    {
                        ws.CancelSheetFormatCondition();
                    }
                }
            }
            catch (Exception ex)
            {
                Interaction.MsgBox(ex.Message);
            }
        }
    }