private void GetWorksheetName()
{
if (_args.FileName.ToLower().EndsWith("csv"))
{
_args.WorksheetName = Path.GetFileName(_args.FileName);
}
else if (_args.WorksheetIndex.HasValue)
{
var worksheetNames = ExcelUtilities.GetWorksheetNames(_args);
if (_args.WorksheetIndex.Value < worksheetNames.Count())
{
_args.WorksheetName = worksheetNames.ElementAt(_args.WorksheetIndex.Value);
}
else
{
throw new DataException("Worksheet Index Out of Range");
}
}
else if (String.IsNullOrEmpty(_args.WorksheetName) && String.IsNullOrEmpty(_args.NamedRangeName))
{
_args.WorksheetName = "Sheet1";
}
}
private void GetWorksheetName()
{
if (this.args.FileName.ToLower().EndsWith("csv"))
{
this.args.WorksheetName = Path.GetFileName(this.args.FileName);
}
else if (this.args.WorksheetIndex.HasValue)
{
var worksheetNames = ExcelUtilities.GetWorksheetNames(this.args);
var enumerable = worksheetNames as string[] ?? worksheetNames.ToArray();
if (this.args.WorksheetIndex.Value < enumerable.Count())
{
this.args.WorksheetName = enumerable.ElementAt(this.args.WorksheetIndex.Value);
}
else
{
throw new DataException("Worksheet Index Out of Range");
}
}
else if (string.IsNullOrEmpty(this.args.WorksheetName) && string.IsNullOrEmpty(this.args.NamedRangeName))
{
this.args.WorksheetName = "Sheet1";
}
}
/// <summary>
/// Executes the sql query and returns the data results
/// </summary>
/// <typeparam name="T">Data type in the main from clause (queryModel.MainFromClause.ItemType)</typeparam>
/// <param name="queryModel">Linq query model</param>
protected IEnumerable <object> GetDataResults(SqlParts sql, QueryModel queryModel)
{
IEnumerable <object> results;
OleDbDataReader data = null;
var conn = ExcelUtilities.GetConnection(_args);
var command = conn.CreateCommand();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.CommandText = sql.ToString();
command.Parameters.AddRange(sql.Parameters.ToArray());
try { data = command.ExecuteReader(); }
catch (OleDbException e)
{
if (e.Message.Contains(_args.WorksheetName))
{
throw new DataException(
string.Format("'{0}' is not a valid worksheet name in file {3}. Valid worksheet names are: '{1}'. Error received: {2}",
_args.WorksheetName, string.Join("', '", ExcelUtilities.GetWorksheetNames(_args.FileName).ToArray()), e.Message, _args.FileName), e);
}
if (!CheckIfInvalidColumnNameUsed(sql))
{
throw e;
}
}
var columns = ExcelUtilities.GetColumnNames(data);
LogColumnMappingWarnings(columns);
if (columns.Count() == 1 && columns.First() == "Expr1000")
{
results = GetScalarResults(data);
}
else if (queryModel.MainFromClause.ItemType == typeof(Row))
{
results = GetRowResults(data, columns);
}
else if (queryModel.MainFromClause.ItemType == typeof(RowNoHeader))
{
results = GetRowNoHeaderResults(data);
}
else
{
results = GetTypeResults(data, columns, queryModel);
}
}
finally
{
command.Dispose();
if (!_args.UsePersistentConnection)
{
conn.Dispose();
_args.PersistentConnection = null;
}
}
return(results);
}
/// <summary>
/// Executes the sql query and returns the data results
/// </summary>
/// <typeparam name="T">Data type in the main from clause (queryModel.MainFromClause.ItemType)</typeparam>
protected IEnumerable <object> GetRowResults(string sql)
{
IEnumerable <object> results;
OleDbDataReader data = null;
var conn = ExcelUtilities.GetConnection(_args);
var command = conn.CreateCommand();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.CommandText = sql.ToString();
try { data = command.ExecuteReader(); }
catch (OleDbException e)
{
if (e.Message.Contains(_args.WorksheetName))
{
throw new DataException(
string.Format("'{0}' is not a valid worksheet name in file {3}. Valid worksheet names are: '{1}'. Error received: {2}",
_args.WorksheetName, string.Join("', '", ExcelUtilities.GetWorksheetNames(_args.FileName).ToArray()), e.Message, _args.FileName), e);
}
}
var columns = ExcelUtilities.GetColumnNames(data);
LogColumnMappingWarnings(columns);
results = GetRowResults(data, columns);
}
finally
{
command.Dispose();
if (!_args.UsePersistentConnection)
{
conn.Dispose();
_args.PersistentConnection = null;
}
}
return(results);
}