/// <summary> /// Deletes the workbook's range contents /// </summary> /// <param name="workbook"></param> /// <param name="range"></param> /// <param name="isNamedRange"></param> public static void DeleteRangeContents(this Excel.Workbook workbook, string range, bool isNamedRange = true) { string newRange; string worksheetName; Excel.Worksheet worksheet; if (isNamedRange) { if (workbook.NamedRangeExists(range)) { workbook.GetNamedRange(range).RefersToRange.Cells.ClearContents(); } } else { if (workbook.IsRange(range)) { if (range.Contains('!')) { worksheetName = range.Split('!').First(); newRange = range.Split('!').Last(); worksheet = workbook.GetWorksheet(worksheetName); worksheet.DeleteRangeContents(newRange, false); } } } }
/// <summary> /// Creates a named range on the workbook scope /// </summary> /// <param name="workbook">Target workbook</param> /// <param name="name">Name of range being created</param> /// <param name="range">Target range</param> /// <param name="scope">Scope named range is to be created on</param> public static void CreateNamedRange(this Excel.Workbook workbook, string name, string range) { Excel.Worksheet worksheet = workbook.ActiveSheet; string worksheetName; // Checks named range exists if (workbook.NamedRangeExists(name)) { throw new ArgumentException($"Name {name} already exists"); } if (!workbook.IsRange(range)) { throw new ArgumentException($"Range entered {range} is not a valid range for the workbook"); } // If range exists on another worksheet if (range.Contains("!")) { worksheetName = range.Split('!').First(); worksheet = workbook.GetWorksheet(worksheetName); } workbook.Names.Add(name, worksheet.Range[range]); }
/// <summary> /// Renames worksheet /// </summary> /// <param name="workbook">Target workbook</param> /// <param name="index">Worksheet to be renamed</param> /// <param name="newName">New sheet name</param> public static void RenameSheet(this Excel.Workbook workbook, int index, string newName = "NewSheet") { Excel.Worksheet worksheet = null; if (workbook.WorksheetExists(index)) { if (!workbook.WorksheetExists(newName)) { worksheet = workbook.GetWorksheet(index); worksheet.Name = newName; } else { throw new ArgumentException($"New name {newName} already has a worksheet with the same name"); } } else { throw new ArgumentException($"Worksheet {index} does not exist"); } }
/// <summary> /// Runs code /// </summary> /// <param name="startMethod">Flags method as first called method</param> /// <param name="rangeName">Named range indicating code blocks starting position</param> /// <returns></returns> public List <string> Run(bool startMethod, string rangeName = "") { Excel.Range startCell = null; Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; try { // Looks for range to start on if (string.IsNullOrEmpty(rangeName)) { // Starting cell is named range that matches the worksheet the run command is being called on startCell = _worksheet.Range[_worksheet.Name + "Type"]; } else { startCell = _worksheet.Range[rangeName]; } if (startCell == null) { throw new Exception("Could not find start range"); } } catch { throw new ArgumentException("Issue finding starting code cell. Please check that start cells match their respective sheet names."); } // Sets starting integers for columns var typeColumn = startCell.Column; var commandColumn = typeColumn + 1; var optionsColumn = commandColumn + 1; var referenceColumn = optionsColumn + 1; var nameColumn = referenceColumn + 1; var targetColumn = nameColumn + 1; var auxillaryColumn = targetColumn + 1; // Starts at cell below starting range for the sheet int i = startCell.Row + 1; // Will continue to run until there is an empty row found in the command type column while (!string.IsNullOrEmpty(GetString(i, typeColumn))) { var commandType = GetString(i, typeColumn).ToUpper(); var command = GetString(i, commandColumn).ToUpper(); string option; string reference; string name; string target; string auxillary; ReferenceEnum referenceType = ReferenceEnum.ByName; PositionalEnum positional = PositionalEnum.AtEnd; // Ignores comment lines if (command == CodeCommands.Comment) { i++; continue; } else { option = GetString(i, optionsColumn).ToUpper(); reference = GetString(i, referenceColumn).ToUpper(); name = GetString(i, nameColumn); target = GetString(i, targetColumn); auxillary = GetString(i, auxillaryColumn); } switch (commandType) { #region Workbook case CommandType.Workbook: switch (command) { case WorkbookCommands.AddSheet: _workbookCommands.AddSheetCommand(workbook, target); break; case WorkbookCommands.CopySheet: referenceType = OptionHelper.GetReferenceEnumFromString(reference); _workbookCommands.CopySheetCommand(workbook, target, name, referenceType); break; case WorkbookCommands.DeleteSheet: referenceType = OptionHelper.GetReferenceEnumFromString(reference); _workbookCommands.DeleteSheetCommand(workbook, target, referenceType); break; case WorkbookCommands.MoveSheet: positional = OptionHelper.GetPositionalEnumFromString(option); referenceType = OptionHelper.GetReferenceEnumFromString(reference); _workbookCommands.MoveWorksheetCommand(workbook, target, positional, name, referenceType); break; case WorkbookCommands.TargetSheet: referenceType = OptionHelper.GetReferenceEnumFromString(reference); _workbookCommands.TargetSheetCommand(workbook, target, referenceType); break; case WorkbookCommands.RenameSheet: referenceType = OptionHelper.GetReferenceEnumFromString(reference); _workbookCommands.RenameSheetCommand(workbook, target, name, referenceType); break; } break; #endregion #region Worksheet case CommandType.Worksheet: switch (command) { case WorksheetCommands.AddColumn: _worksheetCommands.AddColumnCommand(workbook.ActiveSheet, target); break; case WorksheetCommands.AddRow: _worksheetCommands.AddRowCommand(workbook.ActiveSheet, target); break; case WorksheetCommands.MoveColumn: _worksheetCommands.MoveColumnCommand(workbook.ActiveSheet, target, name); break; case WorksheetCommands.MoveRow: _worksheetCommands.MoveRowCommand(workbook.ActiveSheet, target, name); break; case WorksheetCommands.DeleteColumn: _worksheetCommands.DeleteColumnCommand(workbook.ActiveSheet, target); break; case WorksheetCommands.DeleteRow: _worksheetCommands.DeleteRowCommand(workbook.ActiveSheet, target); break; } break; #endregion #region Range case CommandType.Range: string rangeAddress; switch (command) { case RangeCommands.AddNamedRange: switch (option) { case RangeOptions.WorkbookScope: _rangeCommands.AddWorkbookNamedRange(workbook, name, target); break; case RangeOptions.WorksheetScope: _rangeCommands.AddWorksheetNamedRange(workbook.ActiveSheet, name, target); break; default: _rangeCommands.AddWorkbookNamedRange(workbook, name, target); break; } break; case RangeCommands.DeleteRangeContents: referenceType = OptionHelper.GetReferenceEnumFromString(reference); switch (option) { case RangeOptions.WorkbookScope: _rangeCommands.DeleteWorkbookRangeContents(workbook, target, referenceType); break; case RangeOptions.WorksheetScope: _rangeCommands.DeleteWorksheetRangeContents(workbook.ActiveSheet, target, referenceType); break; default: _rangeCommands.DeleteWorkbookRangeContents(workbook, target, referenceType); break; } break; case RangeCommands.RemoveNamedRange: switch (option) { case RangeOptions.WorkbookScope: _rangeCommands.RemoveWorkbookNamedRange(workbook, target); break; case RangeOptions.WorksheetScope: _rangeCommands.RemoveWorksheetNamedRange(workbook.ActiveSheet, target); break; default: _rangeCommands.RemoveWorkbookNamedRange(workbook, target); break; } break; case RangeCommands.RenameRange: switch (option) { case RangeOptions.WorkbookScope: _rangeCommands.RenameWorkbookRange(workbook, target, name); break; case RangeOptions.WorksheetScope: _rangeCommands.RenameWorksheetRange(workbook.ActiveSheet, target, name); break; default: _rangeCommands.RenameWorkbookRange(workbook, target, name); break; } break; case RangeCommands.SetNamedRange: _rangeCommands.SetNamedRangeCommand(workbook, name, target); break; case RangeCommands.GetColumnRange: rangeAddress = _rangeCommands.GetColumnRangeCommand(workbook.ActiveSheet, target); SetValue(i, auxillaryColumn, target + ":" + rangeAddress); break; case RangeCommands.GetRowRange: rangeAddress = _rangeCommands.GetRowRangeCommand(workbook.ActiveSheet, target); SetValue(i, auxillaryColumn, target + ":" + rangeAddress); break; case RangeCommands.GetDataSetRange: rangeAddress = _rangeCommands.GetDataSetRangeCommand(workbook.ActiveSheet, target); SetValue(i, auxillaryColumn, target + ":" + rangeAddress); break; } break; #endregion #region Code case CommandType.Code: switch (command) { case CodeCommands.Sub: MainBuilder runblock = null; var subName = GetString(i, targetColumn); string worksheetName; // Allows for calling sub-routines on different worksheets if (subName.Contains("!")) { var subSplit = subName.Split('!'); worksheetName = subSplit[0]; subName = subSplit[1]; runblock = new MainBuilder(workbook.GetWorksheet(worksheetName)); } else { runblock = new MainBuilder(_worksheet); } runblock.Run(false, subName); break; case CodeCommands.If: ValidateIf(i, commandColumn); var booleanValue = GetBoolean(i, targetColumn); // Skips to end if command line if false if (!booleanValue) { i = GetEndIfRow(i, commandColumn); } break; case CodeCommands.Stop: throw new Exception("Program stopped"); case CodeCommands.Repeat: ValidateRepeat(i, commandColumn); _repeatStart = i; _repeatEnd = GetEndRepeatRow(i, commandColumn); _repeatCount = GetInt(i, targetColumn); _repeatIndex = 1; SetValue(i, targetColumn + 1, _repeatIndex.ToString()); _inRepeat = true; break; } break; #endregion #region Filter case CommandType.Filter: switch (command) { case FilterCommands.AddDataFilter: var referenceInt = GetInt(i, referenceColumn); var criteriaList = GetArrayString(i, nameColumn); auxillary = GetString(i, auxillaryColumn); var filterType = OptionHelper.GetExcelAutoFilterOperatorFromString(option); _filterCommands.AddDataFilterCommand(workbook.ActiveSheet, target, referenceInt, filterType, criteriaList, auxillary); break; case FilterCommands.ClearDataFilters: _filterCommands.ClearDataFiltersCommand(workbook.ActiveSheet); break; case FilterCommands.DeleteDataFilters: _filterCommands.DeleteDataFiltersCommand(workbook.ActiveSheet); break; case "TEST": break; } break; #endregion #region Data case CommandType.Data: switch (command) { case DataCommands.SetValue: _dataCommands.SetValueCommand(workbook.ActiveSheet, target, auxillary); break; case DataCommands.FindAndReplace: bool matchCase = GetBoolean(i, referenceColumn); _dataCommands.FindAndReplaceCommand(workbook.ActiveSheet, target, name, auxillary, option, matchCase); break; } break; #endregion } i++; if (_inRepeat) { if (i == _repeatEnd) { if (_repeatIndex == _repeatCount) { i = _repeatEnd + 1; _repeatStart = 0; _repeatEnd = 0; _repeatCount = 0; _repeatIndex = 0; _inRepeat = false; } else { i = _repeatStart + 1; _repeatIndex++; SetValue(_repeatStart, targetColumn + 1, _repeatIndex.ToString()); } } } } return(new List <string>()); }
/// <summary> /// Make the worksheet with the given index the active sheet in the workbook /// </summary> /// <param name="workbook">Target workbook</param> /// <param name="index">Worksheet index</param> public static void ActivateSheet(this Excel.Workbook workbook, int index) { var worksheet = workbook.GetWorksheet(index); worksheet.Activate(); }
/// <summary> /// Make the worksheet with the given name the active sheet in the workbook /// </summary> /// <param name="workbook">Target workbook</param> /// <param name="name">Worksheet name</param> /// <returns></returns> public static void ActivateSheet(this Excel.Workbook workbook, string name) { var worksheet = workbook.GetWorksheet(name); worksheet.Activate(); }
/// <summary> /// Moves worksheet to desired location within the current workbook /// </summary> /// <param name="worksheet">Worksheet being moved</param> /// <param name="workbook">Active workbook the worksheet exists in</param> /// <param name="positional">Desired position of worksheet. Default position is the end of the workbook.</param> /// <param name="referenceName">Name of worksheet position is relative to</param> /// <param name="referenceType">If relative worksheet is referenced through name or index</param> public static void MoveWorksheet(this Excel.Workbook workbook, string worksheetName, PositionalEnum positional, string referenceName, ReferenceEnum referenceType = ReferenceEnum.ByName) { Excel.Worksheet worksheet = null; var sheetCount = workbook.Worksheets.Count; int indexValue = 1; bool isAnInteger = false; // Checks if target sheet exists if (!workbook.WorksheetExists(worksheetName)) { throw new ArgumentException($"Worksheet [{worksheetName}] does not exist in the workbook"); } worksheet = workbook.GetWorksheet(worksheetName); // If relative sheet for positioning is required if (positional != PositionalEnum.AtBeginning && positional != PositionalEnum.AtEnd) { if (string.IsNullOrEmpty(referenceName)) { throw new ArgumentException("A worksheet reference is required due to selected option"); } } if (referenceType == ReferenceEnum.ByIndex) { // Test if sheet name/index is an integer value isAnInteger = int.TryParse(referenceName, out indexValue); if (isAnInteger == false) { throw new ArgumentException("Index value given is either not a string or is not an integer"); } // Check index value exists within the current workbook if (indexValue <= 0 || indexValue > sheetCount) { throw new ArgumentException("Index value is out of range"); } if (worksheet.Index == indexValue) { throw new ArgumentException("Cannot move a worksheet when referencing its own index"); } } else { // If relative worksheet name is needed if (positional != PositionalEnum.AtBeginning && positional != PositionalEnum.AtEnd) { // If relative worksheet does not exist if (!workbook.WorksheetExists(referenceName)) { throw new ArgumentException("Relative worksheet name does not exist"); } } } // Moves worksheet to desired position switch (positional) { case PositionalEnum.AtBeginning: worksheet.Move(workbook.Worksheets[1]); break; case PositionalEnum.AtEnd: worksheet.Move(After: workbook.Worksheets[sheetCount]); break; case PositionalEnum.After: if (referenceType == ReferenceEnum.ByIndex && isAnInteger == true) { worksheet.Move(After: workbook.Worksheets[indexValue]); } else { worksheet.Move(After: workbook.Worksheets[$"{referenceName}"]); } break; case PositionalEnum.Before: if (referenceType == ReferenceEnum.ByIndex && isAnInteger == true) { worksheet.Move(workbook.Worksheets[indexValue]); } else { worksheet.Move(workbook.Worksheets[$"{referenceName}"]); } break; } }