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);
}
/// <summary>
/// Create a list of ItemBuilder objects and export them in a Excel file
/// </summary>
/// <param name="itemName">Item name</param>
/// <returns>Result of action</returns>
public ActionResult ExportHuman(string itemName, string instanceName, string connectionString)
{
string source = string.Format(CultureInfo.InvariantCulture, @"Excel:List({0})", itemName);
this.itemBuilder = new ItemBuilder(itemName, instanceName);
var res = ActionResult.NoAction;
try
{
this.workBook = new XSSFWorkbook();
this.sheet = (XSSFSheet)this.workBook.CreateSheet(this.itemBuilder.Definition.Layout.LabelPlural);
// HEADER
int countCells = 0;
this.CreateHeader();
ReadOnlyCollection <ItemBuilder> list = Read.Active(this.itemBuilder.Definition, instanceName);
int countRows = 1;
foreach (ItemBuilder item in list)
{
if (item == null)
{
continue;
}
ToolsXlsx.CreateRow(this.sheet, countRows);
countCells = 0;
foreach (ItemField field in this.itemBuilder.HumanFields)
{
this.sheet.GetRow(countRows).CreateCell(countCells);
if (item.ContainsKey(field.Name) && item[field.Name] != null)
{
if (item[field.Name].GetType().Name.ToUpperInvariant() == "JOBJECT")
{
string data = item[field.Name].ToString();
JObject x = item[field.Name] as JObject;
if (x["Description"] != null)
{
data = x["Description"].ToString();
}
this.sheet.GetRow(countRows).GetCell(countCells).SetCellValue(data);
}
else
{
string fieldName = string.Empty;
if (item.Definition.Fields.Any(f => f.Name == field.Name))
{
fieldName = field.Name;
}
else
{
if (item.Definition.Fields.Any(f => f.Name == field.Name + "Id"))
{
fieldName = string.Format(CultureInfo.InvariantCulture, "{0}Id", field.Name);
}
fieldName = field.Name;
}
if (!string.IsNullOrEmpty(fieldName))
{
ItemField fieldDefinition = item.Definition.Fields.Where(f => f.Name == fieldName).First();
this.SetCellValue(countRows, countCells, item[fieldName], fieldDefinition.DataType);
}
}
}
countCells++;
}
countRows++;
}
string path = HttpContext.Current.Request.PhysicalApplicationPath;
if (!path.EndsWith("\\", StringComparison.OrdinalIgnoreCase))
{
path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}", path);
}
path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}\Temp\{1}.{2}", path, this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension);
string link = string.Format(CultureInfo.GetCultureInfo("en-us"), @"/Temp/{0}.{1}", this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension);
using (FileStream fs = new FileStream(path, FileMode.Create))
{
this.workBook.Write(fs);
}
res.SetSuccess(link);
}
catch (IOException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
catch (NullReferenceException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
catch (NotSupportedException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
return(res);
}
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);
}
