private void drawQuestions(Question q, int position, Excel.Worksheet xlWorkSheet, List <Person> guests, int id_atelier , string meetingtable, string sessiontable, string wstable) { List <SubQuestion> subQuestions = q.SubQuestions;; for (int s = 0; s <= subQuestions.Count - 1; s++) { Question qq = subQuestions[s]; if (isPredifinedChoices(qq)) { xlWorkSheet.Cells[position, 1] = qq.Label; xlWorkSheet.Cells[position, 1].Interior.Color = ColorTranslator.ToOle(Color.LightGreen); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Cells[position + 1, 1] = "Nombre de réponses"; xlWorkSheet.Columns.AutoFit(); int i; int cpt = 0; List <Choice> Choices2 = new List <Choice>(); Choices2 = qq.Choices; for (i = 0; i < Choices2.Count; i++) { int nbreponse = 0; if (qq.Category.Equals("Meeting")) { foreach (Person p in guests) { nbreponse += DataExtractionUtils.NumberResponse(meetingtable, qq.Column, Choices2[i].Label, qq.Category, 0, p.Id); } } else if (qq.Category.Equals("Activity")) { nbreponse += DataExtractionUtils.NumberResponse(sessiontable, qq.Column, Choices2[i].Label, qq.Category, id_atelier, 0); } else if (qq.Category.Equals("Workshop")) { nbreponse += DataExtractionUtils.NumberResponse(wstable, qq.Column, Choices2[i].Label, qq.Category, id_atelier, 0); } xlWorkSheet.Cells[position, i + 2] = Choices2[i].Label; xlWorkSheet.Cells[position, i + 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Cells[position + 1, i + 2] = nbreponse; cpt += nbreponse; } xlWorkSheet.Cells[position, i + 2] = "Total"; xlWorkSheet.Cells[position, i + 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Cells[position + 1, i + 2] = cpt; cpt = 0; position += 2; } } }
private void DrawColomn(string table, Excel.Worksheet sheet, int line, int column, Question q) { sheet.Cells[line, column] = q.Label; sheet.Cells[line, column].Interior.Color = ColorTranslator.ToOle(Color.LightGreen); var ds = DataExtractionUtils.GetAnswersByColumn(table, q.Column); var tmp = line + 1; for (var j = 0; j < ds.Tables[0].Rows.Count; j++) { sheet.Cells[tmp, column] = ds.Tables[0].Rows[j][q.Column]; tmp++; } }
public Excel.Workbook Print_into_excel_file2(List <Question> Questions, string surveytable, string meetingtable, string sessiontable, string wstable, List <Meeting> Meetings, List <Atelier> sessionAtelier, List <Atelier> wsAtelier) { int position = 1; int meetingpos = 1; int sessionpos = 1; int wspos = 1; var _excel = new Excel.Application(); var wb = _excel.Workbooks.Add(); try { // var wb = _excel.Workbooks.Add(); var collection = new Microsoft.Office.Interop.Excel.Worksheet[5]; collection[0] = wb.Worksheets.Add(); collection[0].Name = String.Format("Workshop"); collection[1] = wb.Worksheets.Add(); collection[1].Name = String.Format("Session"); collection[2] = wb.Worksheets.Add(); collection[2].Name = String.Format("Meeting"); collection[3] = wb.Worksheets.Add(); collection[3].Name = String.Format("General"); Excel.Worksheet xl_Workshop_Sheet = collection[0]; Excel.Worksheet xl_Session_Sheet = collection[1]; Excel.Worksheet xl_Meeting_Sheet = collection[2]; Excel.Worksheet xl_General_Sheet = collection[3]; //xl_General_Sheet.Cells[1, 1] = "Nombre de participants"; //xl_General_Sheet.Cells[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); //xl_General_Sheet.Cells[2, 1] = getNbParticipants(PollId, surveytable); for (int j = 0; j < Questions.Count; j++) { Question q = Questions[j]; if (isPredifinedChoices(q)) { xl_General_Sheet.Cells[position, 1] = q.Label; xl_General_Sheet.Cells[position, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_General_Sheet.Cells[position + 1, 1] = "Nombre de réponses"; xl_General_Sheet.Columns.AutoFit(); int i; int cpt = 0; List <Choice> Choices = new List <Choice>(); Choices = q.Choices; for (i = 0; i < Choices.Count; i++) { int nbreponse = DataExtractionUtils.NumberResponse_GeneralQuestion(surveytable, q.Column, Choices[i].Label); xl_General_Sheet.Cells[position, i + 2] = Choices[i].Label; xl_General_Sheet.Cells[position, i + 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_General_Sheet.Columns.AutoFit(); xl_General_Sheet.Cells[position + 1, i + 2] = nbreponse; cpt += nbreponse; } xl_General_Sheet.Cells[position, i + 2] = "Total"; xl_General_Sheet.Cells[position, i + 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_General_Sheet.Columns.AutoFit(); xl_General_Sheet.Cells[position + 1, i + 2] = cpt; cpt = 0; position = position + 3; } } for (int d = 0; d <= Meetings.Count - 1; d++) { Meeting meeting = Meetings[d]; string guests = ""; foreach (Person p in meeting.guests) { guests += p.FirstName + "(" + p.CompanyName + ");"; } xl_Meeting_Sheet.Cells[meetingpos, 1] = guests; xl_Meeting_Sheet.Cells[meetingpos, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_Meeting_Sheet.Columns.AutoFit(); int nbquestion = 0; for (int j = 0; j < Questions.Count; j++) { Question q = Questions[j]; if (q.Category.Equals("Meeting")) { drawQuestions(q, meetingpos + 1, xl_Meeting_Sheet, meeting.guests, 0, meetingtable, "", ""); nbquestion++; } } meetingpos = meetingpos + 5 * nbquestion; } for (int d = 0; d <= sessionAtelier.Count - 1; d++) { Atelier session = sessionAtelier[d]; xl_Session_Sheet.Cells[sessionpos, 1] = session.theme; xl_Session_Sheet.Cells[sessionpos, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_Session_Sheet.Columns.AutoFit(); int nbquestion = 0; for (int j = 0; j < Questions.Count; j++) { Question q = Questions[j]; if (q.Category.Equals("Activity")) { drawQuestions(q, sessionpos + 1, xl_Session_Sheet, null, session.id_atelier, "", sessiontable, ""); nbquestion++; } } sessionpos = sessionpos + 5 * nbquestion; } for (int d = 0; d <= wsAtelier.Count - 1; d++) { Atelier workshop = wsAtelier[d]; xl_Workshop_Sheet.Cells[wspos, 1] = workshop.theme; xl_Workshop_Sheet.Cells[wspos, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); xl_Workshop_Sheet.Columns.AutoFit(); int nbquestion = 0; for (int j = 0; j < Questions.Count; j++) { Question q = Questions[j]; if (q.Category.Equals("Workshop")) { drawQuestions(q, wspos + 1, xl_Workshop_Sheet, null, workshop.id_atelier, "", "", wstable); nbquestion++; } } wspos = wspos + 5 * nbquestion; } wb.Worksheets[5].delete(); } finally { Marshal.ReleaseComObject(_excel); } return(wb); }
public int PersonInfoColumns(int pos, Excel.Worksheet worksheet, string questionType, string tableName) { var column = 1; var generalPersonInfo = new Dictionary <string, string> { { "PER_id_person", "Id_person" }, { "PER_id_company", "Id_company" }, { "PER_first_name", "First name" }, { "PER_last_name", "Last name" }, }; var sessionPersonalInfo = new Dictionary <string, string> { { "PER_id_person", "Id_person" }, { "PER_id_company", "Id_company" }, { "PER_first_name", "First name" }, { "PER_last_name", "Last name" }, { "theme", "theme" } }; var wsPersonalInfo = new Dictionary <string, string> { { "PER_id_person", "Id_person" }, { "PER_id_company", "Id_company" }, { "PER_first_name", "First name" }, { "PER_last_name", "Last name" }, { "theme", "theme" } }; var meetingPersonalInfo = new Dictionary <string, string> { { "MEE_id_person", "Id_person" }, { "PER_id_company", "Id_company" }, { "PER_first_name", "First name" }, { "PER_last_name", "Last name" }, { "MEE_id_meeting", "id_meeting" }, { "MEE_owner", "owner" } }; Dictionary <string, string> keys; switch (questionType) { case QuestionType.General: keys = generalPersonInfo; break; case QuestionType.Meeting: keys = meetingPersonalInfo; break; case QuestionType.Workshop: keys = wsPersonalInfo; break; case QuestionType.Session: keys = sessionPersonalInfo; break; default: keys = null; break; } foreach (var key in keys.Keys) { worksheet.Cells[pos, column] = keys[key]; worksheet.Cells[pos, column++].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); worksheet.Columns.AutoFit(); } if (questionType == QuestionType.Meeting) { worksheet.Cells[pos, column] = "Participants"; worksheet.Cells[pos, column++].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); worksheet.Columns.AutoFit(); } pos++; var dataSet = DataExtractionUtils.GetAnswersByColumn(tableName, "person_info"); for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { column = 1; foreach (var columnName in keys.Keys) { worksheet.Cells[pos, column++] = dataSet.Tables[0].Rows[i][columnName]; } if (questionType == QuestionType.Meeting) { var companies = getMeetingParticipants( int.Parse(dataSet.Tables[0].Rows[i]["MEE_id_meeting"].ToString()), int.Parse(dataSet.Tables[0].Rows[i]["PER_id_company"].ToString()) ); worksheet.Cells[pos, column++] = companies; } pos++; } worksheet.Columns.AutoFit(); return(column); }