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