/// <summary>
/// Get a list of cell values covered by the range in the index, e.g. sheetReader["A1","C3"] will return a list of nine
/// values,
/// going left-to-right and then top-to-bottom, of the values A1, A2, A3, B1, B2, B3, C1, C2, C3
/// </summary>
/// <param name="topLeft">The top left cell of the required range</param>
/// <param name="bottomRight">The bottom right cell of the required range</param>
public IEnumerable <object> this[string topLeft, string bottomRight]
{
get
{
var range = CellRef.Range(topLeft, bottomRight).Select(x => x.ToString());
var result = range.Select(x => this[x]);
return(result);
}
}
public static IEnumerable <CellRef> Range(string topLeft, string bottomRight)
{
var tl = new CellRef(topLeft);
var br = new CellRef(bottomRight);
var list = new List <CellRef>();
for (var y = tl.Row; y <= br.Row; y++)
{
for (var x = tl.ColumnNumber; x <= br.ColumnNumber; x++)
{
list.Add(new CellRef(y, x));
}
}
return(list);
}
/// <summary>
/// Returns the value of the cell at the given address, e.g. sheetReader["C3"] returns the value
/// of the cell at C3, if present, or null if the cell is empty
/// </summary>
/// <param name="cellAddress">
/// The address of the cell
/// </param>
/// <exception cref="IndexOutOfRangeException">
/// Will throw if the requested index is beyond the used range of the workbook. Avoid this exception by checking the
/// WorksheetDimension or Max/MinRow and Max/MinColumnNumber properties
/// </exception>
public object this[string cellAddress]
{
get
{
if (_values.ContainsKey(cellAddress))
{
return(_values[cellAddress]);
}
var cellRef = new CellRef(cellAddress);
if (cellRef.ColumnNumber > WorksheetDimension.BottomRight.ColumnNumber ||
cellRef.Row > WorksheetDimension.BottomRight.Row)
{
throw new IndexOutOfRangeException();
}
var value = GetValue(cellAddress);
return(value);
}
}
private object GetValue(string address)
{
var cellRef = new CellRef(address);
while (ReadAndLogRowNumber())
{
if (_xmlReader.IsStartOfElement("c") && !_xmlReader.IsEmptyElement)
{
GetCellAttributesAndReadValue();
_values[Address] = Value;
if (Address == address)
{
return(Value);
}
}
if (_xmlReader.IsStartOfElement("row") && int.Parse(_xmlReader.GetAttribute("r")) > cellRef.Row)
{
return(null);
}
}
return(null);
}
/// <summary>
/// Gets a list of all the cell values within the specified column.
/// </summary>
/// <param name="column">The string representation of the column, e.g. A, C, AAZ, etc. </param>
/// <returns>An enumerable of objects representing the values of cells in the column</returns>
public IEnumerable <object> Column(string column)
{
return(Column(CellRef.ColumnNameToNumber(column)));
}