internal static void CopyConditionalFormatsFrom(this IXLRangeBase targetRange, IXLRangeBase srcRange)
{
var sheet = targetRange.Worksheet;
sheet.SuspendEvents();
foreach (var conditionalFormat in sheet.ConditionalFormats.Where(c => c.Range.Intersects(srcRange)).ToList())
{
conditionalFormat.CopyRelative(srcRange, targetRange, false);
}
sheet.ResumeEvents();
}
internal static IXLRange ShiftRows(this IXLRangeBase range, int rowCount)
{
var firstAddress = range.RangeAddress.FirstAddress;
var lastAddress = range.RangeAddress.LastAddress;
return(range.Worksheet.Range(
firstAddress.RowNumber + rowCount,
firstAddress.ColumnNumber,
lastAddress.RowNumber + rowCount,
lastAddress.ColumnNumber));
}
internal static void CopyRelative(this IXLConditionalFormat format, IXLRangeBase fromRange, IXLRangeBase toRange, bool expand)
{
var frmtRng = Intersection(format.Range, fromRange).Relative(fromRange, toRange);
if (expand && toRange.RangeAddress.RowCount() != format.Range.RowCount())
{
frmtRng = frmtRng.Offset(0, 0, toRange.RangeAddress.RowCount(), frmtRng.ColumnCount()).Unsubscribed();
}
var newFrmt = frmtRng.AddConditionalFormat();
newFrmt.CopyFrom(format);
}
internal static void ExtendRows(this IXLRangeBase range, int rowCount, bool down = true)
{
if (down)
{
var lastAddress = range.RangeAddress.LastAddress;
range.RangeAddress.LastAddress = range.Worksheet.Cell(lastAddress.RowNumber + rowCount, lastAddress.ColumnNumber).Address;
}
else
{
var firstAddress = range.RangeAddress.FirstAddress;
range.RangeAddress.FirstAddress = range.Worksheet.Cell(firstAddress.RowNumber - rowCount, firstAddress.ColumnNumber).Address;
}
}
/// <summary>
/// Get range relative to another range.
/// </summary>
/// <param name="range">range</param>
/// <param name="baseRange">Coordinate system. Coordinates are calculated relative to this range.</param>
/// <param name="targetBase"></param>
public static IXLRange Relative(this IXLRangeBase range, IXLRangeBase baseRange, IXLRangeBase targetBase)
{
using (var xlRange = targetBase.Worksheet.Range(
range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1,
range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1,
range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1,
range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1))
{
var type = targetBase.GetType();
var method = type.GetMethod("Range", new[] { typeof(IXLRangeAddress) });
return((IXLRange)method.Invoke(targetBase, new object[] { xlRange.RangeAddress }));
//return targetBase.Range(xlRange.RangeAddress);
}
}
public static void CopyStylesFrom(this IXLRangeBase trgtRow, IXLRangeBase srcRow)
{
trgtRow.Style = srcRow.Style;
var srcCells = srcRow.Cells(true, XLCellsUsedOptions.All).ToArray();
for (int i = 0; i < srcCells.Length; i++)
{
var rela = srcCells[i].Relative(srcRow.RangeAddress.FirstAddress);
var trgtCell = trgtRow.RangeAddress.FirstAddress.Offset(rela);
trgtCell.Style = srcCells[i].Style;
//trgtCells[i].Style = srcCells[i].Style;
}
//trgtRow.CopyConditionalFormatsFrom(srcRow);
}
public bool Add(IXLRangeBase range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (!range.RangeAddress.IsValid)
{
throw new ArgumentException("Range is invalid");
}
CheckWorksheet(range.Worksheet);
return(_quadTree.Add(range));
}
internal static IXLRangeBase Intersection(IXLRangeBase range, IXLRangeBase crop)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (crop == null || !range.Intersects(crop))
{
return(null);
}
return(range.Worksheet.Range(
Math.Max(range.RangeAddress.FirstAddress.RowNumber, crop.RangeAddress.FirstAddress.RowNumber),
Math.Max(range.RangeAddress.FirstAddress.ColumnNumber, crop.RangeAddress.FirstAddress.ColumnNumber),
Math.Min(range.RangeAddress.LastAddress.RowNumber, crop.RangeAddress.LastAddress.RowNumber),
Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber)));
}
internal static void CopyRelative(this IXLConditionalFormat format, IXLRangeBase fromRange, IXLRangeBase toRange, bool expand)
{
foreach (var sourceFmtRange in format.Ranges)
{
var frmtRng = Intersection(sourceFmtRange, fromRange)
.Relative(fromRange, toRange)
as IXLRange;
if (expand &&
frmtRng.RangeAddress.RowCount() == fromRange.RangeAddress.RowCount() &&
frmtRng.RangeAddress.RowCount() != toRange.RangeAddress.RowCount())
{
frmtRng = frmtRng.Offset(0, 0, toRange.RangeAddress.RowCount(), frmtRng.ColumnCount());
}
var newFrmt = frmtRng.AddConditionalFormat();
newFrmt.CopyFrom(format);
}
}
public new IXLRange CopyTo(IXLRangeBase target)
{
base.CopyTo(target);
Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + RowCount() - 1;
if (lastRowNumber > XLHelper.MaxRowNumber)
{
lastRowNumber = XLHelper.MaxRowNumber;
}
Int32 lastColumnNumber = target.RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1;
if (lastColumnNumber > XLHelper.MaxColumnNumber)
{
lastColumnNumber = XLHelper.MaxColumnNumber;
}
return(target.Worksheet.Range(target.RangeAddress.FirstAddress.RowNumber,
target.RangeAddress.FirstAddress.ColumnNumber,
lastRowNumber,
lastColumnNumber));
}
public IXLNamedRange SetRefersTo(IXLRangeBase range)
{
_rangeList.Clear();
_rangeList.Add(range.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true));
return this;
}
public XLHyperlink(IXLRangeBase range, String tooltip)
{
SetValues(range, tooltip);
}
public void AddConditionalFormats(IEnumerable <IXLConditionalFormat> formats, IXLRangeBase fromRange, IXLRangeBase toRange)
{
foreach (var format in formats)
{
format.CopyRelative(fromRange, toRange, true);
}
}
public XLAutoFilter Set(IXLRangeBase range)
{
Range = range.AsRange();
Enabled = true;
return this;
}
IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range)
{
return(Set(range));
}
public void Add(IXLRangeBase range)
{
Count++;
_ranges.Add(range.AsRange() as XLRange);
}
public XLHyperlink(IXLRangeBase range, String tooltip)
{
SetValues(range, tooltip);
}
public IXLNamedRange SetRefersTo(IXLRangeBase range)
{
_rangeList.Clear();
_rangeList.Add(range.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true));
return(this);
}
public XLHyperlink(IXLRangeBase range)
{
SetValues(range, String.Empty);
}
public void AddConditionalFormats(IEnumerable <IXLConditionalFormat> formats, IXLRangeBase fromRange, IXLRangeBase toRange)
{
//var tempRng = _sheet.Range(_sheet.Cell(1, 1), _sheet.Cell(_prevrow, _prevclmn));
foreach (var format in formats)
{
format.CopyRelative(fromRange, toRange, true);
}
}
IXLRangeRow IXLRow.CopyTo(IXLRangeBase target)
{
var copy = AsRange().CopyTo(target);
return(copy.Row(1));
}
public static IXLCells CellsUsedWithoutFormulas(this IXLRangeBase range)
{
return(range.CellsUsed(c => !c.HasFormula));
}
internal void SetValues(IXLRangeBase range, String tooltip)
{
Tooltip = tooltip;
_internalAddress = range.RangeAddress.ToString(XLReferenceStyle.A1, true);
IsExternal = false;
}
IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target)
{
var copy = AsRange().CopyTo(target);
return(copy.Column(1));
}
IXLRangeRow IXLRow.CopyTo(IXLRangeBase target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return(copy.Row(1));
}
public new IXLRange CopyTo(IXLRangeBase target)
{
base.CopyTo(target);
Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + RowCount() - 1;
if (lastRowNumber > XLHelper.MaxRowNumber)
lastRowNumber = XLHelper.MaxRowNumber;
Int32 lastColumnNumber = target.RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1;
if (lastColumnNumber > XLHelper.MaxColumnNumber)
lastColumnNumber = XLHelper.MaxColumnNumber;
return target.Worksheet.Range(target.RangeAddress.FirstAddress.RowNumber,
target.RangeAddress.FirstAddress.ColumnNumber,
lastRowNumber,
lastColumnNumber);
}
internal void SetValues(IXLRangeBase range, String tooltip)
{
Tooltip = tooltip;
_internalAddress = range.RangeAddress.ToString();
IsExternal = false;
}
public XLAutoFilter Set(IXLRangeBase range)
{
Range = range.AsRange();
Enabled = true;
return(this);
}
/// <summary>
/// Find all sparklines located in a given range
/// </summary>
/// <param name="searchRange">The range to search</param>
/// <returns>The sparkline in the cell or null if no sparklines are found</returns>
public IEnumerable <IXLSparkline> GetSparklines(IXLRangeBase searchRange)
{
return(_sparklineGroups
.SelectMany(g => g.GetSparklines(searchRange)));
}
IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range)
{
return Set(range);
}
public static IXLCells CellsUsedWithoutFormulas(this IXLRangeBase range, XLCellsUsedOptions options)
{
return(range.CellsUsed(options, c => !c.HasFormula));
}
public XLHyperlink(IXLRangeBase range)
{
SetValues(range, String.Empty);
}
public static IXLCells CellsUsedWithoutFormulas(this IXLRangeBase range, XLCellsUsedOptions options, Func <IXLCell, bool> predicate)
{
return(range.CellsUsed(options, c => predicate(c) && !c.HasFormula));
}
public void Add(IXLRangeBase range)
{
Add(range.AsRange() as XLRange);
}
public static IXLCells CellsWithoutFormulas(this IXLRangeBase range, Func <IXLCell, bool> predicate)
{
return(range.Cells(c => predicate(c) && !c.HasFormula));
}
IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return(copy.Column(1));
}
internal void SetValues(IXLRangeBase range, String tooltip)
{
Tooltip = tooltip;
_internalAddress = range.RangeAddress.ToString();
IsExternal = false;
}
IXLRangeRow IXLRow.CopyTo(IXLRangeBase target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return copy.Row(1);
}
IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return copy.Column(1);
}