protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
// 取得名冊內存的最後異動代碼對照
Dictionary <string, string> LastCodeDict = new Dictionary <string, string>();
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "延修生畢業名冊")
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
}
else
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
}
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//複製頁面
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
//填入資料
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
{
ssn = rec.SelectSingleNode("@身份證號").InnerText;
}
if (!LastCodeDict.ContainsKey(ssn))
{
LastCodeDict.Add(ssn, rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
}
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" + rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
if (rec.SelectSingleNode("@特殊身份代碼") != null)
{
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
}
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("合計");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + 24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
//應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
if (LastCodeDict.ContainsKey(rec.IDNumber))
{
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
}
else
{
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
}
//備查文字
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//畢業證書字號
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//備註說明
mdws.Cells[mdws_index, 16].PutValue(rec.Comment);
}
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 24;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
// ���o�W�U���s���̫Ყ�ʥN�X���
Dictionary<string,string> LastCodeDict = new Dictionary<string,string>();
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//�g�J�W�U���O
if (source.SelectSingleNode("@���O").InnerText == "���ץͲ��~�W�U")
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
else
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
//�g�J�N��
ws.Cells[index, 6].PutValue("�N�X�G" + source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O
ws.Cells[index + 2, 0].PutValue("�զW�G" + source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + "�Ǧ~�� ��" + source.SelectSingleNode("@�Ǵ�").InnerText + "�Ǵ�");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@��O").InnerText);
//�g�J���
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
string ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if (ssn == "")
ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if(!LastCodeDict.ContainsKey(ssn))
LastCodeDict.Add(ssn,rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�ͤ�").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@���~�ҮѦr��").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("�X�p");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//����
ws.Cells[index + 23, 6].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + 24, 8);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 5;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "�q�l�榡";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B���ʥN�X)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//�C�W�[�@��,�ƻs�@��
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
//�����~�Ǧ~��
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//�Z�O
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//��O�N�X
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 ��W�����O�A�ШϥΪ̦۶�
//�Ǹ�
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//�m�W
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//�����Ҧr��
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//��1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//�ʧO�N�X
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//�X�ͤ��
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//�S������N�X
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//���ʭ�]�N�X
if(LastCodeDict.ContainsKey(rec.IDNumber))
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
else
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//�Ƭd��r
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//�Ƭd�帹
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//�Ƭd���
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//���~�ҮѦr��
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//�Ƶ�����
mdws.Cells[mdws_index, 16].PutValue(rec.Comment);
}
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
{
strUpdateData = rec.NewData;
}
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
{
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
}
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
//備註說明
mdws.Cells[mdws_index, 19].PutValue(rec.Comment);
}
//foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
//{
// mdws_index++;
// mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// // 學年度
// string schoolYear = "";
// if (!string.IsNullOrEmpty(record.GetAttribute("學生編號")))
// {
// SHSchool.Data.SHLeaveInfoRecord scl = SHSchool.Data.SHLeaveInfo.SelectByStudentID(record.GetAttribute("學生編號"));
// if (scl.SchoolYear.HasValue)
// schoolYear = scl.SchoolYear.Value.ToString();
// }
// mdws.Cells[mdws_index, 0].PutValue(schoolYear);
// mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("班別"));
// mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
// mdws.Cells[mdws_index, 3].PutValue("");
// mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("學號"));
// mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("姓名"));
// mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("身分證號"));
// mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("註1"));
// mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("性別代號"));
// mdws.Cells[mdws_index, 9].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("出生年月日"))));
// mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
// mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("異動代號"));
// mdws.Cells[mdws_index, 12].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("異動日期"))));
// mdws.Cells[mdws_index, 13].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("備查日期"))));
// mdws.Cells[mdws_index, 14].PutValue(GetADDoc(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 15].PutValue(GetADNo(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("新學號"));
// mdws.Cells[mdws_index, 17].PutValue(record.GetAttribute("備註"));
//}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28,false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) +"學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if(st.SelectSingleNode("@特殊身份代碼")!=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0,28,false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
//備註說明
mdws.Cells[mdws_index, 19].PutValue(rec.Comment);
}
//foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
//{
// mdws_index++;
// mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// // 學年度
// string schoolYear = "";
// if (!string.IsNullOrEmpty(record.GetAttribute("學生編號")))
// {
// SHSchool.Data.SHLeaveInfoRecord scl = SHSchool.Data.SHLeaveInfo.SelectByStudentID(record.GetAttribute("學生編號"));
// if (scl.SchoolYear.HasValue)
// schoolYear = scl.SchoolYear.Value.ToString();
// }
// mdws.Cells[mdws_index, 0].PutValue(schoolYear);
// mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("班別"));
// mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
// mdws.Cells[mdws_index, 3].PutValue("");
// mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("學號"));
// mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("姓名"));
// mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("身分證號"));
// mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("註1"));
// mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("性別代號"));
// mdws.Cells[mdws_index, 9].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("出生年月日"))));
// mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
// mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("異動代號"));
// mdws.Cells[mdws_index, 12].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("異動日期"))));
// mdws.Cells[mdws_index, 13].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("備查日期"))));
// mdws.Cells[mdws_index, 14].PutValue(GetADDoc(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 15].PutValue(GetADNo(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("新學號"));
// mdws.Cells[mdws_index, 17].PutValue(record.GetAttribute("備註"));
//}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將學籍異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlElement st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
string updatecode = st.SelectSingleNode("@異動代號").InnerText;
////將學生資料填入適當的位置內
//if (NewStudentNumberCodes.Contains(updatecode))
//{
// string strNum = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
// strNum = st.SelectSingleNode("@新學號").InnerText;
// else
// {
// string sid = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@學生編號").InnerText))
// sid = st.SelectSingleNode("@學生編號").InnerText;
// List<string> ids = new List<string>();
// ids.Add(sid);
// SHSchool.Data.SHStudent.RemoveByIDs(ids);
// SHSchool.Data.SHStudentRecord stud = SHSchool.Data.SHStudent.SelectByID(sid);
// strNum = stud.StudentNumber;
// }
// wb.Worksheets[0].Cells[rowj, 1].PutValue(strNum);
//}
//else
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
//wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + (string.IsNullOrEmpty(st.GetAttribute("更正後資料")) ? "" : "\n" + st.GetAttribute("更正後資料")));
string UpdateData = "";
if (st.SelectSingleNode("@新資料") != null)
{
// 更正學號填到另一格
if (updatecode != "401")
{
UpdateData = st.SelectSingleNode("@新資料").InnerText;
}
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + "\n" + UpdateData);
string strUpdateDate = Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText);
//假設有異動學生學號的類別才出現新學號字樣
if (st.SelectSingleNode("@新學號") != null)
{
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
{
int newNo;
if (int.TryParse(st.SelectSingleNode("@新學號").InnerText, out newNo))
{
strUpdateDate = newNo + "\n" + strUpdateDate;
}
}
else
{
// 更正學號
if (updatecode == "401")
{
if (st.SelectSingleNode("@新資料") != null)
{
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新資料").InnerText))
{
strUpdateDate = st.SelectSingleNode("@新資料").InnerText + "\n" + strUpdateDate;
}
}
}
}
}
wb.Worksheets[0].Cells[rowj, 13].PutValue(strUpdateDate);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
// 因2010年格式不同小修改
#region 學籍異動電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "異動名冊";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_EndRow = TemplateWb.Cells.CreateRange(2, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
// 遇到特殊異動代碼要處理
List <string> spcCode = new List <string>();
spcCode.Add("211");
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, GYear(data.GradeYear), data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//異動日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 異動順序
DyWb.Cells[DyWb_index, 13].PutValue(rec.Order);
//備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.LastADDate);
//備查文字
DyWb.Cells[DyWb_index, 15].PutValue(rec.LastADDoc);
//備查文號
DyWb.Cells[DyWb_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
{
strUpdateData = rec.NewData;
}
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
{
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
}
DyWb.Cells[DyWb_index, 17].PutValue(strUpdateData);
// 註2
DyWb.Cells[DyWb_index, 18].PutValue(rec.Comment2);
//雙學籍編號
DyWb.Cells[DyWb_index, 19].PutValue(rec.ReplicatedSchoolRollNumber);
//備註說明
DyWb.Cells[DyWb_index, 20].PutValue(rec.Comment);
// 2011 新承辦單位修正，轉科讀取新學號
if (NewStudentNumberCodes.Contains(rec.UpdateCode))
{
List <string> ids = new List <string>();
ids.Add(rec.StudentID);
SHSchool.Data.SHStudent.RemoveByIDs(ids);
SHSchool.Data.SHStudentRecord studRec = SHSchool.Data.SHStudent.SelectByID(rec.StudentID);
if (studRec != null)
{
DyWb.Cells[DyWb_index, 3].PutValue(studRec.StudentNumber);
}
DyWb.Cells[DyWb_index, 17].PutValue("");
}
}
// 資料末底 加End
DyWb.Cells.CreateRange(DyWb_index + 1, 1, false).Copy(range_R_EndRow);
DyWb.AutoFitColumns();
//範本
Worksheet TemplateWb_Cover = wb.Worksheets["異動名冊封面範本"];
//實做頁面
Worksheet cover = wb.Worksheets[wb.Worksheets.Add()];
//名稱
cover.Name = "異動名冊封面";
string school_code = source.SelectSingleNode("@學校代號").InnerText;
string school_year = source.SelectSingleNode("@學年度").InnerText;
string school_semester = source.SelectSingleNode("@學期").InnerText;
//範圍
Range range_H_Cover = TemplateWb_Cover.Cells.CreateRange(0, 1, false);
//range_H_Cover
cover.Cells.CreateRange(0, 1, false).Copy(range_H_Cover);
Range range_R_cover = TemplateWb_Cover.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_cover_EndRow = TemplateWb_Cover.Cells.CreateRange(2, 1, false);
int cover_row_counter = 1;
//2018/2/2 穎驊註解 ，下面是新的封面產生方式
foreach (XmlNode list in source.SelectNodes("清單"))
{
//每增加一行,複製一次
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover);
string gradeYear = list.SelectSingleNode("@年級").InnerText;
string deptCode = list.SelectSingleNode("@科別代碼").InnerText;
//學校代碼
cover.Cells[cover_row_counter, 0].PutValue(school_code);
//學年度
cover.Cells[cover_row_counter, 1].PutValue(school_year);
//學期
cover.Cells[cover_row_counter, 2].PutValue(school_semester);
//年級
cover.Cells[cover_row_counter, 3].PutValue(gradeYear);
//科別代碼
cover.Cells[cover_row_counter, 6].PutValue(deptCode);
foreach (XmlElement st in list.SelectNodes("異動名冊封面"))
{
string reportType = st.SelectSingleNode("@名冊別").InnerText;
string classType = st.SelectSingleNode("@班別").InnerText;
string updateType = st.SelectSingleNode("@上傳類別").InnerText;
string approvedClassCount = st.SelectSingleNode("@核定班數").InnerText;
string approvedStudentCount = st.SelectSingleNode("@核定學生數").InnerText;
string actualClassCount = st.SelectSingleNode("@實招班數").InnerText;
string actualStudentCount = st.SelectSingleNode("@實招新生數").InnerText;
string originalStudentCount = st.SelectSingleNode("@原有學生數").InnerText;
string increaseStudentCount = st.SelectSingleNode("@增加學生數").InnerText;
string decreaseStudentCount = st.SelectSingleNode("@減少學生數").InnerText;
string modifiedStudentCount = st.SelectSingleNode("@更正學生數").InnerText;
string currentStudentCount = st.SelectSingleNode("@現有學生數").InnerText;
string remarks1 = st.SelectSingleNode("@註1").InnerText;
string remarksContent = st.SelectSingleNode("@備註說明").InnerText;
//名冊別
cover.Cells[cover_row_counter, 4].PutValue(reportType);
//班別
cover.Cells[cover_row_counter, 5].PutValue(classType);
//上傳類別
cover.Cells[cover_row_counter, 7].PutValue(updateType);
//核定班級
cover.Cells[cover_row_counter, 8].PutValue(approvedClassCount);
//核定學生數
cover.Cells[cover_row_counter, 9].PutValue(approvedStudentCount);
//實招班數
cover.Cells[cover_row_counter, 10].PutValue(actualClassCount);
//實招新生數
cover.Cells[cover_row_counter, 11].PutValue(actualStudentCount);
//原有學生數
cover.Cells[cover_row_counter, 12].PutValue(originalStudentCount);
//增加學生數
cover.Cells[cover_row_counter, 13].PutValue(increaseStudentCount);
//減少學生數
cover.Cells[cover_row_counter, 14].PutValue(decreaseStudentCount);
//更正學生數
cover.Cells[cover_row_counter, 15].PutValue(modifiedStudentCount);
//現有學生數
cover.Cells[cover_row_counter, 16].PutValue(currentStudentCount);
//註1
cover.Cells[cover_row_counter, 17].PutValue(remarks1);
//備註說明
cover.Cells[cover_row_counter, 18].PutValue(remarksContent);
}
cover_row_counter++;
}
// 資料末底 加End
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover_EndRow);
wb.Worksheets.RemoveAt("電子格式範本");
wb.Worksheets.RemoveAt("異動名冊封面範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 23;
//頁面有幾個col
int col = 14;
//資料row數目
int dataRow = 16;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 23, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / dataRow) + 1;
//寫入代號
ws.Cells[index, 11].PutValue(source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別、年級
ws.Cells[index + 2, 1].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 5].PutValue(source.SelectSingleNode("@學年度").InnerText + " 學年度 第 " + source.SelectSingleNode("@學期").InnerText + " 學期");
ws.Cells[index + 2, 8].PutValue(list.SelectSingleNode("@科別").InnerText);
ws.Cells[index + 2, 12].PutValue(list.SelectSingleNode("@年級").InnerText + "年級");
//寫入資料
int recCount = 0;
int dataIndex = index + 6;
for (; currentPage <= totalPage; currentPage++)
{
//複製頁面
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
}
//填入資料
for (int i = 0; i < dataRow && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
if (rec.SelectSingleNode("@新學號") != null)
{
if (string.IsNullOrEmpty(rec.SelectSingleNode("@新學號").InnerText))
{
if (rec.SelectSingleNode("@學號") != null)
{
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText);
}
else
{
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@新學號").InnerText);
}
}
}
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@身分證號").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(rec.SelectSingleNode("@性別代號").InnerText);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@性別").InnerText);
ws.Cells[dataIndex, 5].PutValue(rec.SelectSingleNode("@出生年月日").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@轉入前學生資料_學校").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@轉入前學生資料_學號").InnerText + "\n" + rec.SelectSingleNode("@轉入前學生資料_科別").InnerText);
ws.Cells[dataIndex, 8].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@轉入前學生資料_備查日期").InnerText) + "\n" + rec.SelectSingleNode("@轉入前學生資料_備查文號").InnerText);
ws.Cells[dataIndex, 9].PutValue(rec.SelectSingleNode("@轉入前學生資料_年級").InnerText);
ws.Cells[dataIndex, 10].PutValue(rec.SelectSingleNode("@異動代號").InnerText);
ws.Cells[dataIndex, 11].PutValue(rec.SelectSingleNode("@原因及事項").InnerText);
ws.Cells[dataIndex, 12].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@異動日期").InnerText));
//ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@備註").InnerText);
if (rec.SelectSingleNode("@特殊身份代碼") != null)
{
ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
}
dataIndex++;
count++;
//轉入前學生資料_學校="糕忠高中" 轉入前學生資料_學號="010101" 轉入前學生資料_科別="資訊科" 轉入前學生資料_備查日期="90/09/09" 轉入前學生資料_備查文號="教中三字第09200909090號" 轉入前學生資料_年級="一上"
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells[dataIndex, 0].PutValue("合計 " + list.ChildNodes.Count.ToString() + " 名");
}
//分頁
ws.Cells[index + next - 1, 10].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + next, col);
//索引指向下一頁
index += next;
dataIndex = index + 6;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region 轉入生,電子格式
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
DyWb.Name = "電子格式";
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、學號)
_data = (from data in _data orderby data.ClassType, data.GradeYear, data.DeptCode, data.StudentNumber select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
//學號
if (string.IsNullOrEmpty(rec.NewStudNumber))
{
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
}
else
{
DyWb.Cells[DyWb_index, 3].PutValue(rec.NewStudNumber);
}
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//轉入日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 轉入身分別
DyWb.Cells[DyWb_index, 13].PutValue(rec.TransferStatus);
//原備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.PreviousSchoolLastADDate);
//原備查文字(*)
DyWb.Cells[DyWb_index, 15].PutValue(rec.PreviousSchoolLastADDoc);
//原備查文號(*)
DyWb.Cells[DyWb_index, 16].PutValue(rec.PreviousSchoolLastADNum);
//原學校代碼(*)
DyWb.Cells[DyWb_index, 17].PutValue(rec.PreviousSchoolCode);
//原科別代碼
DyWb.Cells[DyWb_index, 18].PutValue(rec.PreviousDeptCode);
//原學號
DyWb.Cells[DyWb_index, 19].PutValue(rec.PreviousStudentNumber);
// 為支援舊結構年級與學期是用文字字串一上，所以這樣寫
//原年級
DyWb.Cells[DyWb_index, 20].PutValue(Getyear(rec.PreviousGradeYear));
//原學期
DyWb.Cells[DyWb_index, 21].PutValue(Getsemester(rec.PreviousSemester));
//備註說明
DyWb.Cells[DyWb_index, 22].PutValue(rec.Comment);
}
//foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
//{
// DyWb_index++;
// //每增加一行,複製一次
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //班別
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
// //科別代碼
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// //學號
// DyWb.Cells[DyWb_index, 2].PutValue(Record.GetAttribute("新學號"));
// //姓名
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("姓名"));
// //身分證字號
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("身分證號"));
// //註1
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("註1"));
// //性別代碼
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("性別代號"));
// //出生日期
// DyWb.Cells[DyWb_index, 7].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("出生年月日")));
// //特殊身份代碼
// DyWb.Cells[DyWb_index, 8].PutValue(Record.GetAttribute("特殊身份代碼"));
// //年級
// DyWb.Cells[DyWb_index, 9].PutValue((Record.ParentNode as XmlElement).GetAttribute("年級"));
// //異動原因代碼
// DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("異動代號"));
// //轉入日期
// DyWb.Cells[DyWb_index, 11].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("異動日期")));
// //原備查日期
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("轉入前學生資料_備查日期")));
// //原備查文字(*)
// DyWb.Cells[DyWb_index, 13].PutValue(GetNumAndSrt1(Record.GetAttribute("轉入前學生資料_備查文號")));
// //原備查文號(*)
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt2(Record.GetAttribute("轉入前學生資料_備查文號")));
// //原學校代碼(*)
// DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("轉入前學生資料_學校"));
// //原科別代碼
// DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("轉入前學生資料_科別"));
// //原學號
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("轉入前學生資料_學號"));
// //原年級
// DyWb.Cells[DyWb_index, 18].PutValue(Getyear(Record.GetAttribute("轉入前學生資料_年級")));
// //原學期
// DyWb.Cells[DyWb_index, 19].PutValue(Getsemester(Record.GetAttribute("轉入前學生資料_年級")));
// //備註說明
// DyWb.Cells[DyWb_index, 20].PutValue(Record.GetAttribute("備註"));
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
// 取得名冊內存的最後異動代碼對照
Dictionary <string, string> LastCodeDict = new Dictionary <string, string>();
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "延修生畢業名冊")
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
}
else
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
}
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//複製頁面
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
int updateCount = list.SelectNodes("異動紀錄").Count;
//填入資料
for (int i = 0; i < 18 && recCount < updateCount; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
{
ssn = rec.SelectSingleNode("@身份證號").InnerText;
}
if (!LastCodeDict.ContainsKey(ssn))
{
LastCodeDict.Add(ssn, rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
}
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" + rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
if (rec.SelectSingleNode("@特殊身份代碼") != null)
{
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
}
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("合計");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + 24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
//範本
//範本
Worksheet TemplateWb_Cover = wb.Worksheets["延修生畢業名冊封面範本"];
//實做頁面
Worksheet cover = wb.Worksheets[wb.Worksheets.Add()];
//名稱
cover.Name = "延修生畢業名冊封面";
string school_code = source.SelectSingleNode("@學校代號").InnerText;
string school_year = source.SelectSingleNode("@學年度").InnerText;
string school_semester = source.SelectSingleNode("@學期").InnerText;
//範圍
Range range_H_Cover = TemplateWb_Cover.Cells.CreateRange(0, 1, false);
//range_H_Cover
cover.Cells.CreateRange(0, 1, false).Copy(range_H_Cover);
Range range_R_cover = TemplateWb_Cover.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_cover_EndRow = TemplateWb_Cover.Cells.CreateRange(2, 1, false);
int cover_row_counter = 1;
//2018/2/2 穎驊註解 ，下面是新的封面產生方式
foreach (XmlNode list in source.SelectNodes("清單"))
{
//每增加一行,複製一次
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover);
string gradeYear = list.SelectSingleNode("@年級").InnerText;
string deptCode = list.SelectSingleNode("@科別代碼").InnerText;
//學校代碼
cover.Cells[cover_row_counter, 0].PutValue(school_code);
//學年度
cover.Cells[cover_row_counter, 1].PutValue(school_year);
//學期
cover.Cells[cover_row_counter, 2].PutValue(school_semester);
//科別代碼
cover.Cells[cover_row_counter, 6].PutValue(deptCode);
foreach (XmlElement st in list.SelectNodes("異動名冊封面"))
{
string reportType = st.SelectSingleNode("@名冊別").InnerText;
string scheduledGraduateYear = st.SelectSingleNode("@應畢業學年度").InnerText;
string classType = st.SelectSingleNode("@班別").InnerText;
string updateType = st.SelectSingleNode("@上傳類別").InnerText;
string approvedExtendingStudentCount = st.SelectSingleNode("@輔導延修學生數").InnerText;
string waitingExtendingStudentCount = st.SelectSingleNode("@未申請延修學生數").InnerText;
string originalStudentCount = st.SelectSingleNode("@原有學生數").InnerText;
string currentStudentCount = st.SelectSingleNode("@現有學生數").InnerText;
string graduatedStudentCount = st.SelectSingleNode("@畢業學生數").InnerText;
string remarksContent = st.SelectSingleNode("@備註說明").InnerText;
//名冊別
cover.Cells[cover_row_counter, 3].PutValue(reportType);
//應畢業學年度
cover.Cells[cover_row_counter, 4].PutValue(scheduledGraduateYear);
//班別
cover.Cells[cover_row_counter, 5].PutValue(classType);
//上傳類別
cover.Cells[cover_row_counter, 7].PutValue(updateType);
//輔導延修學生數
cover.Cells[cover_row_counter, 8].PutValue(approvedExtendingStudentCount);
//未申請延修學生數
cover.Cells[cover_row_counter, 9].PutValue(waitingExtendingStudentCount);
//原有學生數
cover.Cells[cover_row_counter, 10].PutValue(originalStudentCount);
//現有學生數
cover.Cells[cover_row_counter, 11].PutValue(currentStudentCount);
//畢業學生數
cover.Cells[cover_row_counter, 12].PutValue(graduatedStudentCount);
//備註說明
cover.Cells[cover_row_counter, 13].PutValue(remarksContent);
}
cover_row_counter++;
}
// 資料末底 加End
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover_EndRow);
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
//名稱
mdws.Name = "延修生畢業名冊";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_EndRow = TemplateWb.Cells.CreateRange(2, 1, false);
//拷貝range_H
mdws.Cells.CreateRange(0, 1, false).Copy(range_H);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_R);
//應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
if (LastCodeDict.ContainsKey(rec.IDNumber))
{
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
}
else
{
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
}
//備查文字
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//畢業證書字號
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//畢業證書註記學程代碼 (2019/02/15 穎驊 檢查發現 目前我們系統沒有支援這個概念，要再研究)
mdws.Cells[mdws_index, 16].PutValue("");
//備註說明
mdws.Cells[mdws_index, 17].PutValue(rec.Comment);
}
// 資料末底 加End
mdws.Cells.CreateRange(mdws_index + 1, 1, false).Copy(range_R_EndRow);
wb.Worksheets.RemoveAt("電子格式範本");
wb.Worksheets.RemoveAt("延修生畢業名冊封面範本");
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
//名稱
mdws.Name = "延修生異動名冊";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_EndRow = TemplateWb.Cells.CreateRange(2, 1, false);
//拷貝range_H
mdws.Cells.CreateRange(0, 1, false).Copy(range_H);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 整理封面的列數，能填的值盡量幫他填，剩下無法的 要請使用者自行填寫
// <年級,<班別,科別>>
Dictionary <string, Dictionary <string, List <string> > > cover_row_dict = new Dictionary <string, Dictionary <string, List <string> > >();
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_R);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
{
strUpdateData = rec.NewData;
}
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
{
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
}
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
// 雙重學籍編號
mdws.Cells[mdws_index, 19].PutValue(rec.ReplicatedSchoolRollNumber);
//備註說明
mdws.Cells[mdws_index, 20].PutValue(rec.Comment);
// 整理cover_row_dict
//年級
if (!cover_row_dict.ContainsKey(rec.GradeYear))
{
cover_row_dict.Add(rec.GradeYear, new Dictionary <string, List <string> >());
}
//班別
if (!cover_row_dict[rec.GradeYear].ContainsKey(rec.ClassType))
{
cover_row_dict[rec.GradeYear].Add(rec.ClassType, new List <string>());
}
//科別
if (!cover_row_dict[rec.GradeYear][rec.ClassType].Contains(rec.DeptCode))
{
cover_row_dict[rec.GradeYear][rec.ClassType].Add(rec.DeptCode);
}
}
// 資料末底 加End
mdws.Cells.CreateRange(mdws_index + 1, 1, false).Copy(range_R_EndRow);
mdws.AutoFitColumns();
//範本
Worksheet TemplateWb_Cover = wb.Worksheets["延修生異動名冊封面範本"];
//實做頁面
Worksheet cover = wb.Worksheets[wb.Worksheets.Add()];
cover.Name = "延修生異動名冊封面";
string school_code = source.SelectSingleNode("@學校代號").InnerText;
string school_year = source.SelectSingleNode("@學年度").InnerText;
string school_semester = source.SelectSingleNode("@學期").InnerText;
//範圍
Range range_H_Cover = TemplateWb_Cover.Cells.CreateRange(0, 1, false);
//range_H_Cover
cover.Cells.CreateRange(0, 1, false).Copy(range_H_Cover);
Range range_R_cover = TemplateWb_Cover.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_cover_EndRow = TemplateWb_Cover.Cells.CreateRange(2, 1, false);
int cover_row_counter = 1;
//2018/2/2 穎驊註解 ，下面是新的封面產生方式
foreach (XmlNode list in source.SelectNodes("清單"))
{
//每增加一行,複製一次
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover);
string gradeYear = list.SelectSingleNode("@年級").InnerText;
string deptCode = list.SelectSingleNode("@科別代碼").InnerText;
//學校代碼
cover.Cells[cover_row_counter, 0].PutValue(school_code);
//學年度
cover.Cells[cover_row_counter, 1].PutValue(school_year);
//學期
cover.Cells[cover_row_counter, 2].PutValue(school_semester);
//科別代碼
cover.Cells[cover_row_counter, 6].PutValue(deptCode);
foreach (XmlElement st in list.SelectNodes("異動名冊封面"))
{
string reportType = st.SelectSingleNode("@名冊別").InnerText;
string scheduledGraduateYear = st.SelectSingleNode("@應畢業學年度").InnerText;
string classType = st.SelectSingleNode("@班別").InnerText;
string updateType = st.SelectSingleNode("@上傳類別").InnerText;
string approvedExtendingStudentCount = st.SelectSingleNode("@輔導延修學生數").InnerText;
string originalStudentCount = st.SelectSingleNode("@原有學生數").InnerText;
string decreaseStudentCount = st.SelectSingleNode("@減少學生數").InnerText;
string modifiedStudentCount = st.SelectSingleNode("@更正學生數").InnerText;
string currentStudentCount = st.SelectSingleNode("@現有學生數").InnerText;
string remarksContent = st.SelectSingleNode("@備註說明").InnerText;
//名冊別
cover.Cells[cover_row_counter, 3].PutValue(reportType);
//應畢業學年度
cover.Cells[cover_row_counter, 4].PutValue(scheduledGraduateYear);
//班別
cover.Cells[cover_row_counter, 5].PutValue(classType);
//上傳類別
cover.Cells[cover_row_counter, 7].PutValue(updateType);
//輔導延修學生數
cover.Cells[cover_row_counter, 8].PutValue(approvedExtendingStudentCount);
//原有學生數
cover.Cells[cover_row_counter, 9].PutValue(originalStudentCount);
//減少學生數
cover.Cells[cover_row_counter, 10].PutValue(decreaseStudentCount);
//更正學生數
cover.Cells[cover_row_counter, 11].PutValue(modifiedStudentCount);
//現有學生數
cover.Cells[cover_row_counter, 12].PutValue(currentStudentCount);
//備註說明
cover.Cells[cover_row_counter, 13].PutValue(remarksContent);
}
cover_row_counter++;
}
// 資料末底 加End
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover_EndRow);
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
wb.Worksheets.RemoveAt("電子格式範本");
wb.Worksheets.RemoveAt("延修生異動名冊封面範本");
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 23;
//�������X��col
int col = 14;
//���row�ƥ�
int dataRow = 16;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0,23,false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / dataRow) + 1;
//�g�J�N��
ws.Cells[index, 11].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O�B�~��
ws.Cells[index + 2, 1].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 5].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + " �Ǧ~�� �� " + source.SelectSingleNode("@�Ǵ�").InnerText + " �Ǵ�");
ws.Cells[index + 2, 8].PutValue(list.SelectSingleNode("@��O").InnerText);
ws.Cells[index + 2, 12].PutValue(list.SelectSingleNode("@�~��").InnerText + "�~��");
//�g�J���
int recCount = 0;
int dataIndex = index + 6;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < dataRow && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
if(rec.SelectSingleNode("@�s�Ǹ�")!=null)
if(string.IsNullOrEmpty(rec.SelectSingleNode("@�s�Ǹ�").InnerText))
if(rec.SelectSingleNode("@�Ǹ�")!=null)
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText);
else
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�s�Ǹ�").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�����Ҹ�").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
ws.Cells[dataIndex, 5].PutValue(rec.SelectSingleNode("@�X�ͦ~���").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǯ�").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_��O").InnerText);
ws.Cells[dataIndex, 8].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 9].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�~��").InnerText);
ws.Cells[dataIndex, 10].PutValue(rec.SelectSingleNode("@���ʥN��").InnerText);
ws.Cells[dataIndex, 11].PutValue(rec.SelectSingleNode("@��]�Ψƶ�").InnerText);
ws.Cells[dataIndex, 12].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@���ʤ��").InnerText));
//ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
//��J�e�ǥ��_�Ǯ�="�|������" ��J�e�ǥ��_�Ǹ�="010101" ��J�e�ǥ��_��O="��T��" ��J�e�ǥ��_�Ƭd���="90/09/09" ��J�e�ǥ��_�Ƭd�帹="�Ф��T�r��09200909090��" ��J�e�ǥ��_�~��="�@�W"
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells[dataIndex, 0].PutValue("�X�p " + list.ChildNodes.Count.ToString() + " �W");
}
//����
ws.Cells[index + next -1, 10].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + next, col);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 6;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region ��J��,�q�l�榡
Worksheet TemplateWb = wb.Worksheets["�q�l�榡�d��"];
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
DyWb.Name = "�q�l�榡";
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B�Ǹ�)
_data =(from data in _data orderby data.ClassType,data.GradeYear,data.DeptCode,data.StudentNumber select data).ToList ();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//�C�W�[�@��,�ƻs�@��
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//�Z�O
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//��O�N�X
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
//�Ǹ�
if(string.IsNullOrEmpty(rec.NewStudNumber))
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
else
DyWb.Cells[DyWb_index, 3].PutValue(rec.NewStudNumber);
//�m�W
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//�����Ҧr��
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//��1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//�ʧO�N�X
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//�X�ͤ��
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//�S������N�X
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//�~��
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//���ʭ�]�N�X
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//��J���
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// ��J�����O
DyWb.Cells[DyWb_index, 13].PutValue(rec.TransferStatus);
//��Ƭd���
DyWb.Cells[DyWb_index, 14].PutValue(rec.PreviousSchoolLastADDate);
//��Ƭd��r(*)
DyWb.Cells[DyWb_index, 15].PutValue(rec.PreviousSchoolLastADDoc);
//��Ƭd�帹(*)
DyWb.Cells[DyWb_index, 16].PutValue(rec.PreviousSchoolLastADNum);
//��ǮեN�X(*)
DyWb.Cells[DyWb_index, 17].PutValue(rec.PreviousSchoolCode);
//���O�N�X
DyWb.Cells[DyWb_index, 18].PutValue(rec.PreviousDeptCode);
//��Ǹ�
DyWb.Cells[DyWb_index, 19].PutValue(rec.PreviousStudentNumber);
// ���䴩�µ��c�~�ŻP�Ǵ��O�Τ�r�r��@�W�A�ҥH�o�˼g
//��~��
DyWb.Cells[DyWb_index, 20].PutValue(Getyear(rec.PreviousGradeYear));
//��Ǵ�
DyWb.Cells[DyWb_index, 21].PutValue(Getsemester(rec.PreviousSemester));
//�Ƶ�����
DyWb.Cells[DyWb_index, 22].PutValue(rec.Comment);
}
//foreach (XmlElement Record in source.SelectNodes("�M��/���ʬ���"))
//{
// DyWb_index++;
// //�C�W�[�@��,�ƻs�@��
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //�Z�O
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("�Z�O"));
// //��O�N�X
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("��O�N��"));
// //�Ǹ�
// DyWb.Cells[DyWb_index, 2].PutValue(Record.GetAttribute("�s�Ǹ�"));
// //�m�W
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("�m�W"));
// //�����Ҧr��
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("�����Ҹ�"));
// //��1
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("��1"));
// //�ʧO�N�X
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("�ʧO�N��"));
// //�X�ͤ��
// DyWb.Cells[DyWb_index, 7].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("�X�ͦ~���")));
// //�S������N�X
// DyWb.Cells[DyWb_index, 8].PutValue(Record.GetAttribute("�S������N�X"));
// //�~��
// DyWb.Cells[DyWb_index, 9].PutValue((Record.ParentNode as XmlElement).GetAttribute("�~��"));
// //���ʭ�]�N�X
// DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("���ʥN��"));
// //��J���
// DyWb.Cells[DyWb_index, 11].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("���ʤ��")));
// //��Ƭd���
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("��J�e�ǥ��_�Ƭd���")));
// //��Ƭd��r(*)
// DyWb.Cells[DyWb_index, 13].PutValue(GetNumAndSrt1(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��Ƭd�帹(*)
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt2(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��ǮեN�X(*)
// DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǯ�"));
// //���O�N�X
// DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("��J�e�ǥ��_��O"));
// //��Ǹ�
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǹ�"));
// //��~��
// DyWb.Cells[DyWb_index, 18].PutValue(Getyear(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //��Ǵ�
// DyWb.Cells[DyWb_index, 19].PutValue(Getsemester(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //�Ƶ�����
// DyWb.Cells[DyWb_index, 20].PutValue(Record.GetAttribute("�Ƶ�"));
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("�q�l�榡�d��");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將學籍異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlElement st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
string updatecode = st.SelectSingleNode("@異動代號").InnerText;
////將學生資料填入適當的位置內
//if (NewStudentNumberCodes.Contains(updatecode))
//{
// string strNum = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
// strNum = st.SelectSingleNode("@新學號").InnerText;
// else
// {
// string sid = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@學生編號").InnerText))
// sid = st.SelectSingleNode("@學生編號").InnerText;
// List<string> ids = new List<string>();
// ids.Add(sid);
// SHSchool.Data.SHStudent.RemoveByIDs(ids);
// SHSchool.Data.SHStudentRecord stud = SHSchool.Data.SHStudent.SelectByID(sid);
// strNum = stud.StudentNumber;
// }
// wb.Worksheets[0].Cells[rowj, 1].PutValue(strNum);
//}
//else
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
//wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + (string.IsNullOrEmpty(st.GetAttribute("更正後資料")) ? "" : "\n" + st.GetAttribute("更正後資料")));
string UpdateData = "";
if (st.SelectSingleNode("@新資料")!=null)
{
// 更正學號填到另一格
if (updatecode != "401")
UpdateData = st.SelectSingleNode("@新資料").InnerText;
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText+"\n"+UpdateData);
string strUpdateDate = Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText);
//假設有異動學生學號的類別才出現新學號字樣
if (st.SelectSingleNode("@新學號")!=null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
{
int newNo;
if (int.TryParse(st.SelectSingleNode("@新學號").InnerText, out newNo))
strUpdateDate = newNo + "\n" + strUpdateDate;
}
else
{
// 更正學號
if (updatecode == "401")
{
if (st.SelectSingleNode("@新資料") != null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新資料").InnerText))
strUpdateDate = st.SelectSingleNode("@新資料").InnerText + "\n" + strUpdateDate;
}
}
wb.Worksheets[0].Cells[rowj, 13].PutValue(strUpdateDate);
if(st.SelectSingleNode("@特殊身份代碼") !=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
// 因2010年格式不同小修改
#region 學籍異動電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
// 遇到特殊異動代碼要處理
List<string> spcCode = new List<string>();
spcCode.Add("211");
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, GYear(data.GradeYear), data.DeptCode, data.UpdateCode select data).ToList();
foreach(GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data )
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//異動日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 異動順序
DyWb.Cells[DyWb_index, 13].PutValue(rec.Order);
//備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.LastADDate);
//備查文字
DyWb.Cells[DyWb_index, 15].PutValue(rec.LastADDoc);
//備查文號
DyWb.Cells[DyWb_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
DyWb.Cells[DyWb_index, 17].PutValue(strUpdateData);
// 註2
DyWb.Cells[DyWb_index, 18].PutValue(rec.Comment2);
//備註說明
DyWb.Cells[DyWb_index, 19].PutValue(rec.Comment);
// 2011 新承辦單位修正，轉科讀取新學號
if (NewStudentNumberCodes.Contains(rec.UpdateCode))
{
List<string> ids = new List<string>();
ids.Add(rec.StudentID);
SHSchool.Data.SHStudent.RemoveByIDs(ids);
SHSchool.Data.SHStudentRecord studRec = SHSchool.Data.SHStudent.SelectByID(rec.StudentID);
if(studRec !=null)
DyWb.Cells[DyWb_index, 3].PutValue(studRec.StudentNumber);
DyWb.Cells[DyWb_index, 17].PutValue("");
}
}
//foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
//{
// DyWb_index++;
// //每增加一行,複製一次
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //班別
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
// //科別代碼
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// // 2 放上傳類別，請使用者自填
// //學號
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
// //姓名
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
// //身分證字號
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
// //註1
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("註1"));
// //性別代碼
// DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
// //出生日期
// DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("出生年月日")));
// //特殊身份代碼
// DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身份代碼")); //原為抓取備註欄位
// //年級
// DyWb.Cells[DyWb_index, 10].PutValue((Record.ParentNode as XmlElement).GetAttribute("年級"));
// //異動原因代碼
// DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("異動代號"));
// //異動日期
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("異動日期")));
// //原備查日期
// DyWb.Cells[DyWb_index, 13].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("備查日期")));
// //原備查文字
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt1(Record.GetAttribute("備查文號")));
// //原備查文號
// DyWb.Cells[DyWb_index, 15].PutValue(GetNumAndSrt2(Record.GetAttribute("備查文號")));
// // 捨棄
// ////舊班別
// //DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("舊班別"));
// ////舊科別代碼
// //DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("舊科別代碼"));
// //更正後資料
// string strUpdateData = string.Empty;
// //若是更正後資料有值則填入更正後資料
// if (!string.IsNullOrEmpty(Record.GetAttribute("更正後資料")))
// strUpdateData = Record.GetAttribute("更正後資料");
// //若是新學號中有值則填入新學號
// //判斷strUpdateData是否已有值，若是已有值則加入斷行符號
// if (!string.IsNullOrEmpty(Record.GetAttribute("新學號")))
// strUpdateData += string.IsNullOrEmpty(strUpdateData) ? Record.GetAttribute("新學號") : "\n" + Record.GetAttribute("新學號");
// DyWb.Cells[DyWb_index, 16].PutValue(strUpdateData);
// //備註說明
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("備註"));
// // 2011 新承辦單位修正
// if(spcCode.Contains(Record.GetAttribute("異動代號")))
// {
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("新學號"));
// DyWb.Cells[DyWb_index, 16].PutValue("");
// }
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 23;
//頁面有幾個col
int col = 14;
//資料row數目
int dataRow = 16;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 23, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / dataRow) + 1;
//寫入代號
ws.Cells[index, 11].PutValue(source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別、年級
ws.Cells[index + 2, 1].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 5].PutValue(source.SelectSingleNode("@學年度").InnerText + " 學年度 第 " + source.SelectSingleNode("@學期").InnerText + " 學期");
ws.Cells[index + 2, 8].PutValue(list.SelectSingleNode("@科別").InnerText);
ws.Cells[index + 2, 12].PutValue(list.SelectSingleNode("@年級").InnerText + "年級");
//寫入資料
int recCount = 0;
int dataIndex = index + 6;
for (; currentPage <= totalPage; currentPage++)
{
//複製頁面
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
}
//填入資料 (2018/3/6 穎驊註解，list.ChildNodes.Count-1 因為要扣掉一個 異動名冊封面 資料)
for (int i = 0; i < dataRow && recCount < list.ChildNodes.Count - 1; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
if (rec.SelectSingleNode("@新學號") != null)
{
if (string.IsNullOrEmpty(rec.SelectSingleNode("@新學號").InnerText))
{
if (rec.SelectSingleNode("@學號") != null)
{
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText);
}
else
{
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@新學號").InnerText);
}
}
}
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@身分證號").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(rec.SelectSingleNode("@性別代號").InnerText);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@性別").InnerText);
ws.Cells[dataIndex, 5].PutValue(rec.SelectSingleNode("@出生年月日").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@轉入前學生資料_學校").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@轉入前學生資料_學號").InnerText + "\n" + rec.SelectSingleNode("@轉入前學生資料_科別").InnerText);
ws.Cells[dataIndex, 8].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@轉入前學生資料_備查日期").InnerText) + "\n" + rec.SelectSingleNode("@轉入前學生資料_備查文號").InnerText);
ws.Cells[dataIndex, 9].PutValue(rec.SelectSingleNode("@轉入前學生資料_年級").InnerText);
ws.Cells[dataIndex, 10].PutValue(rec.SelectSingleNode("@異動代號").InnerText);
ws.Cells[dataIndex, 11].PutValue(rec.SelectSingleNode("@原因及事項").InnerText);
ws.Cells[dataIndex, 12].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@異動日期").InnerText));
//ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@備註").InnerText);
if (rec.SelectSingleNode("@特殊身份代碼") != null)
{
ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
}
dataIndex++;
count++;
//轉入前學生資料_學校="糕忠高中" 轉入前學生資料_學號="010101" 轉入前學生資料_科別="資訊科" 轉入前學生資料_備查日期="90/09/09" 轉入前學生資料_備查文號="教中三字第09200909090號" 轉入前學生資料_年級="一上"
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells[dataIndex, 0].PutValue("合計 " + list.ChildNodes.Count.ToString() + " 名");
}
//分頁
ws.Cells[index + next - 1, 10].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + next, col);
//索引指向下一頁
index += next;
dataIndex = index + 6;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region 轉入生,電子格式
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
DyWb.Name = "轉入生名冊";
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_EndRow = TemplateWb.Cells.CreateRange(2, 1, false);
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、學號)
_data = (from data in _data orderby data.ClassType, data.GradeYear, data.DeptCode, data.StudentNumber select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
//學號
if (string.IsNullOrEmpty(rec.NewStudNumber))
{
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
}
else
{
DyWb.Cells[DyWb_index, 3].PutValue(rec.NewStudNumber);
}
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//轉入日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 轉入身分別
DyWb.Cells[DyWb_index, 13].PutValue(rec.TransferStatus);
//原備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.PreviousSchoolLastADDate);
//原備查文字(*)
DyWb.Cells[DyWb_index, 15].PutValue(rec.PreviousSchoolLastADDoc);
//原備查文號(*)
DyWb.Cells[DyWb_index, 16].PutValue(rec.PreviousSchoolLastADNum);
//原學校代碼(*)
DyWb.Cells[DyWb_index, 17].PutValue(rec.PreviousSchoolCode);
//原科別代碼
DyWb.Cells[DyWb_index, 18].PutValue(rec.PreviousDeptCode);
//原學號
DyWb.Cells[DyWb_index, 19].PutValue(rec.PreviousStudentNumber);
// 為支援舊結構年級與學期是用文字字串一上，所以這樣寫
//原年級
DyWb.Cells[DyWb_index, 20].PutValue(Getyear(rec.PreviousGradeYear));
//原學期
DyWb.Cells[DyWb_index, 21].PutValue(Getsemester(rec.PreviousSemester));
//備註說明
DyWb.Cells[DyWb_index, 22].PutValue(rec.Comment);
}
// 資料末底 加End
DyWb.Cells.CreateRange(DyWb_index + 1, 1, false).Copy(range_R_EndRow);
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
//2018/3/6 穎驊 新增轉入生 封面格式支援
//範本
Worksheet TemplateWb_Cover = wb.Worksheets["轉入生名冊封面範本"];
//實做頁面
Worksheet cover = wb.Worksheets[wb.Worksheets.Add()];
//名稱
cover.Name = "轉入生名冊封面";
string school_code = source.SelectSingleNode("@學校代號").InnerText;
string school_year = source.SelectSingleNode("@學年度").InnerText;
string school_semester = source.SelectSingleNode("@學期").InnerText;
//範圍
Range range_H_Cover = TemplateWb_Cover.Cells.CreateRange(0, 1, false);
//range_H_Cover
cover.Cells.CreateRange(0, 1, false).Copy(range_H_Cover);
Range range_R_cover = TemplateWb_Cover.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_cover_EndRow = TemplateWb_Cover.Cells.CreateRange(2, 1, false);
int cover_row_counter = 1;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//每增加一行,複製一次
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover);
string gradeYear = list.SelectSingleNode("@年級").InnerText;
string deptCode = list.SelectSingleNode("@科別代碼").InnerText;
//學校代碼
cover.Cells[cover_row_counter, 0].PutValue(school_code);
//學年度
cover.Cells[cover_row_counter, 1].PutValue(school_year);
//學期
cover.Cells[cover_row_counter, 2].PutValue(school_semester);
//年級
cover.Cells[cover_row_counter, 3].PutValue(gradeYear);
//科別代碼
cover.Cells[cover_row_counter, 6].PutValue(deptCode);
foreach (XmlElement st in list.SelectNodes("異動名冊封面"))
{
string reportType = st.SelectSingleNode("@名冊別").InnerText;
string classType = st.SelectSingleNode("@班別").InnerText;
string updateType = st.SelectSingleNode("@上傳類別").InnerText;
string approvedClassCount = st.SelectSingleNode("@核定班數").InnerText;
string approvedStudentCount = st.SelectSingleNode("@核定學生數").InnerText;
string actualClassCount = st.SelectSingleNode("@實招班數").InnerText;
string actualStudentCount = st.SelectSingleNode("@實招新生數").InnerText;
string originalStudentCount = st.SelectSingleNode("@原有學生數").InnerText;
string transferStudentCount = st.SelectSingleNode("@轉入學生數").InnerText;
string currentStudentCount = st.SelectSingleNode("@現有學生數").InnerText;
string remarks1 = st.SelectSingleNode("@註1").InnerText;
string remarksContent = st.SelectSingleNode("@備註說明").InnerText;
//名冊別
cover.Cells[cover_row_counter, 4].PutValue(reportType);
//班別
cover.Cells[cover_row_counter, 5].PutValue(classType);
//上傳類別
cover.Cells[cover_row_counter, 7].PutValue(updateType);
//核定班級
cover.Cells[cover_row_counter, 8].PutValue(approvedClassCount);
//核定學生數
cover.Cells[cover_row_counter, 9].PutValue(approvedStudentCount);
//實招班數
cover.Cells[cover_row_counter, 10].PutValue(actualClassCount);
//實招新生數
cover.Cells[cover_row_counter, 11].PutValue(actualStudentCount);
//原有學生數
cover.Cells[cover_row_counter, 12].PutValue(originalStudentCount);
//轉入學生數
cover.Cells[cover_row_counter, 13].PutValue(transferStudentCount);
//現有學生數
cover.Cells[cover_row_counter, 14].PutValue(currentStudentCount);
//註1
cover.Cells[cover_row_counter, 15].PutValue(remarks1);
//備註說明
cover.Cells[cover_row_counter, 16].PutValue(remarksContent);
}
cover_row_counter++;
}
// 資料末底 加End
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover_EndRow);
wb.Worksheets.RemoveAt("轉入生名冊封面範本");
wb.Worksheets.ActiveSheetIndex = 0;
//儲存
wb.Save(location, FileFormatType.Excel2003);
}