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