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