// 處理台中縣樣版
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 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);
}
