Пример #1
0
 internal static IEnumerable<string> GetColumnNames(string worksheetName, string fileName)
 {
     var args = new ExcelQueryArgs();
     args.WorksheetName = worksheetName;
     args.FileName = fileName;
     return GetColumnNames(args);
 }
Пример #2
0
        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);
        }
Пример #3
0
        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");
            }
        }
Пример #4
0
 public static IEnumerable<string> GetNamedRanges(string fileName, string worksheetName, ExcelQueryArgs args)
 {
     args.FileName = fileName;
     args.WorksheetName = worksheetName;
     args.ReadOnly = true;
     return GetNamedRanges(args);
 }
Пример #5
0
        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;
        }
Пример #6
0
        internal static IEnumerable <string> GetColumnNames(string worksheetName, string fileName)
        {
            var args = new ExcelQueryArgs();

            args.WorksheetName = worksheetName;
            args.FileName      = fileName;
            return(GetColumnNames(args));
        }
Пример #7
0
        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;
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
 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);
     }
 }
Пример #11
0
        public ExcelQueryExecutor(ExcelQueryArgs args)
        {
            ValidateArgs(args);
            _args = args;

            if (_log.IsDebugEnabled)
            {
                _log.DebugFormat("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString);
            }

            GetWorksheetName();
        }
Пример #12
0
        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));
        }
Пример #13
0
        /// <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));
        }
Пример #14
0
 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);
         }
     }
 }
Пример #15
0
        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);
        }
Пример #19
0
        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);
        }
Пример #20
0
        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;
        }
Пример #21
0
        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;
        }
Пример #22
0
        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);
        }
Пример #23
0
        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);
        }
Пример #24
0
 public static IEnumerable <string> GetNamedRanges(string fileName, string worksheetName, ExcelQueryArgs args)
 {
     args.FileName      = fileName;
     args.WorksheetName = worksheetName;
     args.ReadOnly      = true;
     return(GetNamedRanges(args));
 }
Пример #25
0
        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;
        }
Пример #26
0
 internal static IEnumerable<string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
 {
     args.FileName = fileName;
     args.ReadOnly = true;
     return GetWorksheetNames(args);
 }
Пример #27
0
        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;
        }
Пример #28
0
 internal static IEnumerable <string> GetWorksheetNames(string fileName, ExcelQueryArgs args)
 {
     args.FileName = fileName;
     args.ReadOnly = true;
     return(GetWorksheetNames(args));
 }
Пример #29
0
 public MappingException(string prop, ExcelQueryArgs args)
 {
     mappedProp  = prop;
     this.args   = args;
     columnNames = (List <string>)ExcelHelperClass.GetColumnNames(args);
 }