/// <summary> /// Autofits rows for a range. /// </summary> /// <param name="range">The range.</param> /// <param name="autofitRows">Use true to autofit rows.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetAutofitRows( this Range range, bool?autofitRows) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if ((autofitRows != null) && (bool)autofitRows) { var columnNumbers = range.GetColumnNumbers(); var minColumnNumber = columnNumbers.Min(); var maxColumnNumber = columnNumbers.Max(); foreach (var rowNumber in range.GetRowNumbers()) { var rowRangeCells = range.Worksheet.GetRange(rowNumber, rowNumber, minColumnNumber, maxColumnNumber).GetCells(); if (rowRangeCells.Any(_ => _.IsMerged)) { var autoFitterOptions = new AutoFitterOptions { AutoFitMergedCells = true }; range.Worksheet.AutoFitRow(rowNumber - 1, minColumnNumber - 1, maxColumnNumber - 1, autoFitterOptions); } else { range.Worksheet.AutoFitRow(rowNumber - 1); } } } }
/// <summary> /// Creates an auto-filter on the specified range and /// freezes the top row of that range. /// </summary> /// <param name="range">The range.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetAutoFilterAndFreezeTopRow( this Range range) { if (range == null) { throw new ArgumentNullException(nameof(range)); } range.SetAutoFilter(); var cellToFreezeAt = range.Worksheet.Cells[range.GetRowNumbers().First(), 0]; cellToFreezeAt.SetFreezePanes(PaneKinds.Row); }
/// <summary> /// Sets the per-row height for a range, in pixels. /// </summary> /// <param name="range">The range.</param> /// <param name="rowHeightInPixels">The row height, in pixels.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetPerRowHeightInPixels( this Range range, int?rowHeightInPixels) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if (rowHeightInPixels != null) { foreach (var rowNumber in range.GetRowNumbers()) { range.Worksheet.Cells.SetRowHeightPixel(rowNumber - 1, (int)rowHeightInPixels); } } }
/// <summary> /// Groups the rows in the range. /// </summary> /// <param name="range">The range.</param> /// <param name="collapseGroup">Optional value indicating whether to collapse the group. Default is false; the group will be expanded.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetGroupRows( this Range range, bool collapseGroup = false) { if (range == null) { throw new ArgumentNullException(nameof(range)); } var rowNumbers = range.GetRowNumbers(); if (rowNumbers.Count == 1) { throw new ArgumentOutOfRangeException(nameof(range), "There are too few rows specified in the range. To group rows, at least 2 rows must be specified."); } range.Worksheet.Cells.GroupRows(rowNumbers.First() - 1, rowNumbers.Last() - 1 - 1, collapseGroup); }
/// <summary> /// Sets the total height for a range, in pixels. /// </summary> /// <param name="range">The range.</param> /// <param name="totalHeightInPixels">The total height, in pixels.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetTotalRowHeightInPixels( this Range range, int?totalHeightInPixels) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if (totalHeightInPixels != null) { var rowNumbers = range.GetRowNumbers(); var pixelsToUse = DivideEvenly((int)totalHeightInPixels, rowNumbers.Count).ToArray(); for (var x = 0; x < rowNumbers.Count; x++) { range.Worksheet.Cells.SetRowHeightPixel(rowNumbers[x] - 1, pixelsToUse[x]); } } }
/// <summary> /// Gets the cell area for a range. /// </summary> /// <param name="range">The range.</param> /// <returns> /// The cell area that covers the specified range. /// </returns> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static CellArea GetCellArea( this Range range) { if (range == null) { throw new ArgumentNullException(nameof(range)); } var rowNumbers = range.GetRowNumbers(); var columnNumbers = range.GetColumnNumbers(); var result = new CellArea { StartRow = rowNumbers.First() - 1, EndRow = rowNumbers.Last() - 1, StartColumn = columnNumbers.First() - 1, EndColumn = columnNumbers.Last() - 1, }; return(result); }
/// <summary> /// Gets the individual cells within a range. /// </summary> /// <param name="range">The range.</param> /// <returns> /// The individual cells within the specified range. /// </returns> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static IReadOnlyCollection <Cell> GetCells( this Range range) { if (range == null) { throw new ArgumentNullException(nameof(range)); } var result = new List <Cell>(); var rowNumbers = range.GetRowNumbers(); var columnNumbers = range.GetColumnNumbers(); foreach (var rowNumber in rowNumbers) { foreach (var columnNumber in columnNumbers) { var cell = range.Worksheet.GetCell(rowNumber, columnNumber); result.Add(cell); } } return(result); }
/// <summary> /// Gets the name of the range (e.g. A3:B5). /// </summary> /// <param name="range">The range.</param> /// <returns> /// The name of the range (e.g. A3:B5). /// </returns> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static string GetName( this Range range) { if (range == null) { throw new ArgumentNullException(nameof(range)); } var rowNumbers = range.GetRowNumbers(); var columnNumbers = range.GetColumnNumbers(); string result; if ((rowNumbers.Count == 1) && (columnNumbers.Count == 1)) { result = CellsHelper.CellIndexToName(rowNumbers.First() - 1, columnNumbers.First() - 1); } else { result = Invariant($"{CellsHelper.CellIndexToName(rowNumbers.First() - 1, columnNumbers.First() - 1)}:{CellsHelper.CellIndexToName(rowNumbers.Last() - 1, columnNumbers.Last() - 1)}"); } return(result); }