private void BgWorkerExport_DoWork(object sender, DoWorkEventArgs e) { bgWorkerExport.ReportProgress(1); try { // 取得預設樣板 Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template)); Worksheet wst = wb.Worksheets[0]; // 取得學生基本資料 List <StudentInfo> StudentInfoList = QueryData.GetStudentInfoList3(); List <string> StudentIDList = new List <string>(); foreach (StudentInfo si in StudentInfoList) { StudentIDList.Add(si.StudentID); } // 取得地址資訊 Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>(); List <JHAddressRecord> tmpAddress = JHAddress.SelectByStudentIDs(StudentIDList); foreach (JHAddressRecord rec in tmpAddress) { if (!AddressDict.ContainsKey(rec.RefStudentID)) { AddressDict.Add(rec.RefStudentID, rec); } } // 取得電話資料 Dictionary <string, JHPhoneRecord> PhoneDict = new Dictionary <string, JHPhoneRecord>(); List <JHPhoneRecord> tmpPhone = JHPhone.SelectByStudentIDs(StudentIDList); foreach (JHPhoneRecord rec in tmpPhone) { if (!PhoneDict.ContainsKey(rec.RefStudentID)) { PhoneDict.Add(rec.RefStudentID, rec); } } bgWorkerExport.ReportProgress(20); // 取得監護人父母資訊 Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>(); List <JHParentRecord> tmpParent = JHParent.SelectByStudentIDs(StudentIDList); foreach (JHParentRecord rec in tmpParent) { if (!ParentDict.ContainsKey(rec.RefStudentID)) { ParentDict.Add(rec.RefStudentID, rec); } } // 轉換各項類別對照值 Dictionary <string, string> MappingTag1 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag2 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag3 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag4 = new Dictionary <string, string>(); // 取得學生類別 Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList); // 解析對照設定 XElement elmRoot = XElement.Parse(_Configure.MappingContent); if (elmRoot != null) { foreach (XElement elm in elmRoot.Elements("Group")) { string gpName = elm.Attribute("Name").Value; if (gpName == "學生身分") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0) { MappingTag1.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "身心障礙") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0) { MappingTag2.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "學生報名身分設定") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0) { MappingTag3.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "失業勞工子女") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0) { MappingTag4.Add(tagName, elm1.Attribute("Code").Value); } } } } } // 取得語言認證學生id List <string> hasLanguageCertificateIDList = QueryData.GetLanguageCertificate(StudentIDList); bgWorkerExport.ReportProgress(40); // 取得成績相關資料 Dictionary <string, List <JHSemesterScoreRecord> > SemesterScoreRecordDict = new Dictionary <string, List <JHSemesterScoreRecord> >(); List <JHSemesterScoreRecord> tmpSemsScore = JHSemesterScore.SelectByStudentIDs(StudentIDList); foreach (JHSemesterScoreRecord rec in tmpSemsScore) { if (!SemesterScoreRecordDict.ContainsKey(rec.RefStudentID)) { SemesterScoreRecordDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>()); } SemesterScoreRecordDict[rec.RefStudentID].Add(rec); } // 取得功過紀錄 // 功過對照表 JHMeritDemeritReduceRecord DemeritReduceRecord = JHMeritDemeritReduce.Select(); // 懲 Dictionary <string, List <JHDemeritRecord> > DemeritRecordDict = new Dictionary <string, List <JHDemeritRecord> >(); List <JHDemeritRecord> tmpDemeritRecord = JHDemerit.SelectByStudentIDs(StudentIDList); foreach (JHDemeritRecord rec in tmpDemeritRecord) { if (rec.Cleared == "是") { continue; } if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!DemeritRecordDict.ContainsKey(rec.RefStudentID)) { DemeritRecordDict.Add(rec.RefStudentID, new List <JHDemeritRecord>()); } DemeritRecordDict[rec.RefStudentID].Add(rec); } } // 獎 Dictionary <string, List <JHMeritRecord> > MeritRecordDict = new Dictionary <string, List <JHMeritRecord> >(); List <JHMeritRecord> tmpMeritRecord = JHMerit.SelectByStudentIDs(StudentIDList); foreach (JHMeritRecord rec in tmpMeritRecord) { if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!MeritRecordDict.ContainsKey(rec.RefStudentID)) { MeritRecordDict.Add(rec.RefStudentID, new List <JHMeritRecord>()); } MeritRecordDict[rec.RefStudentID].Add(rec); } } // 填入幹部資料 StudentInfoList = QueryData.FillCad(StudentIDList, StudentInfoList); // 填入中低收入戶 StudentInfoList = QueryData.FillIncomeType(StudentIDList, StudentInfoList); // 填入競賽成績 StudentInfoList = QueryData.FillStudentCompetitionScore(StudentIDList, StudentInfoList, _Configure.EndDate); // 取得學生體適能資料並填入 StudentInfoList = QueryData.FillStudentFitness(StudentIDList, StudentInfoList, _Configure.EndDate); // 填入 Excel 資料 int wstRIdx = 1; bgWorkerExport.ReportProgress(70); // 幹部限制 List <string> CadreName1 = _Configure.LoadCareNames(); foreach (StudentInfo si in StudentInfoList) { // 考區代碼 0, 12/屏東考區 wst.Cells[wstRIdx, 0].PutValue(12); // 集報單位代碼 1,學校代碼 wst.Cells[wstRIdx, 1].PutValue(K12.Data.School.Code); // 序號 2 wst.Cells[wstRIdx, 2].PutValue(wstRIdx); // 學號 3 wst.Cells[wstRIdx, 3].PutValue(si.StudentNumber); // 班級 4 wst.Cells[wstRIdx, 4].PutValue(si.ClassName); // 座號 5 wst.Cells[wstRIdx, 5].PutValue(si.SeatNo); // 學生姓名 6 wst.Cells[wstRIdx, 6].PutValue(si.StudentName); // 身分證統一編號 7 wst.Cells[wstRIdx, 7].PutValue(si.IDNumber); // 性別 8 wst.Cells[wstRIdx, 8].PutValue(si.GenderCode); // 出生年(民國年) 9 wst.Cells[wstRIdx, 9].PutValue(si.BirthYear); // 出生月 10 wst.Cells[wstRIdx, 10].PutValue(si.BirthMonth); // 出生日 11 wst.Cells[wstRIdx, 11].PutValue(si.BirthDay); // 畢業學校代碼 12 wst.Cells[wstRIdx, 12].PutValue(K12.Data.School.Code); // 畢業年(民國年) 13 int gyear; if (int.TryParse(K12.Data.School.DefaultSchoolYear, out gyear)) { wst.Cells[wstRIdx, 13].PutValue(gyear + 1); } // 畢肄業 14 wst.Cells[wstRIdx, 14].PutValue(1); // 就學區 17 // 低收入戶 18 if (si.incomeType1) { wst.Cells[wstRIdx, 18].PutValue(1); } else { wst.Cells[wstRIdx, 18].PutValue(0); } // 中低收入戶 19 if (si.incomeType2) { wst.Cells[wstRIdx, 19].PutValue(1); } else { wst.Cells[wstRIdx, 19].PutValue(0); } wst.Cells[wstRIdx, 15].PutValue("0"); wst.Cells[wstRIdx, 16].PutValue("0"); wst.Cells[wstRIdx, 20].PutValue("0"); wst.Cells[wstRIdx, 28].PutValue("0"); if (StudentTagDict.ContainsKey(si.StudentID)) { foreach (string tagName in StudentTagDict[si.StudentID]) { if (MappingTag1.ContainsKey(tagName)) { // 學生身分 15 wst.Cells[wstRIdx, 15].PutValue(MappingTag1[tagName]); } if (MappingTag2.ContainsKey(tagName)) { si.isSpecial = true; // 身心障礙 16 wst.Cells[wstRIdx, 16].PutValue(MappingTag2[tagName]); } if (MappingTag3.ContainsKey(tagName)) { // 學生報名身分 28 wst.Cells[wstRIdx, 28].PutValue(MappingTag3[tagName]); } if (MappingTag4.ContainsKey(tagName)) { // 失業勞工子女 20 wst.Cells[wstRIdx, 20].PutValue(MappingTag4[tagName]); } } } // 資料授權 21 wst.Cells[wstRIdx, 21].PutValue(0); string parentName = ""; // 家長姓名 22 if (ParentDict.ContainsKey(si.StudentID)) { if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].CustodianName)) { parentName = ParentDict[si.StudentID].CustodianName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].FatherName)) { parentName = ParentDict[si.StudentID].FatherName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].MotherName)) { parentName = ParentDict[si.StudentID].MotherName; } else { } wst.Cells[wstRIdx, 22].PutValue(parentName); } // 市內電話 23 // 行動電話 24 if (PhoneDict.ContainsKey(si.StudentID)) { wst.Cells[wstRIdx, 23].PutValue(PhoneDict[si.StudentID].Contact.Replace("-", "").Replace(")", "").Replace("(", "")); wst.Cells[wstRIdx, 24].PutValue(PhoneDict[si.StudentID].Cell.Replace("-", "").Replace(")", "").Replace("(", "")); } // 郵遞區號 25 if (AddressDict.ContainsKey(si.StudentID)) { string zipCode = ""; if (AddressDict[si.StudentID].MailingZipCode != null) { zipCode = AddressDict[si.StudentID].MailingZipCode; } if (zipCode.Length >= 3) { zipCode = zipCode.Substring(0, 3); } wst.Cells[wstRIdx, 25].PutValue(zipCode); // 通訊地址 26 wst.Cells[wstRIdx, 26].PutValue(AddressDict[si.StudentID].MailingCounty + AddressDict[si.StudentID].MailingTown + AddressDict[si.StudentID].MailingDistrict + AddressDict[si.StudentID].MailingArea + AddressDict[si.StudentID].MailingDetail); } // 非中華民國身分證號 27 if (si.isTaiwanID) { wst.Cells[wstRIdx, 27].PutValue(""); } else { wst.Cells[wstRIdx, 27].PutValue("V"); } // 市內電話分機 29 // 均衡學習 30 // 計算分數 if (SemesterScoreRecordDict.ContainsKey(si.StudentID)) { si.CalcSemsScore5(SemesterScoreRecordDict[si.StudentID]); // 成績滿5學期才顯示 if (si.hasSemester5Score) { wst.Cells[wstRIdx, 30].PutValue(si.Semester5Score); } } // 服務表現 31 si.CalcCadreScore(CadreName1); wst.Cells[wstRIdx, 31].PutValue(si.ServiceScore); // 品德表現 32 if (DemeritRecordDict.ContainsKey(si.StudentID)) { if (MeritRecordDict.ContainsKey(si.StudentID)) { si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], MeritRecordDict[si.StudentID], DemeritReduceRecord); } else { si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], new List <JHMeritRecord>(), DemeritReduceRecord); } wst.Cells[wstRIdx, 32].PutValue(si.MeritDemeritScore); } else { // 沒有懲戒 wst.Cells[wstRIdx, 32].PutValue(10); } // 競賽表現 33 si.CalcCompetitionScore(); wst.Cells[wstRIdx, 33].PutValue(si.CompetitionScore); // 體適能 34 // 計算並填入 si.CalcFitnessScore(); wst.Cells[wstRIdx, 34].PutValue(si.FitnessScore); // 本土語言認證 35 if (hasLanguageCertificateIDList.Contains(si.StudentID)) { wst.Cells[wstRIdx, 35].PutValue(2); } else { wst.Cells[wstRIdx, 35].PutValue(0); } // 36~39 系統無法提供先空 // 適性發展_高中 36 // 適性發展_高職 37 // 適性發展_綜合高中 38 // 適性發展_五專 39 wstRIdx++; } bgWorkerExport.ReportProgress(100); e.Result = wb; } catch (Exception ex) { throw ex; } }