/// <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;
}
}
