Example #1
0
        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);
        }
Example #3
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
        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);
        }
Example #11
0
        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);
        }