/// <summary> /// Adds command worksheet to workbook. Used for general command explanations and option lists /// </summary> private void CreateCommandWorksheet() { // Attempts to find a currently existing Command worksheet Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; var worksheet = workbook.GetWorksheets().FirstOrDefault(x => x.Name == "Commands"); // Adds a worksheet named Commands if it does not already exist worksheet = workbook.CreateNewWorksheet("Commands"); // Adds column headers to command table worksheet.Range["A1"].Value = "Command Type"; worksheet.Range["B1"].Value = "Command"; worksheet.Range["C1"].Value = "Options"; worksheet.Range["D1"].Value = "Reference"; worksheet.Range["E1"].Value = "New/Reference Name"; worksheet.Range["F1"].Value = "Target Value"; worksheet.Range["G1"].Value = "Auxillary Value"; // Add option ranges AddOptions(workbook, nameof(WorkbookOptions), "J", OptionHelper.GetWorkbookOptions()); AddOptions(workbook, nameof(ReferenceOptions), "K", OptionHelper.GetReferenceOptions()); AddOptions(workbook, nameof(RangeOptions), "L", OptionHelper.GetRangeOptions()); AddOptions(workbook, nameof(ExcelAutoFilterOptions), "M", OptionHelper.GetExcelAutoFilterOptions()); AddOptions(workbook, nameof(MatchValueOptions), "N", OptionHelper.GetMatchValueOptions()); // Add command ranges int counter = 2; counter = AddCommands(workbook, nameof(WorkbookCommands), counter, CommandHelper.GetWorkbookCommands()); counter = AddCommands(workbook, nameof(WorksheetCommands), counter, CommandHelper.GetWorksheetCommands()); counter = AddCommands(workbook, nameof(RangeCommands), counter, CommandHelper.GetRangeCommands()); counter = AddCommands(workbook, nameof(CodeCommands), counter, CommandHelper.GetCodeCommands()); counter = AddCommands(workbook, nameof(FilterCommands), counter, CommandHelper.GetFilterCommands()); counter = AddCommands(workbook, nameof(DataCommands), counter, CommandHelper.GetDataCommands()); #region Styling // Styles the command table var stylingRange = (Excel.Range)worksheet.Range["A:G"]; stylingRange.ColumnWidth = 45; stylingRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; stylingRange.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; stylingRange.Cells.WrapText = true; // Selects and styles the command headers var topRange = worksheet.Range["A1:G1"]; topRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue); topRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); topRange.Font.Bold = true; // Alternates color command table rows for easier reading for (int o = 3; o < counter - 1; o++) { Excel.Range colorRange; if (o % 2 != 0) { colorRange = worksheet.Range[$"A{o}:G{o}"]; colorRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); colorRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); } } // Adds all around border to command table Excel.Range borderRange = worksheet.Range[$"A1:G{counter-2}"]; borderRange.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; // Adds filter ability to command table for users borderRange.AutoFilter(1); // Styles the options tables stylingRange = (Excel.Range)worksheet.Range["J:N"]; stylingRange.ColumnWidth = 25; stylingRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; #endregion }