public async Task<Dictionary<int, ExcelTerm>> GetTermsFromExcel(ExcelWorksheet worksheet)
{
var result = new Dictionary<int, ExcelTerm>();
var excelRangeAddress = _providerSettings.GetExcelRangeAddress();
foreach (var cell in worksheet.Cells[excelRangeAddress])
{
var excellCellAddress = new ExcelCellAddress(cell.Address);
if (_providerSettings.HasHeader && excellCellAddress.Row == 1)
{
continue;
}
var id = excellCellAddress.Row;
if (!result.ContainsKey(id))
{
result[id] = new ExcelTerm();
}
SetCellValue(result[id], cell, excellCellAddress.Column);
}
return result;
}
public void Get_Excel_Address_From_String(string address
,string expectedColumn,long expectedRow)
{
var excellAddress = new ExcelCellAddress(address);
Assert.Equal(ExcelCellAddress.GetColumnLetter(excellAddress.Column), expectedColumn);
Assert.Equal(excellAddress.Row, expectedRow);
}
public IEnumerable<IDictionary<string, object>> ExtractData(Stream fileStream)
{
Package = new ExcelPackage(fileStream);
Workbook = Package.Workbook;
Worksheet = (string.IsNullOrEmpty(SheetName)) ? Workbook.Worksheets[1] : Workbook.Worksheets[SheetName];
Dimension = Worksheet.Dimension;
if (Dimension == null) return Enumerable.Empty<IDictionary<string, object>>();
EndAddress = Dimension.End;
ReadHeaders();
return ReadData();
}
protected internal void SetAddress(string address)
{
if(address.StartsWith("'"))
{
int pos = address.LastIndexOf("'");
SetWbWs(address.Substring(1,pos-1).Replace("''","'"));
_address = address.Substring(pos + 2);
}
else if (address.StartsWith("[")) //Remove any external reference
{
SetWbWs(address);
}
else
{
_address = address;
}
if(_address.IndexOfAny(new char[] {',','!'}) > -1)
{
//Advanced address. Including Sheet or multi
ExtractAddress(_address);
}
else
{
//Simple address
GetRowColFromAddress(_address, out _fromRow, out _fromCol, out _toRow, out _toCol);
_addresses = null;
_start = null;
_end = null;
}
_address = address;
Validate();
}
/// <summary>
/// Address is an defined name
/// </summary>
/// <param name="address">the name</param>
/// <param name="isName">Should always be true</param>
internal ExcelAddressBase(string address, bool isName)
{
if (isName)
{
_address = address;
_fromRow = -1;
_fromCol = -1;
_toRow = -1;
_toCol = -1;
_start = null;
_end = null;
}
else
{
SetAddress(address);
}
}
/// <summary>
/// Indexer for the comments collection
/// </summary>
/// <param name="cell">The cell</param>
/// <returns>The comment</returns>
public ExcelComment this[ExcelCellAddress cell]
{
get
{
ulong cellID=ExcelCellBase.GetCellID(Worksheet.SheetID, cell.Row, cell.Column);
if (_comments.IndexOf(cellID) >= 0)
{
return _comments[cellID] as ExcelComment;
}
else
{
return null;
}
}
}
protected internal void SetAddress(string address)
{
address = address.Trim();
if (Utils.ConvertUtil._invariantCompareInfo.IsPrefix(address, "'"))
{
int pos = address.IndexOf("'", 1);
while (pos < address.Length && address[pos + 1] == '\'')
{
pos = address.IndexOf("'", pos+2);
}
var wbws = address.Substring(1,pos-1).Replace("''","'");
SetWbWs(wbws);
_address = address.Substring(pos + 2);
}
else if (Utils.ConvertUtil._invariantCompareInfo.IsPrefix(address, "[")) //Remove any external reference
{
SetWbWs(address);
}
else
{
_address = address;
}
if(_address.IndexOfAny(new char[] {',','!', '['}) > -1)
{
//Advanced address. Including Sheet or multi or table.
ExtractAddress(_address);
}
else
{
//Simple address
GetRowColFromAddress(_address, out _fromRow, out _fromCol, out _toRow, out _toCol, out _fromRowFixed, out _fromColFixed, out _toRowFixed, out _toColFixed);
_addresses = null;
_start = null;
_end = null;
}
_address = address;
Validate();
}
/// <summary>
/// Indexer for the comments collection
/// </summary>
/// <param name="cell">The cell</param>
/// <returns>The comment</returns>
public ExcelComment this[ExcelCellAddress cell]
{
get
{
//ulong cellID=ExcelCellBase.GetCellID(Worksheet.SheetID, cell.Row, cell.Column);
//if (_comments.IndexOf(cellID) >= 0)
//{
// return _comments[cellID] as ExcelComment;
//}
//else
//{
// return null;
//}
int i=-1;
if (Worksheet._commentsStore.Exists(cell.Row, cell.Column, ref i))
{
return _list[i];
}
else
{
return null;
}
}
}
protected internal void SetAddress(string address)
{
_address = address;
if (address.IndexOfAny(new char[] { ',', '!' }) > -1)
{
//Advanced address. Including Sheet or multi
ExtractAddress(_address);
}
else
{
//Simple address
GetRowColFromAddress(_address, out _fromRow, out _fromCol, out _toRow, out _toCol);
_addresses = null;
_start = null;
_end = null;
}
Validate();
}