/// <summary>
/// Convert type of cell value to its predefined type which is specified in the sheet's ScriptMachine setting file.
/// </summary>
protected object ConvertFrom(ICell cell, Type t)
{
object value = null;
if (t == typeof(float) || t == typeof(double) || t == typeof(short) || t == typeof(int) || t == typeof(long))
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = Activator.CreateInstance(t);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
//Get correct numeric value even the cell is string type but defined with a numeric type in a data class.
if (t == typeof(float))
{
value = Convert.ToSingle(cell.StringCellValue);
}
if (t == typeof(double))
{
value = Convert.ToDouble(cell.StringCellValue);
}
if (t == typeof(short))
{
value = Convert.ToInt16(cell.StringCellValue);
}
if (t == typeof(int))
{
value = Convert.ToInt32(cell.StringCellValue);
}
if (t == typeof(long))
{
value = Convert.ToInt64(cell.StringCellValue);
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
// Get value even if cell is a formula
if (t == typeof(float))
{
value = Convert.ToSingle(cell.NumericCellValue);
}
if (t == typeof(double))
{
value = Convert.ToDouble(cell.NumericCellValue);
}
if (t == typeof(short))
{
value = Convert.ToInt16(cell.NumericCellValue);
}
if (t == typeof(int))
{
value = Convert.ToInt32(cell.NumericCellValue);
}
if (t == typeof(long))
{
value = Convert.ToInt64(cell.NumericCellValue);
}
}
}
else if (t == typeof(string) || t.IsArray)
{
// HACK: handles the case that a cell contains numeric value
// but a member field in a data class is defined as string type.
// e.g. string s = "123"
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else
{
value = cell.StringCellValue;
}
}
else if (t == typeof(bool))
{
value = cell.BooleanCellValue;
}
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
var nc = new NullableConverter(t);
return(nc.ConvertFrom(value));
}
if (t.IsEnum)
{
// for enum type, first get value by string then convert it to enum.
value = cell.StringCellValue;
return(Enum.Parse(t, value.ToString(), true));
}
else if (t.IsArray)
{
if (t.GetElementType() == typeof(float))
{
return(ConvertExt.ToSingleArray((string)value));
}
if (t.GetElementType() == typeof(double))
{
return(ConvertExt.ToDoubleArray((string)value));
}
if (t.GetElementType() == typeof(short))
{
return(ConvertExt.ToInt16Array((string)value));
}
if (t.GetElementType() == typeof(int))
{
return(ConvertExt.ToInt32Array((string)value));
}
if (t.GetElementType() == typeof(long))
{
return(ConvertExt.ToInt64Array((string)value));
}
if (t.GetElementType() == typeof(string))
{
return(ConvertExt.ToStringArray((string)value));
}
}
// for all other types, convert its corresponding type.
return(Convert.ChangeType(value, t));
}
/// <summary>
/// Convert type of cell value to its predefined type which is specified in the sheet's ScriptMachine setting file.
/// </summary>
protected object ConvertFrom(ICell cell, Type t)
{
object value = null;
if (t == typeof(uint) || t == typeof(float) || t == typeof(double) || t == typeof(short) || t == typeof(int) || t == typeof(long))
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
//Get correct numeric value even the cell is string type but defined with a numeric type in a data class.
if (t == typeof(float))
{
value = Convert.ToSingle(cell.StringCellValue);
}
if (t == typeof(double))
{
value = Convert.ToDouble(cell.StringCellValue);
}
if (t == typeof(short))
{
value = Convert.ToInt16(cell.StringCellValue);
}
if (t == typeof(int))
{
value = Convert.ToInt32(cell.StringCellValue);
}
if (t == typeof(long))
{
value = Convert.ToInt64(cell.StringCellValue);
}
if (t == typeof(uint))
{
uint tmpValue = 0;
if (cell.StringCellValue == "-" || string.IsNullOrEmpty(cell.StringCellValue))
{
tmpValue = 0;
}
else if (!uint.TryParse(cell.StringCellValue.ToString(), out tmpValue))
{
byte[] idIntBytes = System.Text.Encoding.UTF8.GetBytes(cell.StringCellValue);
if (idIntBytes.Length == 4)
{
tmpValue = 0 | ((uint)idIntBytes[0] << 24)
| ((uint)idIntBytes[1] << 16)
| ((uint)idIntBytes[2] << 8)
| ((uint)idIntBytes[3] << 0);
}
else if (idIntBytes.Length > 4)
{
Debug.LogErrorFormat("ID:[{0}] is too long,length shoudn't be larger than 4", cell.StringCellValue);
}
}
else
{
Debug.LogErrorFormat("ID:[{0}] is not the 4-char format,convert to Numeric", cell.StringCellValue);
}
value = tmpValue;
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
// Get value even if cell is a formula
if (t == typeof(float))
{
value = Convert.ToSingle(cell.NumericCellValue);
}
if (t == typeof(double))
{
value = Convert.ToDouble(cell.NumericCellValue);
}
if (t == typeof(short))
{
value = Convert.ToInt16(cell.NumericCellValue);
}
if (t == typeof(int))
{
value = Convert.ToInt32(cell.NumericCellValue);
}
if (t == typeof(long))
{
value = Convert.ToInt64(cell.NumericCellValue);
}
}
}
else if (t == typeof(string) || t.IsArray)
{
// HACK: handles the case that a cell contains numeric value
// but a member field in a data class is defined as string type.
// e.g. string s = "123"
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else
{
value = cell.StringCellValue;
}
}
else if (t == typeof(bool))
{
value = cell.BooleanCellValue;
}
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
var nc = new NullableConverter(t);
return(nc.ConvertFrom(value));
}
if (t.IsEnum)
{
// for enum type, first get value by string then convert it to enum.
value = cell.StringCellValue;
return(Enum.Parse(t, value.ToString(), true));
}
else if (t.IsArray)
{
if (t.GetElementType() == typeof(float))
{
return(ConvertExt.ToSingleArray((string)value));
}
if (t.GetElementType() == typeof(double))
{
return(ConvertExt.ToDoubleArray((string)value));
}
if (t.GetElementType() == typeof(short))
{
return(ConvertExt.ToInt16Array((string)value));
}
if (t.GetElementType() == typeof(int))
{
return(ConvertExt.ToInt32Array((string)value));
}
if (t.GetElementType() == typeof(long))
{
return(ConvertExt.ToInt64Array((string)value));
}
if (t.GetElementType() == typeof(string))
{
return(ConvertExt.ToStringArray((string)value));
}
}
// for all other types, convert its corresponding type.
return(Convert.ChangeType(value, t));
}
