public void CopyFrom(IXLDataValidation dataValidation)
{
if (dataValidation == this)
{
return;
}
if (Ranges == null && dataValidation.Ranges != null)
{
Ranges = new XLRanges();
dataValidation.Ranges.ForEach(r => Ranges.Add(r));
}
IgnoreBlanks = dataValidation.IgnoreBlanks;
InCellDropdown = dataValidation.InCellDropdown;
ShowErrorMessage = dataValidation.ShowErrorMessage;
ShowInputMessage = dataValidation.ShowInputMessage;
InputTitle = dataValidation.InputTitle;
InputMessage = dataValidation.InputMessage;
ErrorTitle = dataValidation.ErrorTitle;
ErrorMessage = dataValidation.ErrorMessage;
ErrorStyle = dataValidation.ErrorStyle;
AllowedValues = dataValidation.AllowedValues;
Operator = dataValidation.Operator;
MinValue = dataValidation.MinValue;
MaxValue = dataValidation.MaxValue;
}
public IXLRanges Ranges(String ranges)
{
var retVal = new XLRanges();
var rangePairs = ranges.Split(',');
foreach (var range in rangePairs.Select(r => Range(r.Trim())).Where(range => range != null))
{
retVal.Add(range);
}
return(retVal);
}
public IXLRanges Add(XLWorkbook workbook, String rangeAddress)
{
var ranges = new XLRanges();
var byExclamation = rangeAddress.Split('!');
var wsName = byExclamation[0].Replace("'", "");
var rng = byExclamation[1];
var rangeToAdd = workbook.WorksheetsInternal.Worksheet(wsName).Range(rng);
ranges.Add(rangeToAdd);
return(Add(ranges));
}
public IXLNamedRange CopyTo(IXLWorksheet targetSheet)
{
if (targetSheet == _namedRanges.Worksheet)
{
throw new InvalidOperationException("Cannot copy named range to the worksheet it already belongs to.");
}
var ranges = new XLRanges();
foreach (var r in Ranges)
{
if (_namedRanges.Worksheet == r.Worksheet)
{
// Named ranges on the source worksheet have to point to the new destination sheet
ranges.Add(targetSheet.Range(((XLRangeAddress)r.RangeAddress).WithoutWorksheet()));
}
else
{
ranges.Add(r);
}
}
return(targetSheet.NamedRanges.Add(Name, ranges));
}
/// <summary>
/// Add a range to the collection of ranges this rule applies to.
/// If the specified range does not belong to the worksheet of the data validation
/// rule it is transferred to the target worksheet.
/// </summary>
/// <param name="range">A range to add.</param>
public void AddRange(IXLRange range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (range.Worksheet != Worksheet)
{
range = Worksheet.Range(((XLRangeAddress)range.RangeAddress).WithoutWorksheet());
}
_ranges.Add(range);
RangeAdded?.Invoke(this, new RangeEventArgs(range));
}
public void Consolidate()
{
Func <IXLDataValidation, IXLDataValidation, bool> areEqual = (dv1, dv2) =>
{
return
(dv1.IgnoreBlanks == dv2.IgnoreBlanks &&
dv1.InCellDropdown == dv2.InCellDropdown &&
dv1.ShowErrorMessage == dv2.ShowErrorMessage &&
dv1.ShowInputMessage == dv2.ShowInputMessage &&
dv1.InputTitle == dv2.InputTitle &&
dv1.InputMessage == dv2.InputMessage &&
dv1.ErrorTitle == dv2.ErrorTitle &&
dv1.ErrorMessage == dv2.ErrorMessage &&
dv1.ErrorStyle == dv2.ErrorStyle &&
dv1.AllowedValues == dv2.AllowedValues &&
dv1.Operator == dv2.Operator &&
dv1.MinValue == dv2.MinValue &&
dv1.MaxValue == dv2.MaxValue &&
dv1.Value == dv2.Value);
};
var rules = _dataValidations.ToList();
rules.ForEach(Delete);
while (rules.Any())
{
var similarRules = rules.Where(r => areEqual(rules.First(), r)).ToList();
similarRules.ForEach(r => rules.Remove(r));
var consRule = similarRules.First();
var ranges = similarRules.SelectMany(dv => dv.Ranges).ToList();
IXLRanges consolidatedRanges = new XLRanges();
ranges.ForEach(r => consolidatedRanges.Add(r));
consolidatedRanges = consolidatedRanges.Consolidate();
consRule.ClearRanges();
consRule.AddRanges(consolidatedRanges);
Add(consRule);
}
}
public IXLRanges Consolidate()
{
if (!_allRanges.Any())
{
return(_allRanges);
}
var worksheets = _allRanges.Select(r => r.Worksheet).Distinct().OrderBy(ws => ws.Position);
IXLRanges retVal = new XLRanges();
foreach (var ws in worksheets)
{
var matrix = new XLRangeConsolidationMatrix(ws, _allRanges.Where(r => r.Worksheet == ws));
var consRanges = matrix.GetConsolidatedRanges();
foreach (var consRange in consRanges)
{
retVal.Add(consRange);
}
}
return(retVal);
}
/// <summary>
/// The method consolidate the same conditional formats, which are located in adjacent ranges.
/// </summary>
internal void Consolidate()
{
var formats = _conditionalFormats
.Where(cf => cf.Ranges.Any())
.ToList();
_conditionalFormats.Clear();
while (formats.Count > 0)
{
var item = formats.First();
if (!_conditionalFormatTypesExcludedFromConsolidation.Contains(item.ConditionalFormatType))
{
var rangesToJoin = new XLRanges();
item.Ranges.ForEach(r => rangesToJoin.Add(r));
var firstRange = item.Ranges.First();
var skippedRanges = new XLRanges();
Func <IXLConditionalFormat, bool> IsSameFormat = f =>
f != item && f.Ranges.First().Worksheet.Position == firstRange.Worksheet.Position &&
XLConditionalFormat.NoRangeComparer.Equals(f, item);
//Get the top left corner of the rectangle covering all the ranges
var baseAddress = new XLAddress(
item.Ranges.Select(r => r.RangeAddress.FirstAddress.RowNumber).Min(),
item.Ranges.Select(r => r.RangeAddress.FirstAddress.ColumnNumber).Min(),
false, false);
var baseCell = firstRange.Worksheet.Cell(baseAddress) as XLCell;
int i = 1;
bool stop = false;
List <IXLConditionalFormat> similarFormats = new List <IXLConditionalFormat>();
do
{
stop = (i >= formats.Count);
if (!stop)
{
var nextFormat = formats[i];
var intersectsSkipped =
skippedRanges.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any());
var isSameFormat = IsSameFormat(nextFormat);
if (isSameFormat && !intersectsSkipped)
{
similarFormats.Add(nextFormat);
nextFormat.Ranges.ForEach(r => rangesToJoin.Add(r));
}
else if (rangesToJoin.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any()) ||
intersectsSkipped)
{
// if we reached the rule intersecting any of captured ranges stop for not breaking the priorities
stop = true;
}
if (!isSameFormat)
{
nextFormat.Ranges.ForEach(r => skippedRanges.Add(r));
}
}
i++;
} while (!stop);
var consRanges = rangesToJoin.Consolidate();
item.Ranges.RemoveAll();
consRanges.ForEach(r => item.Ranges.Add(r));
var targetCell = item.Ranges.First().FirstCell() as XLCell;
(item as XLConditionalFormat).AdjustFormulas(baseCell, targetCell);
similarFormats.ForEach(cf => formats.Remove(cf));
}
_conditionalFormats.Add(item);
formats.Remove(item);
}
}