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); }