/// <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>
/// Will clear the range of cells including formats
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="name">Name of range</param>
public static void ClearNamedRange(this Excel.Worksheet worksheet, string name)
{
if (worksheet.NamedRangeExists(name))
{
worksheet.Range[name].Clear();
}
}
/// <summary>
/// Renames a range on the worksheet
/// </summary>
/// <param name="worksheet">Target worksheet</param>
/// <param name="oldName">Old range name</param>
/// <param name="newName">New range name</param>
public static void RenameRange(this Excel.Worksheet worksheet, string oldName, string newName)
{
// Checks target range exists
if (worksheet.NamedRangeExists(oldName))
{
// Checks if new name already exists
if (!worksheet.NamedRangeExists(newName))
{
worksheet.Range[$"{oldName}"].Name = newName;
}
else
{
throw new ArgumentException($"New range name {newName} already exists. Please rename to something else.");
}
}
else
{
throw new ArgumentException($"Range {oldName} does not exist on {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}"]);
}
}
