private static object GetCellValueForDataBase(ItemField field, ICell cell)
{
if (cell == null || cell.CellType == CellType.Blank)
{
return(null);
}
object cellValue = null;
switch (field.DataType)
{
case FieldDataType.Text:
cellValue = ToolsXlsx.GetValue <string>(cell);
break;
case FieldDataType.Decimal:
case FieldDataType.NullableDecimal:
cellValue = ToolsXlsx.GetValue <decimal?>(cell);
break;
case FieldDataType.Long:
case FieldDataType.NullableLong:
case FieldDataType.Integer:
case FieldDataType.NullableInteger:
case FieldDataType.Float:
case FieldDataType.NullableFloat:
switch (cell.CellType)
{
case CellType.Numeric:
cellValue = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
break;
default:
cellValue = cell.StringCellValue;
break;
}
break;
case FieldDataType.DateTime:
case FieldDataType.NullableDateTime:
var dateTimeValue = ToolsXlsx.GetValue <DateTime?>(cell);
if (dateTimeValue.HasValue)
{
cellValue = string.Format(CultureInfo.InvariantCulture, @"""{0:dd/MM/yyyy}", dateTimeValue);
}
break;
case FieldDataType.Boolean:
case FieldDataType.NullableBoolean:
if (cell.CellType == CellType.String)
{
string cellData = cell.StringCellValue.ToUpperInvariant();
if (string.IsNullOrEmpty(cellData))
{
cellValue = null;
break;
}
cellValue = cellData == ConstantValue.True.ToUpperInvariant() ? ConstantValue.True : ConstantValue.False;
break;
}
cellValue = ToolsXlsx.GetBooleanValue(cell);
break;
default:
cellValue = null;
break;
}
return(cellValue);
}
private static string GetCellValueForJson(FieldDataType dataType, ICell cell, int?length)
{
if (cell == null || cell.CellType == CellType.Blank)
{
return(string.Empty);
}
string cellValue = string.Empty;
switch (dataType)
{
case FieldDataType.Text:
case FieldDataType.Textarea:
cellValue = string.Format(@"""{0}""", ToolsJson.JsonCompliant(ToolsXlsx.GetValue <string>(cell)));
break;
case FieldDataType.Decimal:
case FieldDataType.NullableDecimal:
var decimalValue = ToolsXlsx.GetValue <decimal?>(cell);
if (decimalValue.HasValue)
{
cellValue = string.Format(CultureInfo.InvariantCulture, "{0:################0.##################}", decimalValue);
}
else
{
cellValue = ConstantValue.Null;
}
break;
case FieldDataType.Long:
case FieldDataType.NullableLong:
case FieldDataType.Integer:
case FieldDataType.NullableInteger:
case FieldDataType.Float:
case FieldDataType.NullableFloat:
switch (cell.CellType)
{
case CellType.Numeric:
cellValue = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
break;
default:
cellValue = cell.StringCellValue;
break;
}
break;
case FieldDataType.Time:
case FieldDataType.NullableTime:
var timeValue = ToolsXlsx.GetValue <DateTime?>(cell);
if (timeValue.HasValue)
{
cellValue = string.Format(CultureInfo.InvariantCulture, @"""{0:hh:mm}""", timeValue);
}
else
{
cellValue = ConstantValue.Null;
}
break;
case FieldDataType.DateTime:
case FieldDataType.NullableDateTime:
var dateTimeValue = ToolsXlsx.GetValue <DateTime?>(cell);
if (dateTimeValue.HasValue)
{
cellValue = string.Format(CultureInfo.InvariantCulture, @"""{0:dd/MM/yyyy}""", dateTimeValue);
}
else
{
cellValue = ConstantValue.Null;
}
break;
case FieldDataType.Boolean:
case FieldDataType.NullableBoolean:
if (cell.CellType == CellType.String)
{
string cellData = cell.StringCellValue.ToUpperInvariant();
if (string.IsNullOrEmpty(cellData))
{
cellValue = string.Empty;
break;
}
cellValue = cellData == ConstantValue.True.ToUpperInvariant() ? ConstantValue.True : ConstantValue.False;
break;
}
var booleanValue = ToolsXlsx.GetBooleanValue(cell);
cellValue = booleanValue.HasValue ? (booleanValue.Value ? ConstantValue.True : ConstantValue.False) : string.Empty;
break;
case FieldDataType.Url:
case FieldDataType.Email:
cellValue = ToolsXlsx.GetValue <string>(cell);
break;
default:
cellValue = string.Format(CultureInfo.InvariantCulture, @"""{0}""", dataType);
break;
}
return(cellValue);
}