// 高雄用
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);
}
