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