private void WriteLogs(List <string> logs)
{
Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
workbook.Worksheets.Add();
var workSheet = workbook.ActiveSheet as Excel.Worksheet;
var logSheets = workbook.GetWorksheets().Where(x => x.Name.ToUpper().Contains("LOGS"));
if (logSheets == null || logSheets.Count() == 0)
{
workSheet.Name = "Logs 1";
}
else
{
workSheet.Name = "Logs " + logSheets.Count() + 1;
}
var i = 1;
foreach (var l in logs)
{
workSheet.Range[$"A{i}"].Value = l;
i++;
}
}
public TargetWorkbook(Excel.Workbook workbook)
{
var sheets = workbook.GetWorksheets();
Worksheet = sheets
.Select(x => new TargetWorksheet(x))
.ToList();
}
private void AddCommands()
{
Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
var workSheet = workbook.GetWorksheets().FirstOrDefault(x => x.Name == ConstantStrings.Commands);
if (workSheet == null)
{
workbook.Worksheets.Add();
workSheet = Globals.ThisAddIn.Application.ActiveSheet;
workSheet.Name = "Commands";
}
workSheet.Range["A1"].Value = "Command";
workSheet.Range["B1"].Value = "Name";
workSheet.Range["C1"].Value = "Parent";
workSheet.Range["D1"].Value = "Value";
workSheet.Range["E1"].Value = "Value 2";
workSheet.Range["F1"].Value = "Notes";
var commands = GetCommands().Where(x => x.ApplicationType == ApplicationType ||
x.ApplicationType == ApplicationTypeEnum.General).ToList();
var notesRange = workSheet.Range[$"A2:G{2 + commands.Count}"];
notesRange.Clear();
var i = 2;
foreach (var c in commands)
{
workSheet.Range[$"A{i}"].Value = c.Command;
workSheet.Range[$"B{i}"].Value = c.Name;
workSheet.Range[$"C{i}"].Value = c.Parent;
workSheet.Range[$"D{i}"].Value = c.Value;
workSheet.Range[$"E{i}"].Value = c.Value2;
workSheet.Range[$"F{i}"].Value = c.Notes;
i++;
}
workSheet.CreateNamedRange("Commands", $"A2:A{i}");
// create suppresion validation
workSheet.ClearNamedRange("Suppression");
workSheet.Range[$"A{i + 5}"].Value = "S";
workSheet.Range[$"A{i + 6}"].Value = "U";
workSheet.CreateNamedRange("Suppression", $"A{i + 5}:A{i + 6}");
}
/// <summary>
/// Checks if worksheet with given name exists
/// </summary>
/// <param name="workbook">Target workbook</param>
/// <param name="name">Worksheet name</param>
/// <returns></returns>
public static bool WorksheetExists(this Excel.Workbook workbook, string name)
{
var worksheets = workbook.GetWorksheets();
var worksheet = worksheets.FirstOrDefault(x => x.Name == name);
if (worksheet == null)
{
return(false);
}
else
{
return(true);
}
}
public static bool ActivateSheet(this Excel.Workbook workbook, string name)
{
var workSheets = workbook.GetWorksheets();
var workSheet = workSheets.FirstOrDefault(x => x.Name == name);
if (workSheet == null)
{
return(false);
}
workSheet.Activate();
return(true);
}
public static bool TryGetWorksheet(this Excel.Workbook workbook, Func <Excel.Worksheet, bool> func, out Excel.Worksheet worksheet)
{
var find = workbook.GetWorksheets(func).ToList();
if (find.Any())
{
worksheet = find.First();
return(true);
}
else
{
worksheet = null;
return(false);
}
}
public static IEnumerable <Excel.Worksheet> GetWorksheets(this Excel.Workbook workbook, Func <Excel.Worksheet, bool> func)
{
return(workbook.GetWorksheets()
.Where(func));
}
/// <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
}
