/// <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));
}
/// <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));
}
protected object UnityConvertFrom(ICell cell, Type t, CellType mycell, bool myiArr)
{
object value = null;
Debug.Log("mycell:" + mycell.ToString() + ",num:" + mycell + ",isArr:" + myiArr);
if (myiArr == false)
{
switch (mycell)
{
case CellType.Undefined:
var nc = new NullableConverter(t);
Debug.Log("CT:nc");
return(nc.ConvertFrom(value));
case CellType.String:
Debug.Log("CT:Str");
if (cell == null)
{
Debug.Log("CT:Str-cell is null");
value = "";
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
Debug.Log("NPOI.SS.UserModel.CellType.Blank:Str-cell is null");
value = "";
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
if (cell.StringCellValue != null)
{
Debug.Log("cell.StringCellV:" + cell.StringCellValue);
value = cell.StringCellValue;
}
else
{
Debug.Log("NPOI.SS.UserModel.CellType.String:Str-cell is null");
value = "";
}
}
}
}
t = typeof(string);
break;
case CellType.Short:
Debug.Log("CT:short");
if (cell == null)
{
value = (Int16)(0);
}
else
{
value = Convert.ToInt16(cell.StringCellValue);
}
t = typeof(short);
break;
case CellType.Int:
Debug.Log("CT:Int");
if (cell == null)
{
value = (Int32)(0);
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
value = Convert.ToInt32(cell.StringCellValue);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = (Int32)(0);
}
Debug.Log("intValue:" + value);
}
t = typeof(int);
break;
case CellType.Long:
Debug.Log("CT:Long");
if (cell == null)
{
value = (Int64)(0);
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
value = Convert.ToInt64(cell.StringCellValue);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = (Int64)(0);
}
}
t = typeof(long);
break;
case CellType.Float:
Debug.Log("CT:Float");
if (cell == null)
{
value = (float)(0f);
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
value = Convert.ToSingle(cell.StringCellValue);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = (float)(0f);
}
}
t = typeof(float);
break;
case CellType.Double:
Debug.Log("CT:Double");
if (cell == null)
{
value = (double)(0.0);
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
value = Convert.ToDouble(cell.StringCellValue);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = (double)(0.0);
}
}
Debug.Log("doubleValue:" + value);
t = typeof(double);
break;
case CellType.Enum:
Debug.Log("CT:Enum");
if (cell == null)
{
value = (0);
}
else
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
value = cell.NumericCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
value = Convert.ToDouble(cell.StringCellValue);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
value = 0;
}
}
return(Enum.Parse(t, value.ToString(), true));
case CellType.Bool:
Debug.Log("CT:Bool");
if (cell == null)
{
value = false;
}
else
{
value = TestBool(cell, t);
}
t = typeof(bool);
break;
}
}
else
{
// string tempCell = cell.StringCellValue;
switch (mycell)
{
case CellType.Bool:
Debug.Log("CT:Bool[]");
List <bool> tempListB = new List <bool>();
foreach (var i in ConvertExt.Split(cell.StringCellValue))
{
tempListB.Add(TestBool((string)i, t));
}
value = tempListB;
t = typeof(List <bool>);
break;
case CellType.Short:
Debug.Log("CT:Short[]");
List <short> tempListS = new List <short>();
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
tempListS.Add(Convert.ToInt16(cell.NumericCellValue));
}
else
{
List <short> addRS = ConvertExt.ToInt16Array(cell.StringCellValue).ToList <short>();
tempListS.AddRange(addRS);
}
value = tempListS;
t = typeof(List <short>);
break;
case CellType.Long:
Debug.Log("CT:Long[]");
List <long> tempListL = new List <long>();
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
tempListL.Add(Convert.ToInt64(cell.NumericCellValue));
}
else
{
List <long> addRL = ConvertExt.ToInt64Array(cell.StringCellValue).ToList <long>();
tempListL.AddRange(addRL);
}
value = tempListL;
t = typeof(List <long>);
break;
case CellType.Float:
Debug.Log("CT:Float[]");
List <float> tempListF = new List <float>();
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
tempListF.Add(Convert.ToSingle(cell.NumericCellValue));
}
else
{
List <float> addRF = ConvertExt.ToSingleArray(cell.StringCellValue).ToList <float>();
tempListF.AddRange(addRF);
}
value = tempListF;
t = typeof(List <float>);
break;
case CellType.Int:
Debug.Log("CT:Int[]");
List <int> tempListI = new List <int>();
if (cell == null)
{
Debug.Log("intArr,cell is null,add tempListI new List<int>()");
}
else
{
Debug.Log("intArr,cell.CellType:" + cell.CellType);
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
Debug.Log("C1,cell.NumericCellValue:" + cell.NumericCellValue);
tempListI.Add(Convert.ToInt32(cell.NumericCellValue));
Debug.Log("Fin!tempListI[0]:" + tempListI[tempListI.Count - 1]);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
Debug.Log("C2,cell.StringCellValue:" + cell.StringCellValue);
foreach (var i in ConvertExt.Split(cell.StringCellValue))
{
tempListI.Add(Int32.Parse((string)i));
}
Debug.Log("Fin!tempListI[0]:" + tempListI[tempListI.Count - 1]);
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
Debug.Log("C3,cell is blank:");
if (tempListI == null)
{
tempListI = new List <int>();
}
Debug.Log("Fin!tempListI is empty");
}
}
// value = tempListI;
// Type tt=typeof(List<int>);
// t = tt;
return(tempListI.ToArray());
// return Convert.ChangeType(value, value.GetType());
break;
case CellType.String:
Debug.Log("CT:String[]");
List <string> tempListSS = new List <string>();
List <string> addRSS = ConvertExt.ToStringArray(cell.StringCellValue).ToList <string>();
tempListSS.AddRange(addRSS);
value = tempListSS;
t = typeof(List <string>);
break;
}
}
Debug.Log("valueType:" + value.GetType().ToString() + ",tType:" + t.GetType().ToString());
return(Convert.ChangeType(value, t));
}
