// 高雄用 private void ProcessKaoHsiung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int peoTotalCount = 0; // 總人數 int peoBoyCount = 0; // 男生人數 int peoGirlCount = 0; // 女生人數 int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } #region 建立 Excel //從 Resources 將死亡異動名冊template讀出來 Workbook template = new Workbook(); template.Open(new MemoryStream(GDResources.JDeadStudentListTemplate), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JDeadStudentListTemplate), FileFormatType.Excel2003); #endregion #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion int rowi = 0, rowj = 1, numcount = 1, j = 0; int recCount = 0; int totalRec = data.Count; rowj = 4; wb.Worksheets[0].Cells[rowi, 4].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年第 " + StudBatchUpdateRecEntity.GetContentSemester() + "學期"); wb.Worksheets[0].Cells[rowi, 8].PutValue("列印日期:" + UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString())); wb.Worksheets[0].Cells[rowi + 1, 8].PutValue("列印時間:" + DateTime.Now.ToLongTimeString()); //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; #region 填入學生資料 // 班級 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetClassName()); // 座號 wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetSeatNo()); // 學號 wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetStudentNumber()); // 姓名 wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetName()); // 身分證 wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetIDNumber()); // 出生年月日 if (!string.IsNullOrEmpty(sburce.GetBirthday())) { wb.Worksheets[0].Cells[rowj, 5].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetBirthday())); } // 性別 wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetGender()); // 異動年級 wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetGradeYear()); // 異動日期 if (!string.IsNullOrEmpty(sburce.GetUpdateDate())) { wb.Worksheets[0].Cells[rowj, 8].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetUpdateDate())); } // 學籍最後核准文號 wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetLastADNumber()); if (sburce.GetGender() == "男") { peoBoyCount++; } if (sburce.GetGender() == "女") { peoGirlCount++; } peoTotalCount++; #endregion rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } // 統計人數 rowj++; wb.Worksheets[0].Cells.CreateRange(rowj, 2, 1, 2).Merge(); wb.Worksheets[0].Cells[rowj, 2].PutValue("男:" + peoBoyCount.ToString()); wb.Worksheets[0].Cells[rowj, 4].PutValue("女:" + peoGirlCount.ToString()); wb.Worksheets[0].Cells[rowj, 8].PutValue("總計:" + peoTotalCount.ToString()); wb.Worksheets[0].Cells.CreateRange(rowj + 1, 0, 1, 10).Merge(); // wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員"); wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長"); // 顯示頁 PageSetup pg = wb.Worksheets[0].PageSetup; string tmp = "&12 " + tmpRptY + "年" + tmpRptM + "月 填報" + "共&N頁"; pg.SetHeader(2, tmp); //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
/// <summary> /// 處理新竹 /// </summary> /// <param name="source"></param> /// <param name="location"></param> private void ProcessHsinChu(XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int tmpY, tmpM, tmpD; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; tmpD = DateTime.Now.Day; string tmpRptY, tmpRptM; string strPrintDate = (tmpY - 1911).ToString() + "/"; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { strPrintDate += "0" + tmpM.ToString() + "/"; tmpRptM = "0" + tmpM.ToString(); } else { strPrintDate += tmpM.ToString() + "/"; tmpRptM = tmpM.ToString(); } if (tmpD < 10) { strPrintDate += "0" + tmpD.ToString(); } else { strPrintDate += tmpD.ToString(); } Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JUpdateStudentPermrecTemplate), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JUpdateStudentPermrecTemplate), FileFormatType.Excel2003); Worksheet ws = wb.Worksheets[0]; //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; int rowj = 5; int recCount = 0; int totalRec = data.Count; string SchoolNameTitle = StudBatchUpdateRecEntity.GetContentSchoolName() + "更 正 學 籍 名 冊"; string SchoolYearSemesterTitle = StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度 第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期"; wb.Worksheets[0].Cells[0, 0].PutValue(SchoolNameTitle); wb.Worksheets[0].Cells[1, 0].PutValue(SchoolYearSemesterTitle); #region 異動紀錄 //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; #region 填入學生資料 //將學生資料填入適當的位置內 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender()); DateTime orinialBirtday, LastUpdateDate, Birthday; if (DateTime.TryParse(sburce.GetBirthday(), out orinialBirtday)) { wb.Worksheets[0].Cells[rowj, 3].PutValue((orinialBirtday.Year - 1911).ToString()); wb.Worksheets[0].Cells[rowj, 4].PutValue(orinialBirtday.Month.ToString()); wb.Worksheets[0].Cells[rowj, 5].PutValue(orinialBirtday.Day.ToString()); } string LastUpdateInfo = ""; if (DateTime.TryParse(sburce.GetLastADDate(), out LastUpdateDate) == true) { LastUpdateInfo = (LastUpdateDate.Year - 1911) + "/" + LastUpdateDate.Month + "/" + LastUpdateDate.Day; } LastUpdateInfo += sburce.GetLastADNumber(); wb.Worksheets[0].Cells[rowj, 6].PutValue(LastUpdateInfo); if (!string.IsNullOrEmpty(sburce.GetNewBirthday())) { if (DateTime.TryParse(sburce.GetNewBirthday(), out Birthday)) { wb.Worksheets[0].Cells[rowj, 11].PutValue((Birthday.Year - 1911).ToString()); wb.Worksheets[0].Cells[rowj, 12].PutValue(Birthday.Month.ToString()); wb.Worksheets[0].Cells[rowj, 13].PutValue(Birthday.Day.ToString()); } } if (!string.IsNullOrEmpty(sburce.GetNewName())) { wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetNewName()); } if (!string.IsNullOrEmpty(sburce.GetNewGender())) { wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetNewGender()); } if (!string.IsNullOrEmpty(sburce.GetNewIDNumber())) { wb.Worksheets[0].Cells[rowj, 14].PutValue(sburce.GetNewIDNumber()); } wb.Worksheets[0].Cells[rowj, 15].PutValue(sburce.GetComment()); #endregion rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } #endregion // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(1, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //儲存 wb.Save(location, FileFormatType.Excel2003); }
// 處理新竹市樣版 private void ProcessHsinChu(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); #region 建立 Excel //從 Resources 將新生名冊template讀出來 Workbook template = new Workbook(); template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_HsinChu), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_HsinChu), FileFormatType.Excel2003); #endregion //#region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; int recCount = 0; int totalRec = data.Count; Worksheet wst = wb.Worksheets[0]; wst.Name = "新生名冊"; string SchoolInfoAndSchoolYear = StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + wst.Cells[0, 0].StringValue; wst.Cells[0, 0].PutValue(SchoolInfoAndSchoolYear); int row = 2; #region 異動紀錄 //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { wst.Cells[row, 0].PutValue(sburce.GetClassName()); wst.Cells[row, 1].PutValue(sburce.GetName()); wst.Cells[row, 2].PutValue(sburce.GetStudentNumber()); wst.Cells[row, 3].PutValue(sburce.GetIDNumber()); DateTime dt; string strDate = ""; if (DateTime.TryParse(sburce.GetBirthday(), out dt)) { strDate = "民國" + (dt.Year - 1911) + "年" + dt.Month + "月" + dt.Day + "日"; } wst.Cells[row, 4].PutValue(strDate); wst.Cells[row, 5].PutValue(sburce.GetGender()); wst.Cells[row, 6].PutValue(sburce.GetGuardian()); wst.Cells[row, 7].PutValue(sburce.GetAddress()); wst.Cells[row, 8].PutValue(sburce.GetPrimarySchoolName()); wst.Cells[row, 9].PutValue(sburce.GetComment()); row++; recCount++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } #endregion // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(1, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
// 處理新竹相關 private void ProcessHsinChu(XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JTransfer01ListTemplate_HsinChu), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JTransfer01ListTemplate_HsinChu), FileFormatType.Excel2003); Worksheet ws = wb.Worksheets[0]; //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #region 初始變數 int rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion rowj = 4; wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 學 生 異 動 名 冊"); wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期"); wb.Worksheets[0].Cells[1, 8].PutValue(tmpRptY + "年" + tmpRptM + "月填報"); //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); //wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetClassYear ());// 穎驊更新,檢查 # 6202 客服,發現年級欄位抓錯,不應該找取班級年級,而該用異動年級 wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear()); wb.Worksheets[0].Cells[rowj, 3].PutValue(StudBatchUpdateRecEntity.GetContentSemester()); DateTime dt; // 用西元轉換 if (DateTime.TryParse(sburce.GetUpdateDate(), out dt)) { wb.Worksheets[0].Cells[rowj, 4].PutValue((dt.Year - 1911) + ""); wb.Worksheets[0].Cells[rowj, 5].PutValue((dt.Month + "")); } wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetUpdateCodeType()); wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetUpdateDescription()); wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetImportExportSchool()); rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //儲存 wb.Save(location, FileFormatType.Excel2003); }
// 處理高雄市樣版 private void ProcessKaoHsiung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); #region 建立 Excel int peoTotalCount = 0; // 總人數 int peoBoyCount = 0; // 男生人數 int peoGirlCount = 0; // 女生人數 int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()); //從 Resources 將新生名冊template讀出來 Workbook template = new Workbook(); //template.Worksheets[0].PageSetup. template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate), FileFormatType.Excel2003); #endregion //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; int rowi = 0, rowj = 1; int recCount = 0; int totalRec = data.Count; rowj = 5; wb.Worksheets[0].Cells[rowi, 4].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期"); wb.Worksheets[0].Cells[rowi, 9].PutValue("列印日期:" + strPrintDate); wb.Worksheets[0].Cells[rowi + 1, 9].PutValue("列印時間:" + DateTime.Now.ToLongTimeString()); //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; //將學生資料填入適當的位置內 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetClassName()); wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetIDNumber()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender()); if (sburce.GetGender() == "男") { peoBoyCount++; } if (sburce.GetGender() == "女") { peoGirlCount++; } if (sburce.GetBirthday() != "") { wb.Worksheets[0].Cells[rowj, 5].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetBirthday())); } if (sburce.GetEnrollmentSchoolYear().Trim() != "") { wb.Worksheets[0].Cells[rowj, 6].PutValue(UpdateRecordUtil.getChineseYearStr(sburce.GetEnrollmentSchoolYear())); wb.Worksheets[0].Cells[rowj, 7].PutValue(UpdateRecordUtil.getMonthStr(sburce.GetEnrollmentSchoolYear(), false)); } wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetPrimarySchoolName()); wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetPermanentAddress()); wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetComment()); peoTotalCount++; rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 4; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(5, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } // 統計人數 rowj++; wb.Worksheets[0].Cells[rowj, 2].PutValue("男:" + peoBoyCount.ToString()); wb.Worksheets[0].Cells[rowj, 4].PutValue("女:" + peoGirlCount.ToString()); wb.Worksheets[0].Cells[rowj, 6].PutValue("總計:" + peoTotalCount.ToString()); // wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員"); wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長"); // 顯示頁 PageSetup pg = wb.Worksheets[0].PageSetup; string tmp = "&12 " + tmpRptY + "年" + tmpRptM + "月 填報" + "共&N頁"; pg.SetHeader(2, tmp); //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
// 處理台中縣樣版 private void ProcessTaiChung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); #region 建立 Excel int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()); //從 Resources 將新生名冊template讀出來 Workbook template = new Workbook(); //template.Worksheets[0].PageSetup. template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003); #endregion #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion int rowj = 1; int recCount = 0; int totalRec = data.Count; wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 國民中學入學學生名冊"); wb.Worksheets[0].Cells[1, 10].PutValue(tmpRptY + "年" + tmpM + "月填製"); rowj = 4; wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期 1年級"); //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; //將學生資料填入適當的位置內 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender()); wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetIDNumber()); DateTime dt; if (DateTime.TryParse(sburce.GetBirthday(), out dt)) { wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911)); wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month); wb.Worksheets[0].Cells[rowj, 6].PutValue("" + dt.Day); } if (sburce.GetEnrollmentSchoolYear() != "") { wb.Worksheets[0].Cells[rowj, 7].PutValue(UpdateRecordUtil.getChineseYearStr(sburce.GetEnrollmentSchoolYear())); wb.Worksheets[0].Cells[rowj, 8].PutValue(UpdateRecordUtil.getMonthStr(sburce.GetEnrollmentSchoolYear(), false)); } wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetPrimarySchoolName()); wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetAddress()); // peoTotalCount++; rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //合計人數 wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 0].PutValue("合計"); wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Count + " 名"); wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白"); //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
// 處理台中相關 private void ProcessTaiChung(XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JExtendListTemplate_TaiChung), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JExtendListTemplate_TaiChung), FileFormatType.Excel2003); Worksheet ws = wb.Worksheets[0]; #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion rowj = 4; wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 學生異動名冊"); string strSemester = StudBatchUpdateRecEntity.GetContentSemester(); wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + strSemester + "學期"); wb.Worksheets[0].Cells[1, 7].PutValue(tmpRptY + "年" + tmpRptM + "月填報"); #region 異動紀錄 //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear()); wb.Worksheets[0].Cells[rowj, 3].PutValue(strSemester); DateTime dt; // 用西元轉換 if (DateTime.TryParse(sburce.GetUpdateDate(), out dt)) { wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911)); wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month); } wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetUpdateDescription()); wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetComment()); rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } #endregion // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //合計人數 wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 0].PutValue("合計"); wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 1].PutValue(data.Count + " 名"); wb.Worksheets[0].Cells[rowj, 6].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 6].PutValue("以下空白"); //儲存 wb.Save(location, FileFormatType.Excel2003); }
// 處理台中樣版 private void ProcessTaiChung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); #region 建立 Excel int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()); // 台中轉入與新生樣式相同 //從 Resources 將新生名冊template讀出來 Workbook template = new Workbook(); //template.Worksheets[0].PageSetup. //template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003); template.Open(new MemoryStream(GDResources.JTransferListTemplate_TaiChung), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); //wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003); wb.Open(new MemoryStream(GDResources.JTransferListTemplate_TaiChung), FileFormatType.Excel2003); #endregion #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 轉入學生名冊"); wb.Worksheets[0].Cells[1, 6].PutValue(tmpRptY + "年" + tmpRptM + "月填報"); //wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName () + " 國民中學入學學生名冊"); //wb.Worksheets[0].Cells[1, 10].PutValue(tmpRptY + "年" + tmpM + "月填製"); Range templateRow = template.Worksheets[0].Cells.CreateRange(4, 7, false); //string strGradeYear=""; rowj = 4; //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { //填入前先複製格式 wb.Worksheets[0].Cells.CreateRange(rowj, 7, false).Copy(templateRow); //if (rowj == 4) //strGradeYear = sburce.GetClassYear (); recCount++; //將學生資料填入適當的位置內 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear()); wb.Worksheets[0].Cells[rowj, 3].PutValue(StudBatchUpdateRecEntity.GetContentSemester()); //異動年月 DateTime dt; if (DateTime.TryParse(sburce.GetUpdateDate(), out dt)) { wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911)); wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month); } //異動情形 wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetImportExportSchool()); //原因 wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetUpdateDescription()); rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // Title //wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester () + "學期 "+strGradeYear +"年級"); wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期 異動:轉入"); //合計人數 wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 0].PutValue("合計"); wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Count + " 名"); wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白"); // 畫表 //Style st2 = wb.Styles[wb.Styles.Add()]; //StyleFlag sf2 = new StyleFlag(); //sf2.Borders = true; //st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //int tmpMaxRow = 0, tmpMaxCol = 0; //for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) //{ // tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; // tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; // wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); //} //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
// 處理高雄 private void ProcessKaoHsiung(XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int peoTotalCount = 0; // 總人數 int peoBoyCount = 0; // 男生人數 int peoGirlCount = 0; // 女生人數 Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JSuspensionStudentUpdateRecordListTemplate), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JSuspensionStudentUpdateRecordListTemplate), FileFormatType.Excel2003); Worksheet ws = wb.Worksheets[0]; //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #region 初始變數 int rowi = 0, rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion rowj = 4; wb.Worksheets[0].Cells[rowi, 3].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期"); //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; #region 填入學生資料 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetClassName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender()); wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetIDNumber()); if (sburce.GetGender() == "男") { peoBoyCount++; } if (sburce.GetGender() == "女") { peoGirlCount++; } peoTotalCount++; if (!string.IsNullOrEmpty(sburce.GetBirthday())) { wb.Worksheets[0].Cells[rowj, 5].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetBirthday())); } if (!string.IsNullOrEmpty(sburce.GetUpdateDate())) { wb.Worksheets[0].Cells[rowj, 6].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetUpdateDate())); } wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetAddress()); wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetLastADNumber()); wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetUpdateDescription()); #endregion rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } // 統計人數 rowj++; wb.Worksheets[0].Cells.CreateRange(rowj, 2, 1, 2).Merge(); wb.Worksheets[0].Cells[rowj, 2].PutValue("男:" + peoBoyCount.ToString()); wb.Worksheets[0].Cells[rowj, 4].PutValue("女:" + peoGirlCount.ToString()); wb.Worksheets[0].Cells[rowj, 8].PutValue("總計:" + peoTotalCount.ToString()); wb.Worksheets[0].Cells.CreateRange(rowj + 1, 0, 1, 10).Merge(); // wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員"); wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長"); //儲存 wb.Save(location, FileFormatType.Excel2003); }
private void ProcessTaiChung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); #region 建立 Excel int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()); //從 Resources 將名冊template讀出來 Workbook template = new Workbook(); template.Open(new MemoryStream(GDResources.JResumeListTemplate_TaiChung), FileFormatType.Excel2003); //產生 excel Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JResumeListTemplate_TaiChung), FileFormatType.Excel2003); #endregion #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowj = 1; bool x = false; int recCount = 0; int totalRec = data.Count; #endregion wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 復學學生名冊"); wb.Worksheets[0].Cells[1, 7].PutValue(tmpRptY + "年" + tmpRptM + "月填報"); Range templateRow = template.Worksheets[0].Cells.CreateRange(4, 7, false); rowj = 4; //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { //填入前先複製格式 wb.Worksheets[0].Cells.CreateRange(rowj, 7, false).Copy(templateRow); recCount++; //將學生資料填入適當的位置內 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear()); wb.Worksheets[0].Cells[rowj, 3].PutValue(StudBatchUpdateRecEntity.GetContentSemester()); //異動年月 DateTime dt; if (DateTime.TryParse(sburce.GetUpdateDate(), out dt)) { wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911)); wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month); } //2020/2/17 俊緯更新 與佳樺、靜妤討論後決定異動情形帶入該該校務系統的學校名稱 ////異動情形 wb.Worksheets[0].Cells[rowj, 6].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName()); //備註 wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetComment()); rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // Title //wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester () + "學期 "+strGradeYear +"年級"); string numSemester = StudBatchUpdateRecEntity.GetContentSemester(); wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + numSemester + "學期 異動:復學"); // 合計人數 wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 0].PutValue("合計"); wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Values.Count + "名"); wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白"); //儲存 Excel wb.Save(location, FileFormatType.Excel2003); }
// 處理新竹 private void ProcessHsinChu(System.Xml.XmlElement source, string location) { Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JGraduateListTemplate_HsinChu), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JGraduateListTemplate_HsinChu), FileFormatType.Excel2003); #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 學年度畢修業 學生名冊"); rowj = 2; #region 異動紀錄 //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetClassName()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetIDNumber()); //出生年月日 wb.Worksheets[0].Cells[rowj, 4].PutValue(DAL.DALTransfer.ConvertDateStr1(sburce.GetBirthday())); // 性別 wb.Worksheets[0].Cells[rowj, 5].PutValue(sburce.GetGender()); // 監護人姓名 wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetGuardian()); // 戶籍地址 wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetPermanentAddress()); wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetGraduate()); wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetGraduateCertificateNumber()); wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetComment()); rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } #endregion // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 1; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(2, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } //儲存 wb.Save(location, FileFormatType.Excel2003); }
// 處理台中 private void ProcessTaiChung(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int tmpY, tmpM; tmpY = DateTime.Now.Year; tmpM = DateTime.Now.Month; string tmpRptY, tmpRptM; tmpRptY = (tmpY - 1911).ToString(); if (tmpM < 10) { tmpRptM = "0" + tmpM.ToString(); } else { tmpRptM = tmpM.ToString(); } Workbook template = new Workbook(); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JGraduateListTemplate_TaiChung), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JGraduateListTemplate_TaiChung), FileFormatType.Excel2003); #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 畢業異動名冊"); wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度"); wb.Worksheets[0].Cells[1, 6].PutValue(tmpRptY + "年" + tmpRptM + "月填報"); rowj = 4; //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; wb.Worksheets[0].Cells.CreateRange(rowj, 0, 2, 1).Merge(); wb.Worksheets[0].Cells.CreateRange(rowj, 1, 2, 1).Merge(); wb.Worksheets[0].Cells.CreateRange(rowj, 2, 2, 1).Merge(); wb.Worksheets[0].Cells.CreateRange(rowj + 1, 3, 1, 3).Merge(); wb.Worksheets[0].Cells.CreateRange(rowj, 6, 2, 1).Merge(); wb.Worksheets[0].Cells.CreateRange(rowj, 7, 2, 1).Merge(); wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName()); wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender()); wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetIDNumber()); // 戶籍地址 wb.Worksheets[0].Cells[rowj + 1, 3].PutValue(sburce.GetPermanentAddress()); wb.Worksheets[0].Cells.SetRowHeight(rowj + 1, 50); //出生年月日 wb.Worksheets[0].Cells[rowj, 4].PutValue(DAL.DALTransfer.ConvertDateStr1(sburce.GetBirthday())); // 入學年月 string tempStr = sburce.GetEnrollmentSchoolYear().Trim(); int enSchoolYearLen = tempStr.Length; string strEnYearMonth = ""; if (enSchoolYearLen > 0) { if (enSchoolYearLen == 4) { strEnYearMonth = tempStr.Substring(0, 2) + "." + tempStr.Substring(4, 2); } if (enSchoolYearLen == 6) { int year; int.TryParse(tempStr.Substring(0, 4), out year); strEnYearMonth = (year - 1911) + "." + tempStr.Substring(4, 2); } wb.Worksheets[0].Cells[rowj, 5].PutValue(strEnYearMonth); } string strDocNo = DAL.DALTransfer.ConvertDateStr1(sburce.GetLastADDate()) + "\n" + sburce.GetLastADNumber().Trim(); string GrdDocNo = DAL.DALTransfer.ConvertDateStr1(sburce.GetUpdateDate()) + "\n" + sburce.GetGraduateCertificateNumber().Trim(); wb.Worksheets[0].Cells[rowj, 6].PutValue(strDocNo); wb.Worksheets[0].Cells[rowj, 7].PutValue(GrdDocNo); rowj += 2; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } // 合計人數 wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 0].PutValue("合計"); wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Values.Count + "名"); wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center; wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白"); //儲存 wb.Save(location, FileFormatType.Excel2003); }
// 處理高雄 private void ProcessKaoHsing(System.Xml.XmlElement source, string location) { // 資料轉換 Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source); int peoTotalCount = 0; // 總人數 int peoBoyCount = 0; // 男生人數 int peoGirlCount = 0; // 女生人數 Workbook template = new Workbook(); string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()); //從Resources把Template讀出來 template.Open(new MemoryStream(GDResources.JGraduatingStudentListTemplate), FileFormatType.Excel2003); //要產生的excel檔 Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(GDResources.JGraduatingStudentListTemplate), FileFormatType.Excel2003); #region 複製樣式-預設樣式、欄寬 //設定預設樣式 wb.DefaultStyle = template.DefaultStyle; #endregion #region 初始變數 int rowi = 0, rowj = 1; int recCount = 0; int totalRec = data.Count; #endregion rowj = 5; int i = 0; wb.Worksheets[0].Cells[rowi, 4].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度 第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期"); wb.Worksheets[0].Cells[rowi + 2, 13].PutValue("列印日期:" + strPrintDate); #region 異動紀錄 //將xml資料填入至excel foreach (StudBatchUpdateRecContentEntity sburce in data.Values) { recCount++; // 合併欄位 wb.Worksheets[0].Cells.CreateRange(rowj, 6, 1, 3).Merge(); //wb.Worksheets[0].Cells.CreateRange(rowj, 11, 1, 2).Merge(); //wb.Worksheets[0].Cells.CreateRange(rowj, 9, 1, 2).Merge(); //將學生資料填入適當的位置內 // 學號 wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber()); // 班級 wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetClassName()); // 座號 wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetSeatNo()); // 身分證號 wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetIDNumber()); // 性別 wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetGender()); // 姓名 wb.Worksheets[0].Cells[rowj, 5].PutValue(sburce.GetName()); //wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetBirthPlace ()); if (sburce.GetGender() == "男") { peoBoyCount++; } if (sburce.GetGender() == "女") { peoGirlCount++; } // 出生日 DateTime dt; if (DateTime.TryParse(sburce.GetBirthday(), out dt)) { string tmpBirth = (dt.Year - 1911) + "年"; if (dt.Month < 10) { tmpBirth += "0" + dt.Month + "月"; } else { tmpBirth += dt.Month + "月"; } if (dt.Day < 10) { tmpBirth += "0" + dt.Day + "日"; } else { tmpBirth += dt.Day + "日"; } wb.Worksheets[0].Cells[rowj, 6].PutValue(tmpBirth); } // 入學年月 if (sburce.GetEnrollmentSchoolYear() != "") { //string EnrollYearMonth = UpdateRecordUtil.getChineseYearStr(sburce.GetEnrollmentSchoolYear ()) + UpdateRecordUtil.getMonthStr(sburce.GetEnrollmentSchoolYear (), true); string EnrolSchoolYear = sburce.GetEnrollmentSchoolYear().Trim(); string EnrollYear = ""; string EnrollMonth = ""; if (EnrolSchoolYear.Length == 6) { int sc; if (int.TryParse(EnrolSchoolYear.Substring(0, 4), out sc)) { EnrollYear = (sc - 1911).ToString(); EnrollMonth = EnrolSchoolYear.Substring(4, 2); } } wb.Worksheets[0].Cells[rowj, 9].PutValue(EnrollYear); wb.Worksheets[0].Cells[rowj, 10].PutValue(EnrollMonth); } // 畢業年月 //if (sburce.GetGraduateSchoolYear()!= "") //{ // string GraduateYearMonth = UpdateRecordUtil.getChineseYearStr(sburce.GetGraduateSchoolYear ()) + UpdateRecordUtil.getMonthStr(sburce.GetGraduateSchoolYear (), true); // wb.Worksheets[0].Cells[rowj, 9].PutValue(GraduateYearMonth); //} wb.Worksheets[0].Cells[rowj, 11].PutValue(sburce.GetGraduateCertificateNumber()); wb.Worksheets[0].Cells[rowj, 12].PutValue(sburce.GetLastADNumber()); wb.Worksheets[0].Cells[rowj, 13].PutValue(sburce.GetComment()); #endregion peoTotalCount++; i++; rowj++; //回報進度 ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec))); } // 畫表 Style st2 = wb.Styles[wb.Styles.Add()]; StyleFlag sf2 = new StyleFlag(); sf2.Borders = true; st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; int tmpMaxRow = 0, tmpMaxCol = 0; for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++) { tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 4; tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1; wb.Worksheets[wbIdx1].Cells.CreateRange(5, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2); } // 統計人數 rowj++; wb.Worksheets[0].Cells[rowj, 2].PutValue("男:" + peoBoyCount.ToString()); wb.Worksheets[0].Cells[rowj, 4].PutValue("女:" + peoGirlCount.ToString()); wb.Worksheets[0].Cells[rowj, 8].PutValue("總計:" + peoTotalCount.ToString()); // wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員"); wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長"); //儲存 wb.Save(location, FileFormatType.Excel2003); }