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