public AprExcelResult ReadColumnExcel(ExcelPackage package)
{
try
{
var sheet = package.Workbook.Worksheets[2];
var firstRow = 10;
var lastRow = sheet.Dimension.End.Row;
var result = new AprExcelResult()
{
ConfirmedIds = new List <Guid>(),
NegativeIds = new List <Guid>(),
TotalCount = 0,
EmptyCount = 0
};
List <string> confirmedComments = new List <string>();
List <string> negativeComments = new List <string>();
for (var row = firstRow; row <= lastRow; row++)
{
if (sheet.Cells[row, 15].Value.ToString() == "Подтверждаю")
{
result.ConfirmedIds.Add(new Guid(sheet.Cells[row, 1].Text));
if (!String.IsNullOrEmpty(sheet.Cells[row, 16].Text))
{
confirmedComments.Add(sheet.Cells[row, 7].Text + " - " + sheet.Cells[row, 16].Text);
}
}
else
if (sheet.Cells[row, 15].Value.ToString() == "Не подтверждаю")
{
result.NegativeIds.Add(new Guid(sheet.Cells[row, 1].Text));
if (!String.IsNullOrEmpty(sheet.Cells[row, 16].Text))
{
negativeComments.Add(sheet.Cells[row, 7].Text + " - " + sheet.Cells[row, 16].Text);
}
}
else
{
result.EmptyCount++;
}
result.TotalCount++;
}
result.ConfirmedCount = result.ConfirmedIds.Count;
result.NegativeCount = result.NegativeIds.Count;
result.ConfirmedComments = String.Join("\r\n", confirmedComments.ToArray());
result.NegativeComments = String.Join("\r\n", negativeComments.ToArray());
return(result);
}
catch (Exception e)
{
return(null);
}
}
public void SetActivityResult(Guid activityId, AprExcelResult result)
{
var activity = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Activity");
activity.AddAllSchemaColumns();
var entity = activity.GetEntity(UserConnection, activityId);
var colorStatusId = GetActivityColorStatus(result);
if (entity == null)
{
return;
}
entity.SetColumnValue("AprIndicatorId", colorStatusId);
entity.SetColumnValue("DetailedResult", result.ConfirmedComments + "\r\n" + result.NegativeComments);
entity.Save();
}
public Guid GetActivityColorStatus(AprExcelResult result)
{
var colorStatusId = Guid.Empty;
if (result.TotalCount == result.ConfirmedCount)
{
colorStatusId = Green;
}
else
if (result.ConfirmedCount >= 0)
{
colorStatusId = Yellow;
}
else
{
colorStatusId = Red;
}
return(colorStatusId);
}