/// <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> /// Sets the per-column width for a range, in pixels. /// </summary> /// <param name="range">The range.</param> /// <param name="columnWidthInPixels">The column width, in pixels.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetPerColumnWidthInPixels( this Range range, int?columnWidthInPixels) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if (columnWidthInPixels != null) { foreach (var columnNumber in range.GetColumnNumbers()) { range.Worksheet.Cells.SetColumnWidthPixel(columnNumber - 1, (int)columnWidthInPixels); } } }
/// <summary> /// Groups the columns 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 SetGroupColumns( this Range range, bool collapseGroup = false) { if (range == null) { throw new ArgumentNullException(nameof(range)); } var columnNumbers = range.GetColumnNumbers(); if (columnNumbers.Count == 1) { throw new ArgumentOutOfRangeException(nameof(range), "There are too few columns specified in the range. To group columns, at least 2 columns must be specified."); } range.Worksheet.Cells.GroupColumns(columnNumbers.First() - 1, columnNumbers.Last() - 1 - 1, collapseGroup); }
/// <summary> /// Sets the total width for a range, in pixels. /// </summary> /// <param name="range">The range.</param> /// <param name="totalWidthInPixels">The total width, in pixels.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetTotalColumnWidthInPixels( this Range range, int?totalWidthInPixels) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if (totalWidthInPixels != null) { var columnNumbers = range.GetColumnNumbers(); var pixelsToUse = DivideEvenly((int)totalWidthInPixels, columnNumbers.Count).ToArray(); for (var x = 0; x < columnNumbers.Count; x++) { range.Worksheet.Cells.SetColumnWidthPixel(columnNumbers[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); }