/// <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);
}
}
}
}
public void SetNamedRangeCommand(Excel.Workbook workbook, string name, string range)
{
if (workbook.NamedRangeExists(name))
{
if (workbook.IsRange(range))
{
workbook.GetNamedRange(name).RefersToLocal = "=" + range;
}
else
{
throw new ArgumentException($"Range entered {range} is not valid");
}
}
else
{
throw new ArgumentException($"Named range {name} does not exist");
}
}
/// <summary>
/// Renames a range in the workbook
/// </summary>
/// <param name="workbook">Target workbook</param>
/// <param name="oldName">Old range name</param>
/// <param name="newName">New range name</param>
public static void RenameRange(this Excel.Workbook workbook, string oldName, string newName)
{
if (workbook.NamedRangeExists(oldName))
{
if (!workbook.NamedRangeExists(newName))
{
var item = workbook.GetNamedRange(oldName);
item.Name = newName;
}
else
{
throw new ArgumentException($"Name {newName} already exists");
}
}
else
{
throw new ArgumentException($"Range {oldName} does not exist");
}
}
/// <summary>
/// Removes named range from workbook scope
/// </summary>
/// <param name="workbook"></param>
/// <param name="name"></param>
public static void RemoveNamedRange(this Excel.Workbook workbook, string name)
{
workbook.GetNamedRange(name).Delete();
}