/// <summary> /// Returns the range of data that exists in the row. Range will start at the parameter cell and end before the first empty column it encounters /// </summary> /// <param name="worksheet">Target worksheet</param> /// <param name="range">Singular cell in the desired column</param> /// <returns>Excel range</returns> public static Excel.Range GetRowRange(this Excel.Worksheet worksheet, string range) { if (worksheet.IsRange(range)) { if (worksheet.Range[range].IsSingularCell()) { int rangeRowNumber = worksheet.Range[range].Row; int lastColumn = worksheet.Range[range].Column; while (!string.IsNullOrEmpty(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn)}" + $"{rangeRowNumber}"].Value)) { lastColumn++; } return(worksheet.Range[$"{WorksheetHelper.GetColumnName(lastColumn - 1)}" + $"{rangeRowNumber}"]); } else { throw new Exception("Please ensure only one cell is referenced to indicate the range"); } } else { throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}"); } }
public void AddDataFilterCommand(Excel.Worksheet worksheet, string range, int columnNumber, Excel.XlAutoFilterOperator filterEnum, string[] criteria = null, string criteria2 = null) { if (!worksheet.IsRange(range)) { throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}"); } // ExcelParse class returns 0 as integer value for blank cells and column indices start at 1 for Excel if (columnNumber == 0) { columnNumber = 1; } if (criteria != null && criteria.Length > 0) { if (string.IsNullOrEmpty(criteria2)) { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria); } else { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria, criteria2); } } else { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum); } }
/// <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> /// Moves a column within the current worksheet /// </summary> /// <param name="worksheet">Target worksheet</param> /// <param name="oldRange">Singular cell range within the target column</param> /// <param name="newRange">Singular cell range within the column the target is to be moved left of</param> public static void MoveColumn(this Excel.Worksheet worksheet, string oldRange, string newRange) { if (string.IsNullOrEmpty(oldRange) || string.IsNullOrEmpty(newRange)) { throw new Exception($"The old or newly specified range is empty. Please specify which column is moving and where you would like to move it to."); } Excel.Range copyRange; Excel.Range insertRange; if (worksheet.IsRange(oldRange)) { if (worksheet.Range[oldRange].IsSingularCell()) { if (worksheet.IsRange(newRange)) { if (worksheet.Range[newRange].IsSingularCell()) { copyRange = worksheet.Range[oldRange].EntireColumn; insertRange = worksheet.Range[newRange].EntireColumn; insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, copyRange.Cut()); } else { throw new Exception($"The column target location, {newRange}, does not specify one cell. Please verify one cell is selected in the desired column location."); } } else { throw new Exception($"The column target location, {newRange}, is not a valid range. Please specify the range exists on the current worksheet and uses the correct formatting."); } } else { throw new Exception($"The targeted column range, {oldRange}, does not specify one cell. Please verify one cell is selected in the targeted column location."); } } else { throw new Exception($"The targeted column range, {oldRange}, is not a valid range. Please specify the range exists on the current worksheet and uses the correct formatting."); } }
public void SetValueCommand(Excel.Worksheet worksheet, string range, string value) { if (worksheet.IsRange(range)) { worksheet.Range[range].Value = value; } else { throw new Exception($"The range, {range}, does not exist on the current worksheet, {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}"]); } }
public void FindAndReplaceCommand(Excel.Worksheet worksheet, string range, string oldText, string newText, string matchValue, bool matchCase) { if (worksheet.IsRange(range)) { if (matchValue == MatchValueOptions.MatchAll) { worksheet.Range[range].FindAndReplace(oldText, newText, true, matchCase); } else { worksheet.Range[range].FindAndReplace(oldText, newText, false, matchCase); } } else { throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}"); } }
/// <summary> /// Adds a row to the worksheet /// </summary> /// <param name="worksheet"></param> /// <param name="range">Range of a single cell on the sheet</param> public static void AddRow(this Excel.Worksheet worksheet, string range) { if (string.IsNullOrEmpty(range)) { throw new Exception($"Please enter a value for the range that is not null or empty"); } if (worksheet.IsRange(range)) { if (worksheet.Range[range].IsSingularCell()) { worksheet.Range[range].EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); } else { throw new Exception("Please ensure only one cell is reference to indicate the row"); } } else { throw new Exception($"The range {range} is not a valid range on worksheet {worksheet.Name}"); } }
/// <summary> /// Deletes a column from the worksheet /// </summary> /// <param name="worksheet">Target worksheet</param> /// <param name="range">Singular cell range within the target column</param> public static void DeleteColumn(this Excel.Worksheet worksheet, string range) { if (string.IsNullOrEmpty(range)) { throw new Exception($"Please enter a value for the range that is not null or empty"); } if (worksheet.IsRange(range)) { if (worksheet.Range[range].IsSingularCell()) { worksheet.Range[range].EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft); } else { throw new Exception("Please ensure only one cell is reference to indicate the column"); } } else { throw new Exception($"The range {range} is not a valid range on worksheet {worksheet.Name}"); } }