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 }