/// <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]);
}
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");
}
}