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(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);
}
private void ValidateArgs(ExcelQueryArgs args)
{
if (_log.IsDebugEnabled)
{
_log.DebugFormat("ExcelQueryArgs = {0}", args);
}
if (args.FileName == null)
{
throw new ArgumentNullException("args", "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");
}
}
public static IEnumerable<string> GetNamedRanges(string fileName, string worksheetName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.WorksheetName = worksheetName;
args.ReadOnly = true;
return GetNamedRanges(args);
}
protected virtual void Dispose(bool disposing)
{
if (!_disposed)
{
return;
}
if (disposing)
{
if (_queryArgs != null && _queryArgs.PersistentConnection != null)
{
try
{
_queryArgs.PersistentConnection.Dispose();
_queryArgs.PersistentConnection = null;
}
catch (Exception ex)
{
_log.Error("Error disposing OleDbConnection", ex);
}
}
}
_queryArgs = null;
_disposed = true;
}
internal static IEnumerable <string> GetColumnNames(string worksheetName, string fileName)
{
var args = new ExcelQueryArgs();
args.WorksheetName = worksheetName;
args.FileName = fileName;
return(GetColumnNames(args));
}
internal static string GetConnectionString(ExcelQueryArgs args)
{
string 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 == ExcelDatabaseEngine.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 == ExcelDatabaseEngine.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;
}
private ExcelQueryArgs PersistQueryArgs(ExcelQueryArgs args)
{
// We want to keep the persistent connection if there is one
if (this.queryArgs != null)
{
args.PersistentConnection = this.queryArgs.PersistentConnection;
}
return(this.queryArgs = args);
}
public ExcelQueryArgs PersistQueryArgs(ExcelQueryArgs args)
{
// We want to keep the persistent connection if there is one
if (_queryArgs != null)
{
args.PersistentConnection = _queryArgs.PersistentConnection;
}
return(_queryArgs = args);
}
public static void Register(this ExcelColumnAttribute att, ExcelQueryArgs args,
PropertyInfo property)
{
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
args.ColumnMappings.Add(property.Name, att.ColumnName);
Func<string, object> transOper = att.Transformation;
if (transOper != null)
args.Transformations.Add(property.Name, transOper);
}
}
public ExcelQueryExecutor(ExcelQueryArgs args)
{
ValidateArgs(args);
_args = args;
if (_log.IsDebugEnabled)
{
_log.DebugFormat("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString);
}
GetWorksheetName();
}
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));
}
/// <summary>
/// Enables Linq queries against a worksheet-scope named range that does not have a header row
/// </summary>
/// <param name="worksheetName">Name of the worksheet</param>
/// <param name="namedRangeName">Name of the worksheet-scope named range</param>
public ExcelQueryable <ExcelRowNoHeader> NamedRangeNoHeader(string worksheetName, string namedRangeName)
{
ExcelQueryArgs args = PersistQueryArgs(
new ExcelQueryArgs(GetConstructorArgs())
{
NoHeader = true,
WorksheetName = worksheetName,
NamedRangeName = namedRangeName
});
return(new ExcelQueryable <ExcelRowNoHeader>(args));
}
public static void Register(this ExcelColumnAttribute att, ExcelQueryArgs args,
PropertyInfo property)
{
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
args.ColumnMappings.Add(property.Name, att.ColumnName);
Func <string, object> transOper = att.Transformation;
if (transOper != null)
{
args.Transformations.Add(property.Name, transOper);
}
}
}
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)));
}
public void Test_NormalCase_ExcelQueryConstructorArgs()
{
_worksheetName = "Normal";
var args = new ExcelQueryArgs(_repo.GetConstructorArgs())
{
WorksheetName = _worksheetName
};
Assert.IsNotNull(args);
Assert.AreEqual(args.WorksheetName, _worksheetName);
Assert.AreEqual(args.ColumnMappings.Count, 2);
Console.Write("ColumnMapping[{0}]:{1}",
args.ColumnMappings.ElementAt(0).Key,
args.ColumnMappings.ElementAt(1).Value);
Assert.IsNull(args.NamedRangeName);
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
!String.IsNullOrEmpty(_args.NamedRangeName) && String.IsNullOrEmpty(_args.WorksheetName) ?
string.Format("[{0}]",
_args.NamedRangeName) :
string.Format("[{0}${1}]",
_args.WorksheetName, _args.NamedRangeName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (!string.IsNullOrEmpty(_args.WorksheetName) && _args.WorksheetName.ToLower().EndsWith(".csv"))
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
public void Test_NormalCase_ExcelQueryConstructorArgs()
{
_worksheetName = "Normal";
var args = new ExcelQueryArgs(_repo.GetConstructorArgs())
{
WorksheetName = _worksheetName
};
Assert.IsNotNull(args);
Assert.AreEqual(args.WorksheetName, _worksheetName);
Assert.AreEqual(args.ColumnMappings.Count, 2);
Console.Write("ColumnMapping[{0}]:{1}",
args.ColumnMappings.ElementAt(0).Key,
args.ColumnMappings.ElementAt(1).Value);
Assert.IsNull(args.NamedRangeName);
}
public static IEnumerable <string> GetNamedRanges(ExcelQueryArgs args)
{
var namedRanges = new List <string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DataTable excelTables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" }))
{
if (excelTables != null)
{
List <DataRow> rows = new List <DataRow>();
foreach (DataRow row in excelTables.Rows)
{
if (IsNamedRange(row) &&
(!string.IsNullOrEmpty(args.WorksheetName)
? row["TABLE_NAME"].ToString().StartsWith(args.WorksheetName)
: !IsWorkseetScopedNamedRange(row)))
{
rows.Add(row);
}
}
IEnumerable <string> names = from row in rows
let tableName = row["TABLE_NAME"].ToString().Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName.Split('$').Last();
namedRanges.AddRange(names);
}
}
}
finally
{
if (!args.UsePersistentConnection)
{
conn.Dispose();
}
}
return(namedRanges);
}
public static IEnumerable<string> GetNamedRanges(ExcelQueryArgs args)
{
var namedRanges = new List<string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
using (DataTable excelTables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] {null, null, null, "TABLE"}))
{
if (excelTables != null)
{
List<DataRow> rows=new List<DataRow>();
foreach (DataRow row in excelTables.Rows)
{
if (IsNamedRange(row)
&& (!string.IsNullOrEmpty(args.WorksheetName)
? row["TABLE_NAME"].ToString().StartsWith(args.WorksheetName)
: !IsWorkseetScopedNamedRange(row)))
{
rows.Add(row);
}
}
IEnumerable<string> names = from row in rows
let tableName = row["TABLE_NAME"].ToString().Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName.Split('$').Last();
namedRanges.AddRange(names);
}
}
}
finally
{
if (!args.UsePersistentConnection)
conn.Dispose();
}
return namedRanges;
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (queryArgs?.PersistentConnection != null)
{
try
{
this.queryArgs.PersistentConnection.Dispose();
this.queryArgs.PersistentConnection = null;
}
catch (Exception ex)
{
log.Error(ex, ex.Message);
}
}
}
this.queryArgs = null;
}
internal static IEnumerable <string> GetWorksheetNames(ExcelQueryArgs args)
{
var worksheetNames = new List <string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DataTable excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" }))
{
if (excelTables != null)
{
worksheetNames.AddRange(
from DataRow row in Enumerable.Cast <DataRow>(excelTables.Rows)
where IsTable(row)
let tableName = row["TABLE_NAME"].ToString()
.Replace("$", "")
.RegexReplace("(^'|'$)", "")
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName);
}
}
}
finally
{
if (!args.UsePersistentConnection)
{
conn.Dispose();
}
}
return(worksheetNames);
}
internal static string GetConnectionString(ExcelQueryArgs args)
{
string 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 == ExcelDatabaseEngine.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 == ExcelDatabaseEngine.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);
}
public static IEnumerable <string> GetNamedRanges(string fileName, string worksheetName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.WorksheetName = worksheetName;
args.ReadOnly = true;
return(GetNamedRanges(args));
}
internal static IEnumerable<string> GetWorksheetNames(ExcelQueryArgs args)
{
var worksheetNames = new List<string>();
var conn = GetConnection(args);
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
using (DataTable excelTables = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new Object[] {null, null, null, "TABLE"}))
{
if (excelTables != null)
{
worksheetNames.AddRange(
from DataRow row in Enumerable.Cast<DataRow>(excelTables.Rows)
where IsTable(row)
let tableName = row["TABLE_NAME"].ToString()
.Replace("$", "")
.RegexReplace("(^'|'$)", "")
.Replace("''", "'")
where IsNotBuiltinTable(tableName)
select tableName);
}
}
}
finally
{
if (!args.UsePersistentConnection)
conn.Dispose();
}
return worksheetNames;
}
internal static IEnumerable<string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.ReadOnly = true;
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> GetWorksheetNames(string fileName, ExcelQueryArgs args)
{
args.FileName = fileName;
args.ReadOnly = true;
return(GetWorksheetNames(args));
}
public MappingException(string prop, ExcelQueryArgs args)
{
mappedProp = prop;
this.args = args;
columnNames = (List <string>)ExcelHelperClass.GetColumnNames(args);
}
