/// <summary>
/// Returns the range of data that exists in the row. Range will start at the parameter cell and end before the first empty column it encounters
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="range">Singular cell in the desired column</param>
/// <returns>Excel range</returns>
public static Excel.Range GetRowRange(this Excel.Worksheet worksheet, string range)
{
if (worksheet.IsRange(range))
{
if (worksheet.Range[range].IsSingularCell())
{
int rangeRowNumber = worksheet.Range[range].Row;
int lastColumn = worksheet.Range[range].Column;
while (!string.IsNullOrEmpty(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn)}" + $"{rangeRowNumber}"].Value))
{
lastColumn++;
}
return(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn - 1)}" + $"{rangeRowNumber}"]);
}
else
{
throw new Exception("Please ensure only one cell is referenced to indicate the range");
}
}
else
{
throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}");
}
}
/// <summary>
/// Method for passing in validation to command rows
/// </summary>
/// <param name="commandType">Command type value</param>
/// <param name="command">Command named range</param>
/// <param name="options">Options named range</param>
/// <param name="reference">Reference named range</param>
private void AddRowValidation(string commandType, string command, string options = null, string reference = null)
{
Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet worksheet = workbook.ActiveSheet;
Excel.Range range = Globals.ThisAddIn.Application.ActiveCell;
//Checks if commands page exists
if (!workbook.WorksheetExists("Commands"))
{
MessageBox.Show("Please run 'Add Commands' and then try again.");
}
// Get row and next column indices
var thisColumn = WorksheetHelper.GetColumnName(range.Column);
var thisRow = range.Row;
Excel.Range startRange = worksheet.Range[$"{thisColumn}" + $"{thisRow}"];
//Clears any previous validation on the row
string tempColumn;
for (int i = 0; i < 6; i++)
{
tempColumn = WorksheetHelper.GetColumnName(range.Column + i);
worksheet.Range[$"{tempColumn}" + $"{thisRow}"].Validation.Delete();
}
// Command type column
range.Value = commandType;
// Gets next column index
thisColumn = WorksheetHelper.GetColumnName(range.Column + 1);
// Command column
range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"];
range.AddDropDownList(command);
// Gets next column index
thisColumn = WorksheetHelper.GetColumnName(range.Column + 1);
// Options column
if (!string.IsNullOrEmpty(options))
{
range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"];
range.AddDropDownList(options);
// Gets next column index
thisColumn = WorksheetHelper.GetColumnName(range.Column + 1);
if (!string.IsNullOrEmpty(reference))
{
// Reference column
range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"];
range.AddDropDownList(nameof(ReferenceOptions));
}
}
}