Exemple #1
0
        private DataTable GetColumnsSchema()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("TABLE_CATALOG", typeof(string));
            dt.Columns.Add("TABLE_SCHEMA", typeof(string));
            dt.Columns.Add("TABLE_NAME", typeof(string));
            dt.Columns.Add("COLUMN_NAME", typeof(string));
            dt.Columns.Add("DATA_TYPE", typeof(string));
            dt.Columns.Add("IS_NULLABLE", typeof(string));
            dt.Columns.Add("DataType", typeof(string));
            dt.Columns.Add("ProviderDbType", typeof(ExcelDbType));

            List <ColumnInfo> columns = this.GetColumns();

            foreach (ColumnInfo column in columns)
            {
                dt.Rows.Add(
                    string.Empty,
                    "Excel",
                    column.TableAlias,
                    column.ColumnName,
                    column.DataType,
                    "YES",
                    DataTypeUtil.GetDotNetType(column.DataType).FullName,
                    column.DataType)
                ;
            }
            return(dt);
        }
 private DbType GetDbType(object value)
 {
     if (value == null)
     {
         return(DbType.Object);
     }
     return(DataTypeUtil.GetDbType(value.GetType()));
 }
Exemple #3
0
        private DataTable GetDataTypesSchema()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("TypeName", typeof(string));
            dt.Columns.Add("ProviderDbType", typeof(int));
            dt.Columns.Add("DataType", typeof(string));

            foreach (ExcelDbType dbType in Enum.GetValues(typeof(ExcelDbType)))
            {
                dt.Rows.Add(DataTypeUtil.GetTypeName(dbType), (int)dbType, DataTypeUtil.GetDotNetType(dbType).FullName);
            }

            return(dt);
        }
        public override Type GetFieldType(int ordinal)
        {
            ColumnInfo column = this._resultSet.GetColumn(ordinal);

            return(DataTypeUtil.GetDotNetType(column.DataType));
        }
        private object GetCellValue(IRange cell, ExcelDbType expectedType, bool displayZero = true)
        {
            object v = cell.Value;

            if (v == null)
            {
                return(null);
            }

            if (!displayZero)
            {
                if ((v is double && (double)v == 0) || (v is TimeSpan && (TimeSpan)v == TimeSpan.Zero))
                {
                    return(null);
                }
            }

            //
            if (v is CalcError)
            {
                switch ((CalcError)v)
                {
                case CalcError.Null:
                    return("#NULL!");

                case CalcError.Div0:
                    return("#DIV/0!");

                case CalcError.Value:
                    return("#VALUE!");

                case CalcError.Ref:
                    return("#REF!");

                case CalcError.Name:
                    return("#NAME?");

                case CalcError.Num:
                    return("#NUM!");

                case CalcError.NA:
                    return("#N/A");

                case CalcError.GettingData:
                    return("#GETTING_DATA");

                default:
                    return(null);
                }
            }

            //
            try
            {
                switch (expectedType)
                {
                case ExcelDbType.Boolean:
                    return(DataTypeUtil.ToBoolean(v));

                case ExcelDbType.Date:
                case ExcelDbType.DateTime:
                    return(DataTypeUtil.ToDateTime(v));

                case ExcelDbType.Time:
                    return(DataTypeUtil.ToTimeSpan(v));

                case ExcelDbType.Decimal:
                case ExcelDbType.Double:
                case ExcelDbType.Single:
                    return(DataTypeUtil.ToDouble(v));

                case ExcelDbType.SByte:
                case ExcelDbType.Int16:
                case ExcelDbType.Int32:
                    return(DataTypeUtil.ToInt(v));

                case ExcelDbType.Int64:
                    return(DataTypeUtil.ToLong(v));

                case ExcelDbType.Object:
                    return(v);

                case ExcelDbType.String:
                    return(DataTypeUtil.ToString(v));

                default:
                    return(null);
                }
            }
            catch
            {
                return(null);
            }
        }
Exemple #6
0
        private ExcelDbType DetectColumnDataType(IRange column)
        {
            var connString = this._connString;

            switch (connString.TypeDetectionScheme)
            {
            //
            case ExcelTypeDetectionScheme.RowScan:
            {
                bool hasNull   = false;
                bool hasString = false;

                bool hasNumber   = false;
                bool hasBoolean  = false;
                bool hasDateTime = false;

                int rowScanDep = Math.Min(connString.RowScanDepth, column.Rows.Count);
                for (int r = 0; r < rowScanDep; r++)
                {
                    object value = column.Cells[r].Value;

                    if (value == null)
                    {
                        hasNull = true;
                    }
                    else if (value is string)
                    {
                        hasString = true;
                    }
                    else if (value is bool)
                    {
                        hasBoolean = true;
                    }
                    else if (DataTypeUtil.IsDate(value))
                    {
                        hasDateTime = true;
                    }
                    else if (DataTypeUtil.IsNumber(value))
                    {
                        hasNumber = true;
                    }
                    else         //
                    {
                        hasString = true;
                    }

                    if (hasString)
                    {
                        break;
                    }
                }

                if (hasString || (hasNull && !hasNumber && !hasBoolean && !hasDateTime))
                {
                    return(ExcelDbType.String);
                }
                if (hasNumber && !hasBoolean && !hasDateTime)
                {
                    return(ExcelDbType.Double);
                }
                if (!hasNumber && hasBoolean && !hasDateTime)
                {
                    return(ExcelDbType.Boolean);
                }
                if (!hasNumber && !hasBoolean && hasDateTime)
                {
                    return(ExcelDbType.DateTime);
                }

                return(ExcelDbType.String);
            }

            //
            case ExcelTypeDetectionScheme.ColumnFormat:
            {
                return(GetFormatType(column.NumberFormat));
            }

            //
            case ExcelTypeDetectionScheme.None:
            default:
                return(ExcelDbType.String);
            }
        }