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