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