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 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 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 XLWorksheetInternals(
XLCellsCollection cellsCollection,
XLColumnsCollection columnsCollection,
XLRowsCollection rowsCollection,
XLRanges mergedRanges
)
{
CellsCollection = cellsCollection;
ColumnsCollection = columnsCollection;
RowsCollection = rowsCollection;
MergedRanges = mergedRanges;
}
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 XLWorksheet(String sheetName, XLWorkbook workbook)
: base(
new XLRangeAddress(
new XLAddress(null, XLHelper.MinRowNumber, XLHelper.MinColumnNumber, false, false),
new XLAddress(null, XLHelper.MaxRowNumber, XLHelper.MaxColumnNumber, false, false)))
{
EventTrackingEnabled = workbook.EventTracking == XLEventTracking.Enabled;
RangeAddress.Worksheet = this;
RangeAddress.FirstAddress.Worksheet = this;
RangeAddress.LastAddress.Worksheet = this;
NamedRanges = new XLNamedRanges(workbook);
SheetView = new XLSheetView();
Tables = new XLTables();
Hyperlinks = new XLHyperlinks();
DataValidations = new XLDataValidations();
PivotTables = new XLPivotTables();
Protection = new XLSheetProtection();
AutoFilter = new XLAutoFilter();
ConditionalFormats = new XLConditionalFormats();
Workbook = workbook;
SetStyle(workbook.Style);
Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(),
new XLRowsCollection(), new XLRanges());
PageSetup = new XLPageSetup((XLPageSetup)workbook.PageOptions, this);
Outline = new XLOutline(workbook.Outline);
_columnWidth = workbook.ColumnWidth;
_rowHeight = workbook.RowHeight;
RowHeightChanged = Math.Abs(workbook.RowHeight - XLWorkbook.DefaultRowHeight) > XLHelper.Epsilon;
Name = sheetName;
SubscribeToShiftedRows(WorksheetRangeShiftedRows);
SubscribeToShiftedColumns(WorksheetRangeShiftedColumns);
Charts = new XLCharts();
ShowFormulas = workbook.ShowFormulas;
ShowGridLines = workbook.ShowGridLines;
ShowOutlineSymbols = workbook.ShowOutlineSymbols;
ShowRowColHeaders = workbook.ShowRowColHeaders;
ShowRuler = workbook.ShowRuler;
ShowWhiteSpace = workbook.ShowWhiteSpace;
ShowZeros = workbook.ShowZeros;
RightToLeft = workbook.RightToLeft;
TabColor = new XLColor();
SelectedRanges = new XLRanges();
Author = workbook.Author;
}
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 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 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 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 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 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;
}
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 Add(IXLRange range)
{
var ranges = new XLRanges {range};
return Add(ranges);
}
public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position)
{
var targetSheet = (XLWorksheet)workbook.WorksheetsInternal.Add(newSheetName, position);
Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c, false));
DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv)));
Internals.ColumnsCollection.ForEach(
kp => targetSheet.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value)));
Internals.RowsCollection.ForEach(kp => targetSheet.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value)));
targetSheet.Visibility = Visibility;
targetSheet.ColumnWidth = ColumnWidth;
targetSheet.ColumnWidthChanged = ColumnWidthChanged;
targetSheet.RowHeight = RowHeight;
targetSheet.RowHeightChanged = RowHeightChanged;
targetSheet.SetStyle(Style);
targetSheet.PageSetup = new XLPageSetup((XLPageSetup)PageSetup, targetSheet);
(targetSheet.PageSetup.Header as XLHeaderFooter).Changed = true;
(targetSheet.PageSetup.Footer as XLHeaderFooter).Changed = true;
targetSheet.Outline = new XLOutline(Outline);
targetSheet.SheetView = new XLSheetView(SheetView);
Internals.MergedRanges.ForEach(
kp => targetSheet.Internals.MergedRanges.Add(targetSheet.Range(kp.RangeAddress.ToString())));
foreach (IXLNamedRange r in NamedRanges)
{
var ranges = new XLRanges();
r.Ranges.ForEach(ranges.Add);
targetSheet.NamedRanges.Add(r.Name, ranges);
}
foreach (XLTable t in Tables.Cast<XLTable>())
{
String tableName = t.Name;
var table = targetSheet.Tables.Any(tt => tt.Name == tableName)
? new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true)
: new XLTable(targetSheet.Range(t.RangeAddress.ToString()), tableName, true);
table.RelId = t.RelId;
table.EmphasizeFirstColumn = t.EmphasizeFirstColumn;
table.EmphasizeLastColumn = t.EmphasizeLastColumn;
table.ShowRowStripes = t.ShowRowStripes;
table.ShowColumnStripes = t.ShowColumnStripes;
table.ShowAutoFilter = t.ShowAutoFilter;
table.Theme = t.Theme;
table._showTotalsRow = t.ShowTotalsRow;
table._uniqueNames.Clear();
t._uniqueNames.ForEach(n => table._uniqueNames.Add(n));
Int32 fieldCount = t.ColumnCount();
for (Int32 f = 0; f < fieldCount; f++)
{
table.Field(f).Index = t.Field(f).Index;
table.Field(f).Name = t.Field(f).Name;
(table.Field(f) as XLTableField).totalsRowLabel = (t.Field(f) as XLTableField).totalsRowLabel;
(table.Field(f) as XLTableField).totalsRowFunction = (t.Field(f) as XLTableField).totalsRowFunction;
}
}
if (AutoFilter.Enabled)
targetSheet.Range(AutoFilter.Range.RangeAddress).SetAutoFilter();
return targetSheet;
}
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;
}
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);
}
