public IXLRangeRows Rows(String rows)
{
var retVal = new XLRangeRows();
var rowPairs = rows.Split(',');
foreach (string tPair in rowPairs.Select(pair => pair.Trim()))
{
String firstRow;
String lastRow;
if (tPair.Contains(':') || tPair.Contains('-'))
{
string[] rowRange = XLHelper.SplitRange(tPair);
firstRow = rowRange[0];
lastRow = rowRange[1];
}
else
{
firstRow = tPair;
lastRow = tPair;
}
foreach (IXLRangeRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow)))
{
retVal.Add(row);
}
}
return(retVal);
}
public IEnumerator <IXLRange> GetEnumerator()
{
var retList = new List <IXLRange>();
retList.AddRange(_ranges.Where(r => XLHelper.IsValidRangeAddress(r.RangeAddress)).Cast <IXLRange>());
return(retList.GetEnumerator());
}
public void Delete(Int32 position)
{
int wsCount = _worksheets.Values.Count(w => w.Position == position);
if (wsCount == 0)
{
throw new ArgumentException("There isn't a worksheet associated with that index.");
}
if (wsCount > 1)
{
throw new ArgumentException(
"Can't delete the worksheet because there are multiple worksheets associated with that index.");
}
var ws = _worksheets.Values.Single(w => w.Position == position);
if (!XLHelper.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId))
{
Deleted.Add(ws.RelId);
}
_worksheets.RemoveAll(w => w.Position == position);
_worksheets.Values.Where(w => w.Position > position).ForEach(w => w._position -= 1);
_workbook.UnsupportedSheets.Where(w => w.Position > position).ForEach(w => w.Position -= 1);
}
private void RescanFieldNames()
{
if (ShowHeaderRow)
{
var detectedFieldNames = new Dictionary <String, IXLTableField>();
var headersRow = HeadersRow(false);
Int32 cellPos = 0;
foreach (var cell in headersRow.Cells())
{
var name = cell.GetString();
if (_fieldNames.ContainsKey(name) && _fieldNames[name].Column.ColumnNumber() == cell.Address.ColumnNumber)
{
(_fieldNames[name] as XLTableField).Index = cellPos;
detectedFieldNames.Add(name, _fieldNames[name]);
cellPos++;
continue;
}
if (XLHelper.IsNullOrWhiteSpace(name))
{
name = GetUniqueName("Column", cellPos + 1, true);
cell.SetValue(name);
cell.DataType = XLDataType.Text;
}
if (_fieldNames.ContainsKey(name))
{
throw new ArgumentException("The header row contains more than one field name '" + name + "'.");
}
_fieldNames.Add(name, new XLTableField(this, name)
{
Index = cellPos++
});
detectedFieldNames.Add(name, _fieldNames[name]);
}
_fieldNames.Keys
.Where(key => !detectedFieldNames.ContainsKey(key))
.ToArray()
.ForEach(key => _fieldNames.Remove(key));
}
else
{
Int32 colCount = ColumnCount();
for (Int32 i = 1; i <= colCount; i++)
{
if (_fieldNames.Values.All(f => f.Index != i - 1))
{
var name = "Column" + i;
_fieldNames.Add(name, new XLTableField(this, name)
{
Index = i - 1
});
}
}
}
}
public Boolean IsDirty()
{
return
(AllowedValues != XLAllowedValues.AnyValue ||
(ShowInputMessage &&
(!XLHelper.IsNullOrWhiteSpace(InputTitle) || !XLHelper.IsNullOrWhiteSpace(InputMessage))) ||
(ShowErrorMessage &&
(!XLHelper.IsNullOrWhiteSpace(ErrorTitle) || !XLHelper.IsNullOrWhiteSpace(ErrorMessage))));
}
public void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase)
{
elements.Add(new XLSortElement()
{
ElementNumber = XLHelper.GetColumnNumberFromLetter(elementNumber),
SortOrder = sortOrder,
IgnoreBlanks = ignoreBlanks,
MatchCase = matchCase
});
}
public new IXLRangeColumn Column(String column)
{
if (XLHelper.IsValidColumn(column))
{
Int32 coNum = XLHelper.GetColumnNumberFromLetter(column);
return(coNum > ColumnCount() ? Column(_table.GetFieldIndex(column) + 1) : Column(coNum));
}
return(Column(_table.GetFieldIndex(column) + 1));
}
public IXLFilterColumn Column(String column)
{
var columnNumber = XLHelper.GetColumnNumberFromLetter(column);
if (columnNumber < 1 || columnNumber > XLHelper.MaxColumnNumber)
{
throw new ArgumentOutOfRangeException(nameof(column), "Column '" + column + "' is outside the allowed column range.");
}
return(Column(columnNumber));
}
public void Rename(String oldSheetName, String newSheetName)
{
if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName))
{
return;
}
var ws = _worksheets[oldSheetName];
_worksheets.Remove(oldSheetName);
_worksheets.Add(newSheetName, ws);
}
/// <summary>
/// Adds the specified range name.
/// </summary>
/// <param name="rangeName">Name of the range.</param>
/// <param name="rangeAddress">The range address.</param>
/// <param name="comment">The comment.</param>
/// <param name="validateName">if set to <c>true</c> validates the name.</param>
/// <param name="validateRangeAddress">if set to <c>true</c> range address will be checked for validity.</param>
/// <returns></returns>
/// <exception cref="NotSupportedException"></exception>
/// <exception cref="ArgumentException">
/// For named ranges in the workbook scope, specify the sheet name in the reference.
/// </exception>
internal IXLNamedRange Add(String rangeName, String rangeAddress, String comment, Boolean validateName, Boolean validateRangeAddress)
{
// When loading named ranges from an existing file, we do not validate the range address or name.
if (validateRangeAddress)
{
var match = XLHelper.NamedRangeReferenceRegex.Match(rangeAddress);
if (!match.Success)
{
if (XLHelper.IsValidRangeAddress(rangeAddress))
{
IXLRange range = null;
if (Scope == XLNamedRangeScope.Worksheet)
{
range = Worksheet.Range(rangeAddress);
}
else if (Scope == XLNamedRangeScope.Workbook)
{
range = Workbook.Range(rangeAddress);
}
else
{
throw new NotSupportedException($"Scope {Scope} is not supported");
}
if (range == null)
{
throw new ArgumentException(string.Format(
"The range address '{0}' for the named range '{1}' is not a valid range.", rangeAddress,
rangeName));
}
if (Scope == XLNamedRangeScope.Workbook || !XLHelper.NamedRangeReferenceRegex.Match(range.ToString()).Success)
{
throw new ArgumentException(
"For named ranges in the workbook scope, specify the sheet name in the reference.");
}
rangeAddress = Worksheet.Range(rangeAddress).ToString();
}
}
}
var namedRange = new XLNamedRange(this, rangeName, validateName, rangeAddress, comment);
_namedRanges.Add(rangeName, namedRange);
return(namedRange);
}
public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary <IXLFontBase, Font> fontCache)
{
if (XLHelper.IsNullOrWhiteSpace(text))
{
return(0);
}
var font = GetCachedFont(fontBase, fontCache);
var textWidth = GraphicsUtils.MeasureString(text, font).Width;
double width = (textWidth / 7d * 256 - 128 / 7) / 256;
width = Math.Round(width + 0.2, 2);
return(width);
}
public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary <IXLFontBase, Font> fontCache)
{
if (XLHelper.IsNullOrWhiteSpace(text))
{
return(0);
}
var font = GetCachedFont(fontBase, fontCache);
var textSize = TextRenderer.MeasureText(text, font);
double width = (((textSize.Width / (double)7) * 256) - (128 / 7)) / 256;
width = (double)decimal.Round((decimal)width + 0.2M, 2);
return(width);
}
public void Rename(String oldSheetName, String newSheetName)
{
if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName))
{
return;
}
if (_worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase)))
{
throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName));
}
var ws = _worksheets[oldSheetName];
_worksheets.Remove(oldSheetName);
Add(newSheetName, ws);
}
public static bool IsValidColumn(string column)
{
var length = column.Length;
if (XLHelper.IsNullOrWhiteSpace(column) || length > 3)
{
return(false);
}
var theColumn = column.ToUpper();
var isValid = theColumn[0] >= 'A' && theColumn[0] <= 'Z';
if (length == 1)
{
return(isValid);
}
if (length == 2)
{
return(isValid && theColumn[1] >= 'A' && theColumn[1] <= 'Z');
}
if (theColumn[0] >= 'A' && theColumn[0] < 'X')
{
return(theColumn[1] >= 'A' && theColumn[1] <= 'Z' &&
theColumn[2] >= 'A' && theColumn[2] <= 'Z');
}
if (theColumn[0] != 'X')
{
return(false);
}
if (theColumn[1] < 'F')
{
return(theColumn[2] >= 'A' && theColumn[2] <= 'Z');
}
if (theColumn[1] != 'F')
{
return(false);
}
return(theColumn[2] >= 'A' && theColumn[2] <= 'D');
}
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 static bool IsValidA1Address(string address)
{
if (XLHelper.IsNullOrWhiteSpace(address))
{
return(false);
}
address = address.Replace("$", "");
var rowPos = 0;
var addressLength = address.Length;
while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0'))
{
rowPos++;
}
return
(rowPos < addressLength &&
IsValidRow(address.Substring(rowPos)) &&
IsValidColumn(address.Substring(0, rowPos)));
}
private void AddToTables(XLRange range, Boolean addToTables)
{
if (!addToTables)
{
return;
}
_uniqueNames = new HashSet <string>();
Int32 co = 1;
foreach (IXLCell c in range.Row(1).Cells())
{
if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
{
c.Value = GetUniqueName("Column", co, true);
}
_uniqueNames.Add(c.GetString());
co++;
}
Worksheet.Tables.Add(this);
}
public IXLRangeColumns Columns(String columns)
{
var retVal = new XLRangeColumns();
var columnPairs = columns.Split(',');
foreach (string tPair in columnPairs.Select(pair => pair.Trim()))
{
String firstColumn;
String lastColumn;
if (tPair.Contains(':') || tPair.Contains('-'))
{
string[] columnRange = XLHelper.SplitRange(tPair);
firstColumn = columnRange[0];
lastColumn = columnRange[1];
}
else
{
firstColumn = tPair;
lastColumn = tPair;
}
Int32 tmp;
if (Int32.TryParse(firstColumn, out tmp))
{
foreach (IXLRangeColumn col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn)))
{
retVal.Add(col);
}
}
else
{
foreach (IXLRangeColumn col in Columns(firstColumn, lastColumn))
{
retVal.Add(col);
}
}
}
return(retVal);
}
internal static string EscapeSheetName(this String sheetName)
{
if (String.IsNullOrEmpty(sheetName))
{
return(sheetName);
}
var needEscape = (!Char.IsLetter(sheetName[0]) && sheetName[0] != '_') ||
XLHelper.IsValidA1Address(sheetName) ||
XLHelper.IsValidRCAddress(sheetName) ||
sheetName.Any(c => (Char.IsPunctuation(c) && c != '.' && c != '_') ||
Char.IsSeparator(c) ||
Char.IsControl(c) ||
Char.IsSymbol(c));
if (needEscape)
{
return(String.Concat('\'', sheetName.Replace("'", "''"), '\''));
}
else
{
return(sheetName);
}
}
public void SetColumnsToRepeatAtLeft(String firstColumnToRepeatAtLeft, String lastColumnToRepeatAtLeft)
{
SetColumnsToRepeatAtLeft(XLHelper.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), XLHelper.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft));
}
public new IXLTableRows InsertRowsAbove(int numberOfRows)
{
return(XLHelper.InsertRowsWithoutEvents(base.InsertRowsAbove, this, numberOfRows, !Table.ShowTotalsRow));
}
/// <summary>
/// Initializes a new <see cref = "XLAddress" /> struct using a mixed notation.
/// </summary>
/// <param name = "worksheet"></param>
/// <param name = "rowNumber">The row number of the cell address.</param>
/// <param name = "columnLetter">The column letter of the cell address.</param>
/// <param name = "fixedRow"></param>
/// <param name = "fixedColumn"></param>
public XLAddress(XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn)
: this(worksheet, rowNumber, XLHelper.GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn)
{
_columnLetter = columnLetter;
}
public IXLTable Resize(IXLRange range)
{
if (!this.ShowHeaderRow)
{
throw new NotImplementedException("Resizing of tables with no headers not supported yet.");
}
if (this.Worksheet != range.Worksheet)
{
throw new InvalidOperationException("You cannot resize a table to a range on a different sheet.");
}
var totalsRowChanged = this.ShowTotalsRow ? range.LastRow().RowNumber() - this.TotalsRow().RowNumber() : 0;
var oldTotalsRowNumber = this.ShowTotalsRow ? this.TotalsRow().RowNumber() : -1;
var existingHeaders = this.FieldNames.Keys;
var newHeaders = new HashSet <string>();
var tempArray = this.Fields.Select(f => f.Column).ToArray();
var firstRow = range.Row(1);
if (!firstRow.FirstCell().Address.Equals(this.HeadersRow().FirstCell().Address) ||
!firstRow.LastCell().Address.Equals(this.HeadersRow().LastCell().Address))
{
_uniqueNames.Clear();
var co = 1;
foreach (var c in firstRow.Cells())
{
if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
{
c.Value = GetUniqueName("Column", co, true);
}
var header = c.GetString();
_uniqueNames.Add(header);
if (!existingHeaders.Contains(header))
{
newHeaders.Add(header);
}
co++;
}
}
if (totalsRowChanged < 0)
{
range.Rows(r => r.RowNumber().Equals(this.TotalsRow().RowNumber() + totalsRowChanged)).Single().InsertRowsAbove(1);
range = Worksheet.Range(range.FirstCell(), range.LastCell().CellAbove());
oldTotalsRowNumber++;
}
else if (totalsRowChanged > 0)
{
this.TotalsRow().RowBelow(totalsRowChanged + 1).InsertRowsAbove(1);
this.TotalsRow().AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp);
}
this.RangeAddress = range.RangeAddress as XLRangeAddress;
RescanFieldNames();
if (this.ShowTotalsRow)
{
foreach (var f in this._fieldNames.Values)
{
var c = this.TotalsRow().Cell(f.Index + 1);
if (!c.IsEmpty() && newHeaders.Contains(f.Name))
{
f.TotalsRowLabel = c.GetFormattedString();
c.DataType = XLDataType.Text;
}
}
if (totalsRowChanged != 0)
{
foreach (var f in this._fieldNames.Values.Cast <XLTableField>())
{
f.UpdateUnderlyingCellFormula();
var c = this.TotalsRow().Cell(f.Index + 1);
if (!XLHelper.IsNullOrWhiteSpace(f.TotalsRowLabel))
{
c.DataType = XLDataType.Text;
//Remove previous row's label
var oldTotalsCell = this.Worksheet.Cell(oldTotalsRowNumber, f.Column.ColumnNumber());
if (oldTotalsCell.Value.ToString() == f.TotalsRowLabel)
{
oldTotalsCell.Value = null;
}
}
if (f.TotalsRowFunction != XLTotalsRowFunction.None)
{
c.DataType = XLDataType.Number;
}
}
}
}
return(this);
}
public IXLRangeColumns Columns(String firstColumn, String lastColumn)
{
return(Columns(XLHelper.GetColumnNumberFromLetter(firstColumn),
XLHelper.GetColumnNumberFromLetter(lastColumn)));
}
public IXLFilterColumn Column(String column)
{
return(Column(XLHelper.GetColumnNumberFromLetter(column)));
}
public new IXLTableRows InsertRowsBelow(int numberOfRows)
{
return(XLHelper.InsertRowsWithoutEvents(base.InsertRowsBelow, _tableRange, numberOfRows, !_tableRange.Table.ShowTotalsRow));
}
public virtual XLRangeColumn Column(String columnLetter)
{
return(Column(XLHelper.GetColumnNumberFromLetter(columnLetter)));
}
public IXLCells Cells(string firstColumn, string lastColumn)
{
return(Cells(XLHelper.GetColumnNumberFromLetter(firstColumn) + ":"
+ XLHelper.GetColumnNumberFromLetter(lastColumn)));
}
public XLRangeColumn Column(String column)
{
return(Column(XLHelper.GetColumnNumberFromLetter(column)));
}