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 IXLNamedRange Add(String rangeName, IXLRange range, String comment)
{
var ranges = new XLRanges {
range
};
return(Add(rangeName, ranges, comment));
}
public IXLRanges Add(IXLRange range)
{
var ranges = new XLRanges {
range
};
return(Add(ranges));
}
private XLConditionalFormat(XLStyleValue style)
: base(XLStyle.Default.Value)
{
Id = Guid.NewGuid();
Ranges = new XLRanges();
Values = new XLDictionary <XLFormula>();
Colors = new XLDictionary <XLColor>();
ContentTypes = new XLDictionary <XLCFContentType>();
IconSetOperators = new XLDictionary <XLCFIconSetOperator>();
}
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 XLWorksheetInternals(
XLCellsCollection cellsCollection,
XLColumnsCollection columnsCollection,
XLRowsCollection rowsCollection,
XLRanges mergedRanges
)
{
CellsCollection = cellsCollection;
ColumnsCollection = columnsCollection;
RowsCollection = rowsCollection;
MergedRanges = mergedRanges;
}
public XLDataValidation(IXLRanges ranges)
{
Ranges = new XLRanges();
ranges.ForEach(r=>
{
var newR =
new XLRange(new XLRangeParameters(r.RangeAddress as XLRangeAddress,
r.Worksheet.Style) {IgnoreEvents = true});
(Ranges as XLRanges).Add(newR);
} );
Initialize();
}
public bool Equals(IXLConditionalFormat x, IXLConditionalFormat y)
{
var xx = (XLConditionalFormat)x;
var yy = (XLConditionalFormat)y;
if (ReferenceEquals(xx, yy))
{
return(true);
}
if (ReferenceEquals(xx, null))
{
return(false);
}
if (ReferenceEquals(yy, null))
{
return(false);
}
if (xx.GetType() != yy.GetType())
{
return(false);
}
var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
var yyValues = yy.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
var xxFormulas = x.Ranges.Any() ? xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
var yyFormulas = y.Ranges.Any() ? yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
var xStyle = xx.StyleValue;
var yStyle = yy.StyleValue;
return(Equals(xStyle, yStyle) &&
xx.CopyDefaultModify == yy.CopyDefaultModify &&
xx.ConditionalFormatType == yy.ConditionalFormatType &&
xx.TimePeriod == yy.TimePeriod &&
xx.IconSetStyle == yy.IconSetStyle &&
xx.Operator == yy.Operator &&
xx.Bottom == yy.Bottom &&
xx.Percent == yy.Percent &&
xx.ReverseIconOrder == yy.ReverseIconOrder &&
xx.StopIfTrue == yy.StopIfTrue &&
xx.ShowIconOnly == yy.ShowIconOnly &&
xx.ShowBarOnly == yy.ShowBarOnly &&
_listComparer.Equals(xxValues, yyValues) &&
_listComparer.Equals(xxFormulas, yyFormulas) &&
_colorsComparer.Equals(xx.Colors, yy.Colors) &&
_contentsTypeComparer.Equals(xx.ContentTypes, yy.ContentTypes) &&
_iconSetTypeComparer.Equals(xx.IconSetOperators, yy.IconSetOperators) &&
(!_compareRange || XLRanges.Equals(xx.Ranges, yy.Ranges)));
}
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);
}
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>
/// 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);
}
}
private XLDataValidation(XLWorksheet worksheet)
{
_worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet));
_ranges = new XLRanges();
Initialize();
}
private XLDataValidation()
{
Ranges = new XLRanges();
Initialize();
}