/// <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}");
}
}
public void AddDataFilterCommand(Excel.Worksheet worksheet, string range, int columnNumber, Excel.XlAutoFilterOperator filterEnum, string[] criteria = null, string criteria2 = null)
{
if (!worksheet.IsRange(range))
{
throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}");
}
// ExcelParse class returns 0 as integer value for blank cells and column indices start at 1 for Excel
if (columnNumber == 0)
{
columnNumber = 1;
}
if (criteria != null && criteria.Length > 0)
{
if (string.IsNullOrEmpty(criteria2))
{
worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria);
}
else
{
worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria, criteria2);
}
}
else
{
worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum);
}
}
/// <summary>
/// Deletes the worksheet's range contents
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="range">Range. Can either be name or range of cells. If not the name, declare next parameter as false</param>
/// <param name="isNamedRange">Flags if range param is the name of the range</param>
public static void DeleteRangeContents(this Excel.Worksheet worksheet, string range, bool isNamedRange = true)
{
if (isNamedRange)
{
if (worksheet.NamedRangeExists(range))
{
worksheet.Range[$"{range}"].Cells.ClearContents();
}
else
{
throw new ArgumentException($"Range, [{range}], does not exist");
}
}
else
{
if (worksheet.IsRange(range))
{
worksheet.Range[$"{range}"].Cells.ClearContents();
}
else
{
throw new ArgumentException($"Range, [{range}], is not a valid range");
}
}
}
/// <summary>
/// Moves a column within the current worksheet
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="oldRange">Singular cell range within the target column</param>
/// <param name="newRange">Singular cell range within the column the target is to be moved left of</param>
public static void MoveColumn(this Excel.Worksheet worksheet, string oldRange, string newRange)
{
if (string.IsNullOrEmpty(oldRange) || string.IsNullOrEmpty(newRange))
{
throw new Exception($"The old or newly specified range is empty. Please specify which column is moving and where you would like to move it to.");
}
Excel.Range copyRange;
Excel.Range insertRange;
if (worksheet.IsRange(oldRange))
{
if (worksheet.Range[oldRange].IsSingularCell())
{
if (worksheet.IsRange(newRange))
{
if (worksheet.Range[newRange].IsSingularCell())
{
copyRange = worksheet.Range[oldRange].EntireColumn;
insertRange = worksheet.Range[newRange].EntireColumn;
insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, copyRange.Cut());
}
else
{
throw new Exception($"The column target location, {newRange}, does not specify one cell. Please verify one cell is selected in the desired column location.");
}
}
else
{
throw new Exception($"The column target location, {newRange}, is not a valid range. Please specify the range exists on the current worksheet and uses the correct formatting.");
}
}
else
{
throw new Exception($"The targeted column range, {oldRange}, does not specify one cell. Please verify one cell is selected in the targeted column location.");
}
}
else
{
throw new Exception($"The targeted column range, {oldRange}, is not a valid range. Please specify the range exists on the current worksheet and uses the correct formatting.");
}
}
public void SetValueCommand(Excel.Worksheet worksheet, string range, string value)
{
if (worksheet.IsRange(range))
{
worksheet.Range[range].Value = value;
}
else
{
throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}");
}
}
/// <summary>
/// Creates a named range on the worksheet scope
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="name">Name of range being created</param>
/// <param name="range">Target range. Must be within the current worksheet</param>
public static void CreateNamedRange(this Excel.Worksheet worksheet, string name, string range)
{
// If cell range isn't valid
if (!worksheet.IsRange(range))
{
throw new ArgumentException($"Range entered {range} is not a valid range or does not exist within the current worksheet");
}
// If named range exists
if (worksheet.NamedRangeExists(name))
{
throw new ArgumentException($"Name {name} already exists");
}
else
{
worksheet.Names.Add(name, worksheet.Range[$"{range}"]);
}
}
public void FindAndReplaceCommand(Excel.Worksheet worksheet, string range, string oldText, string newText, string matchValue, bool matchCase)
{
if (worksheet.IsRange(range))
{
if (matchValue == MatchValueOptions.MatchAll)
{
worksheet.Range[range].FindAndReplace(oldText, newText, true, matchCase);
}
else
{
worksheet.Range[range].FindAndReplace(oldText, newText, false, matchCase);
}
}
else
{
throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}");
}
}
/// <summary>
/// Adds a row to the worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="range">Range of a single cell on the sheet</param>
public static void AddRow(this Excel.Worksheet worksheet, string range)
{
if (string.IsNullOrEmpty(range))
{
throw new Exception($"Please enter a value for the range that is not null or empty");
}
if (worksheet.IsRange(range))
{
if (worksheet.Range[range].IsSingularCell())
{
worksheet.Range[range].EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
else
{
throw new Exception("Please ensure only one cell is reference to indicate the row");
}
}
else
{
throw new Exception($"The range {range} is not a valid range on worksheet {worksheet.Name}");
}
}
/// <summary>
/// Deletes a column from the worksheet
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="range">Singular cell range within the target column</param>
public static void DeleteColumn(this Excel.Worksheet worksheet, string range)
{
if (string.IsNullOrEmpty(range))
{
throw new Exception($"Please enter a value for the range that is not null or empty");
}
if (worksheet.IsRange(range))
{
if (worksheet.Range[range].IsSingularCell())
{
worksheet.Range[range].EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
else
{
throw new Exception("Please ensure only one cell is reference to indicate the column");
}
}
else
{
throw new Exception($"The range {range} is not a valid range on worksheet {worksheet.Name}");
}
}
