public void ImportExcelToPersonnelTest()
{
IExcelImporter impoerter = new ExcelPersonnelNameImporter();
ExcelStructreTable tbl = impoerter.GetTableTemplate();
var exelUrlWithWrongGroupids = "D:\\temp\\work\\personnel - Copy.xlsx";
Stream stream = File.OpenRead(exelUrlWithWrongGroupids);
tbl.Confirmed = true;
impoerter.ImportExcel(stream, tbl);
impoerter.ValidateModels();
var WorkGroupscount = 0;
var personnelcount = 0;
using (var db = new EngineContext())
{
WorkGroupscount = db.WorkGroups.Count();
personnelcount = db.Personnels.Count();
}
impoerter.Save();
// آیا ذخیره می کند
using (var db = new EngineContext())
{
Assert.True(WorkGroupscount <= db.WorkGroups.Count());
Assert.True(personnelcount <= db.Personnels.Count());
WorkGroupscount = db.WorkGroups.Count();
personnelcount = db.Personnels.Count();
}
impoerter.Save();
// باید اگر دوباره ذخیره کردیم ، باید اپدییت کند و جدید ذخیره نکند
using (var db = new EngineContext())
{
Assert.True(WorkGroupscount == db.WorkGroups.Count());
Assert.True(personnelcount == db.Personnels.Count());
}
}
public void ImportExcelToPersonnelTestWrongFileStructure()
{
try
{
IExcelImporter impoerter = new ExcelPersonnelNameImporter();
ExcelStructreTable tbl = impoerter.GetTableTemplate();
var exelUrl = "D:\\temp\\work\\wrong.xlsx";
Stream stream = File.OpenRead(exelUrl);
impoerter.ImportExcel(stream, tbl);
Assert.True(false);
}
catch (ExcelImporterException E)
{
Assert.True(true);
}
}
public void ImportExcelToBiometryDataTestNotConfirmed()
{
IExcelImporter impoerter = new ExcelPersonnelNameImporter();
ExcelStructreTable tbl = impoerter.GetTableTemplate();
var exelUrl = "D:\\temp\\work\\personnel.xlsx";
Stream stream = File.OpenRead(exelUrl);
impoerter.ImportExcel(stream, tbl);
try
{
impoerter.ValidateModels();
Assert.True(false);
}
catch (ExcelImporterException e)
{
Assert.True(true);
}
}
public virtual ExcelStructreTable GetTableTemplate()
{
ExcelStructreTable tbl = new ExcelStructreTable();
var props = typeof(T).GetProperties()
.Where(p => p.PropertyType == typeof(int) ||
p.PropertyType == typeof(long) ||
p.PropertyType == typeof(DateTime) ||
p.PropertyType == typeof(double) ||
p.PropertyType == typeof(float) ||
p.PropertyType == typeof(string) ||
p.PropertyType == typeof(byte) ||
p.PropertyType == typeof(byte[]) ||
p.PropertyType == typeof(TimeSpan) ||
p.PropertyType == typeof(int?) ||
p.PropertyType == typeof(long?) ||
p.PropertyType == typeof(DateTime?) ||
p.PropertyType == typeof(double?) ||
p.PropertyType == typeof(float?) ||
p.PropertyType == typeof(string) ||
p.PropertyType == typeof(byte?) ||
p.PropertyType == typeof(TimeSpan?)
);
var c = 0;
foreach (var propertyInfo in props)
{
tbl.Nodes.Add(new ExcelStructreTableNode
{
ColumnName = propertyInfo.Name,
ColumnTranslate = propertyInfo.GetCustomAttributes(typeof(T), false)
.Where(a => a is BaseAttribute)
.Select(a => a as BaseAttribute)
.Select(a => a.Name)
.FirstOrDefault() ?? propertyInfo.Name,
NumberInExcel = c++,
});
}
return(tbl);
}
public void ImportExcelToPersonnelWrongGroupIds()
{
IExcelImporter impoerter = new ExcelPersonnelNameImporter();
ExcelStructreTable tbl = impoerter.GetTableTemplate();
var exelUrlWithWrongGroupids = "D:\\temp\\work\\personnel.xlsx";
Stream stream = File.OpenRead(exelUrlWithWrongGroupids);
tbl.Confirmed = true;
impoerter.ImportExcel(stream, tbl);
try
{
impoerter.ValidateModels();
Assert.True(false);
}
catch (ExcelImporterException e)
{
Assert.True(true);
}
}
public virtual void ImportExcel(Stream stream, ExcelStructreTable tbl)
{
Table = tbl;
Workbook workbook = new Workbook();
//Load workbook from stream
workbook.LoadFromStream(stream);
//Append a new sheet to worksheet and then name it
int count = workbook.Worksheets.Count + 1;
workbook.Worksheets.Create("Sheet" + count);
var worksheet = workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
{
throw new ExcelImporterException("هیچ worksheet ای یافت نشد");
}
/// باید ببیند ساختار درخواستی با ساختار واقعی فایل یکسان باشد
List <T> models = new List <T>();
foreach (var range in worksheet.Rows)
{
var model = new T();
for (var i = 0; i < range.Columns.Length; i++)
{
var node = tbl.Nodes.FirstOrDefault(n => n.NumberInExcel == i);
if (node == null)
{
continue;
}
var rangeColumn = range.Columns[i];
var propertyInfo = model.GetType().GetProperties().FirstOrDefault(p => p.Name == node.ColumnName);
if (propertyInfo == null)
{
throw new ExcelImporterException("پروپرتی یافت نشد");
}
try
{
if (string.IsNullOrEmpty(rangeColumn.Value))
{
if (node.IsRequired)
{
throw new ExcelImporterException(
$@"مقدار نال برای ستون ضروری ارسال شده است - ستون {node.ColumnTranslate} نال است");
}
}
else
{
var val = Convert.ChangeType(rangeColumn.Value, propertyInfo.PropertyType);
propertyInfo.SetValue(model, val);
}
}
catch (Exception E)
{
throw new ExcelImporterException(node.ColumnTranslate + "ستون ها با اولویت های اشتباه وارد شده اند - ");
}
}
models.Add(model);
}
this.Models = models;
/*//Save workbook to disk
* workbook.Save();*/
}