public void XLRangesCountChangesCorrectly()
{
using (var wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Sheet1") as XLWorksheet;
var range1 = ws.Range("A1:B2");
var range2 = ws.Range("A2:B3");
var range3 = ws.Range("A1:B2"); // same as range1
var ranges = new XLRanges();
ranges.Add(range1);
Assert.AreEqual(1, ranges.Count);
ranges.Add(range2);
Assert.AreEqual(2, ranges.Count);
ranges.Add(range3);
Assert.AreEqual(2, ranges.Count);
Assert.AreEqual(ranges.Count, ranges.Count());
ranges.Remove(range3);
Assert.AreEqual(1, ranges.Count);
ranges.Remove(range2);
Assert.AreEqual(0, ranges.Count);
ranges.Remove(range1);
Assert.AreEqual(0, ranges.Count);
}
}
public void ConsolidateRangesSameWorksheet()
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");
var ranges = new XLRanges();
ranges.Add(ws.Range("A1:E3"));
ranges.Add(ws.Range("A4:B10"));
ranges.Add(ws.Range("E2:F12"));
ranges.Add(ws.Range("C6:I8"));
ranges.Add(ws.Range("G9:G9"));
ranges.Add(ws.Range("C9:D9"));
ranges.Add(ws.Range("H9:H9"));
ranges.Add(ws.Range("I9:I13"));
ranges.Add(ws.Range("C4:D5"));
var consRanges = ranges.Consolidate().ToList();
Assert.AreEqual(6, consRanges.Count);
Assert.AreEqual("A1:E9", consRanges[0].RangeAddress.ToString());
Assert.AreEqual("F2:F12", consRanges[1].RangeAddress.ToString());
Assert.AreEqual("G6:I9", consRanges[2].RangeAddress.ToString());
Assert.AreEqual("A10:B10", consRanges[3].RangeAddress.ToString());
Assert.AreEqual("E10:E12", consRanges[4].RangeAddress.ToString());
Assert.AreEqual("I10:I13", consRanges[5].RangeAddress.ToString());
}
public void RangesRemoveAllByCriteria()
{
var ws = new XLWorkbook().Worksheets.Add("Sheet1");
var ranges = new XLRanges();
ranges.Add(ws.Range("A1:A2"));
ranges.Add(ws.Range("B1:B3"));
ranges.Add(ws.Range("C1:C4"));
var otherRange = ws.Range("A3:D3");
ranges.RemoveAll(r => r.Intersects(otherRange));
Assert.AreEqual(1, ranges.Count);
Assert.AreEqual("A1:A2", ranges.Single().RangeAddress.ToString());
}
public void RangesRemoveAllWithoutDispose()
{
var ws = new XLWorkbook().Worksheets.Add("Sheet1");
var ranges = new XLRanges();
ranges.Add(ws.Range("A1:A2"));
ranges.Add(ws.Range("B1:B2"));
var rangesCopy = ranges.ToList();
ranges.RemoveAll(null, false);
ws.FirstColumn().InsertColumnsBefore(1);
Assert.AreEqual(0, ranges.Count);
// if ranges were not disposed they addresses should change
Assert.AreEqual("B1:B2", rangesCopy.First().RangeAddress.ToString());
Assert.AreEqual("C1:C2", rangesCopy.Last().RangeAddress.ToString());
}
public void ConsolidateSparsedRanges()
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");
var ranges = new XLRanges();
ranges.Add(ws.Range("A1:C1"));
ranges.Add(ws.Range("E1:G1"));
ranges.Add(ws.Range("A3:C3"));
ranges.Add(ws.Range("E3:G3"));
var consRanges = ranges.Consolidate().ToList();
Assert.AreEqual(4, consRanges.Count);
Assert.AreEqual("A1:C1", consRanges[0].RangeAddress.ToString());
Assert.AreEqual("E1:G1", consRanges[1].RangeAddress.ToString());
Assert.AreEqual("A3:C3", consRanges[2].RangeAddress.ToString());
Assert.AreEqual("E3:G3", consRanges[3].RangeAddress.ToString());
}
public void XLRangesCountChangesCorrectly()
{
using (var wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Sheet1") as XLWorksheet;
var range1 = ws.Range("A1:B2");
var range2 = ws.Range("A2:B3");
var range3 = ws.Range("A1:B2"); // same as range1
var ranges = new XLRanges();
ranges.Add(range1);
Assert.AreEqual(1, ranges.Count);
ranges.Add(range2);
Assert.AreEqual(2, ranges.Count);
ranges.Add(range3);
Assert.AreEqual(2, ranges.Count);
Assert.AreEqual(ranges.Count, ranges.Count());
// Add many entries to activate QuadTree
for (int i = 1; i <= TEST_COUNT; i++)
{
ranges.Add(ws.Range(i * 2, 2, i * 2, 4));
}
Assert.AreEqual(2 + TEST_COUNT, ranges.Count);
for (int i = 1; i <= TEST_COUNT; i++)
{
ranges.Remove(ws.Range(i * 2, 2, i * 2, 4));
}
Assert.AreEqual(2, ranges.Count);
ranges.Remove(range3);
Assert.AreEqual(1, ranges.Count);
ranges.Remove(range2);
Assert.AreEqual(0, ranges.Count);
ranges.Remove(range1);
Assert.AreEqual(0, ranges.Count);
}
}
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 void CopyNamedRangeDifferentWorksheets()
{
var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Sheet1");
var ws2 = wb.Worksheets.Add("Sheet2");
var ranges = new XLRanges();
ranges.Add(ws1.Range("B2:E6"));
ranges.Add(ws2.Range("D1:E2"));
var original = ws1.NamedRanges.Add("Named range", ranges);
var copy = original.CopyTo(ws2);
Assert.AreEqual(1, ws1.NamedRanges.Count());
Assert.AreEqual(1, ws2.NamedRanges.Count());
Assert.AreEqual(2, original.Ranges.Count);
Assert.AreEqual(2, copy.Ranges.Count);
Assert.AreEqual(original.Name, copy.Name);
Assert.AreEqual(original.Scope, copy.Scope);
Assert.AreEqual("Sheet1!B2:E6", original.Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
Assert.AreEqual("Sheet2!D1:E2", original.Ranges.Last().RangeAddress.ToString(XLReferenceStyle.A1, true));
Assert.AreEqual("Sheet2!D1:E2", copy.Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
Assert.AreEqual("Sheet2!B2:E6", copy.Ranges.Last().RangeAddress.ToString(XLReferenceStyle.A1, true));
}
public void XLRangesReturnsRangesInDeterministicOrder()
{
var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Sheet1");
var ws2 = wb.Worksheets.Add("Another sheet");
var ranges = new XLRanges();
ranges.Add(ws2.Range("F1:F12"));
ranges.Add(ws1.Range("F12:F16"));
ranges.Add(ws1.Range("B1:F2"));
ranges.Add(ws2.Range("A13:B14"));
ranges.Add(ws2.Range("E1:E2"));
ranges.Add(ws1.Range("E1:H2"));
ranges.Add(ws1.Range("G2:G13"));
ranges.Add(ws1.Range("G20:G20"));
var expectedRanges = new List <IXLRange>
{
ws1.Range("B1:F2"),
ws1.Range("E1:H2"),
ws1.Range("G2:G13"),
ws1.Range("F12:F16"),
ws1.Range("G20:G20"),
ws2.Range("E1:E2"),
ws2.Range("F1:F12"),
ws2.Range("A13:B14"),
};
var actualRanges = ranges.ToList();
Assert.AreEqual(expectedRanges.Count, actualRanges.Count);
for (int i = 0; i < actualRanges.Count; i++)
{
Assert.AreEqual(expectedRanges[i], actualRanges[i]);
}
}
public void ConsolidateWideRangesSameWorksheet()
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");
var ranges = new XLRanges();
ranges.Add(ws.Row(5));
ranges.Add(ws.Row(7));
ranges.Add(ws.Row(6));
ranges.Add(ws.Column("D"));
ranges.Add(ws.Column("F"));
ranges.Add(ws.Column("E"));
var consRanges = ranges.Consolidate()
.OrderBy(r => r.Worksheet.Name)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ToList();
Assert.AreEqual(3, consRanges.Count);
Assert.AreEqual("D:F", consRanges[0].RangeAddress.ToString());
Assert.AreEqual("A5:C7", consRanges[1].RangeAddress.ToString());
Assert.AreEqual("G5:XFD7", consRanges[2].RangeAddress.ToString());
}
public void ConsolidateRangesDifferentWorksheets()
{
var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Sheet1");
var ws2 = wb.Worksheets.Add("Sheet2");
var ranges = new XLRanges();
ranges.Add(ws1.Range("A1:E3"));
ranges.Add(ws1.Range("A4:B10"));
ranges.Add(ws1.Range("E2:F12"));
ranges.Add(ws1.Range("C6:I8"));
ranges.Add(ws1.Range("G9:G9"));
ranges.Add(ws2.Row(5));
ranges.Add(ws2.Row(7));
ranges.Add(ws2.Row(6));
ranges.Add(ws2.Column("D"));
ranges.Add(ws2.Column("F"));
ranges.Add(ws2.Column("E"));
ranges.Add(ws1.Range("C9:D9"));
ranges.Add(ws1.Range("H9:H9"));
ranges.Add(ws1.Range("I9:I13"));
ranges.Add(ws1.Range("C4:D5"));
var consRanges = ranges.Consolidate()
.OrderBy(r => r.Worksheet.Name)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ToList();
Assert.AreEqual(9, consRanges.Count);
Assert.AreEqual("Sheet1!$A$1:$E$9", consRanges[0].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet1!$F$2:$F$12", consRanges[1].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet1!$G$6:$I$9", consRanges[2].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet1!$A$10:$B$10", consRanges[3].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet1!$E$10:$E$12", consRanges[4].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet1!$I$10:$I$13", consRanges[5].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet2!$D:$F", consRanges[6].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet2!$A$5:$C$7", consRanges[7].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
Assert.AreEqual("Sheet2!$G$5:$XFD$7", consRanges[8].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
}
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 new IXLRanges Ranges(String ranges)
{
var retVal = new XLRanges();
foreach (string rangeAddressStr in ranges.Split(',').Select(s => s.Trim()))
{
if (XLHelper.IsValidRangeAddress(rangeAddressStr))
retVal.Add(Range(new XLRangeAddress(Worksheet, rangeAddressStr)));
else if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0))
NamedRange(rangeAddressStr).Ranges.ForEach(retVal.Add);
else
{
Workbook.NamedRanges.First(n =>
String.Compare(n.Name, rangeAddressStr, true) == 0
&& n.Ranges.First().Worksheet == this)
.Ranges.ForEach(retVal.Add);
}
}
return retVal;
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
var newMerge = new XLRanges();
foreach (IXLRange rngMerged in Internals.MergedRanges)
{
if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber
&& rngMerged.RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber
&& rngMerged.RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber)
{
var newRng = Range(
rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted,
rngMerged.RangeAddress.FirstAddress.ColumnNumber,
rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted,
rngMerged.RangeAddress.LastAddress.ColumnNumber);
newMerge.Add(newRng);
}
else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber
&& range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber))
newMerge.Add(rngMerged);
}
Internals.MergedRanges = newMerge;
Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges));
MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges);
ShiftConditionalFormattingRows(range, rowsShifted);
}
public IXLRanges Ranges(String namedRanges)
{
var retVal = new XLRanges();
var rangePairs = namedRanges.Split(',');
foreach (var range in rangePairs.Select(r => Range(r.Trim())).Where(range => range != null))
{
retVal.Add(range);
}
return retVal;
}