private void SetQuestionStyle(ExcelWorksheet ws, QuestionResultVM questionVM, SurveyResultViewModel surveyVM)
{
ws.Cells[2, 2, 2, questionVM.QuestionAnswers.Count + 3].Merge = true;
ws.Cells[2, 2, 2, questionVM.QuestionAnswers.Count + 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[2, 2].Style.Font.Size = 16;
ws.Cells[2, 2].Style.Font.Bold = true;
ws.Row(2).Height = 24;
ws.Cells[2, 2, 2, questionVM.QuestionAnswers.Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[3, 2, 3, questionVM.QuestionAnswers.Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
ws.Cells[3, 3, 3, questionVM.QuestionAnswers.Count + 3].Style.Font.Size = 12;
ws.Cells[3, 3, 3, questionVM.QuestionAnswers.Count + 3].Style.Font.Bold = true;
var numbOfRows = 3;
if (surveyVM.Questions.Where(q => q.AnswerType != AnswerType.Text).Count() > 1)
{
surveyVM.Questions.Where(q => q.Id != questionVM.Id && q.AnswerType != AnswerType.Text).ToList().ForEach(
quest =>
{
numbOfRows++;
if (surveyVM.Questions.Where(q => q.AnswerType != AnswerType.Text).Count() > 1)
{
ws.Cells[numbOfRows, 2].Style.Font.Size = 14;
ws.Cells[numbOfRows, 2].Style.Font.Bold = true;
ws.Cells[numbOfRows, 2, numbOfRows, questionVM.QuestionAnswers.Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[numbOfRows, 2, numbOfRows, 2].Style.WrapText = true;
}
numbOfRows += quest.QuestionAnswers.Count;
ws.Cells[numbOfRows, 2, numbOfRows, questionVM.QuestionAnswers.Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
ws.Cells[numbOfRows - quest.QuestionAnswers.Count, 3, numbOfRows, quest.QuestionAnswers.Count + 3].Style.Font.Bold = true;
ws.Cells[numbOfRows - quest.QuestionAnswers.Count, 3, numbOfRows, quest.QuestionAnswers.Count + 3].Style.Font.Italic = true;
}
);
}
ws.Cells[2, 2, 2, questionVM.QuestionAnswers.Count + 3].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
ws.Cells[numbOfRows, 2, numbOfRows, questionVM.QuestionAnswers.Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
ws.Cells[2, 2, numbOfRows, 2].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
if (surveyVM.Questions.Where(q => q.AnswerType != AnswerType.Text).Count() > 1)
{
ws.Cells[4, 3, numbOfRows, 3].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
ws.Cells[2, questionVM.QuestionAnswers.Count + 3, numbOfRows, questionVM.QuestionAnswers.Count + 3].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
ws.Cells[2, 2, numbOfRows, questionVM.QuestionAnswers.Count + 3].Style.WrapText = true;
}
private void CreateQuestionWorksheet(ExcelPackage e, QuestionResultVM questionVM, SurveyResultViewModel surveyVM, Survey survey)
{
var ws = e.Workbook.Worksheets.Add(questionVM.QuestionText);
ws.SetValue(1, 3, questionVM.QuestionText);
var col = 3;
var question = survey.Questions.Where(q => q.Id == questionVM.Id).FirstOrDefault();
questionVM.QuestionAnswers.ForEach(qa =>
{
var row = 2;
ws.SetValue(row, col, qa.AnswerText);
var answer = question.QuestionAnswers.Where(an => an.Id == qa.Id).FirstOrDefault();
var users = answer.Users.Select(u => u.UserId);
row++;
surveyVM.Questions.Where(q => q.Id != questionVM.Id).ToList().ForEach(q =>
{
var _question = survey.Questions.Where(_q => _q.Id == q.Id).FirstOrDefault();
ws.SetValue(row, 1, q.QuestionText);
ws.Cells[row, 1, row, questionVM.QuestionAnswers.Count() + 2].Merge = true;
row++;
q.QuestionAnswers.ForEach(_qa =>
{
var _answer = _question.QuestionAnswers.Where(an => an.Id == _qa.Id).FirstOrDefault();
var _users = _answer.Users.Select(u => u.UserId);
if (col == 3)
{
ws.SetValue(row, 2, _qa.AnswerText);
}
var value = users.Where(u => _users.Contains(u)).Count();
ws.SetValue(row, col, value);
row++;
});
});
col++;
});
ws.Cells[1, 3, 1, questionVM.QuestionAnswers.Count() + 2].Merge = true;
}
public void CreateQuestionWorksheet(ExcelPackage e, QuestionResultVM questionVM, SurveyResultViewModel surveyVM, Survey survey)
{
var ws = e.Workbook.Worksheets.Add((surveyVM.Questions.IndexOf(questionVM) + 1) + ". " + questionVM.QuestionText);
//first row - question text
var question = survey.Questions.Where(q => q.Id == questionVM.Id).FirstOrDefault();
if (questionVM.AnswerType == AnswerType.Text)
{
this.CreateForTextType(ws, survey, question);
return;
}
ws.SetValue(2, 2, questionVM.QuestionText);
var col = 4;
//fill columns
questionVM.QuestionAnswers.ForEach(qa =>
{
var row = 3;
// [2,3] ponudjen odgovor
ws.SetValue(row, col, qa.AnswerText);
var answer = question.QuestionAnswers.Where(an => an.Id == qa.Id).FirstOrDefault();
var users = answer.Users.Select(u => u.UserId);
row++;
//other questions
if (survey.Questions.Where(q => q.AnswerType != AnswerType.Text).Count() > 1)
{
surveyVM.Questions.Where(q => q.Id != questionVM.Id && q.AnswerType != AnswerType.Text).ToList().ForEach(q =>
{
var _question = survey.Questions.Where(_q => _q.Id == q.Id).FirstOrDefault();
//question text
ws.SetValue(row, 2, q.QuestionText);
row++;
q.QuestionAnswers.ForEach(_qa =>
{
var _answer = _question.QuestionAnswers.Where(an => an.Id == _qa.Id).FirstOrDefault();
var _users = _answer.Users.Select(u => u.UserId);
//answer text
if (col == 4)
{
ws.SetValue(row, 3, _qa.AnswerText);
}
var value = users.Where(u => _users.Contains(u)).Count();
ws.SetValue(row, col, value);
row++;
});
});
col++;
}
else
{
col++;
}
});
{
var users = surveyService.UsersWhoFilledSurvey(survey.Users.Select(us => us.UserId).ToList()).ToList();
col++;
var row = 3;
var oldCol = col;
users.ToList().ForEach(user =>
{
col = oldCol;
ws.Cells[row, col].Value = survey.Anonymous ? "******" : user.FirstName;
col++;
ws.Cells[row, col].Value = survey.Anonymous ? "******" : user.LastName;
col++;
ws.Cells[row, col].Value = survey.Anonymous ? "******" : (user.City == null ? "" : user.City);
col++;
ws.Cells[row, col].Value = survey.Anonymous ? "******" : (user.Birthday == null ? "" : user.Birthday.ToString());
col++;
var questionWithUserAnswer = survey.Questions.Where(quest => quest.Id == questionVM.Id).FirstOrDefault();
questionWithUserAnswer.QuestionAnswers.ToList().ForEach(
a =>
{
var color = a.Users.Select(ua => ua.UserId).Contains(user.Id) ? Color.Green : Color.Red;
ws.Cells[row, col].Style.Font.Color.SetColor(color);
ws.Cells[row, col].Value = a.AnswerText;
col++;
});
row++;
});
ws.Cells[3, oldCol, row, col - 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
ws.Cells[3, oldCol, row, col - 1].Style.Font.Italic = true;
ws.Cells[3, oldCol, row, col - 1].Style.Font.Bold = true;
ws.Cells[3, oldCol, row, col - 1].Style.WrapText = true;
}
if (question.AnswerType == AnswerType.MultiCheckBox || question.AnswerType == AnswerType.MultiSelect)
{
int min = question.MinNumbOfAnswers;
int max = question.MaxNumbOfAnswers;
var answers = question.QuestionAnswers.Select(a => a.Id).ToList();
var oldCol = col;
col++;
oldCol = col;
var row = 3;
for (var i = min; i <= max; i++)
{
//create combinations
var combinations = new Combinations <int>(answers, i);
foreach (var combination in combinations)
{
col = oldCol;
//set answer text
combination.ToList().ForEach(answerId =>
{
ws.Cells[row, col].Value = question.QuestionAnswers.Where(a => a.Id == answerId).First().AnswerText;
col++;
});
var userAnswersByUser = question.UserAnswers.GroupBy(u => u.UserId);
var numbOfUsers = 0;
ws.Cells[row, oldCol, row, col - 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
foreach (var userAnswers in userAnswersByUser)
{
if (userAnswers.All(ua => combination.Contains(ua.AnswerId)) &&
combination.All(c => userAnswers.Select(ua => ua.AnswerId).Contains(c)))
{
numbOfUsers++;
}
}
ws.Cells[row, col].Value = numbOfUsers;
ws.Cells[row, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick, Color.MediumAquamarine);
row++;
}
}
}
this.SetQuestionStyle(ws, questionVM, surveyVM);
}
