+ @"|(?<=\W)(?<three>\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})(?=\W)", RegexOptions.Compiled); // A:A private static string Evaluator(Match match, Int32 row, String column) { if (match.Groups["one"].Success) { var split = match.Groups["one"].Value.Split('$'); if (split.Length == 1) { return(column + row); // A1 } if (split.Length == 3) { return(match.Groups["one"].Value); // $A$1 } var a = XLAddress.Create(match.Groups["one"].Value); if (split[0] == String.Empty) { return("$" + a.ColumnLetter + row); // $A1 } return(column + "$" + a.RowNumber); } if (match.Groups["two"].Success) { return(ReplaceGroup(match.Groups["two"].Value, row.ToString())); } return(ReplaceGroup(match.Groups["three"].Value, column)); }
public XLRangeColumn Column(Int32 column) { if (column <= 0 || column > XLHelper.MaxColumnNumber) throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber)); var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); var lastCellAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); return new XLRangeColumn( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); }
public XLRangeAddress(XLWorksheet worksheet, String rangeAddress) { string addressToUse = rangeAddress.Contains("!") ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) : rangeAddress; string firstPart; string secondPart; if (addressToUse.Contains(':')) { var arrRange = addressToUse.Split(':'); firstPart = arrRange[0]; secondPart = arrRange[1]; } else { firstPart = addressToUse; secondPart = addressToUse; } if (XLHelper.IsValidA1Address(firstPart)) { FirstAddress = XLAddress.Create(worksheet, firstPart); LastAddress = XLAddress.Create(worksheet, secondPart); } else { firstPart = firstPart.Replace("$", String.Empty); secondPart = secondPart.Replace("$", String.Empty); if (char.IsDigit(firstPart[0])) { FirstAddress = XLAddress.Create(worksheet, "A" + firstPart); LastAddress = XLAddress.Create(worksheet, XLHelper.MaxColumnLetter + secondPart); } else { FirstAddress = XLAddress.Create(worksheet, firstPart + "1"); LastAddress = XLAddress.Create(worksheet, secondPart + XLHelper.MaxRowNumber.ToInvariantString()); } } Worksheet = worksheet; }
public virtual XLRangeColumn Column(Int32 columnNumber) { if (columnNumber <= 0 || columnNumber > XLHelper.MaxColumnNumber) { throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber)); } var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnNumber - 1, false, false); var lastCellAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + columnNumber - 1, false, false); return(Worksheet.RangeColumn(new XLRangeAddress(firstCellAddress, lastCellAddress))); }
public XLRangeRow Row(Int32 row) { if (row <= 0 || row > XLHelper.MaxRowNumber) { throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber)); } var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); var lastCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); return(Worksheet.RangeRow(new XLRangeAddress(firstCellAddress, lastCellAddress))); }
/// <summary> /// The method consolidate the same conditional formats, which are located in adjacent ranges. /// </summary> internal void Consolidate() { var formats = _conditionalFormats .Where(cf => cf.Ranges.Any()) .ToList(); _conditionalFormats.Clear(); while (formats.Count > 0) { var item = formats.First(); if (!_conditionalFormatTypesExcludedFromConsolidation.Contains(item.ConditionalFormatType)) { var rangesToJoin = new XLRanges(); item.Ranges.ForEach(r => rangesToJoin.Add(r)); var firstRange = item.Ranges.First(); var skippedRanges = new XLRanges(); Func <IXLConditionalFormat, bool> IsSameFormat = f => f != item && f.Ranges.First().Worksheet.Position == firstRange.Worksheet.Position && XLConditionalFormat.NoRangeComparer.Equals(f, item); //Get the top left corner of the rectangle covering all the ranges var baseAddress = new XLAddress( item.Ranges.Select(r => r.RangeAddress.FirstAddress.RowNumber).Min(), item.Ranges.Select(r => r.RangeAddress.FirstAddress.ColumnNumber).Min(), false, false); var baseCell = firstRange.Worksheet.Cell(baseAddress) as XLCell; int i = 1; bool stop = false; List <IXLConditionalFormat> similarFormats = new List <IXLConditionalFormat>(); do { stop = (i >= formats.Count); if (!stop) { var nextFormat = formats[i]; var intersectsSkipped = skippedRanges.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any()); var isSameFormat = IsSameFormat(nextFormat); if (isSameFormat && !intersectsSkipped) { similarFormats.Add(nextFormat); nextFormat.Ranges.ForEach(r => rangesToJoin.Add(r)); } else if (rangesToJoin.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any()) || intersectsSkipped) { // if we reached the rule intersecting any of captured ranges stop for not breaking the priorities stop = true; } if (!isSameFormat) { nextFormat.Ranges.ForEach(r => skippedRanges.Add(r)); } } i++; } while (!stop); var consRanges = rangesToJoin.Consolidate(); item.Ranges.RemoveAll(); consRanges.ForEach(r => item.Ranges.Add(r)); var targetCell = item.Ranges.First().FirstCell() as XLCell; (item as XLConditionalFormat).AdjustFormulas(baseCell, targetCell); similarFormats.ForEach(cf => formats.Remove(cf)); } _conditionalFormats.Add(item); formats.Remove(item); } }
public XLRangeRow Row(Int32 row) { if (row <= 0 || row > XLHelper.MaxRowNumber) throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber)); var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); var lastCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); return new XLRangeRow( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); }
public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) { Worksheet = firstAddress.Worksheet; FirstAddress = XLAddress.Create(firstAddress); LastAddress = XLAddress.Create(lastAddress); }
public static XLAddress Create(XLAddress cellAddress) { return(new XLAddress(cellAddress.Worksheet, cellAddress.RowNumber, cellAddress.ColumnNumber, cellAddress.FixedRow, cellAddress.FixedColumn)); }
public static XLAddress Create(XLAddress cellAddress) { return new XLAddress(cellAddress.Worksheet, cellAddress.RowNumber, cellAddress.ColumnNumber, cellAddress.FixedRow, cellAddress.FixedColumn); }
public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) : this() { Worksheet = firstAddress.Worksheet; FirstAddress = firstAddress; LastAddress = lastAddress; }