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> 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);
}
internal static IEnumerable <string> GetColumnNames(string worksheetName, string fileName)
{
var args = new ExcelQueryArgs();
args.WorksheetName = worksheetName;
args.FileName = fileName;
return(GetColumnNames(args));
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args)
: base(CreateExecutor(args))
{
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 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)));
}
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> 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" });
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);
}
private static IQueryExecutor CreateExecutor(ExcelQueryArgs args)
{
return(new ExcelQueryExecutor(args));
}
internal static IEnumerable <string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
{
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);
}
