public MoveData(IXLRangeAddress sourceAddress, RangeType type, string groupTitle, int level) { SourceAddress = sourceAddress; Type = type; GroupTitle = groupTitle; Level = level; }
private bool IsRangeToLeft(IXLRangeAddress newAddr, IXLRangeAddress addr) { return(newAddr.FirstAddress.RowNumber == addr.FirstAddress.RowNumber && newAddr.LastAddress.RowNumber == addr.LastAddress.RowNumber && newAddr.FirstAddress.ColumnNumber < addr.FirstAddress.ColumnNumber && (newAddr.LastAddress.ColumnNumber + 1).Between(addr.FirstAddress.ColumnNumber, addr.LastAddress.ColumnNumber)); }
private void SplitExistingRanges(IXLRangeAddress rangeAddress) { if (_skipSplittingExistingRanges) { return; } try { _skipSplittingExistingRanges = true; var entries = _dataValidationIndex.GetIntersectedRanges((XLRangeAddress)rangeAddress) .ToList(); foreach (var entry in entries) { entry.DataValidation.SplitBy(rangeAddress); } } finally { _skipSplittingExistingRanges = false; } //TODO Remove empty data validations }
public static bool Contains(this IXLRangeAddress rangeAddress, IXLAddress address) { return(rangeAddress.FirstAddress.RowNumber <= address.RowNumber && address.RowNumber <= rangeAddress.LastAddress.RowNumber && rangeAddress.FirstAddress.ColumnNumber <= address.ColumnNumber && address.ColumnNumber <= rangeAddress.LastAddress.ColumnNumber); }
public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress) { return(!rangeAddress.IsInvalid && rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber && rangeAddress.FirstAddress.RowNumber <= rangeAddress.LastAddress.RowNumber && rangeAddress.FirstAddress.ColumnNumber <= rangeAddress.LastAddress.ColumnNumber); }
/// <summary> /// Get range coordinates relative to another range. /// </summary> /// <param name="range">range</param> /// <param name="baseAddr">Reference system. Coordinates are calculated relative to this range.</param> public static IXLRangeAddress Relative(this IXLRangeAddress range, IXLRangeAddress baseAddr) { return(baseAddr.Worksheet.Range( range.FirstAddress.RowNumber - baseAddr.FirstAddress.RowNumber + 1, range.FirstAddress.ColumnNumber - baseAddr.FirstAddress.ColumnNumber + 1, range.LastAddress.RowNumber - baseAddr.FirstAddress.RowNumber + 1, range.LastAddress.ColumnNumber - baseAddr.FirstAddress.ColumnNumber + 1) .RangeAddress); }
/// <summary> /// Get all data validation rules applied to ranges that intersect the specified range. /// </summary> public IEnumerable <IXLDataValidation> GetAllInRange(IXLRangeAddress rangeAddress) { if (rangeAddress == null || !rangeAddress.IsValid) { return(Enumerable.Empty <IXLDataValidation>()); } return(_dataValidationIndex.GetIntersectedRanges((XLRangeAddress)rangeAddress) .Select(indexEntry => indexEntry.DataValidation) .Distinct()); }
internal IEnumerable <XLRange> Split(IXLRangeAddress anotherRange, bool includeIntersection) { if (!RangeAddress.Intersects(anotherRange)) { yield return(this); yield break; } var thisRow1 = RangeAddress.FirstAddress.RowNumber; var thisRow2 = RangeAddress.LastAddress.RowNumber; var thisColumn1 = RangeAddress.FirstAddress.ColumnNumber; var thisColumn2 = RangeAddress.LastAddress.ColumnNumber; var otherRow1 = Math.Min(Math.Max(thisRow1, anotherRange.FirstAddress.RowNumber), thisRow2 + 1); var otherRow2 = Math.Max(Math.Min(thisRow2, anotherRange.LastAddress.RowNumber), thisRow1 - 1); var otherColumn1 = Math.Min(Math.Max(thisColumn1, anotherRange.FirstAddress.ColumnNumber), thisColumn2 + 1); var otherColumn2 = Math.Max(Math.Min(thisColumn2, anotherRange.LastAddress.ColumnNumber), thisColumn1 - 1); var candidates = new[] { // to the top of the intersection new XLRangeAddress( new XLAddress(thisRow1, thisColumn1, false, false), new XLAddress(otherRow1 - 1, thisColumn2, false, false)), // to the left of the intersection new XLRangeAddress( new XLAddress(otherRow1, thisColumn1, false, false), new XLAddress(otherRow2, otherColumn1 - 1, false, false)), includeIntersection ? new XLRangeAddress( new XLAddress(otherRow1, otherColumn1, false, false), new XLAddress(otherRow2, otherColumn2, false, false)) : XLRangeAddress.Invalid, // to the right of the intersection new XLRangeAddress( new XLAddress(otherRow1, otherColumn2 + 1, false, false), new XLAddress(otherRow2, thisColumn2, false, false)), // to the bottom of the intersection new XLRangeAddress( new XLAddress(otherRow2 + 1, thisColumn1, false, false), new XLAddress(thisRow2, thisColumn2, false, false)), }; foreach (var rangeAddress in candidates.Where(c => c.IsValid && c.IsNormalized)) { yield return(Worksheet.Range(rangeAddress)); } }
public async Task <byte[]> CreateExcelFileStream(Case Case) { string path = Path.Combine(appEnvironment.ContentRootPath + "/wwwroot", "Template.xlsx"); XLWorkbook workbook; try { workbook = new XLWorkbook(path); } catch { await telegramClient.SendTextMessageAsync(Case.chatId, "Не найден Excel шаблон"); return(null); } var worksheet = workbook.Worksheet(1); var rowCounter = 2; foreach (var asset in Case.Assets) { worksheet.Cell($"A{rowCounter}").Value = $"{asset.Symbol}/{asset.Pair}"; worksheet.Cell($"B{rowCounter}").Value = RoundNum(asset.AvrPrice); worksheet.Cell($"C{rowCounter}").Value = asset.Pair; worksheet.Cell($"D{rowCounter}").Value = RoundNum(asset.Quantity); worksheet.Cell($"E{rowCounter}").Value = RoundNum(asset.CurPrice); worksheet.Cell($"F{rowCounter}").Value = asset.Pair; worksheet.Cell($"G{rowCounter}").Value = RoundNum(asset.CurValue); worksheet.Cell($"H{rowCounter}").Value = asset.Pair; worksheet.Cell($"I{rowCounter}").Value = RoundNum(asset.Profit); worksheet.Cell($"J{rowCounter}").Value = asset.Pair; worksheet.Cell($"K{rowCounter}").Value = asset.PercentProfit.ToString("P"); worksheet.Cell($"L{rowCounter}").Value = asset.Share.ToString("P"); IXLRangeAddress address = worksheet.Range(worksheet.Cell($"I{rowCounter}").Address, worksheet.Cell($"K{rowCounter}").Address).RangeAddress; worksheet.Range(address).Style.Font.FontColor = asset.Profit > 0 ? XLColor.FromHtml("#1D8348") : XLColor.FromHtml("#943126"); rowCounter++; } worksheet.Columns().AdjustToContents(); string savePath = Path.Combine(appEnvironment.ContentRootPath + "/wwwroot", $"{Guid.NewGuid().ToString()}.xlsx"); workbook.SaveAs(savePath); var bytes = System.IO.File.ReadAllBytes(savePath); System.IO.File.Delete(savePath); return(bytes); }
private void AddToBitMatrix(IXLRangeAddress rangeAddress) { var rows = _bitMatrix.Keys .Where(k => k >= rangeAddress.FirstAddress.RowNumber && k <= rangeAddress.LastAddress.RowNumber); var minIndex = rangeAddress.FirstAddress.ColumnNumber - _minColumn + 1; var maxIndex = rangeAddress.LastAddress.ColumnNumber - _minColumn + 1; foreach (var rowNum in rows) { for (int i = minIndex; i <= maxIndex; i++) { _bitMatrix[rowNum][i] = true; } } }
public void ToStringTest() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; Assert.AreEqual("A1:A1", address.ToString()); Assert.AreEqual("A1:A1", address.ToStringRelative()); Assert.AreEqual("'Sheet1'!A1:A1", address.ToStringRelative(true)); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed()); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1)); Assert.AreEqual("R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1)); Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default)); Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true)); Assert.AreEqual("'Sheet1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); }
private IEnumerable <XLSheetPoint> GetAllCellsInRange(IXLRangeAddress rangeAddress) { if (!rangeAddress.IsValid) { yield break; } var normalizedAddress = ((XLRangeAddress)rangeAddress).Normalize(); var minRow = normalizedAddress.FirstAddress.RowNumber; var maxRow = normalizedAddress.LastAddress.RowNumber; var minColumn = normalizedAddress.FirstAddress.ColumnNumber; var maxColumn = normalizedAddress.LastAddress.ColumnNumber; for (var ro = minRow; ro <= maxRow; ro++) { for (var co = minColumn; co <= maxColumn; co++) { yield return(new XLSheetPoint(ro, co)); } } }
/// <summary> /// Get the data validation rule for the range with the specified address if it exists. /// </summary> /// <param name="rangeAddress">A range address.</param> /// <param name="dataValidation">Data validation rule which ranges collection includes the specified /// address. The specified range should be fully covered with the data validation rule. /// For example, if the rule is applied to ranges A1:A3,C1:C3 then this method will /// return True for ranges A1:A3, C1:C2, A2:A3, and False for ranges A1:C3, A1:C1, etc.</param> /// <returns>True is the data validation rule was found, false otherwise.</returns> public bool TryGet(IXLRangeAddress rangeAddress, out IXLDataValidation dataValidation) { dataValidation = null; if (rangeAddress == null || !rangeAddress.IsValid) { return(false); } var candidates = _dataValidationIndex.GetIntersectedRanges((XLRangeAddress)rangeAddress) .Where(c => c.RangeAddress.Contains(rangeAddress.FirstAddress) && c.RangeAddress.Contains(rangeAddress.LastAddress)); if (!candidates.Any()) { return(false); } dataValidation = candidates.First().DataValidation; return(true); }
public new IEnumerable <T> GetIntersectedRanges(IXLRangeAddress rangeAddress) { return(base.GetIntersectedRanges(rangeAddress).Cast <T>()); }
public static int RowCount(this IXLRangeAddress address) { return(address.LastAddress.RowNumber - address.FirstAddress.RowNumber + 1); }
public static bool Contains(this IXLRangeAddress rangeAddress, IXLRangeAddress address) { return(rangeAddress.Contains(address.FirstAddress) && rangeAddress.Contains(address.LastAddress)); }
public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress) { return !rangeAddress.IsInvalid && rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber && rangeAddress.FirstAddress.RowNumber <= rangeAddress.LastAddress.RowNumber && rangeAddress.FirstAddress.ColumnNumber <= rangeAddress.LastAddress.ColumnNumber; }
public bool Intersects(IXLRangeAddress otherAddress) { var xlOtherAddress = (XLRangeAddress)otherAddress; return(Intersects(in xlOtherAddress)); }
IXLRange IXLRange.Range(IXLRangeAddress rangeAddress) { return(Range(rangeAddress)); }
IXLRange IXLRange.Range(IXLRangeAddress rangeAddress) { return Range(rangeAddress); }
public AddressChangedEventArgs(IXLRangeBase range, IXLRangeAddress oldAddress, IXLRangeAddress newAddress) { NewAddress = newAddress; Range = range; OldAddress = oldAddress; }
public IXLTable Resize(IXLRangeAddress rangeAddress) { return(Resize(Worksheet.Range(RangeAddress))); }
/// <summary> /// Filter ranges from a collection that intersect the specified address. Is much more efficient /// that using Linq expression .Where(). /// </summary> public IEnumerable <IXLRange> GetIntersectedRanges(IXLRangeAddress rangeAddress) { var xlRangeAddress = (XLRangeAddress)rangeAddress; return(GetIntersectedRanges(in xlRangeAddress)); }
IXLRange IXLWorksheet.Range(IXLRangeAddress rangeAddress) { return Range(rangeAddress); }
public XLDataValidationIndexEntry(IXLRangeAddress rangeAddress, XLDataValidation dataValidation) { RangeAddress = rangeAddress; DataValidation = dataValidation; }