+ @"|(?<=\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 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 XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) : this()
{
Worksheet = firstAddress.Worksheet;
FirstAddress = firstAddress;
LastAddress = lastAddress;
}
