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