public void FileService_IfColumnMapped()
{
var file = new FileService();
var para = new CSVConvertParameter()
{
HasHeader = true,
Headers = new List <HeaderParameter>()
{
new HeaderParameter()
{
Index = 1,
Name = "test1",
Value = new ConfigurationItemViewModel()
{
dataType = DataTypeEnum.String,
id = Guid.NewGuid(),
name = "testcof",
required = true,
size = 0
}
},
new HeaderParameter()
{
Index = 2,
Name = "test2",
Value = new ConfigurationItemViewModel()
{
dataType = DataTypeEnum.String,
id = Guid.NewGuid(),
name = "testcof",
required = true,
size = 0
}
},
new HeaderParameter()
{
Index = 3,
Name = "test3",
Value = new ConfigurationItemViewModel()
{
dataType = DataTypeEnum.String,
id = Guid.NewGuid(),
name = "testcof",
required = true,
size = 0
}
},
}
};
var mapped = file.IfColumnMapped(para, 3);
Assert.IsTrue(mapped.Name == "test3");
}
public DataTable ConvertCSVResult(CSVConvertParameter parameter)
{
var filePath = $"{Environment.CurrentDirectory}upload/csv/{parameter.id}.csv";
DataTable dt = new DataTable("csvTable");
using (CsvFileReader reader = new CsvFileReader(filePath))
{
CsvRow row = new CsvRow();
int rowcount = 0;
var columnMap = new Dictionary <int, string>();
while (reader.ReadRow(row))
{
DataRow dr = dt.NewRow();
if (rowcount == 0)
{
foreach (var sColumn in row)
{
var column = CreateColumn(parameter, sColumn, row.IndexOf(sColumn));
if (column != null)
{
columnMap.Add(row.IndexOf(sColumn), column.ColumnName);
dt.Columns.Add(column);
}
}
if (!parameter.HasHeader)
{
PopulateRow(parameter, row, columnMap, dr, rowcount);
dt.Rows.Add(dr);
}
}
else
{
PopulateRow(parameter, row, columnMap, dr, rowcount);
dt.Rows.Add(dr);
}
rowcount++;
}
}
return(dt);
}
public JsonResult ConvertCSVToDatatable(CSVConvertParameter para)
{
var result = new ResultData <DataTableViewModel>()
{
data = new DataTableViewModel()
};
try
{
if (para == null)
{
throw new Exception("Parameter error.");
}
var dt = FileService.ConvertCSVResult(para);
var res = FileService.GetDataTableResult(dt);
result.data = res;
result.ok = true;
}
catch (ParseException e)
{
result.ok = false;
result.message = e.Message;
result.error = e.GetData();
LogHelper.LogException("UploadCSVFile Failed", e);
}
catch (Exception e)
{
result.ok = false;
result.error = new ParseExceptionParameter()
{
columnIndex = 0,
rowIndex = 0
};
result.message = e.Message;
LogHelper.LogException("UploadCSVFile Failed", e);
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public DataColumn CreateColumn(CSVConvertParameter parameter, string sColumn, int index)
{
DataColumn column = null;
var mapped = IfColumnMapped(parameter, index);
if (mapped != null)
{
column = new DataColumn();
var type = mapped.Value.dataType.ToString();
if (type == "Number")
{
type = "Int32";
}
column.DataType = Type.GetType($"System.{type}");
column.AllowDBNull = !mapped.Value.required;
if (mapped.Value.size > 0 && mapped.Value.dataType == DataTypeEnum.String)
{
column.MaxLength = mapped.Value.size;
}
column.ColumnName = mapped.Value.name;
}
return(column);
}
public void PopulateRow(CSVConvertParameter parameter, CsvRow row, Dictionary <int, string> columnMap, DataRow dr, int rowIndex)
{
foreach (var sColumn in row)
{
var currentIndex = row.IndexOf(sColumn);
var mapped = IfColumnMapped(parameter, currentIndex);
if (mapped != null)
{
var columnName = columnMap.FirstOrDefault(x => x.Key == currentIndex);
if (!string.IsNullOrEmpty(columnName.Value))
{
// required fields
if (mapped.Value.required && string.IsNullOrEmpty(sColumn))
{
// throw custom exception
throw new ParseException($"Column {row.IndexOf(sColumn)} not allow null values", new ParseExceptionParameter()
{
csv = row,
columnIndex = row.IndexOf(sColumn),
rowIndex = rowIndex + 1
});
}
else if (mapped.Value.size > 0 && sColumn.Length > mapped.Value.size &&
mapped.Value.dataType == DataTypeEnum.String)
{
// throw custom exception
throw new ParseException($"The data length of Column {row.IndexOf(sColumn)} is larger the configuration value: Max Length <= {mapped.Value.size} ",
new ParseExceptionParameter()
{
csv = row,
columnIndex = row.IndexOf(sColumn),
rowIndex = rowIndex + 1
});
}
else
{
switch (mapped.Value.dataType)
{
case DataTypeEnum.Number:
int intvalue = 0;
var parsed = Int32.TryParse(sColumn, out intvalue);
if (!parsed)
{
throw new ParseException($"The Column {row.IndexOf(sColumn)} can't be parsed to : Int32 ",
new ParseExceptionParameter()
{
csv = row,
columnIndex = row.IndexOf(sColumn),
rowIndex = rowIndex + 1
});
}
break;
case DataTypeEnum.Decimal:
Decimal decimalvalue = 0;
var parsedecimal = Decimal.TryParse(sColumn, out decimalvalue);
if (!parsedecimal)
{
throw new ParseException($"The Column {row.IndexOf(sColumn)} can't be parsed to : Decimal ",
new ParseExceptionParameter()
{
csv = row,
columnIndex = row.IndexOf(sColumn),
rowIndex = rowIndex + 1
});
}
break;
}
dr[columnName.Value] = sColumn;
}
}
}
}
}
public HeaderParameter IfColumnMapped(CSVConvertParameter parameter, int index)
{
return(parameter.Headers.FirstOrDefault(x => x.Value != null && x.Index == index));
}
