public void AddSheetCommand(Excel.Workbook workbook, string name = "NewSheet")
{
var newWorksheet = workbook.CreateNewWorksheet(name);
}
/// <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
}
