private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { Workbook wb = new Workbook(); wb.Copy((Workbook)((Control)sender).Tag); Completed(_Title + "_驗證", wb); }
private void wizardPage3_AfterPageDisplayed(object sender, WizardPageChangeEventArgs e) { this.progressBarX1.Value = 0; lblWarningCount.Text = lblErrCount.Text = "0"; this.wizardPage3.FinishButtonEnabled = eWizardButtonState.False; linkLabel1.Visible = false; labelX2.Text = "資料驗證中"; linkLabel3.Tag = null; linkLabel3.Visible = false; Application.DoEvents(); _BKWValidate = new BackgroundWorker(); _BKWValidate.WorkerReportsProgress = true; _BKWValidate.WorkerSupportsCancellation = true; _BKWValidate.DoWork += new DoWorkEventHandler(_BKWValidate_DoWork); _BKWValidate.ProgressChanged += new ProgressChangedEventHandler(_BKWValidate_ProgressChanged); _BKWValidate.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BKWValidate_RunWorkerCompleted); List <string> fields = new List <string>(); string fileName = txtFile.Text; fields.AddRange(_RequiredFields); foreach (ListViewItem item in listView1.Items) { if (item.Checked) { fields.Add(item.Text); } } _ErrorRows = new Dictionary <RowData, string>(); _WarningRows = new Dictionary <RowData, string>(); Workbook wb = new Workbook(); wb.Copy(_WorkBook); _BKWValidate.RunWorkerAsync(new object[] { fields, _ImportFields, wb }); }
public ExportStudentClub(string sy, string s) { //儲存資料 Workbook wb = new Workbook(); Exception exc = null; BackgroundWorker bkw = new BackgroundWorker() { WorkerReportsProgress = true }; bkw.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e) { MotherForm.SetStatusBarMessage("匯出選社結果", e.ProgressPercentage); }; bkw.DoWork += delegate { try { bkw.ReportProgress(1); List <CLUBRecord> _clubList = this.access.Select <CLUBRecord>(); foreach (CLUBRecord club in _clubList) { dicClubNameByID.Add(club.UID, club.ClubName); } //建立Excel範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.匯出選社結果_範本), FileFormatType.Excel97To2003); wb.Copy(template); //取得Sheet Worksheet ws = wb.Worksheets[0]; #region SQL string selectSQL = string.Format(@" SELECT student.id , class.class_name , class.grade_year , seat_no,name , student_number , student.ref_class_id , clubrecord.ref_club_id , clubrecord.lock , clubrecord.club_name , clubrecord.school_year , clubrecord.semester , volunteer.content , $k12.config.universal.content as wish_limit FROM student LEFT OUTER JOIN class on class.id = student.ref_class_id LEFT OUTER JOIN ( SELECT scjoin.* , clubrecord.club_name , clubrecord.school_year , clubrecord.semester FROM $k12.clubrecord.universal AS clubrecord LEFT OUTER JOIN $k12.scjoin.universal AS scjoin ON clubrecord.uid = scjoin.ref_club_id::bigint WHERE clubrecord.school_year = {0} AND clubrecord.semester = {1} ) AS clubrecord ON clubrecord.ref_student_id::bigint = student.id LEFT OUTER JOIN $k12.volunteer.universal AS volunteer ON volunteer.ref_student_id::bigint = student.id AND volunteer.school_year = {0} AND volunteer.semester = {1} LEFT OUTER JOIN $k12.config.universal ON config_name = '學生選填志願數' WHERE student.status in (1, 2) ORDER BY class.grade_year , class.display_order , class.class_name , student.seat_no , student.id" , sy, s); #endregion // 取得學生社團資料 DataTable dt = this.qh.Select(selectSQL); bkw.ReportProgress(10); int index = 1; foreach (DataRow dr in dt.Rows) { bkw.ReportProgress(10 + 90 * index / dt.Rows.Count); int wishLimit = (dr["wish_limit"] == null ? 5 : int.Parse("" + dr["wish_limit"])); if (index == 1) { int countLimit = 6 + wishLimit; for (int i = 6; i < countLimit; i++) { ws.Cells.CopyColumn(ws.Cells, 4, i); ws.Cells[0, i].PutValue("志願" + (i - 5)); } } else { ws.Cells.CopyRow(ws.Cells, 1, index); int countLimit = 6 + wishLimit; for (int i = 0; i < countLimit; i++) { ws.Cells[index, i].PutValue(null); } } ws.Cells[index, 0].PutValue("" + dr["class_name"]); ws.Cells[index, 1].PutValue("" + dr["seat_no"]); ws.Cells[index, 2].PutValue("" + dr["name"]); ws.Cells[index, 3].PutValue("" + dr["student_number"]); ws.Cells[index, 4].PutValue("" + dr["club_name"]); ws.Cells[index, 5].PutValue(("" + dr["lock"]) == "true" ? "是" : ""); // 學生志願序 if (dr["content"] != null && "" + dr["content"] != "") { XDocument content = XDocument.Parse("" + dr["content"]); List <XElement> clubList = content.Element("xml").Elements("Club").ToList(); int count = 6; int countLimit = count + wishLimit - 1; foreach (XElement club in clubList) { if (!dicClubNameByID.ContainsKey(club.Attribute("Ref_Club_ID").Value)) { continue; } ws.Cells[index, count].PutValue(dicClubNameByID[club.Attribute("Ref_Club_ID").Value]); count++; if (count > countLimit) { break; } } } index++; } } catch (Exception ex) { exc = ex; } }; bkw.RunWorkerCompleted += delegate { if (exc == null) { MotherForm.SetStatusBarMessage("匯出選社結果完成"); #region Excel 存檔 { SaveFileDialog SaveFileDialog1 = new SaveFileDialog(); SaveFileDialog1.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*"; SaveFileDialog1.FileName = "匯出選社結果"; try { if (SaveFileDialog1.ShowDialog() == DialogResult.OK) { wb.Save(SaveFileDialog1.FileName, SaveFormat.Excel97To2003); Process.Start(SaveFileDialog1.FileName); MotherForm.SetStatusBarMessage("匯出選社結果,列印完成!!"); } else { FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存"); return; } } catch { FISCA.Presentation.Controls.MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!"); MotherForm.SetStatusBarMessage("檔案儲存錯誤,請檢查檔案是否開啟中!!"); } } #endregion } else { throw new Exception("匯出選社結果 發生錯誤", exc); } }; bkw.RunWorkerAsync(); }
void _BGWSemesterMoralScoresCalculate_DoWork(object sender, DoWorkEventArgs e) { object[] args = (object[])e.Argument; int schoolyear = (int)args[0]; int semester = (int)args[1]; bool over100 = (bool)args[2]; int sizeIndex = (int)args[3]; Dictionary <string, List <string> > userType = (Dictionary <string, List <string> >)args[4]; _BGWSemesterMoralScoresCalculate.ReportProgress(1); #region 取得資料 AccessHelper dataSeed = new AccessHelper(); List <ClassRecord> allClasses = dataSeed.ClassHelper.GetSelectedClass(); List <StudentRecord> allStudents = new List <StudentRecord>(); Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >(); AngelDemonComputer computer = new AngelDemonComputer(); int maxStudents = 0; int totalStudent = 0; int currentStudent = 1; foreach (ClassRecord aClass in allClasses) { List <StudentRecord> studnetList = aClass.Students; if (studnetList.Count > maxStudents) { maxStudents = studnetList.Count; } allStudents.AddRange(studnetList); //computer.FillDemonScore(dataSeed, schoolyear, semester, studnetList); classStudents.Add(aClass.ClassID, studnetList); totalStudent += studnetList.Count; } computer.FillDemonScore(dataSeed, schoolyear, semester, allStudents); if (semester == 2) { dataSeed.StudentHelper.FillSemesterEntryScore(true, allStudents); dataSeed.StudentHelper.FillSchoolYearEntryScore(true, allStudents); dataSeed.StudentHelper.FillSemesterHistory(allStudents); } SystemInformation.getField("DiffItem"); SystemInformation.getField("Degree"); Dictionary <string, decimal> degreeList = (Dictionary <string, decimal>)SystemInformation.Fields["Degree"]; #endregion #region 產生表格 Workbook template = new Workbook(); Workbook prototype = new Workbook(); //列印尺寸 if (sizeIndex == 0) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表A3), FileFormatType.Excel2003); } else if (sizeIndex == 1) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表A4), FileFormatType.Excel2003); } else if (sizeIndex == 2) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表B4), FileFormatType.Excel2003); } prototype.Copy(template); Worksheet templateSheet = template.Worksheets[0]; Worksheet prototypeSheet = prototype.Worksheets[0]; Range tempInfoAndReward = templateSheet.Cells.CreateRange(0, 17, true); Range tempAbsence = templateSheet.Cells.CreateRange(17, 2, true); Range tempBeforeOtherDiff = templateSheet.Cells.CreateRange(19, 3, true); Range tempOtherDiff = templateSheet.Cells.CreateRange(22, 1, true); Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(25, 2, true); Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >(); //紀錄獎懲的 Column Index columnIndexTable.Add("大功", 4); columnIndexTable.Add("小功", 6); columnIndexTable.Add("嘉獎", 8); columnIndexTable.Add("大過", 10); columnIndexTable.Add("小過", 12); columnIndexTable.Add("警告", 14); columnIndexTable.Add("獎懲小計", 16); //缺曠加減分 int ptColIndex = 17; foreach (SmartSchool.Evaluation.AngelDemonComputer.UsefulPeriodAbsence var in computer.UsefulPeriodAbsences) { if (!periodAbsence.ContainsKey(var.Period)) { periodAbsence.Add(var.Period, new List <string>()); } if (!periodAbsence[var.Period].Contains(var.Absence)) { periodAbsence[var.Period].Add(var.Absence); } prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence); ptColIndex += 2; } //foreach (string period in userType.Keys) //{ // if (!periodAbsence.ContainsKey(period)) // { // periodAbsence.Add(period, new List<string>()); // foreach (string absence in userType[period]) // { // if (!periodAbsence[period].Contains(absence)) // { // periodAbsence[period].Add(absence); // prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence); // ptColIndex += 2; // } // } // } //} ptColIndex = 17; foreach (string period in periodAbsence.Keys) { prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count * 2).Merge(); prototypeSheet.Cells[2, ptColIndex].PutValue(period); foreach (string absence in periodAbsence[period]) { prototypeSheet.Cells[3, ptColIndex].PutValue(absence); columnIndexTable.Add(period + "_" + absence, ptColIndex); ptColIndex += 2; } } prototypeSheet.Cells.CreateRange(1, 17, 1, ptColIndex - 15).Merge(); prototypeSheet.Cells[1, 17].PutValue("缺曠加減分"); columnIndexTable.Add("全勤", ptColIndex); columnIndexTable.Add("缺曠小計", ptColIndex + 1); prototypeSheet.Cells.CreateRange(ptColIndex, 3, true).Copy(tempBeforeOtherDiff); ptColIndex += 3; //導師加減分 columnIndexTable.Add("導師加減分", ptColIndex - 1); //其他加減分項目 foreach (string var in (List <string>)SystemInformation.Fields["DiffItem"]) { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue(var); columnIndexTable.Add(var, ptColIndex); ptColIndex++; } if (semester == 2) { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("上學期德行成績"); columnIndexTable.Add("上學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績"); columnIndexTable.Add("學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("等第"); columnIndexTable.Add("等第", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學年德行成績"); columnIndexTable.Add("學年成績", ptColIndex++); } else { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績"); columnIndexTable.Add("學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("等第"); columnIndexTable.Add("等第", ptColIndex++); } prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAfterOtherDiff); columnIndexTable.Add("評語", ptColIndex++); columnIndexTable.Add("更改等第", ptColIndex++); //加上底線 prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black); //填入製表日期 prototypeSheet.Cells[0, 0].PutValue("製表日期:" + DateTime.Today.ToShortDateString()); //填入標題 prototypeSheet.Cells.CreateRange(0, 4, 1, ptColIndex - 4).Merge(); prototypeSheet.Cells[0, 4].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 " + ((semester == 1) ? "上" : "下") + " 學期 德行成績試算表 "); Range ptEachRow = prototypeSheet.Cells.CreateRange(5, 1, false); for (int i = 5; i < maxStudents + 5; i++) { prototypeSheet.Cells.CreateRange(i, 1, false).Copy(ptEachRow); } Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false); #endregion #region 填入表格 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int classTotalRow = maxStudents + 5; foreach (ClassRecord aClass in allClasses) { //複製完成後的樣板 ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt); //填入班級名稱 ws.Cells[index + 1, 0].PutValue(aClass.ClassName); Dictionary <string, int> degreeCount = new Dictionary <string, int>(); foreach (string key in degreeList.Keys) { degreeCount.Add(key, 0); } dataIndex = index + 5; foreach (StudentRecord aStudent in classStudents[aClass.ClassID]) { ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo); ws.Cells[dataIndex, 1].PutValue(aStudent.StudentName); ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber); decimal score = 0; decimal rewardScore = 0; decimal absenceScore = 0; decimal diffScore = 0; XmlElement demonScore = (XmlElement)aStudent.Fields["DemonScore"]; score = decimal.Parse(demonScore.GetAttribute("Score")); foreach (XmlElement var in demonScore.SelectNodes("SubScore")) { if (var.GetAttribute("Type") == "基分") { ws.Cells[dataIndex, 3].PutValue(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "獎懲") { int colIndex = columnIndexTable[var.GetAttribute("Name")]; if (decimal.Parse(var.GetAttribute("Count")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count")); } if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score")); } rewardScore += decimal.Parse(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "缺曠") { string pa = var.GetAttribute("PeriodType") + "_" + var.GetAttribute("Absence"); if (columnIndexTable.ContainsKey(pa)) { int colIndex = columnIndexTable[pa]; if (decimal.Parse(var.GetAttribute("Count")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count")); } if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score")); } } absenceScore += decimal.Parse( var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "加減分") { int colIndex = columnIndexTable[var.GetAttribute("DiffItem")]; if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score")); } diffScore += decimal.Parse(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "全勤") { int colIndex = columnIndexTable["全勤"]; if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score")); } absenceScore += decimal.Parse(var.GetAttribute("Score")); } } //填入獎懲小計 if (rewardScore != 0) { ws.Cells[dataIndex, columnIndexTable["獎懲小計"]].PutValue(rewardScore.ToString()); } //填入缺曠小計 if (absenceScore != 0) { ws.Cells[dataIndex, columnIndexTable["缺曠小計"]].PutValue(absenceScore.ToString()); } //填入學業成績試算 if (!over100 && score > 100) { score = 100; } ws.Cells[dataIndex, columnIndexTable["學期成績"]].PutValue(score.ToString()); //填入上學期&學年成績 if (semester == 2) { int gradeYear = -1; foreach (SemesterHistory sh in aStudent.SemesterHistoryList) { if (sh.SchoolYear == schoolyear && sh.Semester == semester) { gradeYear = sh.GradeYear; break; } } //沒有學期歷程就用當下的學期 if (gradeYear == -1 && schoolyear == SystemInformation.SchoolYear) { int.TryParse(aStudent.RefClass.GradeYear, out gradeYear); } //填入上學期成績 foreach (SemesterEntryScoreInfo semesterEntryScore in aStudent.SemesterEntryScoreList) { if (semesterEntryScore.Entry == "德行" && semesterEntryScore.GradeYear == gradeYear && semesterEntryScore.Semester == 1) { if (!over100 && semesterEntryScore.Score > 100) { ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue("100"); } else { ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue(semesterEntryScore.Score.ToString()); } break; } } //填入學年成績 foreach (SchoolYearEntryScoreInfo schoolyearEntryScore in aStudent.SchoolYearEntryScoreList) { if (schoolyearEntryScore.Entry == "德行" && schoolyearEntryScore.GradeYear == gradeYear) { if (!over100 && schoolyearEntryScore.Score > 100) { ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue("100"); } else { ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue(schoolyearEntryScore.Score.ToString()); } break; } } } //填入等第 string degree = computer.ParseLevel(score); ws.Cells[dataIndex, columnIndexTable["等第"]].PutValue(degree); //計算等第出現次數 if (degreeCount.ContainsKey(degree)) { degreeCount[degree]++; } //評語 if (demonScore.SelectSingleNode("Others/@Comment") != null) { ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.SelectSingleNode("Others/@Comment").InnerText); } dataIndex++; //回報進度 _BGWSemesterMoralScoresCalculate.ReportProgress((int)(currentStudent++ *100.0 / totalStudent)); } ws.Cells.CreateRange(index + classTotalRow + 1, 0, 1, ptColIndex).Merge(); StringBuilder degreeSumString = new StringBuilder(""); degreeSumString.Append("德行等第統計 "); foreach (string key in degreeCount.Keys) { degreeSumString.Append(key + "等: " + degreeCount[key].ToString() + " "); } ws.Cells[index + classTotalRow + 1, 0].Style.Font.Size = 12; ws.Cells.CreateRange(index + classTotalRow + 1, 1, false).RowHeight = 20; ws.Cells[index + classTotalRow + 1, 0].PutValue(degreeSumString.ToString()); ws.Cells[index + classTotalRow + 1, 0].Style.HorizontalAlignment = TextAlignmentType.Left; index += classTotalRow + 3; ws.HPageBreaks.Add(index, ptColIndex); } #endregion e.Result = wb; }
void BGW_DoWork(object sender, DoWorkEventArgs e) { //取得相關的學生資料 //1.基本資料 //2.社團結算成績 GetPoint = new ClubTraMag(); //取得範本 #region 建立範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.社團成績單_範本), FileFormatType.Excel97To2003); //Style sy = template.Worksheets[0].Cells[3, 0].Style; //每一張 Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; #region 建立標頭Column 評量比例 評 = new 評量比例(); ColumnNameList = new List <string>(); ColumnNameList.Add("班級名稱"); ColumnNameList.Add("座號"); ColumnNameList.Add("姓名"); ColumnNameList.Add("學號"); ColumnNameList.Add("性別"); foreach (string each in 評.ColumnDic.Keys) { ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)"); } ColumnNameList.Add("學期成績"); ColumnNameList.Add("社團幹部"); int ColumnNameIndex = 0; //Jean 更新Aspose Style style = prototype.CreateStyle(); style.IsTextWrapped = true; foreach (string each in ColumnNameList) { ptws.Cells[2, ColumnNameIndex].SetStyle(style); ptws.Cells[2, ColumnNameIndex].PutValue(each); if (ColumnNameIndex >= 5) { ptws.Cells.SetColumnWidth(ColumnNameIndex, 8); tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1); } ColumnNameIndex++; } #endregion Range ptHeader = ptws.Cells.CreateRange(0, 3, false); Range ptEachRow = ptws.Cells.CreateRange(3, 1, false); //建立Excel檔案 Workbook wb = new Workbook(); wb.Copy(prototype); //取得第一張 Worksheet ws = wb.Worksheets[0]; int dataIndex = 0; int CountPage = 1; int DetalIndex = 5; #endregion #region 填資料 foreach (string clubID in GetPoint.TraDic.Keys) { //每一個社團 ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader); ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader); CLUBRecord cr = GetPoint.CLUBDic[clubID]; //第一行 - 建立標頭內容 ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count); string TitleName = string.Format("{0}學年度 第{1}學期 {2} 社團成績單", cr.SchoolYear.ToString(), cr.Semester.ToString(), cr.ClubName); ws.Cells[dataIndex, 0].PutValue(TitleName); dataIndex++; //第二行 - 代碼 ws.Cells.Merge(dataIndex, 0, 1, 3); ws.Cells[dataIndex, 0].PutValue("代碼:" + cr.ClubNumber); //第二行 - 老師 //教師 ColumnDic大小後的一格 ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3); string TeacherString = "教師:" + GetTeacherName(cr); ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue(TeacherString); //SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count); dataIndex += 2; GetPoint.TraDic[clubID].Sort(SortTraDic); foreach (ClubTraObj each in GetPoint.TraDic[clubID]) { ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow); ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow); //基本資料 tool.SetCellBro(ws, dataIndex, 0, 1, 1); ws.Cells[dataIndex, 0].PutValue(each.student.Class != null ? each.student.Class.Name : ""); tool.SetCellBro(ws, dataIndex, 1, 1, 1); ws.Cells[dataIndex, 1].PutValue(each.student.SeatNo.HasValue ? each.student.SeatNo.Value.ToString() : ""); tool.SetCellBro(ws, dataIndex, 2, 1, 1); ws.Cells[dataIndex, 2].PutValue(each.student.Name); tool.SetCellBro(ws, dataIndex, 3, 1, 1); ws.Cells[dataIndex, 3].PutValue(each.student.StudentNumber); tool.SetCellBro(ws, dataIndex, 4, 1, 1); ws.Cells[dataIndex, 4].PutValue(each.student.Gender); if (!string.IsNullOrEmpty(each.SCJ.Score)) { XmlElement xml = DSXmlHelper.LoadXml(each.SCJ.Score); foreach (XmlElement each1 in xml.SelectNodes("Item")) { string name = each1.GetAttribute("Name"); if (評.ColumnDic.ContainsKey(name)) { tool.SetCellBro(ws, dataIndex, DetalIndex + 評.ColumnDic[name], 1, 1); ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score")); } } } else { for (int x = 4; x < ColumnNameList.Count; x++) { tool.SetCellBro(ws, dataIndex, x, 1, 1); } } //學期成績 if (each.RSR != null) { ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8); tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 2, 1, 1); string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : ""; ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score); //ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy; } //社團幹部 if (each.RSR != null) { ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8); tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 1, 1, 1); string CardreName = each.RSR.CadreName; ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(CardreName); //ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy; } dataIndex++; } //頁數 string DateName = "日期:" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString(); CountPage++; ws.Cells.Merge(dataIndex, 0, 1, 5); //SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count); ws.Cells[dataIndex, 0].PutValue(DateName); //ws.Cells[dataIndex, 0].Style = sy; ws.HPageBreaks.Add(dataIndex + 1, ColumnNameList.Count); dataIndex++; } #endregion e.Result = wb; }
void _BGWAbsenceDetail_DoWork(object sender, DoWorkEventArgs e) { string reportName = "學生個人缺曠明細"; #region 快取相關資料 //選擇的學生 List <StudentRecord> selectedStudents = Student.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource); selectedStudents.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer)); //紀錄所有學生ID List <string> allStudentID = new List <string>(); //對照表 Dictionary <string, string> absenceList = new Dictionary <string, string>(); Dictionary <string, string> periodList = new Dictionary <string, string>(); //根據節次類型統計每一種缺曠別的次數 Dictionary <string, Dictionary <string, Dictionary <string, int> > > periodStatisticsByType = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >(); //每一位學生的缺曠明細 Dictionary <string, Dictionary <string, Dictionary <string, string> > > studentAbsenceDetail = new Dictionary <string, Dictionary <string, Dictionary <string, string> > >(); //紀錄每一個節次在報表中的 column index Dictionary <string, int> columnTable = new Dictionary <string, int>(); List <AttendanceRecord> AttendanceList; //取得所有學生ID foreach (StudentRecord var in selectedStudents) { allStudentID.Add(var.ID); } //取得 Absence List List <AbsenceMappingInfo> Absencelist = K12.Data.AbsenceMapping.SelectAll(); foreach (AbsenceMappingInfo var in Absencelist) { if (!absenceList.ContainsKey(var.Name)) { absenceList.Add(var.Name, var.Abbreviation); } } //取得 Period List List <PeriodMappingInfo> PerioList = K12.Data.PeriodMapping.SelectAll(); foreach (PeriodMappingInfo var in PerioList) { if (!periodList.ContainsKey(var.Name)) { periodList.Add(var.Name, var.Type); } } //產生 DSRequest,取得缺曠明細 if (form.SelectDayOrSchoolYear) { #region 依日期 //取得學生,開始 & 結束之間的缺曠資料 AttendanceList = Attendance.SelectByDate(Student.SelectByIDs(allStudentID), form.dateTimeInput1.Value, form.dateTimeInput2.Value); #endregion } else { if (form.checkBoxX1Bool) { #region 全部學期列印 AttendanceList = Attendance.SelectByStudentIDs(allStudentID); #endregion } else { #region 指定學期列印 AttendanceList = Attendance.SelectBySchoolYearAndSemester(Student.SelectByIDs(allStudentID), int.Parse(form.SchoolYear), int.Parse(form.Semester)); #endregion } } foreach (AttendanceRecord var in AttendanceList) { string studentID = var.RefStudentID; string schoolYear = var.SchoolYear.ToString(); string semester = var.Semester.ToString(); string occurDate = var.OccurDate.ToShortDateString(); string sso = schoolYear + "_" + semester + "_" + occurDate; //累計資料 if (!periodStatisticsByType.ContainsKey(studentID)) { periodStatisticsByType.Add(studentID, new Dictionary <string, Dictionary <string, int> >()); } foreach (string value in periodList.Values) { if (!periodStatisticsByType[studentID].ContainsKey(value)) { periodStatisticsByType[studentID].Add(value, new Dictionary <string, int>()); } foreach (string absence in absenceList.Keys) { if (!periodStatisticsByType[studentID][value].ContainsKey(absence)) { periodStatisticsByType[studentID][value].Add(absence, 0); } } } //每一位學生缺曠資料 if (!studentAbsenceDetail.ContainsKey(studentID)) { studentAbsenceDetail.Add(studentID, new Dictionary <string, Dictionary <string, string> >()); } if (!studentAbsenceDetail[studentID].ContainsKey(sso)) { studentAbsenceDetail[studentID].Add(sso, new Dictionary <string, string>()); } foreach (AttendancePeriod ap in var.PeriodDetail) { string absenceType = ap.AbsenceType; string inner = ap.Period; if (!periodList.ContainsKey(inner)) { continue; } string periodType = periodList[inner]; if (!studentAbsenceDetail[studentID][sso].ContainsKey(inner) && absenceList.ContainsKey(absenceType)) { studentAbsenceDetail[studentID][sso].Add(inner, absenceList[absenceType]); } if (periodStatisticsByType[studentID][periodType].ContainsKey(absenceType)) { periodStatisticsByType[studentID][periodType][absenceType]++; } } } #endregion if (AttendanceList.Count == 0) { e.Cancel = true; return; } #region 產生範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.學生缺曠明細), FileFormatType.Excel2003); Range tempEachColumn = template.Worksheets[0].Cells.CreateRange(4, 1, true); Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; int startPage = 1; int pageNumber = 1; int colIndex = 4; int startPeriodIndex = colIndex; int endPeriodIndex; //產生每一個節次的欄位 foreach (string periodName in periodList.Keys) { ptws.Cells.CreateRange(colIndex, 1, true).Copy(tempEachColumn); ptws.Cells[3, colIndex].PutValue(periodName); columnTable.Add(periodName, colIndex); colIndex++; } endPeriodIndex = colIndex; ptws.Cells.CreateRange(2, startPeriodIndex, 1, endPeriodIndex - startPeriodIndex).Merge(); ptws.Cells[2, startPeriodIndex].PutValue("節次"); //合併標題列 ptws.Cells.CreateRange(0, 0, 1, endPeriodIndex).Merge(); ptws.Cells.CreateRange(1, 0, 1, endPeriodIndex).Merge(); Range ptHeader = ptws.Cells.CreateRange(0, 4, false); Range ptEachRow = ptws.Cells.CreateRange(4, 1, false); //current++; #endregion #region 產生報表 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int studentCount = 1; foreach (StudentRecord studentInfo in selectedStudents) { string TitleName1 = School.ChineseName + " 個人缺曠明細"; string TitleName2 = "班級:" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號:" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名:" + studentInfo.Name + " 學號:" + studentInfo.StudentNumber); //回報進度 _BGWAbsenceDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count)); if (!studentAbsenceDetail.ContainsKey(studentInfo.ID)) { continue; } //如果不是第一頁,就在上一頁的資料列下邊加黑線 if (index != 0) { ws.Cells.CreateRange(index - 1, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } //複製 Header ws.Cells.CreateRange(index, 4, false).Copy(ptHeader); //填寫基本資料 ws.Cells[index, 0].PutValue(School.ChineseName + " 個人缺曠明細"); ws.Cells[index + 1, 0].PutValue("班級:" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號:" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名:" + studentInfo.Name + " 學號:" + studentInfo.StudentNumber)); dataIndex = index + 4; int recordCount = 0; //學生Row筆數超過40筆,則添加換行符號,與標頭 int CountRows = 0; Dictionary <string, Dictionary <string, string> > absenceDetail = studentAbsenceDetail[studentInfo.ID]; //取總頁數 , 資料數除以38列(70/38=2) int TotlePage = absenceDetail.Count / 40; //目前頁數 int pageCount = 1; //如果還有餘數則+1 if (absenceDetail.Count % 40 != 0) { TotlePage++; } //填寫基本資料 ws.Cells[index, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")"); pageCount++; ws.Cells[index + 1, 0].PutValue(TitleName2); foreach (string sso in absenceDetail.Keys) { CountRows++; string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries); //複製每一個 row ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow); //填寫學生缺曠資料 ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]); ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]); ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]); ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2]))); Dictionary <string, string> record = absenceDetail[sso]; foreach (string periodName in record.Keys) { ws.Cells[dataIndex, columnTable[periodName]].PutValue(record[periodName]); } dataIndex++; recordCount++; if (CountRows == 40 && pageCount <= TotlePage) { CountRows = 0; //分頁 ws.HPageBreaks.Add(dataIndex, endPeriodIndex); //複製 Header ws.Cells.CreateRange(dataIndex, 4, false).Copy(ptHeader); //填寫基本資料 ws.Cells[dataIndex, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")"); pageCount++; //下一頁使用 ws.Cells[dataIndex + 1, 0].PutValue(TitleName2); dataIndex += 4; } } //缺曠統計資訊 Range absenceStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex); absenceStatisticsRange.Merge(); absenceStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black); absenceStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black); absenceStatisticsRange.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black); absenceStatisticsRange.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black); absenceStatisticsRange.RowHeight = 20.0; ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].PutValue("缺曠總計"); dataIndex++; int typeNumber = dataIndex; foreach (string periodType in periodStatisticsByType[studentInfo.ID].Keys) { Dictionary <string, int> byType = periodStatisticsByType[studentInfo.ID][periodType]; int printable = 0; foreach (string type in byType.Keys) { printable += byType[type]; } if (printable == 0) { continue; } ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge(); ws.Cells.CreateRange(dataIndex, 2, 1, endPeriodIndex - 2).Merge(); ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).RowHeight = 27.0; ws.Cells.CreateRange(dataIndex, 0, 1, 2).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black); ws.Cells.CreateRange(dataIndex, 0, 1, 2).SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black); ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black); ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].PutValue(periodType); StringBuilder text = new StringBuilder(""); foreach (string type in byType.Keys) { if (byType[type] > 0) { if (text.ToString() != "") { text.Append(" "); } text.Append(type + ":" + byType[type]); } } ws.Cells[dataIndex, 2].Style.Font.Size = 10; ws.Cells[dataIndex, 2].Style.ShrinkToFit = true; ws.Cells[dataIndex, 2].PutValue(text.ToString()); ws.Cells.CreateRange(dataIndex, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black); dataIndex++; } typeNumber = dataIndex - typeNumber; //資料列上邊加上黑線 //ws.Cells.CreateRange(index + 3, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); //表格最右邊加上黑線 //ws.Cells.CreateRange(index + 2, endPeriodIndex - 1, recordCount + typeNumber + 3, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black); index = dataIndex; //每500頁,增加一個WorkSheet,並於下標顯示(1~500)(501~xxx) if (pageNumber < 500) { ws.HPageBreaks.Add(index, endPeriodIndex); pageNumber++; } else { ws.Name = startPage + " ~ " + (pageNumber + startPage - 1); ws = wb.Worksheets[wb.Worksheets.Add()]; ws.Copy(prototype.Worksheets[0]); startPage += pageNumber; pageNumber = 1; index = 0; } } if (dataIndex > 0) { //最後一頁的資料列下邊加上黑線 ws.Cells.CreateRange(dataIndex - 1, 0, 1, endPeriodIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Name = startPage + " ~ " + (pageNumber + startPage - 2); } else { wb = new Workbook(); } #endregion string path = Path.Combine(Application.StartupPath, "Reports"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = Path.Combine(path, reportName + ".xlt"); e.Result = new object[] { reportName, path, wb }; }
void _BWResitScoreImport_DoWork(object sender, DoWorkEventArgs e) { object[] objectValue = (object[])e.Argument; int schoolyear = (int)objectValue[0]; int semester = (int)objectValue[1]; _BWResitScoreImport.ReportProgress(0); #region 取得所有學生以及補考資訊 AccessHelper helper = new AccessHelper(); List <StudentRecord> allStudents = new List <StudentRecord>(); List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass(); WearyDogComputer computer = new WearyDogComputer(); double currentClass = 1; double totalClasses = allClasses.Count; List <StudentRecord> loadCourseStudents = new List <StudentRecord>(); foreach (ClassRecord aClass in allClasses) { List <StudentRecord> classStudents = aClass.Students; computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents); helper.StudentHelper.FillField("及格標準", classStudents); allStudents.AddRange(classStudents); foreach (StudentRecord aStudent in classStudents) { foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester) { if (!info.Pass && info.Detail.HasAttribute("達補考標準") && info.Detail.GetAttribute("達補考標準") == "是") { if (!loadCourseStudents.Contains(aStudent)) { loadCourseStudents.Add(aStudent); } } } } } _BWResitScoreImport.ReportProgress((int)(currentClass++ *90.0 / totalClasses)); } MultiThreadWorker <StudentRecord> multiThreadWorker = new MultiThreadWorker <StudentRecord>(); multiThreadWorker.MaxThreads = 3; multiThreadWorker.PackageSize = 80; multiThreadWorker.PackageWorker += new EventHandler <PackageWorkEventArgs <StudentRecord> >(multiThreadWorker_PackageWorker); multiThreadWorker.Run(loadCourseStudents, new object[] { schoolyear, semester, helper }); double currentStudent = 1; double totalStudents = allStudents.Count; #endregion #region 產生表格並填入資料 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.補考成績匯入), FileFormatType.Excel2003); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); int headerIndex = 0; string headerString = template.Worksheets[0].Cells[0, headerIndex].StringValue; while (!string.IsNullOrEmpty(headerString)) { columnIndexTable.Add(headerString, headerIndex); headerString = template.Worksheets[0].Cells[0, ++headerIndex].StringValue; } foreach (StudentRecord aStudent in loadCourseStudents) { foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester) { if (!info.Pass && info.Detail.HasAttribute("達補考標準") && info.Detail.GetAttribute("達補考標準") == "是") { #region 搜尋授課教師 foreach (StudentAttendCourseRecord attendCourse in aStudent.AttendCourseList) { if (attendCourse.SchoolYear == schoolyear && attendCourse.Semester == semester && attendCourse.Subject == info.Subject && attendCourse.SubjectLevel == info.Level) { List <TeacherRecord> teachers = helper.TeacherHelper.GetLectureTeacher(attendCourse); if (teachers.Count > 0) { info.Detail.SetAttribute("授課教師", teachers[0].TeacherName); } } } #endregion machine.AddSubject(aStudent, info); } } } _BWResitScoreImport.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents)); } machine.Sort(); int index = 1; foreach (Dictionary <string, string> info in machine.GetSubjects()) { foreach (string key in info.Keys) { ws.Cells[index, columnIndexTable[key]].PutValue(info[key]); } index++; } #endregion e.Result = wb; }
private void printBtn_Click(object sender, EventArgs e) { #region SQL string sql = ""; if (unitCbx.Text == "--全部--") { sql = string.Format(@" WITH data_row AS( SELECT unit.name AS unit_name , room.name AS room_name , room.uid AS room_id , app.uid , app_detail.start_time , app_detail.end_time FROM $ischool.booking.meetingroom_unit AS unit LEFT OUTER JOIN $ischool.booking.meetingroom AS room ON unit.uid = room.ref_unit_id LEFT OUTER JOIN $ischool.booking.meetingroom_application AS app ON room.uid = app.ref_meetingroom_id LEFT OUTER JOIN $ischool.booking.meetingroom_application_detail AS app_detail ON app.uid = app_detail.ref_application_id WHERE room.uid IS NOT NULL AND app.uid IS NOT NULL AND app.is_canceled = false AND app.is_approved = true AND app_detail.start_time >= '{0}' AND app_detail.start_time <= '{1}' ) SELECT unit_name , room_name , count(*) AS 使用次數 , SUM(end_time - start_time) AS 使用時數 FROM data_row GROUP BY unit_name ,room_name ", startTime.Value.ToShortDateString(), endTime.Value.ToShortDateString()); } else { string unitID = unitDic[unitCbx.Text]; sql = string.Format(@" WITH data_row AS( SELECT unit.name AS unit_name , room.name AS room_name , room.uid AS room_id , app.uid , app_detail.start_time , app_detail.end_time FROM $ischool.booking.meetingroom_unit AS unit LEFT OUTER JOIN $ischool.booking.meetingroom AS room ON unit.uid = room.ref_unit_id LEFT OUTER JOIN $ischool.booking.meetingroom_application AS app ON room.uid = app.ref_meetingroom_id LEFT OUTER JOIN $ischool.booking.meetingroom_application_detail AS app_detail ON app.uid = app_detail.ref_application_id WHERE room.uid IS NOT NULL AND app.uid IS NOT NULL AND app.is_canceled = false AND app.is_approved = true AND unit.uid = {2} AND app_detail.start_time >= '{0}' AND app_detail.start_time <= '{1}' ) SELECT unit_name , room_name , count(*) AS 使用次數 , SUM(end_time - start_time) AS 使用時數 FROM data_row GROUP BY unit_name ,room_name ", startTime.Value.ToShortDateString(), endTime.Value.ToShortDateString(), unitID); } #endregion QueryHelper qh = new QueryHelper(); DataTable dt = qh.Select(sql); Workbook book = new Workbook(); Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.統計會議室報表樣板), FileFormatType.Excel2003); book.Copy(template); Worksheet sheet = book.Worksheets[0]; Style style = sheet.Cells[2, 0].Style; #region 寫入資料 int rowIndex = 3; sheet.Cells[0, 1].PutValue(startTime.Value.ToShortDateString() + " ~ " + endTime.Value.ToShortDateString()); foreach (DataRow row in dt.Rows) { int colIndex = 0; sheet.Cells[rowIndex, colIndex].PutValue("" + row["unit_name"]); sheet.Cells[rowIndex, colIndex++].Style = style; sheet.Cells[rowIndex, colIndex].PutValue("" + row["room_name"]); sheet.Cells[rowIndex, colIndex++].Style = style; sheet.Cells[rowIndex, colIndex].PutValue("" + row["使用次數"]); sheet.Cells[rowIndex, colIndex++].Style = style; string[] times = ("" + row["使用時數"]).Split(':'); sheet.Cells[rowIndex, colIndex].PutValue(times[0] /*DateTime.Parse("" + row["使用時數"]).ToString("hh")*/); sheet.Cells[rowIndex, colIndex++].Style = style; } #endregion SaveFileDialog saveFile = new SaveFileDialog(); saveFile.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*"; string fileName = "會議室統計報表"; saveFile.FileName = fileName; try { if (saveFile.ShowDialog() == DialogResult.OK) { book.Save(saveFile.FileName); MsgBox.Show("會議室統計報表列印完成!"); this.Close(); Process.Start(saveFile.FileName); } else { FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存"); return; } } catch { MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!"); } }
private void BGW_DoWork(object sender, DoWorkEventArgs e) { //取得列印紙張 int sizeIndex = GetSizeIndex(); //取得需列印的項目清單 List <String> DisplayList = GetUserType(); //取得資料 BGW.ReportProgress(10, "取得所選班級"); #region 取得使用者所選擇的班級學生 //取得所選班級紀錄 List <ClassRecord> allClasses = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource); //從班級紀錄取得學生清單 List <StudentRecord> studentList = new List <StudentRecord>(); //學生記錄清單 List <String> StudentIDList = new List <string>(); //學生ID清單 foreach (ClassRecord classrecord in allClasses) { if (!_ClassNameDic.ContainsKey(classrecord.ID)) //儲存班級ID及Name方便往後查詢 { _ClassNameDic.Add(classrecord.ID, classrecord.Name); } foreach (StudentRecord student in classrecord.Students) //取得班級學生 { //只取得狀態為一般及延修的學生 if (student.Status == StudentRecord.StudentStatus.一般 || student.Status == StudentRecord.StudentStatus.延修) { studentList.Add(student); StudentIDList.Add(student.ID); } } } //建立班級字典存放各班級的學生 Dictionary <String, List <StudentRecord> > classDic = new Dictionary <string, List <StudentRecord> >(); foreach (StudentRecord student in studentList) { if (!classDic.ContainsKey(student.RefClassID)) //若該班級ID不存在就建立key { classDic.Add(student.RefClassID, new List <StudentRecord>()); } classDic[student.RefClassID].Add(student); //按對應班級ID將學生加入 } int totalStudent = studentList.Count; //全部學生的總數,進度回報用 foreach (String classid in classDic.Keys) { classDic[classid].Sort(SortStudent); //按學生座號排序字典內的清單 } #endregion BGW.ReportProgress(20, "取得資料紀錄"); #region 取得獎懲和缺曠紀錄 //獎勵紀錄 Dictionary <string, RewardRecord> MeritDemeritAttDic = new Dictionary <string, RewardRecord>(); foreach (String id in StudentIDList) //建立清單中全部學生的獎懲紀錄字典 { if (!MeritDemeritAttDic.ContainsKey(id)) { MeritDemeritAttDic.Add(id, new RewardRecord()); } } foreach (SHMeritRecord each in SHMerit.SelectByStudentIDs(StudentIDList)) { //if (_Semester == 1) //{ // if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) // continue; //} //else //{ // if (each.SchoolYear != _Schoolyear) continue; //} if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } else { MeritDemeritAttDic[each.RefStudentID].MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0; MeritDemeritAttDic[each.RefStudentID].MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0; MeritDemeritAttDic[each.RefStudentID].MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0; } } //懲罰紀錄 foreach (SHDemeritRecord each in SHDemerit.SelectByStudentIDs(StudentIDList)) { //if (_Semester == 1) //{ // if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) // continue; //} //else //{ // if (each.SchoolYear != _Schoolyear) continue; //} if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } else { if (each.Cleared == "是") { continue; } if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } else { MeritDemeritAttDic[each.RefStudentID].DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; MeritDemeritAttDic[each.RefStudentID].DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; MeritDemeritAttDic[each.RefStudentID].DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; } } //留查紀錄 if (each.MeritFlag == "2") { MeritDemeritAttDic[each.RefStudentID].Flag = true; } //MeritDemeritAttDic[each.RefStudentID].DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; //MeritDemeritAttDic[each.RefStudentID].DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; //MeritDemeritAttDic[each.RefStudentID].DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; } //取得節次對照表 Dictionary <String, String> periodDic = new Dictionary <String, String>(); foreach (PeriodMappingInfo var in PeriodMapping.SelectAll()) { if (!periodDic.ContainsKey(var.Name)) { periodDic.Add(var.Name, var.Type); //key=升降旗,一,二,三,午休...etc , value=一般,集會...etc } } //取得影響缺曠紀錄的假別清單 List <AbsenceMappingInfo> infoList = K12.Data.AbsenceMapping.SelectAll(); List <String> Noabsence = new List <string>(); foreach (AbsenceMappingInfo info in infoList) { if (!info.Noabsence) //若該假別會影響全勤就加入清單 { if (!Noabsence.Contains(info.Name)) { Noabsence.Add(info.Name); } } } //缺曠紀錄 foreach (SHAttendanceRecord each in SHAttendance.SelectByStudentIDs(StudentIDList)) { if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } foreach (AttendancePeriod record in each.PeriodDetail) { if (periodDic.ContainsKey(record.Period)) //確認是否有此節次 { string typename = periodDic[record.Period] + "_" + record.AbsenceType; //ex...一般_曠課,集會_曠課 if (!DisplayList.Contains(typename)) { continue; } if (Noabsence.Contains(record.AbsenceType)) //如果此缺曠紀錄的假別會影響全勤,該學生的前勤紀錄則為false { MeritDemeritAttDic[each.RefStudentID].全勤 = false; } if (!MeritDemeritAttDic[each.RefStudentID].Attendance.ContainsKey(record.AbsenceType)) { MeritDemeritAttDic[each.RefStudentID].Attendance.Add(record.AbsenceType, 0); } MeritDemeritAttDic[each.RefStudentID].Attendance[record.AbsenceType]++; } } } #endregion //產生表格 BGW.ReportProgress(30, "產生表格"); #region 產生表格 Workbook template = new Workbook(); Workbook prototype = new Workbook(); //列印尺寸 if (sizeIndex == 0) { template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表A3)); } else if (sizeIndex == 1) { template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表A4)); } else if (sizeIndex == 2) { template.Open(new MemoryStream(Properties.Resources.班級缺曠獎懲總表B4)); } prototype.Copy(template); Worksheet prototypeSheet; #region 範本sheet製作 //在範本sheet新增假別欄位 prototypeSheet = prototype.Worksheets[0]; _AbsenceType.Clear(); foreach (string item in DisplayList) { string type = item.Split('_')[1]; if (!_AbsenceType.Contains(type)) { _AbsenceType.Add(type); } } for (int i = 0; i < _AbsenceType.Count; i++) //依照勾選的顯示清單數量插入新的欄位 { prototypeSheet.Cells.InsertColumn(_DynamicIndex + 1); } //刪除兩個範本格式Column prototypeSheet.Cells.DeleteColumn(_DynamicIndex); prototypeSheet.Cells.DeleteColumn(_DynamicIndex); //標記新增的假別項目欄位索引 Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); //Excel欄位索引 //標記欄位索引 int index = _DynamicIndex; columnIndexTable.Add("座號", 0); columnIndexTable.Add("學號", 1); columnIndexTable.Add("姓名", 2); columnIndexTable.Add("嘉獎", 3); columnIndexTable.Add("小功", 4); columnIndexTable.Add("大功", 5); columnIndexTable.Add("警告", 6); columnIndexTable.Add("小過", 7); columnIndexTable.Add("大過", 8); columnIndexTable.Add("累嘉獎", 9); columnIndexTable.Add("累小功", 10); columnIndexTable.Add("累大功", 11); columnIndexTable.Add("累警告", 12); columnIndexTable.Add("累小過", 13); columnIndexTable.Add("累大過", 14); columnIndexTable.Add("留查", 15); //標記動態欄位索引並列印標題 //Dictionary<String, int> mergeIndex = new Dictionary<string, int>(); //紀錄需要merge的column數量 foreach (String type in _AbsenceType) { if (!columnIndexTable.ContainsKey(type)) { columnIndexTable.Add(type, index); prototypeSheet.Cells.CreateRange(1, columnIndexTable[type], 2, 1).Merge(); prototypeSheet.Cells[1, columnIndexTable[type]].PutValue(type); index++; } //columnIndexTable.Add(str, index); //標記動態欄位索引 //String[] strs = str.Split('_'); //將"一般_曠課"字串以_字元拆開 //prototypeSheet.Cells[2, columnIndexTable[str]].PutValue(strs[1]); //列印標題...ex:曠課 //if (!mergeIndex.ContainsKey(strs[0])) //若是相同title,則數量加1 //{ //mergeIndex.Add(strs[0], 0); //} //mergeIndex[strs[0]]++; //若是相同title,則數量加1 } //int start = _DynamicIndex; //merge的起始值 //foreach (String s in mergeIndex.Keys) //{ // prototypeSheet.Cells.CreateRange(1, start, 1, mergeIndex[s]).Merge(); // prototypeSheet.Cells[1, start].PutValue(s); // start += mergeIndex[s]; //} //全勤為最後標記 columnIndexTable.Add("全勤", index); for (int i = 3; i <= index; i++) { prototypeSheet.Cells.SetColumnWidth(i, 11); } #endregion #region 各班級sheet製作 int page = 1; foreach (String id in classDic.Keys) { prototype.Worksheets.AddCopy(0); //複製範本sheet prototypeSheet = prototype.Worksheets[page]; //從第二個分頁開始畫製表格,page++; prototypeSheet.Name = GetClassName(id); //sheet.Name = 班級名稱 //每5行加一條分隔線 Range eachFiveLine = prototype.Worksheets[0].Cells.CreateRange(_StartIndex, 5, false); //從第一個sheet複製 for (int i = _StartIndex; i < classDic[id].Count + _StartIndex; i += 5) //依照該班級學生數給予適量的分隔線 { prototypeSheet.Cells.CreateRange(i, 5, false).CopyStyle(eachFiveLine); } page++; //完成一個班級換下個sheet的畫製 } prototype.Worksheets.RemoveAt(0); //都完成後刪除第一個範本sheet #endregion #endregion //填入表格 BGW.ReportProgress(40, "填入表格"); #region 填入表格 _WK = new Workbook(); int sheetIndex = 0; _WK.Copy(prototype); //複製畫製好欄位的範本 Worksheet ws; Cells cs; //取得功過換算比例 MeritDemeritReduceRecord mdrr = MeritDemeritReduce.Select(); int?MAB = mdrr.MeritAToMeritB; int?MBC = mdrr.MeritBToMeritC; int?DAB = mdrr.DemeritAToDemeritB; int?DBC = mdrr.DemeritBToDemeritC; float progress = 50; float rate = (float)(100 - progress) / totalStudent; //進度百分比計算 foreach (String classid in classDic.Keys) { ws = _WK.Worksheets[sheetIndex]; cs = ws.Cells; index = _StartIndex; //列印起始索引 _CountAllColumnValue = new Dictionary <int, int>(); //重制個項目的總數 foreach (StudentRecord student in classDic[classid]) { progress += rate; BGW.ReportProgress((int)progress, "正在填入資料..."); String id = student.ID; int? 獎 = MeritDemeritAttDic[id].MeritCCount; int? 小功 = MeritDemeritAttDic[id].MeritBCount; int? 大功 = MeritDemeritAttDic[id].MeritACount; int? 警告 = MeritDemeritAttDic[id].DemeritCCount; int? 小過 = MeritDemeritAttDic[id].DemeritBCount; int? 大過 = MeritDemeritAttDic[id].DemeritACount; //將功過轉為嘉獎和警告,做功過相抵計算 獎 = 大功 * MAB * MBC + 小功 * MBC + 獎; 警告 = 大過 * DAB * DBC + 小過 * DBC + 警告; int?[] i = 功過相抵(獎, 警告); 獎 = i[0]; 警告 = i[1]; //獎勵換算 int?累嘉獎 = 獎 % MBC; int?累小功 = (獎 / MBC) % MAB; int?累大功 = (獎 / MBC) / MAB; //懲戒換算 int?累警告 = 警告 % DBC; int?累小過 = (警告 / DBC) % DAB; int?累大過 = (警告 / DBC) / DAB; cs[index, columnIndexTable["座號"]].PutValue(student.SeatNo); cs[index, columnIndexTable["學號"]].PutValue(student.StudentNumber); cs[index, columnIndexTable["姓名"]].PutValue(student.Name); SetColumnValue(cs[index, columnIndexTable["嘉獎"]], MeritDemeritAttDic[id].MeritCCount); SetColumnValue(cs[index, columnIndexTable["小功"]], MeritDemeritAttDic[id].MeritBCount); SetColumnValue(cs[index, columnIndexTable["大功"]], MeritDemeritAttDic[id].MeritACount); SetColumnValue(cs[index, columnIndexTable["警告"]], MeritDemeritAttDic[id].DemeritCCount); SetColumnValue(cs[index, columnIndexTable["小過"]], MeritDemeritAttDic[id].DemeritBCount); SetColumnValue(cs[index, columnIndexTable["大過"]], MeritDemeritAttDic[id].DemeritACount); SetColumnValue(cs[index, columnIndexTable["累嘉獎"]], 累嘉獎); SetColumnValue(cs[index, columnIndexTable["累小功"]], 累小功); SetColumnValue(cs[index, columnIndexTable["累大功"]], 累大功); SetColumnValue(cs[index, columnIndexTable["累警告"]], 累警告); SetColumnValue(cs[index, columnIndexTable["累小過"]], 累小過); SetColumnValue(cs[index, columnIndexTable["累大過"]], 累大過); SetColumnValue(cs[index, columnIndexTable["留查"]], MeritDemeritAttDic[id].Flag ? "是" : ""); SetColumnValue(cs[index, columnIndexTable["全勤"]], MeritDemeritAttDic[id].全勤 ? "是" : ""); foreach (String type in _AbsenceType) //列印勾選的假別 { if (MeritDemeritAttDic[id].Attendance.ContainsKey(type)) { SetColumnValue(cs[index, columnIndexTable[type]], MeritDemeritAttDic[id].Attendance[type]); } } index++; //換下一列 } //最後總計 index = FixIndex(index); Range endRow = cs.CreateRange(0, 1, false); cs.CreateRange(index, 1, false).Copy(endRow); cs[index, 0].PutValue("總計"); foreach (int cloumnIndex in _CountAllColumnValue.Keys) { cs[index, cloumnIndex].PutValue(_CountAllColumnValue[cloumnIndex]); } //列印日期及學校班級資訊 cs[0, 0].PutValue("列印日期:" + DateTime.Today.ToShortDateString()); cs.CreateRange(0, 3, 1, columnIndexTable.Last().Value - 2).Merge(); //合併標題欄位的儲存格 String title = String.Format("{0} {1} 學年度 {2} 學期 {3} 缺曠獎懲總表", K12.Data.School.ChineseName, _Schoolyear, _Semester == 1 ? "上" : "下", GetClassName(classid)); cs[0, 3].PutValue(title); cs[0, 3].Style.Font.Size = 28; //設定標題文字大小 cs[0, 3].Style.Font.IsBold = true; sheetIndex++; //換下一個sheet(下一個班級班) } //int sheet = _WK.Worksheets.Count; //for (int i = 0; i < sheet; i++) //{ // _WK.Worksheets[i].AutoFitColumns(); // _WK.Worksheets[i].AutoFitRows(); //} BGW.ReportProgress(100, "已完成 班級缺曠獎懲總表"); #endregion }
private void btnPrint_Click(object sender, EventArgs e) { DataTable dt; if (cbxUnit.Text == "--全部--") { dt = DAO.StatisticalReportDAO.GetReportData("--全部--", dtStar.Value.ToShortDateString(), dtEnd.Value.ToShortDateString()); } else { string unitID = dicUnitNameID[cbxUnit.Text]; dt = DAO.StatisticalReportDAO.GetReportData(unitID, dtStar.Value.ToShortDateString(), dtEnd.Value.ToShortDateString()); } // 列印樣板設定 Workbook book = new Workbook(); Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.統計設備報表樣板), FileFormatType.Excel2003); book.Copy(template); Worksheet sheet = book.Worksheets[0]; Style style = sheet.Cells[2, 0].Style; #region 寫入資料 int rowIndex = 3; sheet.Cells[0, 1].PutValue(dtStar.Value.ToShortDateString() + " ~ " + dtEnd.Value.ToShortDateString()); foreach (DataRow row in dt.Rows) { int colIndex = 0; sheet.Cells[rowIndex, colIndex].PutValue("" + row["unit_name"]); sheet.Cells[rowIndex, colIndex++].Style = style; sheet.Cells[rowIndex, colIndex].PutValue("" + row["equip_name"]); sheet.Cells[rowIndex, colIndex++].Style = style; sheet.Cells[rowIndex, colIndex].PutValue("" + row["使用次數"]); sheet.Cells[rowIndex, colIndex++].Style = style; sheet.Cells[rowIndex, colIndex].PutValue(DateTime.Parse("" + row["使用時數"]).ToString("hh")); sheet.Cells[rowIndex, colIndex++].Style = style; } #endregion SaveFileDialog saveFile = new SaveFileDialog(); saveFile.Filter = "Excel (*.xls)|*.xls|所有檔案 (*.*)|*.*"; string fileName = "設備使用狀況統計報表"; saveFile.FileName = fileName; try { if (saveFile.ShowDialog() == DialogResult.OK) { book.Save(saveFile.FileName); MsgBox.Show("設備使用狀況統計報表列印完成!"); this.Close(); Process.Start(saveFile.FileName); } else { FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存"); return; } } catch { MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!"); } }
void BGW_DoWork(object sender, DoWorkEventArgs e) { //建立社團比對學生ID的清單 //以每個社團為一個單位 //進行學生資料列印 SDL = new SCJoinDataLoad(); //建立Excel範本 Workbook template = new Workbook(); //Jean Open template.Open(new MemoryStream(Properties.Resources.社團點名單_範本), FileFormatType.Excel97To2003); //每一張 Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; //範圍 Range ptHeader = ptws.Cells.CreateRange(0, 4, false); Range ptEachRow = ptws.Cells.CreateRange(4, 1, false); //儲存資料 Workbook wb = new Workbook(); wb.Copy(prototype); //取得Sheet Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; //每一個社團 foreach (string club in SDL.ClubByStudentList.Keys) { //社團資訊收集 CLUBRecord cr = SDL.CLUBRecordDic[club]; //社團標頭 string TitleName1 = string.Format("{0}學年度/第{1}學期 社團點名單", cr.SchoolYear.ToString(), cr.Semester.ToString()); string TitleName2 = cr.ClubName + " (類型:" + cr.ClubCategory + ")"; ws.Cells.CreateRange(dataIndex, 4, false).CopyStyle(ptHeader); ws.Cells.CreateRange(dataIndex, 4, false).CopyValue(ptHeader); ws.Cells[dataIndex, 0].PutValue(TitleName1); dataIndex += 1; ws.Cells[dataIndex, 0].PutValue(TitleName2); dataIndex += 1; ws.Cells[dataIndex, 0].PutValue("代碼:" + cr.ClubNumber); ws.Cells[dataIndex, 1].PutValue("場地:" + cr.Location); ws.Cells[dataIndex, 3].PutValue("人數:" + SDL.ClubByStudentList[club].Count); //社團老師 string TeacherNameA = GetTeacherName(cr.RefTeacherID); string TeacherNameB = GetTeacherName(cr.RefTeacherID2); string TeacherNameC = GetTeacherName(cr.RefTeacherID3); string TeacherName = "老師:" + TeacherNameA + " " + TeacherNameB + " " + TeacherNameC; ws.Cells[dataIndex, 4].PutValue(TeacherName); dataIndex += 2; foreach (StudentRecord stud in SDL.ClubByStudentList[club]) { ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow); ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow); string classname = string.IsNullOrEmpty(stud.RefClassID) ? "" : stud.Class.Name; ws.Cells[dataIndex, 0].PutValue(classname); ws.Cells[dataIndex, 1].PutValue(stud.SeatNo.HasValue ? stud.SeatNo.Value.ToString() : ""); ws.Cells[dataIndex, 2].PutValue(stud.Name); ws.Cells[dataIndex, 3].PutValue(stud.StudentNumber); ws.Cells[dataIndex, 4].PutValue(stud.Gender); dataIndex += 1; } ws.Cells.CreateRange(dataIndex - 1, 0, 1, 6).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.HPageBreaks.Add(dataIndex, 6); } e.Result = wb; }
void _BGWDisciplineDetail_DoWork(object sender, DoWorkEventArgs e) { string reportName = "學生獎勵懲戒記錄明細"; #region 快取相關資料 //選擇的學生 List <StudentRecord> selectedStudents = Student.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource); selectedStudents.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer)); //紀錄所有學生ID List <string> allStudentID = new List <string>(); //每一位學生的獎懲明細(學生id<字串組合,物件>) Dictionary <string, Dictionary <string, DisciplineRecord> > studentDisciplineDetail = new Dictionary <string, Dictionary <string, DisciplineRecord> >(); //每一位學生的獎懲累計資料(學生id,特殊物件) Dictionary <string, DisciplineStatistics> studentDisciplineStatistics = new Dictionary <string, DisciplineStatistics>(); //紀錄每一種獎懲在報表中的 column index Dictionary <string, int> columnTable = new Dictionary <string, int>(); List <DisciplineRecord> DisciplineList = new List <DisciplineRecord>(); //取得所有學生ID foreach (StudentRecord var in selectedStudents) { allStudentID.Add(var.ID); } //初始化 string[] columnString; if (form.checkBoxX2Bool) //使用者已勾選"排除懲戒已銷過資料" { columnString = new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告", "留察", "登錄日期", "事由" } } ; else { columnString = new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告", "留察", "銷過", "銷過日期", "登錄日期", "事由" } }; int i = 4; foreach (string s in columnString) { columnTable.Add(s, i++); } #region 取得獎勵懲戒明細 if (form.SelectDayOrSchoolYear) //依日期 { if (form.SetupTime) //依發生日期 { DisciplineList = Discipline.SelectByOccurDate(allStudentID, form.dateTimeInput1.Value, form.dateTimeInput2.Value); } else //依登錄日期 { DisciplineList = Discipline.SelectByRegisterDate(allStudentID, form.dateTimeInput1.Value, form.dateTimeInput2.Value); } } else //依學期 { if (form.checkBoxX1Bool) //全部學期列印 { #region 全部學期列印 DisciplineList = Discipline.SelectByStudentIDs(allStudentID); #endregion } else //指定學期列印 { #region 指定學期列印 foreach (DisciplineRecord each in Discipline.SelectByStudentIDs(allStudentID)) { if (each.SchoolYear.ToString() == form.cbSchoolYear.Text && each.Semester.ToString() == form.cbSemester.Text) { DisciplineList.Add(each); } } #endregion } } #endregion if (form.checkBoxX2Bool) //使用者已勾選"排除懲戒已銷過資料" { IsOrRemoveData(DisciplineList); } if (DisciplineList.Count == 0) { e.Cancel = true; return; } foreach (DisciplineRecord each in DisciplineList) { string studentID = each.RefStudentID; string schoolYear = each.SchoolYear.ToString(); string semester = each.Semester.ToString(); string occurDate = each.OccurDate.ToShortDateString(); string reason = each.Reason; string disciplineID = each.ID; string sso = schoolYear + "_" + semester + "_" + occurDate + "_" + disciplineID; //初始化累計資料 if (!studentDisciplineStatistics.ContainsKey(studentID)) { studentDisciplineStatistics.Add(studentID, new DisciplineStatistics(studentID)); } //每一位學生獎勵懲戒資料 if (!studentDisciplineDetail.ContainsKey(studentID)) { studentDisciplineDetail.Add(studentID, new Dictionary <string, DisciplineRecord>()); } if (!studentDisciplineDetail[studentID].ContainsKey(sso)) { studentDisciplineDetail[studentID].Add(sso, each); } if (each.MeritFlag == "1") { studentDisciplineStatistics[studentID].大功 += each.MeritA.HasValue ? each.MeritA.Value : 0; studentDisciplineStatistics[studentID].小功 += each.MeritB.HasValue ? each.MeritB.Value : 0; studentDisciplineStatistics[studentID].獎 += each.MeritC.HasValue ? each.MeritC.Value : 0; } else if (each.MeritFlag == "0") { if (each.Cleared != "是") { studentDisciplineStatistics[studentID].大過 += each.DemeritA.HasValue ? each.DemeritA.Value : 0; studentDisciplineStatistics[studentID].小過 += each.DemeritB.HasValue ? each.DemeritB.Value : 0; studentDisciplineStatistics[studentID].警告 += each.DemeritC.HasValue ? each.DemeritC.Value : 0; } } else if (each.MeritFlag == "2") //留察 { } } #endregion #region 產生範本 Workbook template = new Workbook(); if (form.checkBoxX2Bool) { template.Open(new MemoryStream(K12.學生獎懲明細.Properties.Resources.學生獎懲明細_2), FileFormatType.Excel2003); } else { template.Open(new MemoryStream(K12.學生獎懲明細.Properties.Resources.學生獎懲明細), FileFormatType.Excel2003); } Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; int startPage = 1; int pageNumber = 1; int columnNumber = 15; if (form.checkBoxX2Bool) { columnNumber = 13; } //合併標題列 ptws.Cells.CreateRange(0, 0, 1, columnNumber).Merge(); ptws.Cells.CreateRange(1, 0, 1, columnNumber).Merge(); Range ptHeader = ptws.Cells.CreateRange(0, 4, false); Range ptEachRow = ptws.Cells.CreateRange(4, 1, false); #endregion #region 產生報表 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int studentCount = 1; foreach (StudentRecord studentInfo in selectedStudents) { string TitleName1 = School.ChineseName + " 個人獎勵懲戒明細"; string TitleName2 = "班級:" + ((studentInfo.Class == null ? " " : studentInfo.Class.Name) + " 座號:" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名:" + studentInfo.Name + " 學號:" + studentInfo.StudentNumber); //回報進度 _BGWDisciplineDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count)); if (!studentDisciplineDetail.ContainsKey(studentInfo.ID)) { continue; } //如果不是第一頁,就在上一頁的資料列下邊加黑線 if (index != 0) { ws.Cells.CreateRange(index - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } //複製 Header ws.Cells.CreateRange(index, 4, false).Copy(ptHeader); dataIndex = index + 4; int recordCount = 0; //學生Row筆數超過40筆,則添加換行符號,與標頭 int CountRows = 0; Dictionary <string, DisciplineRecord> disciplineDetail = studentDisciplineDetail[studentInfo.ID]; //取總頁數 , 資料數除以38列(70/38=2) int TotlePage = disciplineDetail.Count / 40; //目前頁數 int pageCount = 1; //如果還有餘數則+1 if (disciplineDetail.Count % 40 != 0) { TotlePage++; } //填寫基本資料 ws.Cells[index, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")"); pageCount++; ws.Cells[index + 1, 0].PutValue(TitleName2); foreach (string sso in disciplineDetail.Keys) { CountRows++; string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries); //複製每一個 row ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow); //填寫學生獎懲資料 ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]); ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]); ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]); ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2]))); DisciplineRecord record = disciplineDetail[sso]; if (record.MeritFlag == "1") { ws.Cells[dataIndex, columnTable["大功"]].PutValue(record.MeritA); ws.Cells[dataIndex, columnTable["小功"]].PutValue(record.MeritB); ws.Cells[dataIndex, columnTable["嘉獎"]].PutValue(record.MeritC); } else if (record.MeritFlag == "0") { if (record.Cleared == "是") { ws.Cells[dataIndex, columnTable["大過"]].PutValue(record.DemeritA); ws.Cells[dataIndex, columnTable["小過"]].PutValue(record.DemeritB); ws.Cells[dataIndex, columnTable["警告"]].PutValue(record.DemeritC); if (!form.checkBoxX2Bool) { ws.Cells[dataIndex, columnTable["銷過"]].PutValue(record.Cleared); ws.Cells[dataIndex, columnTable["銷過日期"]].PutValue(record.ClearDate.HasValue ? record.ClearDate.Value.ToShortDateString() : ""); } } else { ws.Cells[dataIndex, columnTable["大過"]].PutValue(record.DemeritA); ws.Cells[dataIndex, columnTable["小過"]].PutValue(record.DemeritB); ws.Cells[dataIndex, columnTable["警告"]].PutValue(record.DemeritC); } } else if (record.MeritFlag == "2") { ws.Cells[dataIndex, columnTable["留察"]].PutValue("是"); } ws.Cells[dataIndex, columnTable["事由"]].PutValue(record.Reason); ws.Cells[dataIndex, columnTable["登錄日期"]].PutValue(record.RegisterDate.HasValue ? record.RegisterDate.Value.ToShortDateString() : ""); dataIndex++; recordCount++; if (CountRows == 40 && pageCount <= TotlePage) { CountRows = 0; //分頁 ws.HPageBreaks.Add(dataIndex, columnNumber); //複製 Header ws.Cells.CreateRange(dataIndex, 4, false).Copy(ptHeader); //填寫基本資料 ws.Cells[dataIndex, 0].PutValue(TitleName1 + "(" + pageCount.ToString() + "/" + TotlePage.ToString() + ")"); pageCount++; //下一頁使用 ws.Cells[dataIndex + 1, 0].PutValue(TitleName2); dataIndex += 4; } } //獎懲統計資訊 Range disciplineStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber); disciplineStatisticsRange.Copy(ptEachRow); disciplineStatisticsRange.Merge(); disciplineStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black); disciplineStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black); disciplineStatisticsRange.RowHeight = 20.0; ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].PutValue("獎勵懲戒總計"); dataIndex++; //獎懲統計內容 ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Copy(ptEachRow); ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).RowHeight = 27.0; ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Merge(); ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].Style.ShrinkToFit = true; StringBuilder text = new StringBuilder(""); DisciplineStatistics disciplineStatistics = studentDisciplineStatistics[studentInfo.ID]; List <string> list = new List <string>(); if (disciplineStatistics.大功 > 0) { list.Add("大功:" + disciplineStatistics.大功); } if (disciplineStatistics.小功 > 0) { list.Add("小功:" + disciplineStatistics.小功); } if (disciplineStatistics.獎 > 0) { list.Add("嘉獎:" + disciplineStatistics.獎); } if (disciplineStatistics.大過 > 0) { list.Add("大過:" + disciplineStatistics.大過); } if (disciplineStatistics.小過 > 0) { list.Add("小過:" + disciplineStatistics.小過); } if (disciplineStatistics.警告 > 0) { list.Add("警告:" + disciplineStatistics.警告); } text.Append(string.Join(" ", list.ToArray())); ws.Cells[dataIndex, 0].PutValue(text.ToString()); ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black); dataIndex++; //資料列上邊加上黑線 //ws.Cells.CreateRange(index + 3, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black); //表格最右邊加上黑線 //ws.Cells.CreateRange(index + 2, columnNumber - 1, recordCount + 4, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black); index = dataIndex; //每500頁,增加一個WorkSheet,並於下標顯示(1~500)(501~xxx) if (pageNumber < 500) { ws.HPageBreaks.Add(index, columnNumber); pageNumber++; } else { ws.Name = startPage + " ~ " + (pageNumber + startPage - 1); ws = wb.Worksheets[wb.Worksheets.Add()]; ws.Copy(prototype.Worksheets[0]); startPage += pageNumber; pageNumber = 1; index = 0; } } if (dataIndex > 0) { //最後一頁的資料列下邊加上黑線 ws.Cells.CreateRange(dataIndex - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Name = startPage + " ~ " + (pageNumber + startPage - 2); } else { wb = new Workbook(); } #endregion string path = Path.Combine(Application.StartupPath, "Reports"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = Path.Combine(path, reportName + ".xlt"); e.Result = new object[] { reportName, path, wb }; }
void BGW_DoWork(object sender, DoWorkEventArgs e) { ClassClubTraMag mag = new ClassClubTraMag(_SchoolYear, _Semester, PrintLost); //依據使用者所選擇的學年期 //取得相關學生之社團結算成績 //列印不及格學生清單時,排除所有學期成績60分(含)以上之學生 //取得範本 #region 建立範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.班級社團成績單_範本), FileFormatType.Excel97To2003); if (PrintLost) //不及格確認單 { PriontName = "班級社團成績不及格(確認單)"; } else { PriontName = "班級社團成績單"; } Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; #region 建立標頭Column 評量比例 評 = new 評量比例(); if (評._wp == null) { e.Cancel = true; return; } ColumnNameList = new List <string>(); ColumnNameList.Add("座號"); ColumnNameList.Add("姓名"); ColumnNameList.Add("學號"); ColumnNameList.Add("性別"); ColumnNameList.Add("社團"); foreach (string each in 評.ColumnDic.Keys) { ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)"); } ColumnNameList.Add("學期成績"); if (PrintLost) //不及格確認單 { ColumnNameList.Add("簽名"); } int ColumnNameIndex = 0; //Jean Aspose更新 Style style = prototype.CreateStyle(); style.IsTextWrapped = true; foreach (string each in ColumnNameList) { ptws.Cells[2, ColumnNameIndex].SetStyle(style); ptws.Cells[2, ColumnNameIndex].PutValue(each); if (ColumnNameIndex >= 5) { ptws.Cells.SetColumnWidth(ColumnNameIndex, 10); tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1); } ColumnNameIndex++; } #endregion Range ptHeader = ptws.Cells.CreateRange(0, 3, false); Range ptEachRow = ptws.Cells.CreateRange(3, 1, false); //建立Excel檔案 Workbook wb = new Workbook(); wb.Copy(prototype); //取得第一張 Worksheet ws = wb.Worksheets[0]; int dataIndex = 0; int CountPage = 1; int DetalIndex = 5; #endregion #region 填資料 foreach (string classID in mag.TraDic.Keys) { if (mag.TraDic[classID].Count == 0) { continue; } ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader); ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader); ClassRecord cr = mag.ClassDic[classID]; ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count); string TitleName = string.Format("{0}學年度 第{1}學期 {2}", _SchoolYear.ToString(), _Semester.ToString(), PriontName); ws.Cells[dataIndex, 0].PutValue(TitleName); dataIndex++; //班級 ws.Cells.Merge(dataIndex, 0, 1, 3); ws.Cells[dataIndex, 0].PutValue(string.Format("班級:{0}", cr.Name)); ws.Cells.Merge(dataIndex, 4, 1, 3); //教師 if (!string.IsNullOrEmpty(cr.RefTeacherID)) { #region 教師 if (mag.TeacherDic.ContainsKey(cr.RefTeacherID)) { TeacherRecord tr = mag.TeacherDic[cr.RefTeacherID]; //是否有暱稱 if (!string.IsNullOrEmpty(tr.Nickname)) { string TeacherString = "班導師:" + tr.Name + "(" + tr.Nickname + ")"; ws.Cells[dataIndex, 4].PutValue(TeacherString); } else { string TeacherString = "班導師:" + mag.TeacherDic[cr.RefTeacherID].Name; ws.Cells[dataIndex, 4].PutValue(TeacherString); } } #endregion } //頁數 ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3); ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue("日期:" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString()); dataIndex += 2; mag.TraDic[classID].Sort(SortTraDic); foreach (ClassClubTraObj each in mag.TraDic[classID]) { ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow); ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow); ws.Cells[dataIndex, 0].PutValue(each.studentRecord.SeatNo.HasValue ? each.studentRecord.SeatNo.Value.ToString() : ""); ws.Cells[dataIndex, 1].PutValue(each.studentRecord.Name); ws.Cells[dataIndex, 2].PutValue(each.studentRecord.StudentNumber); ws.Cells[dataIndex, 3].PutValue(each.studentRecord.Gender); //社團 if (each.club != null) { ws.Cells[dataIndex, 4].PutValue(each.club.ClubName); } if (each.SCJoin != null) { if (!string.IsNullOrEmpty(each.SCJoin.Score)) { int x = 0; XmlElement xml = DSXmlHelper.LoadXml(each.SCJoin.Score); foreach (XmlElement each1 in xml.SelectNodes("Item")) { x++; string name = each1.GetAttribute("Name"); if (評.ColumnDic.ContainsKey(name)) { ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score")); } } } } for (int x = 3; x < ColumnNameList.Count; x++) { tool.SetCellBro(ws, dataIndex, x, 1, 1); } //學期成績 if (PrintLost) //不及格確認單 { ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 14); if (each.RSR != null) { ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8); string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : ""; ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score); } else { } } else { if (each.RSR != null) //有學期成績 { ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8); string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : ""; ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(Score); } else { } } dataIndex++; } CountPage++; //每班增加1頁 ws.HPageBreaks.Add(dataIndex, ColumnNameList.Count); } #endregion e.Result = wb; }
void _BGWResitList_DoWork(object sender, DoWorkEventArgs e) { object[] objectValue = (object[])e.Argument; int schoolyear = (int)objectValue[0]; int semester = (int)objectValue[1]; _BGWResitList.ReportProgress(0); #region 取得所有學生以及補考資訊 AccessHelper helper = new AccessHelper(); List <StudentRecord> allStudents = new List <StudentRecord>(); List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass(); WearyDogComputer computer = new WearyDogComputer(); double currentClass = 1; double totalClasses = allClasses.Count; foreach (ClassRecord aClass in allClasses) { List <StudentRecord> classStudents = aClass.Students; computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents); allStudents.AddRange(classStudents); _BGWResitList.ReportProgress((int)(currentClass++ *90.0 / totalClasses)); } double currentStudent = 1; double totalStudents = allStudents.Count; #endregion #region 產生表格並填入資料 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.補考名單_依學生), FileFormatType.Excel2003); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; Range eachRow = template.Worksheets[0].Cells.CreateRange(2, 1, false); ws.Cells[0, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 學生補考名單"); int index = 2; foreach (StudentRecord aStudent in allStudents) { string className = aStudent.RefClass.ClassName; string seatNo = aStudent.SeatNo; string studentNumber = aStudent.StudentNumber; string studentName = aStudent.StudentName; aStudent.SemesterSubjectScoreList.Sort(SortBySemesterSubjectScore); foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester && !info.Pass) { if (info.Detail.GetAttribute("達補考標準") == "是") { string subject = info.Subject; string levelString = ""; int level; if (int.TryParse(info.Level, out level)) { levelString = GetNumber(level); } string credit = info.CreditDec().ToString(); string score = info.Detail.GetAttribute("原始成績"); string limit = info.Detail.GetAttribute("補考標準"); ws.Cells.CreateRange(index, 1, false).Copy(eachRow); ws.Cells[index, 0].PutValue(className); ws.Cells[index, 1].PutValue(seatNo); ws.Cells[index, 2].PutValue(studentNumber); ws.Cells[index, 3].PutValue(studentName); ws.Cells[index, 4].PutValue(subject + (string.IsNullOrEmpty(levelString) ? "" : " " + levelString)); ws.Cells[index, 5].PutValue(credit); ws.Cells[index, 6].PutValue(score); ws.Cells[index, 7].PutValue(limit); index++; } } } _BGWResitList.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents)); } #endregion e.Result = wb; }
void _BWClassSemesterScore_DoWork(object sender, DoWorkEventArgs e) { object[] objectValue = (object[])e.Argument; int schoolyear = (int)objectValue[0]; int semester = (int)objectValue[1]; bool over100 = (bool)objectValue[2]; int papersize = (int)objectValue[3]; bool UseSourceScore = (bool)objectValue[4]; _BWClassSemesterScore.ReportProgress(0); Workbook template = new Workbook(); if (papersize == 0) { template.Open(new MemoryStream(Properties.Resources.班級學生學期成績一覽表B4)); } else if (papersize == 1) { template.Open(new MemoryStream(Properties.Resources.班級學生學期成績一覽表A3)); } Worksheet tempws = template.Worksheets[template.Worksheets.Add()]; // 表頭 0~5 垂直複製 Range tempHeader = template.Worksheets[0].Cells.CreateRange(0, 5, false); // 學業 Range tempEachEntry = template.Worksheets[0].Cells.CreateRange(1, 33, 4, 2); // 學分數 Range tempCreditHeader = template.Worksheets[0].Cells.CreateRange(1, 35, 4, 4); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; ScoreAverageMachine machine = new ScoreAverageMachine(); AccessHelper helper = new AccessHelper(); List <StudentRecord> allClassStudent = new List <StudentRecord>(); foreach (ClassRecord aClass in helper.ClassHelper.GetSelectedClass()) { allClassStudent.AddRange(aClass.Students); } double currentStudent = 1; double totalStudent = allClassStudent.Count; int rowIndex = 0; int headerColIndex = 0; foreach (ClassRecord aClass in helper.ClassHelper.GetSelectedClass()) { List <StudentRecord> allStudent = aClass.Students; int headerIndex = rowIndex; int count = 0; if (rowIndex > 0) { ws.Cells.CreateRange(headerIndex - 2, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Cells.CreateRange(headerIndex - 1, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } helper.StudentHelper.FillSchoolYearSubjectScore(true, allStudent); helper.StudentHelper.FillSemesterSubjectScore(true, allStudent); helper.StudentHelper.FillSemesterEntryScore(true, allStudent); helper.StudentHelper.FillField("SemesterEntryClassRating", allStudent); //學期分項班排名。 List <string> subjectHeader = new List <string>(); Dictionary <string, string> subjectCreditHeader = new Dictionary <string, string>(); Dictionary <string, string> subjectReqHeader = new Dictionary <string, string>(); List <string> entryHeader = new List <string>(); Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); foreach (StudentRecord student in allStudent) { foreach (SemesterSubjectScoreInfo info in student.SemesterSubjectScoreList) { // 不計學分、不須評分跳過 || info.Detail.GetAttribute("不需評分") == "是" if (info.Detail.GetAttribute("不計學分") == "是") { continue; } if (info.SchoolYear == schoolyear && info.Semester == semester) { int level; string levelString = ""; if (int.TryParse(info.Level, out level)) { levelString = GetNumber(level); } // //string header = info.Subject + levelString + "_" + (info.Require ? "必 " : "選 ") + "_" + info.Credit; //string creditHeader = (info.Require ? "必 " : "選 ") + info.Credit; string header = info.Subject + levelString + "_" + info.CreditDec(); string creditHeader = info.CreditDec().ToString(); string reqHeader = info.Require ? "必 " : "選 "; if (!subjectHeader.Contains(header)) { subjectHeader.Add(header); subjectCreditHeader.Add(header, creditHeader); subjectReqHeader.Add(header, reqHeader); } } } foreach (SemesterEntryScoreInfo info in student.SemesterEntryScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester) { string header = info.Entry; // 先過濾原始 // 使用原始成績 if (UseSourceScore && header == "學業(原始)") { header = "學業平均(原始)"; } if (UseSourceScore && header == "學業") { continue; } if (UseSourceScore == false && header == "學業(原始)") { continue; } if (UseSourceScore == false && header == "學業") { header = "學業平均(擇優)"; } if (!entryHeader.Contains(header)) { entryHeader.Add(header); } } } } entryHeader.Add("班級排名"); entryHeader.Sort(SortByEntryName); subjectHeader.Sort(SortBySubjectName); string sString = ""; if (UseSourceScore) { sString = "(原始)"; } else { sString = "(擇優)"; } ws.Cells.CreateRange(rowIndex, 4, false).Copy(tempHeader); ws.Cells[rowIndex, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 班級學生成績一覽表 班級: " + aClass.ClassName + " " + sString); headerColIndex = 3; foreach (string subject in subjectHeader) { columnIndexTable.Add(subject, headerColIndex); machine.AddItem(subject); string sl = subject.Split('_')[0]; ws.Cells[rowIndex + 1, headerColIndex].PutValue(sl); ws.Cells[rowIndex + 2, headerColIndex].PutValue(subjectReqHeader[subject]); ws.Cells[rowIndex + 3, headerColIndex].PutValue(subjectCreditHeader[subject]); headerColIndex++; } headerColIndex = 33; foreach (string entry in entryHeader) { columnIndexTable.Add(entry, headerColIndex); machine.AddItem(entry); ws.Cells.CreateRange(rowIndex + 1, headerColIndex, 4, 2).Copy(tempEachEntry); ws.Cells[rowIndex + 1, headerColIndex].PutValue(entry); headerColIndex++; } ws.Cells.CreateRange(rowIndex + 1, headerColIndex, 4, 4).Copy(tempCreditHeader); columnIndexTable.Add("應得學分", headerColIndex); columnIndexTable.Add("實得學分", headerColIndex + 1); columnIndexTable.Add("應得學分累計", headerColIndex + 2); columnIndexTable.Add("實得學分累計", headerColIndex + 3); tempws.Cells.CreateRange(0, 1, false).Copy(ws.Cells.CreateRange(rowIndex + 4, 1, false)); Range eachStudent = tempws.Cells.CreateRange(0, 1, false); rowIndex += 4; int defSS = schoolyear * 10 + semester; foreach (StudentRecord student in allStudent) { count++; ws.Cells.CreateRange(rowIndex, 1, false).Copy(eachStudent); ws.Cells[rowIndex, 0].PutValue(student.StudentNumber); ws.Cells[rowIndex, 1].PutValue(student.SeatNo); ws.Cells[rowIndex, 2].PutValue(student.StudentName); decimal shouldGetCredit = 0; decimal gotCredit = 0; decimal shouldGetTotalCredit = 0; decimal gotTotalCredit = 0; foreach (SemesterSubjectScoreInfo info in student.SemesterSubjectScoreList) { // 不計學分、不須評分跳過 || info.Detail.GetAttribute("不需評分") == "是" if (info.Detail.GetAttribute("不計學分") == "是") { continue; } if (info.SchoolYear == schoolyear && info.Semester == semester) { shouldGetCredit += info.CreditDec(); if (info.Pass) { gotCredit += info.CreditDec(); } int level; string levelString = ""; if (int.TryParse(info.Level, out level)) { levelString = GetNumber(level); } //之前 //string key = info.Subject + levelString + "_" + (info.Require ? "必 " : "選 ") + "_" + info.Credit; string key = info.Subject + levelString + "_" + info.CreditDec(); if (columnIndexTable.ContainsKey(key)) { // 判斷使用原始/擇優 decimal iScore = 0; if (UseSourceScore) { decimal.TryParse(info.Detail.GetAttribute("原始成績"), out iScore); } else { iScore = info.Score; // 擇優 } //ws.Cells[rowIndex, columnIndexTable[key]].PutValue((info.Pass ? "" : "*") + info.Score); //machine.AddScore(key, info.Score); ws.Cells[rowIndex, columnIndexTable[key]].PutValue((info.Pass ? "" : "*") + iScore); machine.AddScore(key, iScore); } } // 累計應得、累計實得,調整判斷學年度學期 int iss = info.SchoolYear * 10 + info.Semester; if (iss <= defSS) { shouldGetTotalCredit += info.CreditDec(); if (info.Pass) { gotTotalCredit += info.CreditDec(); } } } foreach (SemesterEntryScoreInfo info in student.SemesterEntryScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester) { if (UseSourceScore && info.Entry == "學業(原始)") { decimal score = info.Score; if (!over100 && score > 100) { score = 100; } ws.Cells[rowIndex, columnIndexTable["學業平均(原始)"]].PutValue(score); machine.AddScore("學業平均(原始)", score); } else if (UseSourceScore == false && info.Entry == "學業") { decimal score = info.Score; if (!over100 && score > 100) { score = 100; } ws.Cells[rowIndex, columnIndexTable["學業平均(擇優)"]].PutValue(score); machine.AddScore("學業平均(擇優)", score); } else { if (columnIndexTable.ContainsKey(info.Entry)) { decimal score = info.Score; if (!over100 && score > 100) { score = 100; } ws.Cells[rowIndex, columnIndexTable[info.Entry]].PutValue(score); machine.AddScore(info.Entry, score); } } } } ws.Cells[rowIndex, columnIndexTable["應得學分"]].PutValue(shouldGetCredit.ToString()); ws.Cells[rowIndex, columnIndexTable["實得學分"]].PutValue(gotCredit.ToString()); ws.Cells[rowIndex, columnIndexTable["應得學分累計"]].PutValue(shouldGetTotalCredit.ToString()); ws.Cells[rowIndex, columnIndexTable["實得學分累計"]].PutValue(gotTotalCredit.ToString()); SemesterEntryRating rating = new SemesterEntryRating(student); ws.Cells[rowIndex, columnIndexTable["班級排名"]].PutValue(rating.GetPlace(schoolyear, semester)); if (count % 5 == 0) { ws.Cells.CreateRange(rowIndex, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } rowIndex++; _BWClassSemesterScore.ReportProgress((int)(currentStudent++ *100.0 / totalStudent)); } ws.Cells.CreateRange(headerIndex, 0, 4, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Cells.CreateRange(rowIndex, 1, false).Copy(eachStudent); //for (int i = 0; i < headerColIndex + 4; i++) //{ // ws.Cells[rowIndex, i].PutValue(""); //} ws.Cells[rowIndex, 0].PutValue("平均"); foreach (string name in machine.GetAllItemName()) { ws.Cells[rowIndex, columnIndexTable[name]].PutValue(machine.GetAverage(name).ToString()); } machine.Clear(); rowIndex++; ws.HPageBreaks.Add(rowIndex, headerColIndex + 4); } ws.Cells.CreateRange(rowIndex - 2, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Cells.CreateRange(rowIndex - 1, 0, 1, headerColIndex + 4).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); e.Result = wb; }
void _BGWDisciplineDetail_DoWork(object sender, DoWorkEventArgs e) { string reportName = "學生獎勵明細"; #region 快取相關資料 //選擇的學生 List <SHStudentRecord> selectedStudents = SHStudent.SelectByIDs(K12.Presentation.NLDPanels.Student.SelectedSource); selectedStudents.Sort(new Comparison <SHStudentRecord>(CommonMethods.SHClassSeatNoComparer)); //紀錄所有學生ID List <string> allStudentID = new List <string>(); //每一位學生的獎勵明細 Dictionary <string, Dictionary <string, Dictionary <string, string> > > studentDisciplineDetail = new Dictionary <string, Dictionary <string, Dictionary <string, string> > >(); //每一位學生的獎勵累計資料 Dictionary <string, Dictionary <string, int> > studentDisciplineStatistics = new Dictionary <string, Dictionary <string, int> >(); //紀錄每一種獎勵在報表中的 column index Dictionary <string, int> columnTable = new Dictionary <string, int>(); //取得所有學生ID foreach (SHStudentRecord var in selectedStudents) { allStudentID.Add(var.ID); } //對照表 Dictionary <string, string> meritTable = new Dictionary <string, string>(); meritTable.Add("A", "大功"); meritTable.Add("B", "小功"); meritTable.Add("C", "嘉獎"); //初始化 string[] columnString = new string[] { "嘉獎", "小功", "大功", "事由", "備註" }; int i = 4; foreach (string s in columnString) { columnTable.Add(s, i++); } //產生 DSRequest,取得缺曠明細 DSResponse dsrsp; if (form.SelectDayOrSchoolYear) //依日期 { if (form.SetupTime) //依發生日期 { #region 依發生日期 DSXmlHelper helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Condition", "StartDate", form.StartDay); helper.AddElement("Condition", "EndDate", form.EndDay); helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "asc"); dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper)); #endregion } else //依登錄日期 { #region 依登錄日期 DSXmlHelper helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Condition", "StartRegisterDate", form.StartDay); helper.AddElement("Condition", "EndRegisterDate", form.EndDay); helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "asc"); dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper)); #endregion } } else //依學期 { if (form.checkBoxX1Bool) //全部學期列印 { #region 全部學期列印 DSXmlHelper helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "asc"); dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper)); #endregion } else //指定學期列印 { #region 指定學期列印 DSXmlHelper helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Condition", "SchoolYear", form.SchoolYear); helper.AddElement("Condition", "Semester", form.Semester); helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "asc"); dsrsp = QueryDiscipline.GetDiscipline(new DSRequest(helper)); #endregion } } if (dsrsp == null) { MsgBox.Show("未取得獎勵資料"); return; } foreach (XmlElement var in dsrsp.GetContent().GetElements("Discipline")) { if (var.SelectSingleNode("MeritFlag").InnerText == "1") { string studentID = var.SelectSingleNode("RefStudentID").InnerText; string schoolYear = var.SelectSingleNode("SchoolYear").InnerText; string semester = var.SelectSingleNode("Semester").InnerText; string occurDate = DateTime.Parse(var.SelectSingleNode("OccurDate").InnerText).ToShortDateString(); string reason = var.SelectSingleNode("Reason").InnerText; string remark = var.SelectSingleNode("Remark").InnerText; //2019/12/31 - 新增 string disciplineID = var.GetAttribute("ID"); string sso = schoolYear + "_" + semester + "_" + occurDate + "_" + disciplineID; //初始化累計資料 if (!studentDisciplineStatistics.ContainsKey(studentID)) { studentDisciplineStatistics.Add(studentID, new Dictionary <string, int>()); } //每一位學生獎勵資料 if (!studentDisciplineDetail.ContainsKey(studentID)) { studentDisciplineDetail.Add(studentID, new Dictionary <string, Dictionary <string, string> >()); } if (!studentDisciplineDetail[studentID].ContainsKey(sso)) { studentDisciplineDetail[studentID].Add(sso, new Dictionary <string, string>()); } //加入事由 if (!studentDisciplineDetail[studentID][sso].ContainsKey("事由")) { studentDisciplineDetail[studentID][sso].Add("事由", reason); } //加入事由 if (!studentDisciplineDetail[studentID][sso].ContainsKey("備註")) { studentDisciplineDetail[studentID][sso].Add("備註", remark); } XmlElement discipline = (XmlElement)var.SelectSingleNode("Detail/Discipline/Merit"); foreach (XmlAttribute attr in discipline.Attributes) { if (meritTable.ContainsKey(attr.Name)) { string name = meritTable[attr.Name]; if (!studentDisciplineStatistics[studentID].ContainsKey(name)) { studentDisciplineStatistics[studentID].Add(name, 0); } int v; if (int.TryParse(attr.InnerText, out v)) { studentDisciplineStatistics[studentID][name] += v; } if (!studentDisciplineDetail[studentID][sso].ContainsKey(name)) { studentDisciplineDetail[studentID][sso].Add(name, attr.InnerText); } } } } } #endregion #region 產生範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.學生獎勵記錄明細), FileFormatType.Excel2003); Workbook prototype = new Workbook(); prototype.Copy(template); Worksheet ptws = prototype.Worksheets[0]; int startPage = 1; int pageNumber = 1; int columnNumber = 9; //合併標題列 ptws.Cells.CreateRange(0, 0, 1, columnNumber).Merge(); ptws.Cells.CreateRange(1, 0, 1, columnNumber).Merge(); Range ptHeader = ptws.Cells.CreateRange(0, 4, false); Range ptEachRow = ptws.Cells.CreateRange(4, 1, false); #endregion #region 產生報表 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int studentCount = 1; foreach (SHStudentRecord studentInfo in selectedStudents) { //回報進度 _BGWDisciplineDetail.ReportProgress((int)(((double)studentCount++ *100.0) / (double)selectedStudents.Count)); if (!studentDisciplineDetail.ContainsKey(studentInfo.ID)) { continue; } //如果不是第一頁,就在上一頁的資料列下邊加黑線 if (index != 0) { ws.Cells.CreateRange(index - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } //複製 Header ws.Cells.CreateRange(index, 4, false).Copy(ptHeader); //填寫基本資料 ws.Cells[index, 0].PutValue(School.ChineseName + " 個人獎勵明細"); ws.Cells[index + 1, 0].PutValue("班級:" + ((studentInfo.Class == null) ? " " : studentInfo.Class.Name) + " 座號:" + ((studentInfo.SeatNo == null) ? " " : studentInfo.SeatNo.ToString()) + " 姓名:" + studentInfo.Name + " 學號:" + studentInfo.StudentNumber); dataIndex = index + 4; int recordCount = 0; Dictionary <string, Dictionary <string, string> > disciplineDetail = studentDisciplineDetail[studentInfo.ID]; foreach (string sso in disciplineDetail.Keys) { string[] ssoSplit = sso.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries); //複製每一個 row ws.Cells.CreateRange(dataIndex, 1, false).Copy(ptEachRow); //填寫學生獎勵資料 ws.Cells[dataIndex, 0].PutValue(ssoSplit[0]); ws.Cells[dataIndex, 1].PutValue(ssoSplit[1]); ws.Cells[dataIndex, 2].PutValue(ssoSplit[2]); ws.Cells[dataIndex, 3].PutValue(CommonMethods.GetChineseDayOfWeek(DateTime.Parse(ssoSplit[2]))); Dictionary <string, string> record = disciplineDetail[sso]; foreach (string name in record.Keys) { if (meritTable.ContainsValue(name)) { int v; if (int.TryParse(record[name], out v)) { if (v > 0) { ws.Cells[dataIndex, columnTable[name]].PutValue(record[name]); } } } else { if (columnTable.ContainsKey(name)) { ws.Cells[dataIndex, columnTable[name]].PutValue(record[name]); } } } dataIndex++; recordCount++; } //獎懲統計資訊 Range disciplineStatisticsRange = ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber); disciplineStatisticsRange.Copy(ptEachRow); disciplineStatisticsRange.Merge(); disciplineStatisticsRange.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black); disciplineStatisticsRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black); disciplineStatisticsRange.RowHeight = 20.0; ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].PutValue("獎勵總計"); dataIndex++; //獎懲統計內容 ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Copy(ptEachRow); ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).RowHeight = 27.0; ws.Cells.CreateRange(dataIndex, 0, 1, columnNumber).Merge(); ws.Cells[dataIndex, 0].Style.HorizontalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.VerticalAlignment = TextAlignmentType.Center; ws.Cells[dataIndex, 0].Style.Font.Size = 10; ws.Cells[dataIndex, 0].Style.ShrinkToFit = true; StringBuilder text = new StringBuilder(""); Dictionary <string, int> disciplineStatistics = studentDisciplineStatistics[studentInfo.ID]; foreach (string type in disciplineStatistics.Keys) { if (disciplineStatistics[type] > 0) { if (text.ToString() != "") { text.Append(" "); } text.Append(type + ":" + disciplineStatistics[type]); } } ws.Cells[dataIndex, 0].PutValue(text.ToString()); dataIndex++; //資料列上邊加上黑線 ws.Cells.CreateRange(index + 3, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); //表格最右邊加上黑線 ws.Cells.CreateRange(index + 2, columnNumber - 1, recordCount + 4, 1).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black); index = dataIndex; //設定分頁 if (pageNumber < 500) { ws.HPageBreaks.Add(index, columnNumber); pageNumber++; } else { ws.Name = startPage + " ~ " + (pageNumber + startPage - 1); ws = wb.Worksheets[wb.Worksheets.Add()]; ws.Copy(prototype.Worksheets[0]); startPage += pageNumber; pageNumber = 1; index = 0; } } if (dataIndex > 0) { //最後一頁的資料列下邊加上黑線 ws.Cells.CreateRange(dataIndex - 1, 0, 1, columnNumber).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); ws.Name = startPage + " ~ " + (pageNumber + startPage - 2); } else { wb = new Workbook(); } #endregion string path = Path.Combine(Application.StartupPath, "Reports"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = Path.Combine(path, reportName + ".xlt"); e.Result = new object[] { reportName, path, wb }; }
void _BGWAbsenceWeekListByAbsence_DoWork(object sender, DoWorkEventArgs e) { string reportName = "缺曠週報表"; object[] args = e.Argument as object[]; Dictionary <string, List <string> > config = args[0] as Dictionary <string, List <string> >; DateTime startDate = (DateTime)args[1]; DateTime endDate = (DateTime)args[2]; int size = (int)args[3]; bool CheckClass = (bool)args[4]; bool CheckWeek = (bool)args[5]; DateTime firstDate = startDate; #region 快取學生缺曠紀錄資料 List <ClassRecord> selectedClass = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource); selectedClass.Sort(new Comparison <ClassRecord>(CommonMethods.ClassComparer)); Dictionary <string, List <StudentRecord> > classStudentList = new Dictionary <string, List <StudentRecord> >(); List <string> allStudentID = new List <string>(); //紀錄每一個 Column 的 Index Dictionary <string, int> columnTable = new Dictionary <string, int>(); //紀錄每一個學生的缺曠紀錄 Dictionary <string, Dictionary <string, Dictionary <string, int> > > studentAbsenceList = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >(); //紀錄每一個學生本週累計的缺曠紀錄 Dictionary <string, Dictionary <string, int> > studentWeekAbsenceList = new Dictionary <string, Dictionary <string, int> >(); //紀錄每一個學生學期累計的缺曠紀錄 Dictionary <string, Dictionary <string, int> > studentSemesterAbsenceList = new Dictionary <string, Dictionary <string, int> >(); //節次對照表 Dictionary <string, string> periodList = new Dictionary <string, string>(); int allStudentNumber = 0; //計算學生總數,取得所有學生ID foreach (ClassRecord aClass in selectedClass) { List <StudentRecord> classStudent = new List <StudentRecord>(); //取得一般生 foreach (StudentRecord each in aClass.Students) { if (each.Status == StudentRecord.StudentStatus.一般) { classStudent.Add(each); } } classStudent.Sort(new Comparison <StudentRecord>(CommonMethods.ClassSeatNoComparer)); foreach (StudentRecord aStudent in classStudent) { allStudentID.Add(aStudent.ID); } if (!classStudentList.ContainsKey(aClass.ID)) { classStudentList.Add(aClass.ID, classStudent); } allStudentNumber += classStudent.Count; } //取得 Period List List <K12.Data.PeriodMappingInfo> PeriodInfoList = K12.Data.PeriodMapping.SelectAll(); foreach (PeriodMappingInfo var in PeriodInfoList) { string name = var.Name; string type = var.Type; if (!periodList.ContainsKey(name)) { periodList.Add(name, type); } } //產生 DSRequest DSXmlHelper helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Condition", "StartDate", startDate.ToShortDateString()); if (CheckWeek) //new,True就是取得至星期日內 { helper.AddElement("Condition", "EndDate", endDate.ToShortDateString()); } else //new,false就是取得到星期五的缺曠內容 { helper.AddElement("Condition", "EndDate", endDate.AddDays(-2).ToShortDateString()); } helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "desc"); DSResponse dsrsp = Get.GetAttendance(new DSRequest(helper)); foreach (XmlElement var in dsrsp.GetContent().GetElements("Attendance")) { string studentID = var.SelectSingleNode("RefStudentID").InnerText; string occurDate = DateTime.Parse(var.SelectSingleNode("OccurDate").InnerText).ToShortDateString(); if (!studentAbsenceList.ContainsKey(studentID)) { studentAbsenceList.Add(studentID, new Dictionary <string, Dictionary <string, int> >()); } if (!studentAbsenceList[studentID].ContainsKey(occurDate)) { studentAbsenceList[studentID].Add(occurDate, new Dictionary <string, int>()); } if (!studentWeekAbsenceList.ContainsKey(studentID)) { studentWeekAbsenceList.Add(studentID, new Dictionary <string, int>()); } foreach (XmlElement period in var.SelectNodes("Detail/Attendance/Period")) { string type = periodList.ContainsKey(period.InnerText) ? periodList[period.InnerText] : ""; string absence = period.GetAttribute("AbsenceType"); if (!studentAbsenceList[studentID][occurDate].ContainsKey(type + "_" + absence)) { studentAbsenceList[studentID][occurDate].Add(type + "_" + absence, 0); } studentAbsenceList[studentID][occurDate][type + "_" + absence]++; if (!studentWeekAbsenceList[studentID].ContainsKey(type + "_" + absence)) { studentWeekAbsenceList[studentID].Add(type + "_" + absence, 0); } studentWeekAbsenceList[studentID][type + "_" + absence]++; } } //產生 DSRequest,本學期累計 helper = new DSXmlHelper("Request"); helper.AddElement("Field"); helper.AddElement("Field", "All"); helper.AddElement("Condition"); foreach (string var in allStudentID) { helper.AddElement("Condition", "RefStudentID", var); } helper.AddElement("Condition", "SchoolYear", K12.Data.School.DefaultSchoolYear); helper.AddElement("Condition", "Semester", K12.Data.School.DefaultSemester); if (CheckWeek) //new,True就是取得至星期日內 { helper.AddElement("Condition", "EndDate", endDate.ToShortDateString()); } else { helper.AddElement("Condition", "EndDate", endDate.AddDays(-2).ToShortDateString()); } helper.AddElement("Order"); helper.AddElement("Order", "OccurDate", "desc"); dsrsp = Get.GetAttendance(new DSRequest(helper)); foreach (XmlElement var in dsrsp.GetContent().GetElements("Attendance")) { string studentID = var.SelectSingleNode("RefStudentID").InnerText; if (!studentSemesterAbsenceList.ContainsKey(studentID)) { studentSemesterAbsenceList.Add(studentID, new Dictionary <string, int>()); } foreach (XmlElement period in var.SelectNodes("Detail/Attendance/Period")) { string type = periodList.ContainsKey(period.InnerText) ? periodList[period.InnerText] : ""; string absence = period.GetAttribute("AbsenceType"); if (!studentSemesterAbsenceList[studentID].ContainsKey(type + "_" + absence)) { studentSemesterAbsenceList[studentID].Add(type + "_" + absence, 0); } studentSemesterAbsenceList[studentID][type + "_" + absence]++; } } #endregion //取得學生英文別名 //Dictionary<string, string> StudentEXTDic = tool.GetStudentEXT(allStudentID); //計算使用者自訂項目 int allAbsenceNumber = 7; foreach (string type in config.Keys) { allAbsenceNumber += config[type].Count; } int current = 1; int all = allAbsenceNumber + allStudentNumber; #region 動態產生範本 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.缺曠週報表_依假別), FileFormatType.Excel2003); Range tempStudent = template.Worksheets[0].Cells.CreateRange(0, 4, true); Range tempEachColumn = template.Worksheets[0].Cells.CreateRange(4, 1, true); Workbook prototype = new Workbook(); prototype.Copy(template); prototype.Worksheets[0].Cells.CreateRange(0, 4, true).Copy(tempStudent); int titleRow = 2; int dayNumber; if (CheckWeek) { dayNumber = 7; } else { dayNumber = 5; } int colIndex = 4; int dayStartIndex = colIndex; int dayEndIndex; int dayColumnNumber; //根據使用者設定的缺曠別,產生 Column List <string> perList = new List <string>(); foreach (string type in config.Keys) { if (config[type].Count == 0) { continue; } int typeStartIndex = colIndex; foreach (string var in config[type]) { string SaveVar = type + "_" + var; prototype.Worksheets[0].Cells.CreateRange(colIndex, 1, true).Copy(tempEachColumn); prototype.Worksheets[0].Cells[titleRow + 2, colIndex].PutValue(var); Cell _c = prototype.Worksheets[0].Cells[titleRow + 2, colIndex]; _c.Style.Rotation = -90; //將文字改為垂直 columnTable.Add(SaveVar, colIndex - 4); if (!perList.Contains(SaveVar)) { perList.Add(type + "_" + var); } colIndex++; _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all)); } int typeEndIndex = colIndex; Range typeRange = prototype.Worksheets[0].Cells.CreateRange(titleRow, typeStartIndex, titleRow + 2, typeEndIndex - typeStartIndex); typeRange.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Black); typeRange.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black); prototype.Worksheets[0].Cells.CreateRange(titleRow + 1, typeStartIndex, 1, typeEndIndex - typeStartIndex).Merge(); prototype.Worksheets[0].Cells[titleRow + 1, typeStartIndex].PutValue(type); } dayEndIndex = colIndex; dayColumnNumber = dayEndIndex - dayStartIndex; if (dayColumnNumber == 0) { dayColumnNumber = 1; } prototype.Worksheets[0].Cells.CreateRange(titleRow, dayStartIndex, 1, dayColumnNumber).Merge(); Range dayRange = prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true); //prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue(firstDate.ToShortDateString() + " (" + CommonMethods.GetChineseDayOfWeek(firstDate) + ")"); //columnTable.Add(firstDate.ToShortDateString(), dayStartIndex); //以一個日期為單位進行 Column 複製 //for (int i = 1; i < dayNumber; i++) //{ // firstDate = firstDate.AddDays(1); // dayStartIndex += dayColumnNumber; // prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange); // prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue(firstDate.ToShortDateString() + " (" + CommonMethods.GetChineseDayOfWeek(firstDate) + ")"); // columnTable.Add(firstDate.ToShortDateString(), dayStartIndex); // _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ * 100.0) / (double)all)); //} //dayStartIndex += dayColumnNumber; //prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange); prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue("本週合計(Week)"); columnTable.Add("本週合計(Week)", dayStartIndex); _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all)); dayStartIndex += dayColumnNumber; prototype.Worksheets[0].Cells.CreateRange(dayStartIndex, dayColumnNumber, true).Copy(dayRange); //2011/3/10 - 調整顯示字樣 prototype.Worksheets[0].Cells[titleRow, dayStartIndex].PutValue("本學期累計(Semester)"); columnTable.Add("本學期累計(Semester)", dayStartIndex); _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all)); dayStartIndex += dayColumnNumber; //合併標題列 prototype.Worksheets[0].Cells.CreateRange(0, 0, 1, dayStartIndex).Merge(); prototype.Worksheets[0].Cells.CreateRange(1, 0, 1, dayStartIndex).Merge(); Range prototypeRow = prototype.Worksheets[0].Cells.CreateRange(5, 1, false); Range prototypeHeader = prototype.Worksheets[0].Cells.CreateRange(0, 5, false); current++; #endregion #region 產生報表 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; #region 判斷紙張大小 if (size == 0) { ws.PageSetup.PaperSize = PaperSizeType.PaperA3; ws.PageSetup.Zoom = 90; } else if (size == 1) { ws.PageSetup.PaperSize = PaperSizeType.PaperA4; ws.PageSetup.Zoom = 65; } else if (size == 2) { ws.PageSetup.PaperSize = PaperSizeType.PaperB4; ws.PageSetup.Zoom = 80; } #endregion int index = 0; int dataIndex = 0; List <string> list = new List <string>(); #region 檢查是否有資料 if (CheckClass) //如果需要過慮資料 { foreach (ClassRecord CheckClassData in selectedClass) { List <StudentRecord> classStudent = classStudentList[CheckClassData.ID]; bool jumpNext = false; foreach (StudentRecord CheckStudentData in classStudent) { if (studentWeekAbsenceList.ContainsKey(CheckStudentData.ID)) //如果studentWeekAbsenceList內包含了該學生ID { foreach (string ajn in studentWeekAbsenceList[CheckStudentData.ID].Keys) //取得該學生假別 { if (perList.Contains(ajn)) //如果假別的確包含於清單中 { if (!list.Contains(CheckClassData.ID)) //如果清單中不包含就加入 { list.Add(CheckClassData.ID); jumpNext = true; } } if (jumpNext) { break; } } } if (jumpNext) { break; } } } } else //如果不需要過慮資料 { foreach (ClassRecord CheckClassData in selectedClass) { list.Add(CheckClassData.ID); } } #endregion foreach (ClassRecord classInfo in selectedClass) { if (list.Contains(classInfo.ID)) { List <StudentRecord> classStudent = classStudentList[classInfo.ID]; //如果不是第一頁,就在上一頁的資料列下邊加黑線 if (index != 0) { ws.Cells.CreateRange(index - 1, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } //複製 Header ws.Cells.CreateRange(index, 5, false).Copy(prototypeHeader); //填寫基本資料 string TeacherName = ""; if (classInfo.Teacher != null) { TeacherName = classInfo.Teacher.Name + " 老師"; } ws.Cells[index, 0].PutValue("SchoolYear:" + tool.GetSchoolChange(K12.Data.School.DefaultSchoolYear) + " Semester:" + K12.Data.School.DefaultSemester + " " + School.ChineseName + " 學生缺曠課表"); if (CheckWeek) //new,True就是取得至星期日內 { ws.Cells[index + 1, 0].PutValue("班導師: " + TeacherName + " 缺曠統計區間: " + startDate.ToShortDateString() + " ~ " + endDate.ToShortDateString() + " 列印日期:" + DateTime.Today.ToShortDateString()); } else { ws.Cells[index + 1, 0].PutValue("班導師: " + TeacherName + " 缺曠統計區間: " + startDate.ToShortDateString() + " ~ " + endDate.AddDays(-2).ToShortDateString() + " 列印日期:" + DateTime.Today.ToShortDateString()); } dataIndex = index + 5; int studentCount = 0; while (studentCount < classStudent.Count) { //複製每一個 row ws.Cells.CreateRange(dataIndex, 1, false).Copy(prototypeRow); if (studentCount % 5 == 0 && studentCount != 0) { Range eachFiveRow = ws.Cells.CreateRange(dataIndex, 0, 1, dayStartIndex); eachFiveRow.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Double, Color.Black); } //填寫學生缺曠資料 StudentRecord student = classStudent[studentCount]; string studentID = student.ID; ws.Cells[dataIndex, 0].PutValue(student.Class != null ? student.Class.Name : ""); ws.Cells[dataIndex, 1].PutValue(student.SeatNo); ws.Cells[dataIndex, 2].PutValue(student.Name); ws.Cells[dataIndex, 3].PutValue(student.EnglishName); //英文別名 //if (StudentEXTDic.ContainsKey(student.ID)) //{ // ws.Cells[dataIndex, 4].PutValue(StudentEXTDic[student.ID]); //} int startCol; //if (studentAbsenceList.ContainsKey(studentID)) //{ // foreach (string date in studentAbsenceList[studentID].Keys) // { // Dictionary<string, int> dateAbsence = studentAbsenceList[studentID][date]; // startCol = columnTable[date]; // foreach (string var in dateAbsence.Keys) // { // if (columnTable.ContainsKey(var)) // { // ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(dateAbsence[var]); // } // } // } //} if (studentWeekAbsenceList.ContainsKey(studentID)) { startCol = columnTable["本週合計(Week)"]; Dictionary <string, int> studentWeek = studentWeekAbsenceList[studentID]; foreach (string var in studentWeek.Keys) { if (columnTable.ContainsKey(var)) { ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(studentWeekAbsenceList[studentID][var]); } } } if (studentSemesterAbsenceList.ContainsKey(studentID)) { startCol = columnTable["本學期累計(Semester)"]; Dictionary <string, int> studentSemester = studentSemesterAbsenceList[studentID]; foreach (string var in studentSemester.Keys) { if (columnTable.ContainsKey(var)) { ws.Cells[dataIndex, startCol + columnTable[var]].PutValue(studentSemester[var]); } } } studentCount++; dataIndex++; _BGWAbsenceWeekListByAbsence.ReportProgress((int)(((double)current++ *100.0) / (double)all)); } //資料列上邊各加上黑線 ws.Cells.CreateRange(index + 4, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); index = dataIndex; //設定分頁 ws.HPageBreaks.Add(index, dayStartIndex); } } //最後一頁的資料列下邊加上黑線 if (dataIndex != 0) { ws.Cells.CreateRange(dataIndex - 1, 0, 1, dayStartIndex).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black); } #endregion string path = Path.Combine(Application.StartupPath, "Reports"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = Path.Combine(path, reportName + ".xlt"); e.Result = new object[] { reportName, path, wb }; }
void _BGWTotalDisciplineAndAbsence_DoWork(object sender, DoWorkEventArgs e) { string reportName = "歷年功過及出席統計"; _BGWTotalDisciplineAndAbsence.ReportProgress(0); #region 取得資料 AccessHelper helper = new AccessHelper(); List <StudentRecord> students = helper.StudentHelper.GetSelectedStudent(); helper.StudentHelper.FillSemesterEntryScore(true, students); helper.StudentHelper.FillReward(students); helper.StudentHelper.FillAttendance(students); StatisticsCollection stat = new StatisticsCollection(); Dictionary <string, List <string> > detailList = new Dictionary <string, List <string> >(); Dictionary <string, int> rewardDict = new Dictionary <string, int>(); foreach (StudentRecord each in students) { StatisticsData data = new StatisticsData(); List <string> list = new List <string>(); //獎懲 foreach (RewardInfo info in each.RewardList) { int schoolyear = info.SchoolYear; int semester = info.Semester; if (info.UltimateAdmonition == true) { data.AddItem(schoolyear, semester, "留校察看", 1); list.Add(CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " 留校察看"); continue; } rewardDict.Clear(); data.AddItem(schoolyear, semester, "大功", (decimal)info.AwardA); data.AddItem(schoolyear, semester, "小功", (decimal)info.AwardB); data.AddItem(schoolyear, semester, "嘉獎", (decimal)info.AwardC); rewardDict.Add("大功", info.AwardA); rewardDict.Add("小功", info.AwardB); rewardDict.Add("嘉獎", info.AwardC); if (_print_cleared == true || info.Cleared == false) { data.AddItem(schoolyear, semester, "大過", (decimal)info.FaultA); data.AddItem(schoolyear, semester, "小過", (decimal)info.FaultB); data.AddItem(schoolyear, semester, "警告", (decimal)info.FaultC); rewardDict.Add("大過", info.FaultA); rewardDict.Add("小過", info.FaultB); rewardDict.Add("警告", info.FaultC); } string rewardStat = ""; foreach (string var in new string[] { "大功", "小功", "嘉獎", "大過", "小過", "警告" }) { if (rewardDict.ContainsKey(var) && rewardDict[var] > 0) { if (!string.IsNullOrEmpty(rewardStat)) { rewardStat += ", "; } rewardStat += var + rewardDict[var] + "次"; } } //兩個條件可加入明細清單 //1.如果勾選消過也列印 //2.資料未消過 if (_print_cleared == true) { string all = ""; if (info.Cleared) { all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat + " (已銷過)"; } else { all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat; } list.Add(all); } else if (info.Cleared == false) { string all = CDATE(info.OccurDate.ToShortDateString()) + " " + info.OccurReason + " " + rewardStat; list.Add(all); } #region 註解掉了 //if (((info.OccurReason + " " + rewardStat) as string).Length >= 20) //{ // List<string> mini = new List<string>(); // string reason = info.OccurReason; // while (reason.Length >= 20) // { // mini.Add(reason.Substring(0, 19)); // reason = reason.Substring(19); // } // if (((reason + " " + rewardStat) as string).Length >= 20) // { // mini.Add(reason); // mini.Add(rewardStat); // } // else // mini.Add(reason + " " + rewardStat); // //mini.Add(CDATE(info.OccurDate.ToShortDateString()) + " "); // for (int i = 0; i < mini.Count; i++) // { // if (i == 0) // list.Add(CDATE(info.OccurDate.ToShortDateString()) + " " + mini[i]); // else // list.Add(" " + mini[i]); // } //} //else // list.Add(all); #endregion } detailList.Add(each.StudentID, list); //缺曠 foreach (AttendanceInfo info in each.AttendanceList) { int schoolyear = info.SchoolYear; int semester = info.Semester; if (PrintDic.ContainsKey(info.Period)) { data.AddItem(schoolyear, semester, PrintDic[info.Period] + "_" + info.Absence, 1); } } //德行成績 foreach (SemesterEntryScoreInfo info in each.SemesterEntryScoreList) { if (info.Entry == "德行") { int schoolyear = info.SchoolYear; int semester = info.Semester; data.AddItem(schoolyear, semester, "德行成績", info.Score); } } data.MendSemester(); stat.Add(each.StudentID, data); } #endregion #region 產生範本 int AsbCount = 0; foreach (string each in _print_types.Keys) { AsbCount += _print_types[each].Count; } totalRow = detailRow + 11 + AsbCount; foreach (StudentRecord each in students) { if (detailRow < detailList[each.StudentID].Count / 2 + 1) { detailRow = detailList[each.StudentID].Count / 2 + 1; totalRow = detailRow + 11 + AsbCount; } } Workbook pt1 = GetTemplate(6); Workbook pt2 = GetTemplate(8); Workbook pt3 = GetTemplate(10); Dictionary <int, Workbook> pts = new Dictionary <int, Workbook>(); pts.Add(6, pt1); pts.Add(8, pt2); pts.Add(10, pt3); Range ptr1 = pt1.Worksheets[0].Cells.CreateRange(0, totalRow, false); Range ptr2 = pt2.Worksheets[0].Cells.CreateRange(0, totalRow, false); Range ptr3 = pt3.Worksheets[0].Cells.CreateRange(0, totalRow, false); Dictionary <int, Range> ptrs = new Dictionary <int, Range>(); ptrs.Add(6, ptr1); ptrs.Add(8, ptr2); ptrs.Add(10, ptr3); #endregion #region 產生報表 Workbook wb = new Workbook(); wb.Copy(pt1); wb.Worksheets[0].Copy(pt1.Worksheets[0]); int wsCount = wb.Worksheets.Count; for (int i = wsCount - 1; i > 0; i--) { wb.Worksheets.RemoveAt(i); } wb.Worksheets[0].Name = "一般(6學期)"; Dictionary <int, int> sheetIndex = new Dictionary <int, int>(); Dictionary <int, Worksheet> sheets = new Dictionary <int, Worksheet>(); sheets.Add(6, wb.Worksheets[0]); sheetIndex.Add(6, 0); Worksheet ws = wb.Worksheets[0]; int index = 0; int cur = 6; int pages = 500; // int start = 1; int limit = pages; int studentCount = 0; //學生數 int allStudentCount = students.Count; foreach (StudentRecord each in students) { //取得學生統計 StatisticsData data = stat[each.StudentID]; #region 判斷學期數 if (data.GetSemesterNumber() > 6 && data.GetSemesterNumber() <= 8) { //學期數大於6小於8 //特殊報表 if (!sheets.ContainsKey(8)) { int new_ws_index = wb.Worksheets.Add(); wb.Worksheets[new_ws_index].Copy(pt2.Worksheets[0]); wb.Worksheets[new_ws_index].Name = "特殊(8學期)"; sheets.Add(8, wb.Worksheets[new_ws_index]); sheetIndex.Add(8, 0); } //變更預設報表位置 ws = sheets[8]; //取得報表定位 index = sheetIndex[8]; //取得報表定位? cur = 8; studentCount++; } else if (data.GetSemesterNumber() == 6) { ws = sheets[6]; index = sheetIndex[6]; cur = 6; studentCount++; } else { //學期數大於8小於10 //特殊1報表 if (!sheets.ContainsKey(10)) { int new_ws_index = wb.Worksheets.Add(); wb.Worksheets[new_ws_index].Copy(pt3.Worksheets[0]); wb.Worksheets[new_ws_index].Name = "特殊(其它)"; sheets.Add(10, wb.Worksheets[new_ws_index]); sheetIndex.Add(10, 0); } ws = sheets[10]; index = sheetIndex[10]; cur = 10; studentCount++; } #endregion //取得畫面範圍 Range(0~TotalRow) ws.Cells.CreateRange(index, totalRow, false).Copy(ptrs[cur]); //填入標題 ws.Cells[index + 1, 0].PutValue(string.Format("班級:{0} 學號:{1} 姓名:{2}", (each.RefClass != null) ? each.RefClass.ClassName : "", each.StudentNumber, each.StudentName)); //第一行位置(標題+2) int firstRow = index + 2; //第一列位置 int col = 2; //處理每個學期的統計 foreach (string semsString in data.Semesters) { //填入學年度/學期 ws.Cells[firstRow, col].PutValue(DisplaySemester(semsString)); //取得學期統計資料 Dictionary <string, decimal> semsDict = data.GetItem(semsString); foreach (string item in semsDict.Keys) { if (rowIndexTable.ContainsKey(item)) { ws.Cells[index + rowIndexTable[item], col].PutValue((semsDict[item] <= 0) ? "" : semsDict[item].ToString()); } } col++; } if (rowIndexTable.ContainsKey("明細")) { //資料一律由第二列進行列印 int detailColIndex = 2; // int detailIndex = index + rowIndexTable["明細"]; //明細定位點 int detailCount = 0; //取得學生明細清單 foreach (string var in detailList[each.StudentID]) { //明細定位點+1 detailCount++; //定位點大於明細畫面範圍時 //將換為第二區進行列印 if (detailCount > detailRow) { detailColIndex += (cur / 2); detailIndex = index + rowIndexTable["明細"]; detailCount = 0; } ws.Cells[detailIndex++, detailColIndex].PutValue(var); } } //? index += totalRow; sheetIndex[cur] = index; //增加列印分割線 ws.HPageBreaks.Add(index, 0); //當學生大於極限值 , 並且列印完所有學生 if (studentCount >= limit && studentCount < allStudentCount) { //取得工作表名稱 //string orig_name = ws.Name; // //ws.Name = ws.Name + " (" + start + " ~ " + studentCount + ")"; ws = wb.Worksheets[wb.Worksheets.Add()]; ws.Copy(pts[cur].Worksheets[0]); //ws.Name = orig_name; start += pages; limit += pages; index = 0; sheetIndex[cur] = index; sheets[cur] = ws; } //回報進度 _BGWTotalDisciplineAndAbsence.ReportProgress((int)(((double)studentCount * 100.0) / (double)allStudentCount)); } //if (cur == 6) //ws.Name = ws.Name + " (" + start + " ~ " + studentCount + ")"; #endregion string path = Path.Combine(Application.StartupPath, "Reports"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = Path.Combine(path, reportName + ".xlt"); e.Result = new object[] { reportName, path, wb }; }
void BGW_DoWork(object sender, DoWorkEventArgs e) { //電子報表 string time = DateTime.Now.Hour.ToString().PadLeft(2) + DateTime.Now.Minute.ToString().PadLeft(2); paperForClass = new SmartSchool.ePaper.ElectronicPaper(string.Format("日常表現記錄表(新制_{0})", time), _Schoolyear.ToString(), _Semester.ToString(), SmartSchool.ePaper.ViewerType.Class); BGW.ReportProgress(1, "取得紙張設定"); //取得列印紙張 int sizeIndex = GetSizeIndex(); BGW.ReportProgress(4, "取得假別設定"); //取得列印假別內容 Dictionary <string, List <string> > UserType = GetUserType(); #region 取得資料 //取得使用者選擇班級 BGW.ReportProgress(8, "取得所選班級"); List <ClassRecord> allClasses = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource); //排序(因為上面沒有照班級排序) int displayOrder; allClasses = allClasses.OrderBy(i => i.GradeYear).ThenBy(i => displayOrder = Int32.TryParse(i.DisplayOrder, out displayOrder)? displayOrder :0).ThenBy(i => i.Name).ToList(); #region 取得使用者所選擇的班級學生 BGW.ReportProgress(12, "取得學生清單"); string classidlist = string.Join(",", K12.Presentation.NLDPanels.Class.SelectedSource); StringBuilder sb = new StringBuilder(); sb.Append("select student.id,student.ref_class_id from student "); sb.Append("join class on class.id=student.ref_class_id "); sb.Append("where student.status=1 "); sb.Append(string.Format("and class.id in ({0})", classidlist)); List <string> StudentIDList = new List <string>(); DataTable dt = _QueryHelper.Select(sb.ToString()); foreach (DataRow row in dt.Rows) { StudentIDList.Add("" + row[0]); } BGW.ReportProgress(15, "取得學生清單"); List <StudentRecord> allStudents = Student.SelectByIDs(StudentIDList); int maxStudents = 0; int totalStudent = allStudents.Count; Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >(); foreach (StudentRecord each in allStudents) { if (!classStudents.ContainsKey(each.RefClassID)) { classStudents.Add(each.RefClassID, new List <StudentRecord>()); } classStudents[each.RefClassID].Add(each); } foreach (string each in classStudents.Keys) { if (classStudents[each].Count > maxStudents) { maxStudents = classStudents[each].Count; } classStudents[each].Sort(SortStudent); } #endregion Dictionary <string, RewardRecord> MeritDemeritAttDic = new Dictionary <string, RewardRecord>(); Dictionary <string, RewardRecord> TotalMeritDemeritDic = new Dictionary <string, RewardRecord>(); BGW.ReportProgress(20, "取得獎勵資料"); #region 獎勵 foreach (SHMeritRecord each in SHMerit.SelectByStudentIDs(StudentIDList)) { // 2018/1/8 羿均 新增 累計獎勵紀錄資料 RewardRecord totalMerit = new RewardRecord(); if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { totalMerit = TotalMeritDemeritDic[each.RefStudentID]; } totalMerit.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0; totalMerit.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0; totalMerit.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0; if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { TotalMeritDemeritDic.Add(each.RefStudentID, totalMerit); } if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } rr.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0; rr.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0; rr.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0; if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion BGW.ReportProgress(25, "取得懲戒資料"); #region 懲戒 foreach (SHDemeritRecord each in SHDemerit.SelectByStudentIDs(StudentIDList)) { // 2018/1/8 羿均 新增 累計懲戒紀錄資料 RewardRecord totalDemerit = new RewardRecord(); if (each.Cleared == "是") { continue; } if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { totalDemerit = TotalMeritDemeritDic[each.RefStudentID]; } totalDemerit.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; totalDemerit.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; totalDemerit.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { TotalMeritDemeritDic.Add(each.RefStudentID, totalDemerit); } if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } rr.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; rr.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; rr.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion BGW.ReportProgress(30, "取得節次對照"); //取得節次對照表 Dictionary <string, string> periodDic = new Dictionary <string, string>(); foreach (PeriodMappingInfo var in PeriodMapping.SelectAll()) { string name = var.Type; //取得對照表並且對照出節次->類別的清單(99/11/24 by dylan) if (!periodDic.ContainsKey(var.Name)) { periodDic.Add(var.Name, var.Type); } } BGW.ReportProgress(35, "取得缺曠資料"); #region 缺曠 foreach (SHAttendanceRecord each in SHAttendance.SelectByStudentIDs(StudentIDList)) { if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } foreach (AttendancePeriod _Period in each.PeriodDetail) { if (periodDic.ContainsKey(_Period.Period)) { string typename = periodDic[_Period.Period] + "_" + _Period.AbsenceType; if (rr.Attendance.ContainsKey(typename)) { rr.Attendance[typename]++; } else { rr.Attendance.Add(typename, 1); } } } if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion //日常表現資料 BGW.ReportProgress(40, "取得日常表現"); Dictionary <string, SHMoralScoreRecord> SHMoralScoreDic = new Dictionary <string, SHMoralScoreRecord>(); foreach (SHMoralScoreRecord each in SHMoralScore.Select(null, StudentIDList, _Schoolyear, _Semester)) { if (!SHMoralScoreDic.ContainsKey(each.RefStudentID)) { SHMoralScoreDic.Add(each.RefStudentID, each); } } //文字評量對照表 BGW.ReportProgress(45, "取得文字評量"); List <string> TextScoreList = new List <string>(); SmartSchool.Customization.Data.SystemInformation.getField("文字評量對照表"); System.Xml.XmlElement ElmTextScoreList = (System.Xml.XmlElement)SmartSchool.Customization.Data.SystemInformation.Fields["文字評量對照表"]; foreach (System.Xml.XmlNode Node in ElmTextScoreList.SelectNodes("Content/Morality")) { TextScoreList.Add(Node.Attributes["Face"].InnerText); } #endregion #region 產生表格 BGW.ReportProgress(50, "產生報表樣式"); Workbook template = new Workbook(); Workbook prototype = new Workbook(); //列印尺寸 if (sizeIndex == 0) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A3), FileFormatType.Excel2003); } else if (sizeIndex == 1) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A4), FileFormatType.Excel2003); } else if (sizeIndex == 2) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制B4), FileFormatType.Excel2003); } prototype.Copy(template); Worksheet templateSheet = template.Worksheets[0]; Worksheet prototypeSheet = prototype.Worksheets[0]; Range tempAbsence = templateSheet.Cells.CreateRange(9, 1, true); Range tempScoreText = templateSheet.Cells.CreateRange(10, 1, true); Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(11, 1, true); Range oder = templateSheet.Cells.CreateRange(12, 1, true); Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >(); //紀錄獎懲的 Column Index columnIndexTable.Add("大功", 3); columnIndexTable.Add("小功", 4); columnIndexTable.Add("嘉獎", 5); columnIndexTable.Add("大過", 6); columnIndexTable.Add("小過", 7); columnIndexTable.Add("警告", 8); //缺曠 int ptColIndex = 9; foreach (string var in UserType.Keys) { foreach (string absence in UserType[var]) { if (!periodAbsence.ContainsKey(var)) { periodAbsence.Add(var, new List <string>()); } if (!periodAbsence[var].Contains(absence)) { periodAbsence[var].Add(absence); } prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAbsence); ptColIndex += 1; } } ptColIndex = 9; foreach (string period in periodAbsence.Keys) { prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count).Merge(); prototypeSheet.Cells[2, ptColIndex].PutValue(period); foreach (string absence in periodAbsence[period]) { prototypeSheet.Cells[3, ptColIndex].PutValue(absence); columnIndexTable.Add(period + "_" + absence, ptColIndex); ptColIndex++; } } if (ptColIndex > 9) { prototypeSheet.Cells.CreateRange(1, 9, 1, ptColIndex - 9).Merge(); prototypeSheet.Cells[1, 9].PutValue("缺曠"); } //用來調整Column寬度的定位 int ColumnMax = ptColIndex; //文字評量 foreach (string textscore in TextScoreList) { columnIndexTable.Add(textscore, ptColIndex); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempScoreText); prototypeSheet.Cells[4, ptColIndex].PutValue(textscore); ptColIndex++; } prototypeSheet.Cells[1, ptColIndex - TextScoreList.Count].PutValue("學生綜合表現"); if ((ptColIndex - TextScoreList.Count > 0) && (TextScoreList.Count > 0)) { prototypeSheet.Cells.CreateRange(1, ptColIndex - TextScoreList.Count, 3, TextScoreList.Count).Merge(); } prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAfterOtherDiff); columnIndexTable.Add("評語", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(oder); columnIndexTable.Add("是否留察", ptColIndex++); //填入製表日期 prototypeSheet.Cells[0, 0].PutValue("製表日期:" + DateTime.Today.ToShortDateString()); //填入標題 prototypeSheet.Cells.CreateRange(0, 3, 1, ptColIndex - 3).Merge(); prototypeSheet.Cells[0, 3].PutValue(K12.Data.School.ChineseName + " " + _Schoolyear + " 學年度 " + ((_Semester == 1) ? "上" : "下") + " 學期 日常表現記錄表(新制)"); Range ptEachRow = prototypeSheet.Cells.CreateRange(5, 1, false); for (int i = 5; i < maxStudents + 5; i++) { prototypeSheet.Cells.CreateRange(i, 1, false).Copy(ptEachRow); } //加上底線 prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black); for (int i = 12; i >= ptColIndex; i--) { prototypeSheet.Cells.DeleteColumn(i); } Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false); #endregion #region 填入表格 BGW.ReportProgress(53, "填入報表資料"); Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int classTotalRow = maxStudents + 5; BGW.ReportProgress(57, "填入老師姓名"); #region 取得全校班級,與老師姓名/暱稱(2012/5/24) Dictionary <string, string> TeacherDic = new Dictionary <string, string>(); string st = "SELECT class.id,teacher.teacher_name,teacher.nickname FROM class JOIN teacher ON class.ref_teacher_id = teacher.id"; dt = _QueryHelper.Select(st); foreach (DataRow row in dt.Rows) { string classID = "" + row[0]; string teacherName = "" + row[1]; string teacherNickname = "" + row[2]; if (!TeacherDic.ContainsKey(classID)) { if (string.IsNullOrEmpty(teacherNickname)) { TeacherDic.Add(classID, teacherName); } else { TeacherDic.Add(classID, teacherName + "(" + teacherNickname + ")"); } } } #endregion BGW.ReportProgress(61, "學生留察資料"); #region 取得全校本學年度留查之記錄(2012/5/24) List <string> MeritFlagIs2 = new List <string>(); st = string.Format("SELECT ref_student_id from discipline where merit_flag=2 and school_year={0} and semester={1}", _Schoolyear, _Semester); dt = _QueryHelper.Select(st); foreach (DataRow row in dt.Rows) { if (!MeritFlagIs2.Contains("" + row[0])) { MeritFlagIs2.Add("" + row[0]); } } #endregion BGW.ReportProgress(70, "開始列印資料"); int PeogressNow1 = totalStudent / 30; int PeogressNow2 = 0; int PeogressNow3 = 70; foreach (ClassRecord aClass in allClasses) { //電子報表用 #region 電子報表用 Workbook Paper_wb = new Workbook(); Paper_wb.Copy(prototype); Worksheet Paper_ws = Paper_wb.Worksheets[0]; int Paper_index = 0; int Paper_dataIndex = 0; int Paper_classTotalRow = maxStudents + 5; int Paper_PeogressNow1 = totalStudent / 30; #endregion if (!classStudents.ContainsKey(aClass.ID)) { continue; } string TeacherName = ""; if (TeacherDic.ContainsKey(aClass.ID)) { TeacherName = TeacherDic[aClass.ID]; } //複製完成後的樣板 ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt); Paper_ws.Cells.CreateRange(Paper_index, Paper_classTotalRow, false).Copy(pt); //填入班級名稱 ws.Cells[index + 1, 0].PutValue("班級:" + aClass.Name); Paper_ws.Cells[Paper_index + 1, 0].PutValue("班級:" + aClass.Name); //填入老師名稱 ws.Cells[index + 3, 0].PutValue("教師:" + TeacherName); Paper_ws.Cells[Paper_index + 3, 0].PutValue("教師:" + TeacherName); dataIndex = index + 5; Paper_dataIndex = Paper_index + 5; foreach (StudentRecord aStudent in classStudents[aClass.ID]) { PeogressNow2++; if (PeogressNow2 > PeogressNow1 && PeogressNow3 < 101) { PeogressNow3++; PeogressNow2 = 0; BGW.ReportProgress(PeogressNow3, "開始列印資料"); } ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo); ws.Cells[dataIndex, 1].PutValue(aStudent.Name); ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber); Paper_ws.Cells[Paper_dataIndex, 0].PutValue(aStudent.SeatNo); Paper_ws.Cells[Paper_dataIndex, 1].PutValue(aStudent.Name); Paper_ws.Cells[Paper_dataIndex, 2].PutValue(aStudent.StudentNumber); // 2018/01/17 羿均 // MeritDemeritAttDic 為當學年度學期 缺曠獎懲資料 // TotalMeritDemeritDic 為獎懲累計資料 if (MeritDemeritAttDic.ContainsKey(aStudent.ID) || TotalMeritDemeritDic.ContainsKey(aStudent.ID)) { RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(aStudent.ID)) { rr = MeritDemeritAttDic[aStudent.ID]; } if (TotalMeritDemeritDic.ContainsKey(aStudent.ID)) { RewardRecord totalRecord = TotalMeritDemeritDic[aStudent.ID]; ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount); } else { //有缺曠沒獎懲,要印獎懲0/0 //https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594 ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); } foreach (string each in rr.Attendance.Keys) { if (columnIndexTable.ContainsKey(each)) { ws.Cells[dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]); Paper_ws.Cells[Paper_dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]); } } } else { //沒缺曠沒獎懲,要印獎懲0/0 // https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594 ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); } //文字評量部份 SHMoralScoreRecord demonScore; if (SHMoralScoreDic.ContainsKey(aStudent.ID)) { demonScore = SHMoralScoreDic[aStudent.ID]; //文字評量 XmlElement xml = demonScore.TextScore; foreach (XmlElement each in xml.SelectNodes("Morality")) { string strFace = each.GetAttribute("Face"); if (columnIndexTable.ContainsKey(strFace)) { int colIndex = columnIndexTable[strFace]; ws.Cells[dataIndex, colIndex].PutValue(each.InnerText); Paper_ws.Cells[Paper_dataIndex, colIndex].PutValue(each.InnerText); } } //導師評語 ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment); } //留察 if (MeritFlagIs2.Contains(aStudent.ID)) { ws.Cells[dataIndex, columnIndexTable["是否留察"]].PutValue("是"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["是否留察"]].PutValue("是"); } foreach (int each in columnIndexTable.Values) { if (each >= ColumnMax) { Paper_ws.AutoFitColumn(each); } } dataIndex++; Paper_dataIndex++; } //電子報表 MemoryStream stream = Paper_wb.SaveToStream(); paperForClass.Append(new PaperItem(PaperFormat.Office2003Xls, stream, aClass.ID)); index += classTotalRow + 2; ws.HPageBreaks.Add(index, ptColIndex); } foreach (int each in columnIndexTable.Values) { if (each >= ColumnMax) { ws.AutoFitColumn(each); } } #endregion if (Carty_paper) { BGW.ReportProgress(90, "上傳電子報表"); SmartSchool.ePaper.DispatcherProvider.Dispatch(paperForClass); } BGW.ReportProgress(100, "資料列印完成"); e.Result = wb; }
private Workbook GetTemplate(int sems_num) { Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.歷年功過及出席統計), FileFormatType.Excel2003); Worksheet tempWorksheet = template.Worksheets["" + sems_num]; Range tempHeader = tempWorksheet.Cells.CreateRange(0, 2, false); Range tempRow = tempWorksheet.Cells.CreateRange(2, 1, false); Range tempStatistics = tempWorksheet.Cells.CreateRange(3, 1, false); Range tempDetail = tempWorksheet.Cells.CreateRange(5, 1, false); Workbook prototype = new Workbook(); prototype.Copy(template); int count = prototype.Worksheets.Count; for (int i = count - 1; i > 0; i--) { prototype.Worksheets.RemoveAt(i); } prototype.Worksheets[0].Copy(tempWorksheet); prototype.Worksheets[0].Cells.CreateRange(0, 2, false).Copy(tempHeader); int index = 2; rowIndexTable.Clear(); //標題 prototype.Worksheets[0].Cells[0, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolChineseName + "學生個人歷年功過及出席統計表"); //學期 prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempRow); prototype.Worksheets[0].Cells[index, 0].PutValue("學年度學期"); index++; //獎懲部分 foreach (string var in new string[] { "大功", "小功", "嘉獎" }) { prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics); prototype.Worksheets[0].Cells[index, 1].PutValue(var); rowIndexTable.Add(var, index); index++; } foreach (string var in new string[] { "大過", "小過", "警告", "留校察看" }) { prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics); prototype.Worksheets[0].Cells[index, 1].PutValue(var); rowIndexTable.Add(var, index); index++; } prototype.Worksheets[0].Cells.CreateRange(index - 7, 0, 3, 1).Merge(); prototype.Worksheets[0].Cells[index - 7, 0].PutValue("獎勵"); prototype.Worksheets[0].Cells.CreateRange(index - 4, 0, 4, 1).Merge(); prototype.Worksheets[0].Cells[index - 4, 0].PutValue("懲戒"); //缺曠部分 foreach (string period in _print_types.Keys) { foreach (string absence in _print_types[period]) { prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempStatistics); prototype.Worksheets[0].Cells[index, 1].PutValue(absence); rowIndexTable.Add(period + "_" + absence, index); index++; } prototype.Worksheets[0].Cells.CreateRange(index - _print_types[period].Count, 0, _print_types[period].Count, 1).Merge(); prototype.Worksheets[0].Cells[index - _print_types[period].Count, 0].PutValue(period); } //德行成績部分 prototype.Worksheets[0].Cells.CreateRange(index, 1, false).Copy(tempRow); prototype.Worksheets[0].Cells[index, 0].PutValue("德行成績"); rowIndexTable.Add("德行成績", index); index++; //獎懲明細部分 ////檢查每個學生獎懲明細是否會超過表格大小 //foreach (string var in disciplineDetail.Keys) //{ // if (disciplineDetail[var].Count > (totalRows - index) * 2) // { // totalRows += (disciplineDetail[var].Count - (totalRows - index) * 2) / 2; // totalRows += (disciplineDetail[var].Count - (totalRows - index) * 2) % 2; // } //} rowIndexTable.Add("明細", index); //if ((index + detailRow) > totalRow) //{ // detailRow = totalRow - index; //} for (int j = index; j < index + detailRow; j++) { prototype.Worksheets[0].Cells.CreateRange(j, 1, false).Copy(tempDetail); } prototype.Worksheets[0].Cells.CreateRange(index, 0, detailRow, 1).Merge(); prototype.Worksheets[0].Cells[index, 0].PutValue("獎懲明細"); //加上報表底線 prototype.Worksheets[0].Cells.CreateRange(index + detailRow - 1, 0, 1, sems_num + 2).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black); return(prototype); }
private void exportBtn_Click(object sender, EventArgs e) { // 建立範本 Workbook template = new Workbook(new MemoryStream(Properties.Resources.班級課堂點名明細樣板)); // 複製範本 Workbook book = new Workbook(); book.Copy(template); Worksheet sheet = book.Worksheets[0]; sheet.Name = classLb.Text; // 填入資料 int row = 1; Style style = sheet.Cells.GetCellStyle(0, 0); foreach (DataGridViewRow datarow in dataGridViewX1.Rows) { int col = 0; sheet.Cells[row, col].PutValue(datarow.Cells["teacher"].Value); sheet.Cells[row, col++].SetStyle(style); //sheet.Cells[row, col].PutValue(datarow.Cells["course"].Value); //sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["className"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["seatNo"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["stuNumber"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["name"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["rollCallLog"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["attendance"].Value); sheet.Cells[row, col++].SetStyle(style); sheet.Cells[row, col].PutValue(datarow.Cells["rollCallTime"].Value); sheet.Cells[row, col++].SetStyle(style); row++; } string date = DateTime.Parse(dateTimeLb.Text).ToString("yyyy_MM_dd"); char[] ca = periodLb.Text.ToCharArray(); string period = ""; int i = 0; foreach (char c in ca) { if (c == '/') { ca[i] = '_'; } period += ca[i]; i++; } // 存檔 SaveFileDialog SaveFileDialog = new SaveFileDialog(); SaveFileDialog.Filter = "Excel (*.xlsx)|*.xlsx|所有檔案 (*.*)|*.*"; SaveFileDialog.FileName = string.Format("{0}_{1}_{2}_課堂點名", date, classLb.Text, period); try { if (SaveFileDialog.ShowDialog() == DialogResult.OK) { book.Save(SaveFileDialog.FileName); Process.Start(SaveFileDialog.FileName); MotherForm.SetStatusBarMessage("課堂點名明細,列印完成!!"); } else { FISCA.Presentation.Controls.MsgBox.Show("檔案未儲存"); return; } } catch { FISCA.Presentation.Controls.MsgBox.Show("檔案儲存錯誤,請檢查檔案是否開啟中!!"); MotherForm.SetStatusBarMessage("檔案儲存錯誤,請檢查檔案是否開啟中!!"); } }
private Workbook FillWorkBookData() { Workbook wb = new Workbook(); wb.Copy(wbTemplate); string schoolName = School.ChineseName; string schoolYear = School.DefaultSchoolYear; int sheetIndex = 0; foreach (string classID in listClassID) { if (sheetIndex > 0) { wb.Worksheets.Add(); } int rowIndex = 0; ClassRecord classRec = Class.SelectByID(classID); Worksheet sheet = wb.Worksheets[sheetIndex]; sheet.Copy(wbTemplate.Worksheets[0]); sheet.Name = classRec.Name; sheet.Cells[rowIndex++, 0].PutValue($"{schoolName} {schoolYear}學年度 {classRec.Name} 修讀學分統計表"); rowIndex = 4; Range range = sheet.Cells.CreateRange(1, 1, 1, 30); Style style = sheet.Cells.GetCellStyle(4, 0); StyleFlag styleFlag = new StyleFlag() { All = true }; // 有班級學生資料 if (dicCreditRecByClassStuKey.ContainsKey(classID)) { foreach (string stuID in dicCreditRecByClassStuKey[classID].Keys) { // 沒有任何核心必修科目 bool nototalCoreCredit = true; if (studentScoreRuleDict.ContainsKey(stuID)) { if (ruleCoreSubjectNameDict.ContainsKey(studentScoreRuleDict[stuID])) { if (ruleCoreSubjectNameDict[studentScoreRuleDict[stuID]].Count > 0) { nototalCoreCredit = false; } } } int colIndex = 0; //修讀合計 float totalCredit = 0; // 累計實得 float totalHaveCredit = 0; float totalCoreCredit = 0; float totalHaveToCredit = 0; float totalSelectedCredit = 0; Dictionary <string, CreditRec> dicCreditRecByKey = dicCreditRecByClassStuKey[classID][stuID]; // 座號 姓名 if (dicStudentRecByID.ContainsKey(stuID)) { StudentRec stuRec = dicStudentRecByID[stuID]; sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.SeatNo); sheet.Cells[rowIndex, colIndex++].PutValue(stuRec.Name); } // 成績年級學期學分數 for (int gradeYear = 1; gradeYear <= 3; gradeYear++) { for (int semester = 1; semester <= 2; semester++) { string key = $"{gradeYear}_{semester}"; if (dicCreditRecByKey.ContainsKey(key)) { CreditRec creditRec = dicCreditRecByKey[key]; sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.TotalCredit); if (nototalCoreCredit) { sheet.Cells[rowIndex, colIndex++].PutValue(""); } else { sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.CoredCredit); } sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.HaveToCredit); sheet.Cells[rowIndex, colIndex++].PutValue(creditRec.SelectedCredit); totalCredit += creditRec.TotalCredit; totalCoreCredit += creditRec.CoredCredit; totalHaveToCredit += creditRec.HaveToCredit; totalSelectedCredit += creditRec.SelectedCredit; totalHaveCredit += (creditRec.HaveToCredit + creditRec.SelectedCredit); } else { colIndex += 4; } } } // 累計實得學分 //修讀合計 sheet.Cells[rowIndex, colIndex++].PutValue(totalCredit); // 核心必修 if (nototalCoreCredit) { sheet.Cells[rowIndex, colIndex++].PutValue(""); } else { sheet.Cells[rowIndex, colIndex++].PutValue(totalCoreCredit); } // 必修 sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveToCredit); // 選修 sheet.Cells[rowIndex, colIndex++].PutValue(totalSelectedCredit); // 合計 sheet.Cells[rowIndex, colIndex++].PutValue(totalHaveCredit); Range currentRow = sheet.Cells.CreateRange(rowIndex, 0, 1, 30); currentRow.ApplyStyle(style, styleFlag); rowIndex++; } } // 沒有班級學生資料 else { sheet.Cells.Merge(4, 0, 3, 30); sheet.Cells[4, 0].PutValue("無資料"); } sheetIndex++; } return(wb); }
private void wizardPage3_AfterPageDisplayed(object sender, WizardPageChangeEventArgs e) { this.progressBarX1.Value = 0; // 讀取畫面上選取教師狀態 switch (cboStudStatus.Text) { case "一般": _StudStatus = K12.Data.TeacherRecord.TeacherStatus.一般; break; //case "休學": // _StudStatus = K12.Data.StudentRecord.StudentStatus.休學; // break; //case "輟學": // _StudStatus = K12.Data.StudentRecord.StudentStatus.輟學; // break; //case "畢業或離校": // _StudStatus = K12.Data.StudentRecord.StudentStatus.畢業或離校; // break; case "刪除": _StudStatus = K12.Data.TeacherRecord.TeacherStatus.刪除; break; } lblWarningCount.Text = lblErrCount.Text = "0"; this.wizardPage3.FinishButtonEnabled = eWizardButtonState.False; linkLabel1.Visible = false; labelX2.Text = "資料驗證中"; linkLabel3.Tag = null; linkLabel3.Visible = false; Application.DoEvents(); _BKWValidate = new BackgroundWorker(); _BKWValidate.WorkerReportsProgress = true; _BKWValidate.WorkerSupportsCancellation = true; _BKWValidate.DoWork += new DoWorkEventHandler(_BKWValidate_DoWork); _BKWValidate.ProgressChanged += new ProgressChangedEventHandler(_BKWValidate_ProgressChanged); _BKWValidate.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BKWValidate_RunWorkerCompleted); List<string> fields = new List<string>(); string fileName = txtFile.Text; fields.AddRange(_RequiredFields); foreach (ListViewItem item in listView1.Items) { if (item.Checked) fields.Add(item.Text); } _ErrorRows = new Dictionary<RowData, Dictionary<string, string>>(); _WarningRows = new Dictionary<RowData, Dictionary<string, string>>(); Workbook wb = new Workbook(); wb.Copy(_WorkBook); _BKWValidate.RunWorkerAsync(new object[] { fields, _ImportFields, wb }); }
private void _BWRepeatScoreImport_DoWork(object sender, DoWorkEventArgs e) { object[] obj = (object[])e.Argument; int schoolyear = (int)obj[0]; int semester = (int)obj[1]; bool printAll = (bool)obj[2]; _BWRepeatScoreImport.ReportProgress(0); #region 取得所有學生以及補考資訊 AccessHelper helper = new AccessHelper(); List <SmartSchool.Customization.Data.StudentRecord> allStudents = new List <SmartSchool.Customization.Data.StudentRecord>(); List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass(); double currentClass = 1; double totalClasses = allClasses.Count; foreach (ClassRecord aClass in allClasses) { List <SmartSchool.Customization.Data.StudentRecord> classStudents = aClass.Students; helper.StudentHelper.FillSemesterSubjectScore(true, classStudents); helper.StudentHelper.FillField("及格標準", classStudents); allStudents.AddRange(classStudents); _BWRepeatScoreImport.ReportProgress((int)(currentClass++ *90.0 / totalClasses)); } double currentStudent = 1; double totalStudents = allStudents.Count; #endregion #region 產生表格並填入資料 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.重修成績匯入), FileFormatType.Excel2003); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); int headerIndex = 0; string headerString = template.Worksheets[0].Cells[0, headerIndex].StringValue; while (!string.IsNullOrEmpty(headerString)) { columnIndexTable.Add(headerString, headerIndex); headerString = template.Worksheets[0].Cells[0, ++headerIndex].StringValue; } foreach (SmartSchool.Customization.Data.StudentRecord aStudent in allStudents) { foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if ((info.SchoolYear == schoolyear && info.Semester == semester) || printAll) { if (info.Pass && machine.IsExistInList(aStudent.StudentNumber, aStudent.StudentName, info.Subject, info.Level)) { machine.RemoveSubject(aStudent.StudentName, info.Subject, info.Level); } else if (!info.Pass) { machine.AddSubject(aStudent, info); } } } _BWRepeatScoreImport.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents)); } machine.Sort(); int index = 1; foreach (Dictionary <string, string> info in machine.GetSubjects()) { foreach (string key in info.Keys) { ws.Cells[index, columnIndexTable[key]].PutValue(info[key]); } index++; } #endregion e.Result = wb; }
void _BGWResitList_DoWork(object sender, DoWorkEventArgs e) { object[] objectValue = (object[])e.Argument; int schoolyear = (int)objectValue[0]; int semester = (int)objectValue[1]; _BGWResitList.ReportProgress(0); #region 取得所有學生以及補考資訊 AccessHelper helper = new AccessHelper(); List <StudentRecord> allStudents = new List <StudentRecord>(); List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass(); WearyDogComputer computer = new WearyDogComputer(); double currentClass = 1; double totalClasses = allClasses.Count; foreach (ClassRecord aClass in allClasses) { List <StudentRecord> classStudents = aClass.Students; computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents); allStudents.AddRange(classStudents); _BGWResitList.ReportProgress((int)(currentClass++ *90.0 / totalClasses)); } double currentStudent = 1; double totalStudents = allStudents.Count; #endregion #region 產生表格並填入資料 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.補考名單_依科目), FileFormatType.Excel2003); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; ws.Name = "未達補考標準名單"; Range eachSubject = template.Worksheets[0].Cells.CreateRange(0, 4, false); Range eachRow = template.Worksheets[0].Cells.CreateRange(4, 1, false); int index = 0; Dictionary <string, Dictionary <string, string> > subjectInfo = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, List <Dictionary <string, string> > > subjectStudentList = new Dictionary <string, List <Dictionary <string, string> > >(); foreach (StudentRecord aStudent in allStudents) { string className = aStudent.RefClass.ClassName; string seatNo = aStudent.SeatNo; string studentName = aStudent.StudentName; string studentNumber = aStudent.StudentNumber; //aStudent.SemesterSubjectScoreList.Sort(SortBySemesterSubjectScore); foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester && !info.Pass) { if (info.Detail.GetAttribute("達補考標準") == "否") { string sl = info.Subject + "_" + info.Level + "_" + info.CreditDec(); if (!subjectInfo.ContainsKey(sl)) { subjectInfo.Add(sl, new Dictionary <string, string>()); subjectInfo[sl].Add("科目", info.Subject); subjectInfo[sl].Add("級別", info.Level); subjectInfo[sl].Add("學分", info.CreditDec().ToString()); } if (!subjectStudentList.ContainsKey(sl)) { subjectStudentList.Add(sl, new List <Dictionary <string, string> >()); } Dictionary <string, string> data = new Dictionary <string, string>(); data.Add("班級", className); data.Add("座號", seatNo); data.Add("姓名", studentName); data.Add("學號", studentNumber); data.Add("必選修", info.Require ? "必修" : "選修"); data.Add("校部訂", info.Detail.HasAttribute("修課校部訂") ? info.Detail.GetAttribute("修課校部訂") : ""); data.Add("補考標準", info.Detail.HasAttribute("補考標準") ? info.Detail.GetAttribute("補考標準") : ""); data.Add("原始成績", info.Detail.HasAttribute("原始成績") ? info.Detail.GetAttribute("原始成績") : ""); subjectStudentList[sl].Add(data); } } } _BGWResitList.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents)); } List <string> sortedList = new List <string>(); sortedList.AddRange(subjectStudentList.Keys); sortedList.Sort(SortBySemesterSubjectScore); foreach (string key in sortedList) { int level; string levelString = ""; if (!string.IsNullOrEmpty(subjectInfo[key]["級別"]) && int.TryParse(subjectInfo[key]["級別"], out level)) { levelString = GetNumber(level); } string sl = subjectInfo[key]["科目"] + levelString; ws.Cells.CreateRange(index, 4, false).Copy(eachSubject); ws.Cells[index, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 學生未達補考標準名單"); ws.Cells[index + 1, 2].PutValue(sl); ws.Cells[index + 1, 7].PutValue(subjectInfo[key]["學分"]); index += 4; foreach (Dictionary <string, string> dict in subjectStudentList[key]) { ws.Cells.CreateRange(index, 1, false).Copy(eachRow); ws.Cells[index, 0].PutValue(dict["班級"]); ws.Cells[index, 1].PutValue(dict["座號"]); ws.Cells[index, 2].PutValue(dict["姓名"]); ws.Cells[index, 3].PutValue(dict["學號"]); ws.Cells[index, 4].PutValue(dict["必選修"]); ws.Cells[index, 5].PutValue(dict["校部訂"]); ws.Cells[index, 6].PutValue(dict["補考標準"]); ws.Cells[index, 7].PutValue(dict["原始成績"]); index++; } index++; } #endregion e.Result = wb; }
private Workbook FillWorkBookData(ParameterRec data) { Workbook prototype = new Workbook(); prototype.Copy(wbTemplate); Worksheet ps = prototype.Worksheets[0]; Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; Range colRange = ps.Cells.CreateRange(2, 1, 1, 1); Range rowRange = ps.Cells.CreateRange(3, 1, false); int rowIndex = 0; int colIndex = 0; int domainCount = listDomainFromData.Count; string title = cbxIsSchoolYear.Checked ? $"{data.SchoolYear}學年度 領域不及格人數統計表" : $"{data.SchoolYear}學年度 {data.Semester}學期 領域不及格人數統計表"; ws.Cells.Merge(rowIndex, 0, 1, domainCount * 2); ws.Cells[rowIndex++, 0].PutValue(title); // 各學習領域學生成績評量情形 { ws.Cells.Merge(1, 1, 1, domainCount); Range range = ws.Cells.CreateRange(1, 1, 1, domainCount); range.SetOutlineBorders(CellBorderType.Thin, Color.Black); range.PutValue("各學習領域學生成績評量情形", false, false); Cell cell = ws.Cells.GetCell(1, 1); Style style = cell.GetStyle(); style.HorizontalAlignment = TextAlignmentType.Center; cell.SetStyle(style); } // 學生成績評量不及格領域數情形 { ws.Cells.Merge(1, domainCount + 1, 1, domainCount); Range range = ws.Cells.CreateRange(1, domainCount + 1, 1, domainCount); range.SetOutlineBorders(CellBorderType.Thin, Color.Black); range.PutValue("學生成績評量不及格領域數情形", false, false); Cell cell = ws.Cells.GetCell(1, domainCount + 1); Style style = cell.GetStyle(); style.HorizontalAlignment = TextAlignmentType.Center; cell.SetStyle(style); } rowIndex++; colIndex = 1; // 領域 foreach (string domain in listDomainFromData) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); range.ColumnWidth = colRange.ColumnWidth; ws.Cells[rowIndex, colIndex++].PutValue(domain); } // 不及格數 for (int i = 1; i <= listDomainFromData.Count; i++) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); range.ColumnWidth = colRange.ColumnWidth; ws.Cells[rowIndex, colIndex++].PutValue($"{i}個學習領域不及格人數"); } rowIndex++; // 年級 foreach (string gradeYear in listGradeYear) { colIndex = 0; if (rowIndex > 3) { ws.Cells.InsertRow(rowIndex); } ws.Cells.CreateRange(rowIndex, 1, false).CopyStyle(rowRange); ws.Cells[rowIndex, colIndex++].PutValue($"{gradeYear}年級"); // 領域不及格人數 foreach (string domain in listDomainFromData) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); if (dicUnPassCountByDomainGradeYear[gradeYear].ContainsKey(domain)) { int unPassCount = dicUnPassCountByDomainGradeYear[gradeYear][domain]; ws.Cells[rowIndex, colIndex++].PutValue(unPassCount); } else { ws.Cells[rowIndex, colIndex++].PutValue(0); } } // 不及格領域人數 for (int i = 1; i <= listDomainFromData.Count; i++) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); if (dicUnPassCountByUnPassGradeYear.ContainsKey(gradeYear)) { if (dicUnPassCountByUnPassGradeYear[gradeYear].ContainsKey(i)) { int unPassCount = dicUnPassCountByUnPassGradeYear[gradeYear][i]; ws.Cells[rowIndex, colIndex++].PutValue(unPassCount); } else { ws.Cells[rowIndex, colIndex++].PutValue(0); } } else { ws.Cells[rowIndex, colIndex++].PutValue(0); } } rowIndex++; } colIndex = 1; // 總計 領域 foreach (string domain in listDomainFromData) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByDomain[domain]); } // 總計 不及格領域數 foreach (int i in dicTotalCountByUnPass.Keys) { Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1); range.CopyStyle(colRange); ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByUnPass[i]); } rowIndex++; int year = DateTime.Now.Year - 1911; int month = DateTime.Now.Month; int day = DateTime.Now.Day; ws.Cells[rowIndex, 0].PutValue($"列印日期: {year}年{month}月{day}日"); return(wb); }