public static ExcelDocument LoadMainProfileExcel(string filePath, string infoFileName, string resultFileName, Dictionary <string, string> excelProfileMap)
{
List <ExcelQuestionType> infoListContent;
List <ExcelResult> answerListContent;
List <ExcelProfile> profilesListContent = new List <ExcelProfile>();
infoListContent = GetExcelService.GetProfileNamesEP(filePath, infoFileName);
answerListContent = GetExcelService.GetResultsEP(filePath, resultFileName);
foreach (var item in excelProfileMap)
{
ExcelQuestionType profileInfo = infoListContent.SingleOrDefault(info => info.ProfileName == item.Key);
string profileType = profileInfo.GetProfileType();
List <ExcelQuestion> questions = GetExcelService.GetQuestionsEP(filePath, item.Value);
ExcelProfile excelProfile = new ExcelProfile
{
Id = profileInfo.Id,
Name = profileInfo.ProfileName,
Type = profileType,
Answers = profileInfo.Answers,
Questions = questions
};
profilesListContent.Add(excelProfile);
}
string documentName = Path.GetFileNameWithoutExtension(filePath);
return(new ExcelDocument
{
DocumentName = documentName,
AnswerListContent = answerListContent,
ProfilesListContent = profilesListContent
});
}
public static ExcelDocument LoadMainProfileDB(MainProfile mainProfile)
{
List <ExcelResult> answerListContent = new List <ExcelResult>();
List <ExcelProfile> profilesListContent = new List <ExcelProfile>();
using (profileContext db = new profileContext())
{
mainProfile = db.MainProfile.SingleOrDefault(p => p == mainProfile);
db.Entry(mainProfile).Collection(t => t.Profile).Load();
db.Entry(mainProfile).Collection(t => t.Questioned).Load();
foreach (var profileItem in mainProfile.Profile)
{
db.Entry(profileItem).Collection(t => t.Question).Load();
db.Entry(profileItem).Collection(t => t.Result).Load();
db.Entry(profileItem).Reference(t => t.Type).Load();
List <ExcelQuestion> questions = new List <ExcelQuestion>();
foreach (var questionItem in profileItem.Question)
{
if (questionItem.LeftLimit == null && questionItem.RightLimit == null)
{
questions.Add(new ExcelQuestion {
Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = "", RightLimit = ""
});
}
else
{
questions.Add(new ExcelQuestion {
Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = questionItem.LeftLimit, RightLimit = questionItem.RightLimit
});
}
}
ExcelProfile excelProfile = new ExcelProfile {
Id = profileItem.SerialNumber, Answers = profileItem.Answer, Name = profileItem.Name, Type = profileItem.Type.Type, Questions = questions
};
profilesListContent.Add(excelProfile);
foreach (var resultItem in profileItem.Result)
{
string questionedId = mainProfile.Questioned.SingleOrDefault(q => q.Id == resultItem.QuestionedId).Number;
ExcelResult excelResult = new ExcelResult
{
Id = questionedId,
ProfileNum = resultItem.Profile.SerialNumber,
QuestionNum = resultItem.Question.SerialNumber,
Answer = resultItem.Answer
};
answerListContent.Add(excelResult);
}
}
return(new ExcelDocument {
DocumentName = mainProfile.Name, AnswerListContent = answerListContent, ProfilesListContent = profilesListContent
});
}
}
private void saveQuestionInfoBtn_Click(object sender, EventArgs e)
{
DataGridViewRow selectedDataGridRow = saveQuestionInfoBtn.Tag as DataGridViewRow;
ExcelQuestion selectedQuestion = selectedDataGridRow.Cells["question"].Value as ExcelQuestion;
ExcelProfile selectedProfile = mainTab.SelectedTab.Tag as ExcelProfile;
string filePath = CommonService.SaveFilePath("*.xlsx|*.xlsx", "Вопрос " + selectedQuestion.Id + " результаты");
if (filePath != null)
{
DataManipulationService.SaveQuestionInfoExcel(selectedQuestion, selectedProfile, Document, filePath);
}
}
public static void SaveQuestionInfoExcel(ExcelQuestion excelQuestion, ExcelProfile excelProfile, ExcelDocument document, string filePath)
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
var questionInfo = ProccesingDataService.GetQuestionInfo(excelQuestion, excelProfile, document);
var points = questionInfo.Item1;
var respondedCount = questionInfo.Item2;
var questionedCount = questionInfo.Item3;
excelPackage.Workbook.Properties.Author = "User";
excelPackage.Workbook.Properties.Title = excelQuestion.GetForSeries();
excelPackage.Workbook.Properties.Created = DateTime.Now;
ExcelWorksheet infoSheet = excelPackage.Workbook.Worksheets.Add("Результаты");
int rowInfoNumber = 1;
infoSheet.Cells[rowInfoNumber, 1].Value = excelProfile.Name;
rowInfoNumber++;
int column = WriteRowExcel(new string[] { "Номер вопроса", "Вопрос", "Число ответивших", "Число прошедших" }, points.Keys.ToArray(), infoSheet, 1, rowInfoNumber);
if (points.ContainsKey("другое"))
{
var questionOpenInfo = ProccesingDataService.GetOpenInfo(excelQuestion, excelProfile, document);;
var openPoints = questionOpenInfo.Item1;
WriteRowExcel(new string[] { }, openPoints.Keys.ToArray(), infoSheet, column, rowInfoNumber);
}
rowInfoNumber++;
//-----------------------------------------------
string[] pointsValue = points.Values.Select(x => x.ToString()).ToArray();
column = WriteRowExcel(new string[] { excelQuestion.Id.ToString(), excelQuestion.GetForSeries(), respondedCount.ToString(), questionedCount.ToString() }, pointsValue, infoSheet, 1, rowInfoNumber);
if (points.ContainsKey("другое"))
{
var questionOpenInfo = ProccesingDataService.GetOpenInfo(excelQuestion, excelProfile, document);;
var openPoints = questionOpenInfo.Item1;
string[] openPointsValue = openPoints.Values.Select(x => x.ToString()).ToArray();
column = WriteRowExcel(new string[] { }, openPointsValue, infoSheet, column, rowInfoNumber);
}
infoSheet.Cells[infoSheet.Dimension.Address].AutoFitColumns();
infoSheet.Column(2).Width = 125;
infoSheet.Column(2).Style.WrapText = true;
FileInfo fi = new FileInfo(filePath);
excelPackage.SaveAs(fi);
}
}
private void openAnswerInfoBtn_Click(object sender, EventArgs e)
{
DataGridViewRow selectedDataGridRow = saveQuestionInfoBtn.Tag as DataGridViewRow;
ExcelQuestion selectedQuestion = selectedDataGridRow.Cells["question"].Value as ExcelQuestion;
ExcelProfile selectedProfile = mainTab.SelectedTab.Tag as ExcelProfile;
Dictionary <ExcelQuestion, Tuple <Dictionary <string, int>, int, int> > questionInfoMap = new Dictionary <ExcelQuestion, Tuple <Dictionary <string, int>, int, int> >();
questionInfoMap.Add(selectedQuestion, ProccesingDataService.GetOpenInfo(selectedQuestion, selectedProfile, Document));
using (QuestionInfoForm qif = new QuestionInfoForm())
{
qif.QuestionInfoMap = questionInfoMap;
qif.ShowDialog();
}
}
private void SaveFunctionAccept()
{
Dictionary <string, ExcelProfile> excelProfileMap = new Dictionary <string, ExcelProfile>();
try
{
InfoFileName = tableL2.Controls["firstControl"].Text;
if (string.IsNullOrEmpty(InfoFileName))
{
throw new Exception("Пропущено поле с названием файла с информацией об анкетах.");
}
ResultFileName = tableL2.Controls["secondControl"].Text;
if (string.IsNullOrEmpty(ResultFileName))
{
throw new Exception("Пропущено поле с названием файла с результатами анкетирования.");
}
foreach (DataGridViewRow rowItem in chooseDG.Rows)
{
ExcelProfile profile = rowItem.Cells["profile"].Value as ExcelProfile;
if (rowItem.Cells["sheetName"].Value == null)
{
throw new Exception("Пропущено поле с названием файла с вопросами для анкеты \"" + profile.Name + "\".");
}
string sheetName = rowItem.Cells["sheetName"].Value.ToString();
if (string.IsNullOrEmpty(sheetName))
{
throw new Exception("Пропущено поле с названием файла с вопросами для анкеты \"" + profile.Name + "\".");
}
else
{
excelProfileMap.Add(sheetName, profile);
}
}
ExcelProfileMap = excelProfileMap;
Status = true;
}
catch (Exception ex)
{
Status = false;
MessageBox.Show(ex.Message);
}
}
public static void SaveCSV(string infoFileName, string resultFileName, Dictionary <string, ExcelProfile> excelProfileMap, ExcelDocument document)
{
List <List <object> > questionFiles = new List <List <object> >();
List <string> questionFilesName = new List <string>();
List <object> infoFile = new List <object>();
foreach (var profileItem in excelProfileMap)
{
string sheetName = profileItem.Key;
ExcelProfile profile = profileItem.Value;
infoFile.Add(new { ID = profile.GetId(), Name = profile.GetName(), Answers = profile.GetAnswers() });
List <object> questionFile = new List <object>();
foreach (var questuionItem in profile.Questions)
{
if (!string.IsNullOrEmpty(questuionItem.LeftLimit) && !string.IsNullOrEmpty(questuionItem.RightLimit))
{
questionFile.Add(new { ID = questuionItem.GetId(), Content = questuionItem.GetContent(), LeftLimit = questuionItem.GetLeftLimit(), RightLimit = questuionItem.GetRightLimit() });
}
else
{
questionFile.Add(new { ID = questuionItem.GetId(), Content = questuionItem.GetContent() });
}
}
questionFiles.Add(questionFile);
questionFilesName.Add(sheetName);
}
List <object> resultFile = new List <object>();
resultFile.Add(new { ID = "id", Profile = "анкета", QuestionNum = "номер вопроса", Answer = "ответ" });
foreach (var resultItem in document.AnswerListContent)
{
resultFile.Add(new { ID = resultItem.GetId(), Profile = resultItem.GetProfileNum(), QuestionNum = resultItem.GetQuestionNum(), Answer = resultItem.GetAnswer() });
}
string selectedPath = CommonService.GetFolderPath();
string dirName = selectedPath + "\\" + document.DocumentName;
if (!Directory.Exists(dirName))
{
Directory.CreateDirectory(dirName);
}
try
{
WriteFile(dirName + "\\" + infoFileName + ".csv", infoFile);
WriteFile(dirName + "\\" + resultFileName + ".csv", resultFile);
for (int i = 0; i < questionFiles.Count; i++)
{
WriteFile(dirName + "\\" + questionFilesName[i] + ".csv", questionFiles[i]);
}
}
catch (Exception ex)
{
Directory.Delete(dirName, true);
throw ex;
}
}
public static void SaveExcel(string infoFileName, string resultFileName, Dictionary <string, ExcelProfile> excelProfileMap, ExcelDocument document, string filePath)
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "User";
excelPackage.Workbook.Properties.Title = document.DocumentName;
excelPackage.Workbook.Properties.Created = DateTime.Now;
ExcelWorksheet infoSheet = excelPackage.Workbook.Worksheets.Add(infoFileName);
int rowInfoNumber = 2;
foreach (var profileItem in excelProfileMap)
{
string sheetName = profileItem.Key;
ExcelProfile profile = profileItem.Value;
infoSheet.Cells["A" + rowInfoNumber].Value = profile.Id;
infoSheet.Cells["B" + rowInfoNumber].Value = profile.Name;
infoSheet.Cells["F" + rowInfoNumber].Value = profile.Answers;
ExcelWorksheet questionSheet = excelPackage.Workbook.Worksheets.Add(sheetName);
int rowQuestionIndex = 1;
foreach (var questuionItem in profile.Questions)
{
questionSheet.Cells["A" + rowQuestionIndex].Value = questuionItem.Id;
questionSheet.Cells["B" + rowQuestionIndex].Value = questuionItem.Content;
if (!string.IsNullOrEmpty(questuionItem.LeftLimit) && !string.IsNullOrEmpty(questuionItem.RightLimit))
{
questionSheet.Cells["C" + rowQuestionIndex].Value = questuionItem.LeftLimit;
questionSheet.Cells["D" + rowQuestionIndex].Value = questuionItem.RightLimit;
}
rowQuestionIndex++;
}
rowInfoNumber++;
questionSheet.Cells[questionSheet.Dimension.Address].AutoFitColumns();
}
ExcelWorksheet resultSheet = excelPackage.Workbook.Worksheets.Add(resultFileName);
resultSheet.Cells["A1"].Value = "id";
resultSheet.Cells["B1"].Value = "анкета";
resultSheet.Cells["C1"].Value = "номер вопроса";
resultSheet.Cells["D1"].Value = "ответ";
int rowResultNumber = 2;
foreach (var answerItem in document.AnswerListContent)
{
resultSheet.Cells["A" + rowResultNumber].Value = answerItem.Id;
resultSheet.Cells["B" + rowResultNumber].Value = answerItem.ProfileNum;
resultSheet.Cells["C" + rowResultNumber].Value = answerItem.QuestionNum;
resultSheet.Cells["D" + rowResultNumber].Value = answerItem.Answer;
rowResultNumber++;
}
infoSheet.Cells[infoSheet.Dimension.Address].AutoFitColumns();
resultSheet.Cells[resultSheet.Dimension.Address].AutoFitColumns();
FileInfo fi = new FileInfo(filePath);
excelPackage.SaveAs(fi);
}
}
public static Tuple <Dictionary <string, int>, int, int> GetQuestionInfo(ExcelQuestion selectedQuestion, ExcelProfile selectedProfile, ExcelDocument selectedDocument)
{
var points = GetQuestionPoints(selectedQuestion, selectedProfile, selectedDocument);
int questionedCount = selectedDocument.AnswerListContent.Where(a => a.ProfileNum == selectedProfile.Id && a.QuestionNum == selectedQuestion.Id).Count();
int respondedCount = selectedDocument.AnswerListContent.Where(a => a.ProfileNum == selectedProfile.Id && a.QuestionNum == selectedQuestion.Id && a.Answer != "").Count();
return(new Tuple <Dictionary <string, int>, int, int>(points, respondedCount, questionedCount));
}
private static Dictionary <string, int> GetQuestionPoints(ExcelQuestion selectedQuestion, ExcelProfile selectedProfile, ExcelDocument selectedDocument)
{
Dictionary <string, int> points = new Dictionary <string, int>();
var answersList = selectedProfile.GetProfileAnswers();
int openAnswerCount = 0;
foreach (var answerItem in answersList)
{
if (answerItem == "другое")
{
var currentResults = selectedDocument.AnswerListContent.Where(a => a.ProfileNum == selectedProfile.Id && a.QuestionNum == selectedQuestion.Id).ToList();
foreach (var resultItem in currentResults)
{
var a = resultItem.GetAnswers(selectedProfile.Type).Except(answersList).ToList();
if (a.Count != 0)
{
openAnswerCount++;
}
}
points.Add(answerItem, openAnswerCount);
}
else
{
var countCurrentAnswers = selectedDocument.AnswerListContent.Where(a => a.ProfileNum == selectedProfile.Id && a.QuestionNum == selectedQuestion.Id && a.GetAnswers(selectedProfile.Type).Contains(answerItem)).Count();
points.Add(answerItem, countCurrentAnswers);
}
}
return(points);
}
public static Dictionary <string, int> GetOpenAnswers(ExcelQuestion selectedQuestion, ExcelProfile selectedProfile, ExcelDocument selectedDocument)
{
Dictionary <string, int> points = new Dictionary <string, int>();
var currentResults = selectedDocument.AnswerListContent.Where(a => a.ProfileNum == selectedProfile.Id && a.QuestionNum == selectedQuestion.Id).ToList();
foreach (var resultItem in currentResults)
{
var openAnswers = resultItem.GetAnswers(selectedProfile.Type).Except(selectedProfile.GetProfileAnswers()).ToList();
if (openAnswers.Count != 0)
{
foreach (var item in openAnswers)
{
if (points.ContainsKey(item))
{
points[item]++;
}
else
{
points.Add(item, 1);
}
}
}
}
return(points);
}
