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; }