private void ValidateArgs(ExcelQueryArgs args)
{
log.Debug("ExcelQueryArgs = {0}", args);
if (args.FileName == null)
{
throw new ArgumentNullException(nameof(args));
}
if (!string.IsNullOrEmpty(args.StartRange) &&
!Regex.Match(args.StartRange, "^[a-zA-Z]{1,3}[0-9]{1,7}$").Success)
{
throw new ArgumentException($"StartRange argument '{args.StartRange}' is invalid format for cell name");
}
if (!string.IsNullOrEmpty(args.EndRange) &&
!Regex.Match(args.EndRange, "^[a-zA-Z]{1,3}[0-9]{1,7}$").Success)
{
throw new ArgumentException($"EndRange argument '{args.EndRange}' is invalid format for cell name");
}
if (args.NoHeader &&
!string.IsNullOrEmpty(args.StartRange) &&
args.FileName.ToLower().Contains(".csv"))
{
throw new ArgumentException("Cannot use WorksheetRangeNoHeader on csv files");
}
}
private void ValidateArgs(ExcelQueryArgs args)
{
if (_log.IsDebugEnabled)
{
_log.DebugFormat("ExcelQueryArgs = {0}", args);
}
if (args.FileName == null)
{
throw new ArgumentNullException("FileName", "FileName property cannot be null.");
}
if (!String.IsNullOrEmpty(args.StartRange) &&
!Regex.Match(args.StartRange, "^[a-zA-Z]{1,3}[0-9]{1,7}$").Success)
{
throw new ArgumentException(string.Format(
"StartRange argument '{0}' is invalid format for cell name", args.StartRange));
}
if (!String.IsNullOrEmpty(args.EndRange) &&
!Regex.Match(args.EndRange, "^[a-zA-Z]{1,3}[0-9]{1,7}$").Success)
{
throw new ArgumentException(string.Format(
"EndRange argument '{0}' is invalid format for cell name", args.EndRange));
}
if (args.NoHeader &&
!String.IsNullOrEmpty(args.StartRange) &&
args.FileName.ToLower().Contains(".csv"))
{
throw new ArgumentException("Cannot use WorksheetRangeNoHeader on csv files");
}
}
internal static IEnumerable <string> GetWorksheetNames(ExcelQueryArgs args)
{
var worksheetNames = new List <string>();
using (var conn = new OleDbConnection(GetConnectionString(args)))
{
conn.Open();
var excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });
worksheetNames.AddRange(
from DataRow row in excelTables.Rows
where IsTable(row)
let tableName = row["TABLE_NAME"].ToString()
.Replace("$", "")
.RegexReplace("(^'|'$)", "")
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName);
excelTables.Dispose();
}
return(worksheetNames);
}
internal static IEnumerable <string> GetWorksheetNames(string fileName)
{
var args = new ExcelQueryArgs();
args.FileName = fileName;
return(GetWorksheetNames(args));
}
internal static IEnumerable <string> GetColumnNames(ExcelQueryArgs args)
{
var columns = new List <string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (var command = conn.CreateCommand())
{
command.CommandText = string.Format("SELECT TOP 1 * FROM [{0}{1}]", string.Format("{0}{1}", args.WorksheetName, "$"), args.NamedRangeName);
var data = command.ExecuteReader();
columns.AddRange(GetColumnNames(data));
}
}
finally
{
if (!args.UsePersistentConnection)
{
conn.Dispose();
}
}
return(columns);
}
internal static IEnumerable<string> GetColumnNames(string worksheetName, string fileName)
{
var args = new ExcelQueryArgs();
args.WorksheetName = worksheetName;
args.FileName = fileName;
return GetColumnNames(args);
}
internal static IEnumerable <string> GetColumnNames(string worksheetName, string fileName, ExcelQueryArgs origArgs)
{
var args = new ExcelQueryArgs(origArgs);
args.WorksheetName = worksheetName;
args.FileName = fileName;
return(GetColumnNames(args));
}
internal ExcelQueryExecutor(ExcelQueryArgs args)
{
this.ValidateArgs(args);
this.args = args;
log.Debug("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString);
this.GetWorksheetName();
}
internal static IEnumerable <string> GetWorksheetNames(string fileName, ExcelQueryArgs origArgs)
{
var args = new ExcelQueryArgs(origArgs);
args.FileName = fileName;
args.ReadOnly = true;
return(GetWorksheetNames(args));
}
internal static string GetConnectionString(ExcelQueryArgs args)
{
var connString = "";
var fileNameLower = args.FileName.ToLower();
if (fileNameLower.EndsWith("xlsx") ||
fileNameLower.EndsWith("xlsm"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""",
args.FileName);
}
else if (fileNameLower.EndsWith("xlsb"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName);
}
else if (fileNameLower.EndsWith("csv"))
{
if (args.DatabaseEngine == DatabaseEngine.Jet)
{
connString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""text;HDR=YES;FMT=Delimited;IMEX=1""",
Path.GetDirectoryName(args.FileName));
}
else
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""text;Excel 12.0;HDR=YES;IMEX=1""",
Path.GetDirectoryName(args.FileName));
}
}
else
{
if (args.DatabaseEngine == DatabaseEngine.Jet)
{
connString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
args.FileName);
}
else
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName);
}
}
if (args.NoHeader)
connString = connString.Replace("HDR=YES", "HDR=NO");
if (args.ReadOnly)
connString = connString.Replace("IMEX=1", "IMEX=1;READONLY=TRUE");
return connString;
}
internal static IEnumerable <string> GetColumnNames(string worksheetName, string namedRange, string fileName)
{
var args = new ExcelQueryArgs();
args.WorksheetName = worksheetName;
args.NamedRangeName = namedRange;
args.FileName = fileName;
return(GetColumnNames(args));
}
internal static OleDbConnection GetConnection(ExcelQueryArgs args)
{
if (args.UsePersistentConnection)
{
return(args.PersistentConnection ?? (args.PersistentConnection = new OleDbConnection(GetConnectionString(args))));
}
return(new OleDbConnection(GetConnectionString(args)));
}
internal static string GetConnectionString(ExcelQueryArgs args)
{
var connString = "";
var fileNameLower = args.FileName.ToLower();
if (fileNameLower.EndsWith("xlsx") ||
fileNameLower.EndsWith("xlsm"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""",
args.FileName);
}
else if (fileNameLower.EndsWith("xlsb"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName);
}
else if (fileNameLower.EndsWith("csv"))
{
if (args.DatabaseEngine == DatabaseEngine.Jet)
{
connString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""text;HDR=YES;FMT=Delimited;IMEX=1""",
Path.GetDirectoryName(args.FileName));
}
else
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""text;Excel 12.0;HDR=YES;IMEX=1""",
Path.GetDirectoryName(args.FileName));
}
}
else
{
if (args.DatabaseEngine == DatabaseEngine.Jet)
{
connString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
args.FileName);
}
else
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName);
}
}
if (args.NoHeader)
{
connString = connString.Replace("HDR=YES", "HDR=NO");
}
return(connString);
}
internal static IEnumerable <string> GetColumnNames(string worksheetName, string fileName)
{
var args = new ExcelQueryArgs
{
WorksheetName = worksheetName,
FileName = fileName
};
return(GetColumnNames(args));
}
internal ExcelQueryExecutor(ExcelQueryArgs args)
{
ValidateArgs(args);
_args = args;
_connectionString = ExcelUtilities.GetConnectionString(args);
if (_log.IsDebugEnabled)
{
_log.DebugFormat("Connection String: {0}", _connectionString);
}
GetWorksheetName();
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
: base(CreateExecutor(args, logManagerFactory))
{
foreach (var property in typeof(T).GetProperties())
{
ExcelColumnAttribute att = (ExcelColumnAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelColumnAttribute));
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
args.ColumnMappings.Add(property.Name, att.ColumnName);
}
}
}
internal static OleDbConnection GetConnection(ExcelQueryArgs args)
{
if (args.UsePersistentConnection)
{
if (args.PersistentConnection == null)
args.PersistentConnection = new OleDbConnection(GetConnectionString(args));
return args.PersistentConnection;
}
return new OleDbConnection(GetConnectionString(args));
}
internal static string GetConnectionString(ExcelQueryArgs args)
{
var connString = "";
var fileNameLower = args.FileName.ToLower();
if (fileNameLower.EndsWith("xlsx") ||
fileNameLower.EndsWith("xlsm"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services={1:d};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""",
args.FileName,
args.OleDbServices);
}
else if (fileNameLower.EndsWith("xlsb"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services={1:d};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName,
args.OleDbServices);
}
else if (fileNameLower.EndsWith("csv"))
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services={1:d};Extended Properties=""text;Excel 12.0;HDR=YES;IMEX=1""",
Path.GetDirectoryName(args.FileName),
args.OleDbServices);
}
else
{
connString = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services={1:d};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""",
args.FileName,
args.OleDbServices);
}
if (args.NoHeader)
{
connString = connString.Replace("HDR=YES", "HDR=NO");
}
if (args.ReadOnly)
{
connString = connString.Replace("IMEX=1", "IMEX=1;READONLY=TRUE");
}
if (args.CodePageIdentifier > 0)
{
connString = connString.Replace("IMEX=1", string.Format("IMEX=1;CharacterSet={0:000}", args.CodePageIdentifier));
}
return(connString);
}
internal static IEnumerable<string> GetColumnNames(ExcelQueryArgs args)
{
var columns = new List<string>();
using (var conn = new OleDbConnection(GetConnectionString(args)))
using (var command = conn.CreateCommand())
{
conn.Open();
command.CommandText = string.Format("SELECT TOP 1 * FROM [{0}$]", args.WorksheetName);
var data = command.ExecuteReader();
columns.AddRange(GetColumnNames(data));
}
return columns;
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
string.Format("[{0}$]",
_args.WorksheetName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (_args.WorksheetName.ToLower().EndsWith(".csv"))
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
internal static IEnumerable <string> GetColumnNames(ExcelQueryArgs args)
{
var columns = new List <string>();
using (var conn = new OleDbConnection(GetConnectionString(args)))
using (var command = conn.CreateCommand())
{
conn.Open();
command.CommandText = string.Format("SELECT TOP 1 * FROM [{0}$]", args.WorksheetName);
var data = command.ExecuteReader();
columns.AddRange(GetColumnNames(data));
}
return(columns);
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
string.Format("[{0}$]",
_args.WorksheetName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (_args.WorksheetName.ToLower().EndsWith(".csv"))
{
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
}
internal ExcelQueryExecutor(ExcelQueryArgs args)
{
ValidateArgs(args);
_args = args;
string connectionString = args.PersistentConnection != null ?
args.PersistentConnection.ConnectionString :
ExcelUtilities.GetConnectionString(args);
if (_log.IsDebugEnabled)
{
_log.DebugFormat("Connection String: {0}", connectionString);
}
GetWorksheetName();
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
: base(QueryParser.CreateDefault(), CreateExecutor(args, logManagerFactory))
{
foreach (var property in typeof(T).GetProperties())
{
ExcelColumnAttribute att = (ExcelColumnAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelColumnAttribute));
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
var columnNames = ExcelUtilities.GetColumnNames(args);
args.ColumnMappings.Add(property.Name, !att.IsForced ?
att.ColumnName :
columnNames.ToList().Find(x => att.HasSimilarColumn(x)) ?? att.ColumnName);
}
}
}
internal ExcelQueryExecutor(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
{
ValidateArgs(args);
_args = args;
if (logManagerFactory != null)
{
_logManagerFactory = logManagerFactory;
_log = _logManagerFactory.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
}
if (_log != null && _log.IsDebugEnabled == true)
{
_log.DebugFormat("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString);
}
GetWorksheetName();
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs excelQueryArgs)
{
this.excelQueryArgs = excelQueryArgs;
SqlStatement = new SqlParts
{
Table = (string.IsNullOrEmpty(this.excelQueryArgs.StartRange)) ?
!string.IsNullOrEmpty(this.excelQueryArgs.NamedRangeName) &&
string.IsNullOrEmpty(this.excelQueryArgs.WorksheetName) ?
$"[{this.excelQueryArgs.NamedRangeName}]" :
$"[{this.excelQueryArgs.WorksheetName}${this.excelQueryArgs.NamedRangeName}]" :
$"[{this.excelQueryArgs.WorksheetName}${this.excelQueryArgs.StartRange}:{this.excelQueryArgs.EndRange}]"
};
if (!string.IsNullOrEmpty(this.excelQueryArgs.WorksheetName) && this.excelQueryArgs.WorksheetName.ToLower().EndsWith(".csv"))
{
this.SqlStatement.Table = this.SqlStatement.Table.Replace("$]", "]");
}
}
internal static IEnumerable <string> GetWorksheetNames(ExcelQueryArgs args)
{
var worksheetNames = new List <string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
if (excelTables != null)
{
worksheetNames.AddRange(
from DataRow row in excelTables.Rows
where IsTable(row)
let tableName = row["TABLE_NAME"].ToString()
.Replace("$", "")
.RegexReplace("(^'|'$)", "")
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName);
excelTables.Dispose();
}
}
finally
{
if (!args.UsePersistentConnection)
{
conn.Dispose();
}
}
return(worksheetNames);
}
internal static IEnumerable <string> GetNamedRanges(ExcelQueryArgs args)
{
var namedRanges = new List <string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
if (excelTables != null)
{
namedRanges.AddRange(
from DataRow row in excelTables.Rows
where IsNamedRange(row) &&
(!string.IsNullOrEmpty(args.WorksheetName) ? row["TABLE_NAME"].ToString().StartsWith(args.WorksheetName) : !IsWorkseetScopedNamedRange(row))
let tableName = row["TABLE_NAME"].ToString()
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName.Split('$').Last());
excelTables.Dispose();
}
}
finally
{
if (!args.UsePersistentConnection)
{
conn.Dispose();
}
}
return(namedRanges);
}
/// <summary>
/// Copy constructor
/// </summary>
internal ExcelQueryArgs(ExcelQueryArgs orig) : this()
{
if (orig != null)
{
FileName = orig.FileName;
WorksheetName = orig.WorksheetName;
WorksheetIndex = orig.WorksheetIndex;
ColumnMappings = orig.ColumnMappings;
Transformations = orig.Transformations;
NamedRangeName = orig.NamedRangeName;
StartRange = orig.StartRange;
EndRange = orig.EndRange;
NoHeader = orig.NoHeader;
StrictMapping = orig.StrictMapping;
ReadOnly = orig.ReadOnly;
UsePersistentConnection = orig.UsePersistentConnection;
PersistentConnection = orig.PersistentConnection;
TrimSpaces = orig.TrimSpaces;
OleDbServices = orig.OleDbServices;
}
}
internal static IEnumerable<string> GetColumnNames(ExcelQueryArgs args)
{
var columns = new List<string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = string.Format("SELECT TOP 1 * FROM [{0}$]", args.WorksheetName);
var data = command.ExecuteReader();
columns.AddRange(GetColumnNames(data));
}
}
finally
{
if (!args.UsePersistentConnection)
conn.Dispose();
}
return columns;
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args)
: base(CreateExecutor(args))
{
}
private static IQueryExecutor CreateExecutor(ExcelQueryArgs args)
{
return(new ExcelQueryExecutor(args));
}
internal static IEnumerable <string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
{
args.FileName = fileName;
return(GetWorksheetNames(args));
}
internal static IEnumerable<string> GetNamedRanges(ExcelQueryArgs args)
{
var namedRanges = new List<string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
var excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });
namedRanges.AddRange(
from DataRow row in excelTables.Rows
where IsNamedRange(row)
&& (!string.IsNullOrEmpty(args.WorksheetName) ? row["TABLE_NAME"].ToString().StartsWith(args.WorksheetName) : !IsWorkseetScopedNamedRange(row))
let tableName = row["TABLE_NAME"].ToString()
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName.Split('$').Last());
excelTables.Dispose();
}
finally
{
if (!args.UsePersistentConnection)
conn.Dispose();
}
return namedRanges;
}
internal static IEnumerable<string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.ReadOnly = true;
return GetWorksheetNames(args);
}
internal static IEnumerable <string> GetNamedRanges(string fileName, string worksheetName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.WorksheetName = worksheetName;
args.ReadOnly = true;
return(GetNamedRanges(args));
}
private static IQueryExecutor CreateExecutor(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
{
return(new ExcelQueryExecutor(args, logManagerFactory));
}
internal static IEnumerable<string> GetWorksheetNames(ExcelQueryArgs args)
{
var worksheetNames = new List<string>();
using (var conn = new OleDbConnection(GetConnectionString(args)))
{
conn.Open();
var excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });
worksheetNames.AddRange(
from DataRow row in excelTables.Rows
where IsTable(row)
let tableName = row["TABLE_NAME"].ToString()
.Replace("$", "")
.RegexReplace("(^'|'$)", "")
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName);
excelTables.Dispose();
}
return worksheetNames;
}
