public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create a Workbook.
            // Open the excel file.
            Workbook wbk = new Aspose.Cells.Workbook(dataDir + "mergingcells.xls");

            // Create a Worksheet and get the first sheet.
            Worksheet worksheet = wbk.Worksheets[0];

            // Create a Cells object ot fetch all the cells.
            Cells cells = worksheet.Cells;

            // Unmerge the cells.
            cells.UnMerge(5, 2, 2, 3);

            // Save the file.
            wbk.Save(dataDir + "unmergingcells.out.xls");
            // ExEnd:1


        }
        public static void Run() 
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
            // Define memory stream object
            System.IO.MemoryStream objImage;

            // Define web client object
            System.Net.WebClient objwebClient;

            // Define a string which will hold the web image url
            string sURL = "http:// Www.aspose.com/Images/aspose-logo.jpg";

            try
            {
                // Instantiate the web client object
                objwebClient = new System.Net.WebClient();

                // Now, extract data into memory stream downloading the image data into the array of bytes
                objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));

                // Create a new workbook
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

                // Get the first worksheet in the book
                Aspose.Cells.Worksheet sheet = wb.Worksheets[0];

                // Get the first worksheet pictures collection
                Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;

                // Insert the picture from the stream to B2 cell
                pictures.Add(1, 1, objImage);

                // Save the excel file
                wb.Save(dataDir+ "webimagebook.out.xlsx");
            }
            catch (Exception ex)
            {
                // Write the error message on the console
                Console.WriteLine(ex.Message);
            }
            // ExEnd:1
            
            
        }
Пример #3
1
		static void WriteExcelFile (Dictionary<string, List<List<string>>> data, string fileName)
		{
			Console.WriteLine ("Escribiendo: " + fileName);
			using (Workbook book = new Aspose.Cells.Workbook ()) {
				foreach (var key in data.Keys) {
					var sheet = book.Worksheets.Add (key);
		
					var rows = data [key];
					for (int i = 0; i < rows.Count; i++) {
					
						for (int j = 0; j < rows [i].Count; j++) {
							sheet.Cells.Rows [i] [j].Value = rows [i] [j];
							if (i == 0) {
								var style = sheet.Cells.Rows [i] [j].GetStyle ();
								style.Font.IsBold = true;
								sheet.Cells.Rows [i] [j].SetStyle (style);
							}
						}
					}
				}

				book.Save (fileName);
			}
		}
Пример #4
0
        public DeleteExcelColumnResponse Process(string keyWordName, int rowIndex)
        {
            var result = new DeleteExcelColumnResponse();

            if (HttpContext.Current.Request.Files.Count == 0)
            {
                return(result);
            }
            var file  = HttpContext.Current.Request.Files[0];
            var excel = new Aspose.Cells.Workbook(file.InputStream);
            var sheet = excel.Worksheets[0];

            for (int i = 0; i < sheet.Cells.MaxDataColumn + 100; i++)
            {
                if (ExcelHelper.GetCellValue(sheet, rowIndex, i) == keyWordName)
                {
                    sheet.Cells.DeleteColumn(i, false);
                    i--;
                }
            }
            var directory     = HttpContext.Current.Request.PhysicalApplicationPath;
            var relativePath  = string.Concat("\\Files\\FinancialExcel\\", Guid.NewGuid().ToString("n"), ".xlsx");
            var filePath      = string.Concat(directory, relativePath);
            var directoryPath = Path.GetDirectoryName(filePath);

            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
            }
            excel.Save(filePath, SaveFormat.Xlsx);
            result.success  = true;
            result.FilePath = relativePath;
            return(result);
        }
Пример #5
0
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="datatable"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
        {
            error     = "";
            datatable = null;
            var p = System.Web.HttpContext.Current.Server.MapPath("~/") + filepath;

            try
            {
                if (File.Exists(p) == false)
                {
                    error     = "文件不存在";
                    datatable = null;
                    return(false);
                }
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                workbook.Open(p);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                return(true);
            }
            catch (System.Exception e)
            {
                error = e.Message;
                return(false);
            }
        }
Пример #6
0
        /// <summary>
        /// Excel文件转换为DataTable.(指定开始行列,以及导入的记录行数)
        /// </summary>
        /// <param name="filepath">Excel文件的全路径</param>
        /// <param name="datatable">DataTable:返回值</param>
        /// <param name="iFirstRow">起始行</param>
        /// <param name="iFirstCol">起始列</param>
        /// <param name="rowNum">导入行数</param>
        /// <param name="colNum">列数</param>
        /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
        /// <returns></returns>
        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, bool exportColumnName, out string error)
        {
            error     = "";
            datatable = null;
            try
            {
                if (File.Exists(filepath) == false)
                {
                    error     = "文件不存在";
                    datatable = null;
                    return(false);
                }
                Aspose.Cells.Workbook  workbook  = new Aspose.Cells.Workbook(filepath);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                datatable           = worksheet.Cells.ExportDataTableAsString(iFirstRow, iFirstCol, rowNum + 1, colNum + 1, exportColumnName);
                datatable.TableName = worksheet.Name;//记录Sheet的名称

                return(true);
            }
            catch (Exception ex)
            {
                error = ex.Message;
                LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
                return(false);
            }
        }
Пример #7
0
        /// <summary>
        /// 保存到文件
        /// </summary>
        /// <param name="stream">流</param>
        public void Save(Stream stream)
        {
            var xls   = new Aspose.Cells.Workbook();
            var sheet = xls.Worksheets[0];
            var style = this.GetHeaderStyle();

            for (var i = 0; i < this.fields.Count; i++)
            {
                var header = sheet.Cells[0, i];
                header.SetStyle(style, true);
                header.PutValue(this.fields[i].Name);
            }

            var row = 0;

            foreach (var model in this.Models)
            {
                row = row + 1;
                for (var i = 0; i < this.fields.Count; i++)
                {
                    var fieldValue = this.fields[i].GetValue(model);
                    sheet.Cells[row, i].PutValue(fieldValue);
                }
            }

            sheet.AutoFitColumns();
            xls.Save(stream, Aspose.Cells.SaveFormat.Excel97To2003);
        }
 /// <summary>
 /// Excel文件转换为DataTable.
 /// </summary>
 /// <param name="filepath">Excel文件的全路径</param>
 /// <param name="datatable">DataTable:返回值</param>
 /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
 /// <returns>true:函数正确执行 false:函数执行错误</returns>
 public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
 {
     error     = "";
     datatable = null;
     try
     {
         if (File.Exists(filepath) == false)
         {
             error     = "文件不存在";
             datatable = null;
             return(false);
         }
         Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
         workbook.Open(filepath);
         Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
         datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);
         //-------------图片处理-------------
         Aspose.Cells.Pictures pictures = worksheet.Pictures;
         if (pictures.Count > 0)
         {
             string error2 = "";
             if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
             {
                 error = error + error2;
             }
         }
         return(true);
     }
     catch (System.Exception e)
     {
         error = e.Message;
         return(false);
     }
 }
Пример #9
0
 /// <summary>
 /// 无须电脑安装Excel,读取Excel文件
 /// </summary>
 /// <param name="excelPath"></param>
 /// <returns></returns>
 public static System.Data.DataTable GetExcelDataTableByAspose(string excelPath)
 {
     Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(excelPath);
     Aspose.Cells.Cells    cells    = workbook.Worksheets[0].Cells;
     System.Data.DataTable dt       = cells.ExportDataTableAsString(0, 0, cells.MaxRow + 1, cells.MaxColumn + 1, true);
     return(dt);
 }
Пример #10
0
        private void Dispose()
        {
            A_workbook = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        public void AsposeXls()
        {
            var wb = new Aspose.Cells.Workbook(file);

            foreach (var ws in wb.Worksheets)
            {
                var cells    = ws.Cells;
                var rowCount = cells.GetLastDataRow(0);
                foreach (Aspose.Cells.Row row in cells.Rows)
                {
                    foreach (Aspose.Cells.Cell ce in row)
                    {
                        switch (ce.Type)
                        {
                        case CellValueType.IsString:
                            var s = ce.StringValue;
                            break;

                        case CellValueType.IsNumeric:
                            var n = ce.DoubleValue;
                            break;
                        }
                    }
                }
            }
        }
Пример #12
0
 /// <summary>
 /// 打开指定路径excel
 /// </summary>
 /// <param name="path">excel 文件路径</param>
 public void Open(string path)
 {
     if (!string.IsNullOrEmpty(path))
     {
         wb = new Aspose.Cells.Workbook(path);
     }
 }
Пример #13
0
        public static void Main(string[] args)
        {
            //ExStart:1
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


            try
            {
                //Get the template excel file path.
                string designerFile = dataDir + "SampleInput.xlsx";
                //Specify the pdf file path.
                string pdfFile = dataDir + "Output.out.pdf";
                //Create a new Workbook.
                //Open the template excel file which you have to
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
                //Save the pdf file.
                wb.Save(pdfFile, SaveFormat.Pdf);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            //ExEnd:1
        }
Пример #14
0
        /// <summary>
        /// 生成Excel文件流
        /// </summary>
        /// <param name="titles"></param>
        /// <param name="fields"></param>
        /// <param name="list"></param>
        /// <param name="saveFormat"></param>
        /// <param name="isPropertyNameShown"></param>
        /// <param name="firstRow"></param>
        /// <param name="firstColumn"></param>
        /// <param name="insertRows"></param>
        /// <param name="dateFormatString"></param>
        /// <param name="convertStringToNumber"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] GenerateExcelFileStream(string[] titles, string[] fields, ICollection list
                                                     , SaveFormat saveFormat, bool isPropertyNameShown, int firstRow, int firstColumn
                                                     , bool insertRows, string dateFormatString, bool convertStringToNumber, string sheetName = "Sheet1")
        {
            AsposeXls.Workbook  workbook = new AsposeXls.Workbook();
            AsposeXls.Worksheet sheet    = workbook.Worksheets[0];
            sheet.Name = sheetName;

            sheet.FreezePanes(1, 1, 1, 0); //冻结第一行

            for (int i = 0; i < titles.Length; i++)
            {
                sheet.Cells[0, i].PutValue(titles[i]);
            }

            sheet.Cells.ImportCustomObjects(list, fields, isPropertyNameShown, firstRow, firstColumn,
                                            list.Count, insertRows, dateFormatString, convertStringToNumber);

            sheet.AutoFitColumns();//让各列自适应宽度,这个很有用
            using (MemoryStream stream = new MemoryStream())
            {
                workbook.Save(stream, saveFormat);
                return(stream.ToArray());
            }
        }
        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.FileName = "常模轉換訊息";
            saveFileDialog1.Filter   = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() != DialogResult.OK)
            {
                return;
            }

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            DataTable             dt = new DataTable();

            dt.Columns.Add("班級");
            dt.Columns.Add("座號");
            dt.Columns.Add("姓名");
            dt.Columns.Add("訊息");

            foreach (FitInfo each in _list)
            {
                DataRow row = dt.NewRow();
                row["班級"] = each._class;
                row["座號"] = each._seatno;
                row["姓名"] = each._name;
                row["訊息"] = each._info;
                dt.Rows.Add(row);
            }
            wb.Worksheets[0].Cells.ImportDataTable(dt, true, "A1");
            wb.Save(saveFileDialog1.FileName, FileFormatType.Xlsx);
            System.Diagnostics.Process.Start(saveFileDialog1.FileName);
        }
Пример #16
0
        static void WriteExcelFile(Dictionary <string, List <List <string> > > data, string fileName)
        {
            Console.WriteLine("Escribiendo: " + fileName);
            using (Workbook book = new Aspose.Cells.Workbook()) {
                foreach (var key in data.Keys)
                {
                    var sheet = book.Worksheets.Add(key);

                    var rows = data [key];
                    for (int i = 0; i < rows.Count; i++)
                    {
                        for (int j = 0; j < rows [i].Count; j++)
                        {
                            sheet.Cells.Rows [i] [j].Value = rows [i] [j];
                            if (i == 0)
                            {
                                var style = sheet.Cells.Rows [i] [j].GetStyle();
                                style.Font.IsBold = true;
                                sheet.Cells.Rows [i] [j].SetStyle(style);
                            }
                        }
                    }
                }

                book.Save(fileName);
            }
        }
Пример #17
0
        /// <summary>
        /// Excel文件转换为DataTable.(指定开始行列,以及导入的记录行数)
        /// </summary>
        /// <param name="filepath">Excel文件的全路径</param>
        /// <param name="datatable">DataTable:返回值</param>
        /// <param name="iFirstRow">起始行</param>
        /// <param name="iFirstCol">起始列</param>
        /// <param name="rowNum">导入行数</param>
        /// <param name="colNum">列数</param>
        /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
        /// <returns></returns>
        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, bool exportColumnName, out string error)
        {
            error     = "";
            datatable = null;
            try
            {
                if (File.Exists(filepath) == false)
                {
                    error     = "文件不存在";
                    datatable = null;
                    return(false);
                }
                Aspose.Cells.Workbook  workbook  = new Aspose.Cells.Workbook(filepath);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                datatable           = worksheet.Cells.ExportDataTableAsString(iFirstRow, iFirstCol, rowNum + 1, colNum + 1, exportColumnName);
                datatable.TableName = worksheet.Name;//记录Sheet的名称

                return(true);
            }
            catch (System.Exception e)
            {
                error = e.Message;
                return(false);
            }
        }
        ///<Summary>
        /// ConvertXlsToSvg method to convert XLS file to SVG
        ///</Summary>
        public Response ConvertXlsToSvg(string fileName, string folderName)
        {
            return(ProcessTask(fileName, folderName, ".svg", true, true, delegate(string inFilePath, string outPath, string zipOutFolder)
            {
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(inFilePath);

                ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
                imgOptions.SaveFormat = SaveFormat.SVG;
                imgOptions.OnePagePerSheet = true;
                int sheetCount = workbook.Worksheets.Count;
                foreach (var sheet in workbook.Worksheets)
                {
                    Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);
                    int srPageCount = sr.PageCount;
                    for (int i = 0; i < sr.PageCount; i++)
                    {
                        string outfileName = "";
                        if ((sheetCount > 1) || (srPageCount > 1))
                        {
                            outfileName = sheet.Name + "_" + (i + 1) + ".svg";
                            outPath = zipOutFolder + "/" + outfileName;
                        }
                        else
                        {
                            outfileName = System.IO.Path.GetFileNameWithoutExtension(fileName) + ".svg";
                            outPath = zipOutFolder + "/" + outfileName;
                        }
                        sr.ToImage(i, outPath);
                    }
                }
            }));
        }
        /// <summary>
        ///DataSet导出到Excel文件
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="fileName"></param>
        public static void ExportToExcel(DataSet ds, string fileName)
        {
            Aspose.Cells.Workbook book = new Aspose.Cells.Workbook();
            book.Worksheets.Clear();
            int tbCount = ds.Tables.Count;

            if (tbCount > 0)
            {
                for (int i = 0; i < tbCount; i++)
                {
                    string sheetName = ds.Tables[i].TableName;
                    book.Worksheets.Add(sheetName);
                    Aspose.Cells.Worksheet sheet;
                    sheet      = book.Worksheets[i];
                    sheet.Name = sheetName;
                    sheet.IsGridlinesVisible = true;

                    //AddBody(ds.Tables[i]);
                    for (int r = 0; r < ds.Tables[i].Rows.Count; r++)
                    {
                        for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
                        {
                            sheet.Cells[0, c].PutValue(ds.Tables[i].Columns[c].ColumnName);//列标题
                            sheet.Cells[r + 1, c].PutValue(ds.Tables[i].Rows[r][c].ToString());
                        }
                    }

                    sheet.AutoFitColumns();
                    sheet.AutoFitRows();
                }
            }

            book.Save(fileName, Aspose.Cells.FileFormatType.Excel2007Xlsx);
        }
 /// <summary>
 /// 自己定义的
 /// </summary>
 public static bool Ecu_Id_DataTableToExcel(DataTable datatable, string filepath, out string error)
 {
     //string file = Application.StartupPath + "\\13id\\" +"id.xls";
     //filepath = file;
     error = "";
     try
     {
         if (datatable == null)
         {
             error = "DataTableToExcel:datatable 为空";
             return(false);
         }
         Aspose.Cells.Workbook  workbook = new Aspose.Cells.Workbook();
         Aspose.Cells.Worksheet sheet    = workbook.Worksheets[0];
         Aspose.Cells.Cells     cells    = sheet.Cells;
         cells.SetColumnWidth(4, 40);
         cells.SetColumnWidth(3, 40);
         Aspose.Cells.Style style = new Style();
         style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
         style.Pattern             = BackgroundType.Solid;
         style.Font.IsBold         = true;
         StyleFlag a = new StyleFlag();
         a.WrapText          = true;
         style.IsTextWrapped = true;           //文本换行 这里很重要的;有助于格式的的显示内容
         cells.ApplyStyle(style, a);
         cells.ImportDataTable(datatable, true, "A5");
         workbook.Save(filepath);
     }
     catch (System.Exception e)
     {
         error = error + " DataTableToExcel: " + e.Message;
         return(false);
     }
     return(true);
 }
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            try
            {

                //Get the template excel file path.
                string designerFile = dataDir + "SampleInput.xlsx";
                //Specify the pdf file path.
                string pdfFile = dataDir + "Output.out.pdf";
                //Create a new Workbook.
                //Open the template excel file which you have to
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
                //Save the pdf file.
                wb.Save(pdfFile, SaveFormat.Pdf);

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();

            }
        }
Пример #22
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            //Open an Excel file
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(dataDir + "Book1.xls");

            //Get the first worksheet
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

            //Apply different Image and Print options
            Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

            //Set the Format
            options.SaveFormat = SaveFormat.XPS;

            //Render the sheet with respect to specified printing options
            Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);

            // Save
            sr.ToImage(0, dataDir + "out_printingxps.xps");

            //Export the whole workbook to XPS
            Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);
            wr.ToImage(dataDir + "out_whole_printingxps.xps");
        }
        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            try
            {
                // Get the template excel file path.
                string designerFile = dataDir + "SampleInput.xlsx";

                // Specify the pdf file path.
                string pdfFile = dataDir + "Output.out.pdf";

                // Open the template excel file
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);

                // Save the pdf file.
                wb.Save(pdfFile, SaveFormat.Pdf);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            // ExEnd:1
        }
Пример #24
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            try
            {

                //Get the template excel file path.
                string designerFile = dataDir + "SampleInput.xlsx";
                //Specify the pdf file path.
                string pdfFile = dataDir + "Output.pdf";
                //Create a new Workbook.
                //Open the template excel file which you have to
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(designerFile);
                //Save the pdf file.
                wb.Save(pdfFile, SaveFormat.Pdf);

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();

            }
        }
        public static void Run()
        {
            string dataDir = RunExamples.GetDataDir_Charts();
            //ExStart:SetChartDataFromWorkBook
            Presentation pres = new Presentation(dataDir + "Test.pptx");

            IChart chart = pres.Slides[0].Shapes.AddChart(ChartType.Pie, 50, 50, 500, 400);

            chart.ChartData.ChartDataWorkbook.Clear(0);

            Workbook workbook = null;

            try
            {
                workbook = new Aspose.Cells.Workbook("a1.xlsx");
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            MemoryStream mem = new MemoryStream();

            workbook.Save(mem, Aspose.Cells.SaveFormat.Xlsx);

            chart.ChartData.WriteWorkbookStream(mem);

            chart.ChartData.SetRange("Sheet1!$A$1:$B$9");
            IChartSeries series = chart.ChartData.Series[0];

            series.ParentSeriesGroup.IsColorVaried = true;
            pres.Save(dataDir + "response2.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
            //ExEnd:SetChartDataFromWorkBook
        }
Пример #26
0
        public void OutputMemoExcel(string v_excelPath)
        {
            Excel.Workbook   book  = new Excel.Workbook(v_excelPath);
            Excel.Worksheet  sheet = book.Worksheets["CString"];
            ExcelSheetObject eso   = new ExcelSheetObject(sheet, "CString");

            eso.init_header();
            int row = 3;

            foreach (string words in _words)
            {
                eso.set_vali("Id", row, row - 2);
                eso.set_vals("Key", row, words);
                if (_data.ContainsKey(words))
                {
                    Dictionary <string, string> language = _data[words];
                    foreach (string lanHeader in language.Keys)
                    {
                        if (lanHeader != "Id")
                        {
                            eso.set_vals(lanHeader, row, language[lanHeader]);
                        }
                    }
                }
                row = row + 1;
            }
            book.Save(v_excelPath);
        }
 public static bool GetPicturesFromExcelFile(string filepath, out Pictures[] pictures, out string error)
 {
     error    = "";
     pictures = null;
     try
     {
         if (File.Exists(filepath) == false)
         {
             error    = "文件不存在";
             pictures = null;
             return(false);
         }
         Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
         workbook.Open(filepath);
         pictures = new Pictures[workbook.Worksheets.Count];
         for (int i = 0; i < workbook.Worksheets.Count; i++)
         {
             //pictures.Add();
             pictures[i] = workbook.Worksheets[i].Pictures;
         }
         return(true);
     }
     catch (System.Exception e)
     {
         error = e.Message;
         return(false);
     }
 }
Пример #28
0
    /// <summary>
    /// 仅读取Excel中的数据
    /// </summary>
    /// <param name="filepath">文件路径</param>
    /// <param name="isallsheet">是否读取多个Sheet</param>
    /// <returns></returns>
    public static System.Text.StringBuilder ReadExcelDataList(string filepath, bool isallsheet)
    {
        ex.Workbook workbook      = new ex.Workbook(filepath);
        var         sheetDataList = workbook.Worksheets;

        if (isallsheet)
        {
            System.Text.StringBuilder sbSheetList = new System.Text.StringBuilder();
            sbSheetList.Append("[");
            for (int i = 0; i < sheetDataList.Count; i++)
            {
                var sheetdata = sheetDataList[i];
                var cells     = sheetdata.Cells;
                if (cells.Count > 0)
                {
                    DataTable dt = cells.ExportDataTable(0, 0, cells.MaxRow + 1, cells.MaxColumn + 1, false);
                    dt.TableName = sheetDataList[i].CodeName;

                    sbSheetList.Append(TableToJSON(dt) + ",");
                }
            }
            sbSheetList.Remove(sbSheetList.Length - 1, 1);
            sbSheetList.Append("]");
            return(sbSheetList);
        }
        else
        {
            var       sheetdata = sheetDataList[0];
            var       cells     = sheetdata.Cells;
            DataTable dt        = cells.ExportDataTable(0, 0, cells.MaxRow + 1, cells.MaxColumn + 1, false);
            return(TableToJSON(dt));
        }
    }
        /// <summary>
        ///DataTable导出到Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        public static void ExportToExcel(DataTable dt, string fileName, bool isTextWrapped = false)
        {
            Aspose.Cells.Worksheet sheet;
            Aspose.Cells.Workbook  book = new Aspose.Cells.Workbook();
            sheet = book.Worksheets[0];
            sheet.IsGridlinesVisible = true;
            //AddBody(dt);

            if (dt.Rows.Count == 0)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    sheet.Cells[0, c].PutValue(dt.Columns[c].ColumnName);//列标题
                }
            }
            else
            {
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        sheet.Cells[0, c].PutValue(dt.Columns[c].ColumnName);//列标题
                        sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                        if (isTextWrapped && sheet.Cells[r + 1, c].Style != null)
                        {
                            sheet.Cells[r + 1, c].Style.IsTextWrapped = true;
                        }
                    }
                }
            }

            sheet.AutoFitColumns();
            sheet.AutoFitRows();
            book.Save(fileName, Aspose.Cells.FileFormatType.Excel2007Xlsx);
        }
        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Open an Excel file
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(dataDir + "Book1.xls");

            // Get the first worksheet
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

            // Apply different Image and Print options
            Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

            // Set the Format
            options.SaveFormat = SaveFormat.XPS;

            // Render the sheet with respect to specified printing options
            Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);

            // Save
            sr.ToImage(0, dataDir + "out_printingxps.out.xps");

            // Export the whole workbook to XPS
            Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);
            wr.ToImage(dataDir + "out_whole_printingxps.out.xps");
            // ExEnd:1
        }
Пример #31
0
    /// <summary>
    /// 转pdf
    /// </summary>
    /// <param name="inputPath">word路径</param>
    /// <param name="pdfPath">pdf路径</param>
    /// <returns></returns>
    public static bool ConverToPdf(string inputPath, string pdfPath)
    {
        try
        {
            FileInfo f1   = new FileInfo(inputPath);
            string   ext1 = f1.Extension.ToLower().Substring(1);

            switch (ext1)
            {
            case "xls":
            case "xlsx":
                Aspose.Cells.Workbook book1 = new Aspose.Cells.Workbook(inputPath);
                book1.Save(pdfPath, Aspose.Cells.SaveFormat.Pdf);
                break;

            case "doc":
            case "docx":
                Aspose.Words.Document doc = new Aspose.Words.Document(inputPath);
                doc.Save(pdfPath);
                break;

            default:
                break;
            }
        }
        catch (Exception ex1)
        {
            return(false);
        }

        return(true);
    }
Пример #32
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            //Open an Excel file
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(dataDir + "Book1.xls");

            //Get the first worksheet
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

            //Apply different Image and Print options
            Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
            
            //Set the Format
            options.SaveFormat = SaveFormat.XPS;
            
            //Render the sheet with respect to specified printing options
            Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);
            
            // Save
            sr.ToImage(0, dataDir + "out_printingxps.xps");

            //Export the whole workbook to XPS
            Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);
            wr.ToImage(dataDir + "out_whole_printingxps.xps");
        }
Пример #33
0
        public Form1()
        {
            InitializeComponent();

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

            //合併欄位
            wb.Worksheets[0].Cells.Merge(0, 0, 1, 30);

            labelX1.Text = StringSplit(labelX1.Text, 12);

            //SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
            //sd.Title = "另存新檔";
            //sd.FileName = "日常生活表現總表.xls";
            //sd.Filter = "Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
            //if (sd.ShowDialog() == DialogResult.OK)
            //{
            //    try
            //    {
            //        wb.Save(sd.FileName, FileFormatType.Excel2003);
            //        System.Diagnostics.Process.Start(sd.FileName);

            //    }
            //    catch
            //    {
            //        MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            //    }
            //}
        }
Пример #34
0
        private void write(List <List <CellEntity> > content)
        {
            try
            {
                String result = txtSavePath.Text.Trim();
                Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook();
                Worksheet             sheet = excel.Worksheets[0];

                for (int rowIndex = 0; rowIndex < content.Count; rowIndex++)
                {
                    List <CellEntity> row = content[rowIndex];
                    for (int colIndex = 0; colIndex < row.Count; colIndex++)
                    {
                        //Cell cell = sheet.Cells[currentRowIndex, colIndex];
                        // sheet.Cells.
                        CellEntity cellEntity = row[colIndex];
                        //sheet.Cells[currentRowIndex, colIndex].SetStyle(cellEntity.Style);

                        sheet.Cells[rowIndex, colIndex].Copy(cellEntity.Cell);
                        sheet.Cells[rowIndex, colIndex].PutValue(cellEntity.Cell.Value);
                        //sheet.Cells[currentRowIndex].Formula
                        //sheet.Cells[currentRowIndex, colIndex] = row[colIndex];
                        //sheet.Cells[currentRowIndex, colIndex].PutValue(row[colIndex]);
                    }
                }
                sheet.AutoFitColumns();
                excel.Save(result, FileFormatType.Excel2007Xlsx);
                MessageBox.Show("success");
            }
            catch (Exception)
            {
                throw;
            }
        }
Пример #35
0
        private void process()
        {
            excel.Workbook  workbook = new excel.Workbook(this.ucFilesAndButtons1.TxbTemplateFileName.Text);
            excel.Worksheet sheet    = workbook.Worksheets[0];
            int             count    = sheet.TextBoxes.Count();

            for (int i = 0; i < count; i++)
            {
                excel.Drawing.TextBox t = sheet.TextBoxes[i];
                if (t.Text == "二维码")
                {
                    t.HasLine = false;
                    t.Text    = "";
                    int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
                    excel.Drawing.Picture picture = sheet.Pictures[x];
                    picture.LeftToCorner = t.LeftToCorner;
                    picture.TopToCorner  = t.TopToCorner;
                    picture.Width        = t.Width;
                    picture.Height       = t.Height;
                    break;
                }
            }
            //excel.Drawing.TextBox txb = sheet.TextBoxes[0];
            //txb.HasLine = false;
            //txb.Text = "123";
            //int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
            //excel.Drawing.Picture picture = sheet.Pictures[x];
            //picture.LeftToCorner = txb.LeftToCorner;
            //picture.TopToCorner = txb.TopToCorner;
            //picture.Width = txb.Width;
            //picture.Height = txb.Height;
            workbook.Save(this.ucFilesAndButtons1.TxbExportFileName.Text);
            workbook.Save(this.ucFilesAndButtons1.TxbPdf.Text);
            MessageBox.Show("ok");
        }
Пример #36
0
 /// <summary>
 /// 获取Excel文件里面的图片,并把图片存储到图片对象列表中
 /// </summary>
 /// <param name="filepath">Excel文件的全路径</param>
 /// <param name="pictures">图片对象列表</param>
 /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
 /// <returns></returns>
 public static bool GetPicturesFromExcelFile(string filepath, out PictureCollection[] pictures, out string error)
 {
     error    = "";
     pictures = null;
     try
     {
         if (File.Exists(filepath) == false)
         {
             error    = "文件不存在";
             pictures = null;
             return(false);
         }
         Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
         pictures = new Aspose.Cells.Drawing.PictureCollection[workbook.Worksheets.Count];
         for (int i = 0; i < workbook.Worksheets.Count; i++)
         {
             //pictures.Add();
             pictures[i] = workbook.Worksheets[i].Pictures;
         }
         return(true);
     }
     catch (Exception ex)
     {
         error = ex.Message;
         LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
         return(false);
     }
 }
Пример #37
0
        private void btnOptDesign_Click(object sender, EventArgs e)
        {
            try
            {
                for (int i = 0; i < Config.designer_opt_configs.Count; i++)
                {
                    Output_designer_config optRequest       = Config.designer_opt_configs[i];
                    Excel.Workbook         src_book         = new Excel.Workbook(optRequest.src_path);
                    Excel.Worksheet        src_sheet        = src_book.Worksheets[optRequest.src_sheet];
                    SheetHeader            src_sheet_header = new SheetHeader();
                    src_sheet_header.readHeader(src_sheet, optRequest.src_head_row);
                    src_sheet_header.readDataWithIndex(optRequest.src_pm_key, optRequest.src_row_beg);

                    Excel.Workbook  tar_book         = new Excel.Workbook(optRequest.tar_path);
                    Excel.Worksheet tar_sheet        = tar_book.Worksheets[optRequest.tar_sheet];
                    SheetHeader     tar_sheet_header = new SheetHeader();
                    tar_sheet_header.readHeader(tar_sheet, optRequest.tar_head_row);
                    tar_sheet_header.readDataWithIndex(optRequest.tar_pm_key, optRequest.tar_row_beg);
                    ExcelTableConvert.convert(src_sheet_header, tar_sheet_header, optRequest.src_cols, optRequest.tar_cols);

                    tar_book.Save(optRequest.tar_path);
                }
            }
            catch (Exception ex) { Debug.Error(ex.ToString()); }

            Debug.Info("导出完成");
        }
        private void btnMerge_Click(object sender, System.EventArgs e)
        {
            //Create a Workbook.
            Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook();

            //Create a Worksheet and get the first sheet.
            Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];

            //Create a Cells object ot fetch all the cells.
            Aspose.Cells.Cells cells = worksheet.Cells;

            //Merge some Cells (C6:E7) into a single C6 Cell.
            cells.Merge(5, 2, 2, 3);

            //Input data into C6 Cell.
            worksheet.Cells[5, 2].PutValue("This is my value");

            //Create a Style object to fetch the Style of C6 Cell.
            Aspose.Cells.Style style = worksheet.Cells[5, 2].GetStyle();

            //Create a Font object
            Aspose.Cells.Font font = style.Font;

            //Set the name.
            font.Name = "Times New Roman";

            //Set the font size.
            font.Size = 18;

            //Set the font color
            font.Color = Color.Blue;

            //Bold the text
            font.IsBold = true;

            //Make it italic
            font.IsItalic = true;

            //Set the backgrond color of C6 Cell to Red
            style.ForegroundColor = Color.Red;

            style.Pattern = BackgroundType.Solid;

            //Apply the Style to C6 Cell.
            cells[5, 2].SetStyle(style);

            //Save the excel file
            wbk.Save(HttpContext.Current.Response,"MergeCells.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));

            // End response to avoid unneeded html after xls
            Response.End();
        }
Пример #39
0
        /// <summary>
        /// DataTableToExcel
        /// DataTabel转换成Excel文件
        ///
        /// </summary>
        /// <param name="datatable">DataTable</param>
        /// <param name="filepath">目标文件路径,Excel文件的全路径<</param>
        /// <param name="error">错误信息:返回错误信息,没有错误返回""</param>
        /// <returns>true:函数正确执行 false:函数执行错误</returns>
        public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
        {
            error = "";
            if (datatable == null)
            {
                error = "DataTableToExcel:datatable 为空";
                return false;
            }

            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            workbook.Open(fromfile);
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
            Aspose.Cells.Cells cells = sheet.Cells;
            //-------------插入数据-------------
            int nRow = 0;
            foreach (DataRow row in datatable.Rows)
            {
                nRow++;

                try
                {
                    cells.InsertRow(beginRow);
                    for (int i = 0; i < colNameList.Count; i++)
                    {
                        string colName = colNameList[i].ToString();
                        for (int j = 0; j < datatable.Columns.Count; j++)
                        {
                            if (colName == datatable.Columns[j].ColumnName)
                            {
                                object temp = row[datatable.Columns[j].ColumnName];
                                cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
                                break;
                            }
                        }
                    }
                }
                catch (System.Exception e)
                {
                    error = error + " DataTableInsertToExcel: " + e.Message;
                }

            }
            //-------------保存-------------
            workbook.Save(fromfile);
            return true;
        }
    public void CreateStaticReport()
    {
        //Define memory stream object
        System.IO.MemoryStream objImage;

        //Define web client object
        System.Net.WebClient objwebClient;

        //Define a string which will hold the web image url
        string sURL = "http://www.xlsoft.com/jp/products/aspose/images/Aspose_Cells-Product-Box.jpg";

        //Instantiate the web client object
        objwebClient = new System.Net.WebClient();

        //Now, extract data into memory stream downloading the image data into the array of bytes
        objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));

        //Create a new workbook
        Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

        //Get the first worksheet in the book
        Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

        //Get the first worksheet pictures collection
        Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures;

        //Insert the picture from the stream to B2 cell
        pictures.Add(1, 1, objImage);

        if (ddlFileVersion.SelectedItem.Value == "XLS")
        {
            ////Save file and send to client browser using selected format
            workbook.Save(HttpContext.Current.Response, "AddingImageFromWeb.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
        }
        else
        {
            workbook.Save(HttpContext.Current.Response, "AddingImageFromWeb.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
        }

        //end response to avoid unneeded html
        HttpContext.Current.Response.End();
    }
Пример #41
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            //Create a Workbook.
            //Open the excel file.
            Workbook wbk = new Aspose.Cells.Workbook(dataDir + "mergingcells.xls");

            //Create a Worksheet and get the first sheet.
            Worksheet worksheet = wbk.Worksheets[0];

            //Create a Cells object ot fetch all the cells.
            Cells cells = worksheet.Cells;

            //Unmerge the cells.
            cells.UnMerge(5, 2, 2, 3);

            //Save the file.
            wbk.Save(dataDir + "unmergingcells.xls");
        }
        protected override void Build(System.Xml.XmlElement source, string location)
        {
            #region 建立 Excel

            //從 Resources 將學籍異動名冊template讀出來
            Workbook template = new Workbook();
            template.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);

            //產生 excel
            Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);

            #endregion

            #region 複製樣式-預設樣式、欄寬

            //設定預設樣式
            wb.DefaultStyle = template.DefaultStyle;

            //複製樣版中前18個 Column(欄寬)
            for (int m = 0; m < 18; m++)
            {
                /*
                 * 複製 template的第一個 Sheet的第 m個 Column
                 * 到 wb的第一個 Sheet的第 m個 Column
                 */
                wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
            }

            #endregion

            #region 初始變數

            /******************************
                * rowi 填入學校資料用
                * rowj 填入學生資料用
                * num 計算清單份數
                * numcount 計算每份清單頁數
                * j 計算所產生清單頁數
                * x 判斷個數是否為20被數用
                ******************************/
            int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
            bool x = false;

            int recCount = 0;
            int totalRec = source.SelectNodes("清單/異動紀錄").Count;

            #endregion

            foreach (XmlNode list in source.SelectNodes("清單"))
            {
                int i = 0;

                #region 找出資料總數及判斷

                //找出資料總數方便評估進度
                int count = list.SelectNodes("異動紀錄").Count;

                //判斷個數是否為20被數
                if (count % 20 == 0)
                {
                    x = true;
                }

                #endregion

                #region 異動紀錄

                //將xml資料填入至excel
                foreach (XmlElement st in list.SelectNodes("異動紀錄"))
                {
                    recCount++;
                    if (i % 20 == 0)
                    {
                        #region 複製樣式-欄高、範圍

                        //複製樣版中前287個 Row(欄高)
                        //for (int m = 0; m < 28; m++)
                        //{
                        //    /*
                        //     * 複製 template的第一個 Sheet的第m個 Row
                        //     * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
                        //     */
                        //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
                        //}

                        /*
                         * 複製Style(包含儲存格合併的資訊)
                         * 先用CreateRange()選取要複製的Range("A1", "R28")
                         * 再用CopyStyle複製另一個Range中的格式
                         */
                        Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
                        int t = j * 28;
                        wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);

                        #endregion

                        #region 填入學校資料

                        //將學校資料填入適當的位置內
                        wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
                        wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
                        wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
                        wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
                        wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
                        wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);

                        #endregion

                        if (j > 0)
                        {
                            //插入分頁(在 j * 28 跟 (j * 28) +1 中間,R跟S中間)
                            wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
                            rowj += 8;
                        }
                        else
                        {
                            rowj = 6;
                        }

                        rowi += 28;
                        j++;

                        #region 顯示頁數

                        //顯示頁數
                        if (x != true)
                        {
                            wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁,共" + Math.Ceiling((double)count / 20) + "頁");
                        }
                        else
                        {
                            wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁,共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
                        }
                        numcount++;

                        #endregion
                    }

                    #region 填入學生資料

                    string updatecode = st.SelectSingleNode("@異動代號").InnerText;

                    ////將學生資料填入適當的位置內
                    //if (NewStudentNumberCodes.Contains(updatecode))
                    //{

                    //    string strNum = "";
                    //    if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
                    //        strNum = st.SelectSingleNode("@新學號").InnerText;
                    //    else
                    //    {
                    //        string sid = "";
                    //        if (!string.IsNullOrEmpty(st.SelectSingleNode("@學生編號").InnerText))
                    //            sid = st.SelectSingleNode("@學生編號").InnerText;
                    //        List<string> ids = new List<string>();
                    //        ids.Add(sid);
                    //        SHSchool.Data.SHStudent.RemoveByIDs(ids);

                    //        SHSchool.Data.SHStudentRecord stud = SHSchool.Data.SHStudent.SelectByID(sid);
                    //        strNum = stud.StudentNumber;
                    //    }
                    //    wb.Worksheets[0].Cells[rowj, 1].PutValue(strNum);
                    //}
                    //else
                        wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);

                    wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
                    wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
                    wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
                    wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);

                    //wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + (string.IsNullOrEmpty(st.GetAttribute("更正後資料")) ? "" : "\n" + st.GetAttribute("更正後資料")));

                    string UpdateData = "";
                    if (st.SelectSingleNode("@新資料")!=null)
                    {
                        // 更正學號填到另一格
                        if (updatecode != "401")
                            UpdateData = st.SelectSingleNode("@新資料").InnerText;
                    }

                    wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText+"\n"+UpdateData);

                    string strUpdateDate = Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText);

                        //假設有異動學生學號的類別才出現新學號字樣
                        if (st.SelectSingleNode("@新學號")!=null)
                            if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
                            {
                                int newNo;
                                if (int.TryParse(st.SelectSingleNode("@新學號").InnerText, out newNo))
                                    strUpdateDate = newNo + "\n" + strUpdateDate;
                            }
                            else
                            {
                                // 更正學號
                                if (updatecode == "401")
                                {
                                    if (st.SelectSingleNode("@新資料") != null)
                                        if (!string.IsNullOrEmpty(st.SelectSingleNode("@新資料").InnerText))
                                            strUpdateDate = st.SelectSingleNode("@新資料").InnerText + "\n" + strUpdateDate;
                                }
                            }

                    wb.Worksheets[0].Cells[rowj, 13].PutValue(strUpdateDate);
                    if(st.SelectSingleNode("@特殊身份代碼") !=null )
                        wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
                    //wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);

                    #endregion

                    i++;
                    rowj++;

                    //回報進度
                    ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
                }

                #endregion

                #region 若個數為20倍數,處理單一頁面

                if (x == true)
                {

                    #region 複製樣式-欄高、範圍

                    //複製樣版前28個 Row(欄高)
                    //for (int m = 0; m < 28; m++)
                    //{
                    //    /*
                    //     * 複製 template的第一個 Sheet的第m個 Row
                    //     * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
                    //     */
                    //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
                    //}

                    /*
                     * 複製Style(包含儲存格合併的資訊)
                     * 先用CreateRange()選取要複製的Range("A1", "R28")
                     * 再用CopyStyle複製另一個Range中的格式
                     */
                    Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
                    int t = j * 28;
                    wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);

                    #endregion

                    #region 填入學校資料

                    //將學校資料填入適當的位置內
                    wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
                    wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
                    wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
                    wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
                    wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
                    wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);

                    #endregion

                    if (j > 0)
                    {
                        //插入分頁(在i跟i+1中間,O跟P中間)
                        wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
                        rowj += 8;
                    }

                    rowi += 28;
                    j++;

                    #region 顯示頁數

                    //顯示頁數
                    wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁,共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
                    numcount++;

                    #endregion
                }

                #endregion

                #region 統計人數

                //填入統計人數
                wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
                wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
                wb.Worksheets[0].Cells[rowj, 1].PutValue("合  計 " + count.ToString() + " 名");

                #endregion

                wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);

                #region 設定變數

                //調整新清單所使用變數
                numcount = 1;
                rowj = (28 * j) - 2;
                rowi = (28 * j);
                x = false;

                #endregion
            }

            // 因2010年格式不同小修改

            #region 學籍異動電子格式
            //範本
            Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
            //實做頁面
            Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
            //名稱
            DyWb.Name = "電子格式";
            //範圍
            Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
            Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
            //拷貝range_H
            DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);

            int DyWb_index = 0;
            // 遇到特殊異動代碼要處理
            List<string> spcCode = new List<string>();
            spcCode.Add("211");

            DAL.DALTransfer DALTranser = new DAL.DALTransfer();

            // 格式轉換
            List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);

            // 排序 (依 班別、年級、科別代碼、異動代碼)
            _data = (from data in _data orderby data.ClassType, GYear(data.GradeYear), data.DeptCode, data.UpdateCode select data).ToList();

            foreach(GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data )
            {
                DyWb_index++;
                //每增加一行,複製一次
                DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);

                //班別
                DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
                //科別代碼
                DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);

                // 2 放上傳類別,請使用者自填

                //學號
                DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
                //姓名
                DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
                //身分證字號
                DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);

                //註1
                DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);

                //性別代碼
                DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
                //出生日期
                DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);

                //特殊身份代碼
                DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
                //年級
                DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
                //異動原因代碼
                DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
                //異動日期
                DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);

                // 異動順序
                DyWb.Cells[DyWb_index, 13].PutValue(rec.Order);

                //備查日期
                DyWb.Cells[DyWb_index, 14].PutValue(rec.LastADDate);
                //備查文字
                DyWb.Cells[DyWb_index, 15].PutValue(rec.LastADDoc);
                //備查文號
                DyWb.Cells[DyWb_index, 16].PutValue(rec.LastADNum);

                //更正後資料
                string strUpdateData = string.Empty;

                //若是更正後資料有值則填入更正後資料
                if (!string.IsNullOrEmpty(rec.NewData))
                    strUpdateData = rec.NewData;

                //若是新學號中有值則填入新學號
                //判斷strUpdateData是否已有值,若是已有值則加入斷行符號
                if (!string.IsNullOrEmpty(rec.NewStudNumber))
                    strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;

                DyWb.Cells[DyWb_index, 17].PutValue(strUpdateData);

                // 註2
                DyWb.Cells[DyWb_index, 18].PutValue(rec.Comment2);

                //備註說明
                DyWb.Cells[DyWb_index, 19].PutValue(rec.Comment);

                // 2011 新承辦單位修正,轉科讀取新學號
                if (NewStudentNumberCodes.Contains(rec.UpdateCode))
                {
                    List<string> ids = new List<string>();
                    ids.Add(rec.StudentID);
                    SHSchool.Data.SHStudent.RemoveByIDs(ids);
                    SHSchool.Data.SHStudentRecord studRec = SHSchool.Data.SHStudent.SelectByID(rec.StudentID);
                    if(studRec !=null)
                        DyWb.Cells[DyWb_index, 3].PutValue(studRec.StudentNumber);
                    DyWb.Cells[DyWb_index, 17].PutValue("");
                }
            }

            //foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
            //{
            //    DyWb_index++;
            //    //每增加一行,複製一次
            //    DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);

            //    //班別
            //    DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
            //    //科別代碼
            //    DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));

            //    // 2 放上傳類別,請使用者自填

            //    //學號
            //    DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
            //    //姓名
            //    DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
            //    //身分證字號
            //    DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));

            //    //註1
            //    DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("註1"));

            //    //性別代碼
            //    DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
            //    //出生日期
            //    DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("出生年月日")));

            //    //特殊身份代碼
            //    DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身份代碼")); //原為抓取備註欄位
            //    //年級
            //    DyWb.Cells[DyWb_index, 10].PutValue((Record.ParentNode as XmlElement).GetAttribute("年級"));
            //    //異動原因代碼
            //    DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("異動代號"));
            //    //異動日期
            //    DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("異動日期")));
            //    //原備查日期
            //    DyWb.Cells[DyWb_index, 13].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("備查日期")));
            //    //原備查文字
            //    DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt1(Record.GetAttribute("備查文號")));
            //    //原備查文號
            //    DyWb.Cells[DyWb_index, 15].PutValue(GetNumAndSrt2(Record.GetAttribute("備查文號")));

            //    // 捨棄
            //    ////舊班別
            //    //DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("舊班別"));
            //    ////舊科別代碼
            //    //DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("舊科別代碼"));

            //    //更正後資料
            //    string strUpdateData = string.Empty;

            //    //若是更正後資料有值則填入更正後資料
            //    if (!string.IsNullOrEmpty(Record.GetAttribute("更正後資料")))
            //        strUpdateData = Record.GetAttribute("更正後資料");

            //    //若是新學號中有值則填入新學號
            //    //判斷strUpdateData是否已有值,若是已有值則加入斷行符號
            //    if (!string.IsNullOrEmpty(Record.GetAttribute("新學號")))
            //        strUpdateData += string.IsNullOrEmpty(strUpdateData) ? Record.GetAttribute("新學號") : "\n" + Record.GetAttribute("新學號");

            //    DyWb.Cells[DyWb_index, 16].PutValue(strUpdateData);
            //    //備註說明
            //    DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("備註"));

            //    // 2011 新承辦單位修正
            //    if(spcCode.Contains(Record.GetAttribute("異動代號")))
            //    {
            //        DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("新學號"));
            //        DyWb.Cells[DyWb_index, 16].PutValue("");
            //    }
            //}

            DyWb.AutoFitColumns();

            wb.Worksheets.RemoveAt("電子格式範本");

            #endregion

            wb.Worksheets.ActiveSheetIndex = 0;
            //儲存 Excel
            wb.Save(location, FileFormatType.Excel2003);
        }
        protected override void Build(XmlElement source, string location)
        {
            Workbook template = new Workbook();

            //�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);
        }
Пример #44
0
        //列印
        private void btnPrint_Click(object sender, EventArgs e)
        {
            //0. 使用者選擇檔名
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "另存新檔";
            sfd.FileName = "座位表_" + this.layout.ClassRoomName +  "_" + this.expandablePanel1.TitleText + ".xls";
            sfd.Filter = "Excel 2003 相容檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
            DialogResult dr = sfd.ShowDialog();
            if (dr != System.Windows.Forms.DialogResult.OK)
                return;

            //1. Open Excel File
            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            //  讀取樣版檔
            if (this.layout.GetUDTLayout().ExcelTemplate =="B")
                wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100B));
            else
                wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100A));

            //  讀取樣版工作表
            Worksheet templateSheet = wb.Worksheets[0];

            // 複製樣版
            int instanceSheetIndex = wb.Worksheets.AddCopy("範本");
            Worksheet instanceSheet = wb.Worksheets[instanceSheetIndex];
            instanceSheet.Name = this.expandablePanel1.TitleText;

            //填入學生座位
            foreach (string studID in this.stud_coords.Keys)
            {
                //取得學生資料
                if (this.students.ContainsKey(studID))
                {
                    SeatTableStudent stud = this.students[studID];

                    //取得學生位置座標
                    CellCoordinate coord = this.stud_coords[studID];

                    //將座標轉換為 Excel 格子位置
                    int offsetX = coord.X;
                    int offsetY = (this.layout.YCount - coord.Y -1) * 2 + 1 ;

                    //放置照片
                    if (stud.Photo != null)
                    {
                        MemoryStream ms = new MemoryStream();
                        stud.Photo.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                        Cell cell = instanceSheet.Cells[offsetY, offsetX];
                        instanceSheet.Pictures.Add(cell.Row, cell.Column, cell.Row + 1, cell.Column + 1, ms);
                    }
                    //寫入姓名
                    instanceSheet.Cells[offsetY+1, offsetX].PutValue(stud.Name);
                }

            } //end of foreach loop

            try
            {
                wb.Worksheets.RemoveAt("範本");
                wb.Save(sfd.FileName);
                if (System.IO.File.Exists(sfd.FileName))
                    System.Diagnostics.Process.Start(sfd.FileName);
            }
            catch (Exception ex)
            {
                Util.ShowMsg(ex.Message,"注意");
            }
        }
        protected override void Build(System.Xml.XmlElement source, string location)
        {
            #region �إ� Excel

            //�q Resources �N���y���ʦW�UtemplateŪ�X��
            Workbook template = new Workbook();
            template.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);

            //���� excel
            Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);

            #endregion

            #region �ƻs�˦�-�w�]�˦��B��e

            //�]�w�w�]�˦�
            wb.DefaultStyle = template.DefaultStyle;

            //�ƻs�˪����e18�� Column(��e)
            for (int m = 0; m < 18; m++)
            {
                /*
                 * �ƻs template���Ĥ@�� Sheet���� m�� Column
                 * �� wb���Ĥ@�� Sheet���� m�� Column
                 */
                wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
            }

            #endregion

            #region ��l�ܼ�

                /******************************
                * rowi ��J�Ǯո�ƥ�
                * rowj ��J�ǥ͸�ƥ�
                * num �p��M�����
                * numcount �p��C���M�歶��
                * j �p��Ҳ��ͲM�歶��
                * x �P�_�ӼƬO�_��20�Q�ƥ�
                ******************************/
                int rowi = 0, rowj = 1, num = source.SelectNodes("�M��").Count, numcount = 1, j = 0;
                bool x = false;

                int recCount = 0;
                int totalRec = source.SelectNodes("�M��/���ʬ���").Count;

            #endregion

            foreach (XmlNode list in source.SelectNodes("�M��"))
            {
                int i = 0;

                #region ��X����`�ƤΧP�_

                //��X����`�Ƥ�K����i��
                int count = list.SelectNodes("���ʬ���").Count;

                //�P�_�ӼƬO�_��20�Q��
                if (count % 20 == 0)
                {
                    x = true;
                }

                #endregion

                #region ���ʬ���

                //�Nxml��ƶ�J��excel
                foreach (XmlNode st in list.SelectNodes("���ʬ���"))
                {
                    recCount++;
                    if (i % 20 == 0)
                    {
                        #region �ƻs�˦�-�氪�B�d��

                        //�ƻs�˪����e287�� Row(�氪)
                        //for (int m = 0; m < 28; m++)
                        //{
                        //    /*
                        //     * �ƻs template���Ĥ@�� Sheet����m�� Row
                        //     * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
                        //     */
                        //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
                        //}

                        /*
                         * �ƻsStyle(�]�t�x�s��X�֪���T)
                         * ����CreateRange()����n�ƻs��Range("A1", "R28")
                         * �A��CopyStyle�ƻs�t�@��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 ��J�Ǯո��

                        //�N�Ǯո�ƶ�J�A�����m��
                        wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
                        wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
                        wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").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("@��O").InnerText);
                        wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@�~��").InnerText);

                        #endregion

                        if (j > 0)
                        {
                            //���J����(�b j * 28 �� (j * 28) +1 �����AR��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 + "���A�@" + Math.Ceiling((double)count / 20) + "��");
                        }
                        else
                        {
                            wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
                        }
                        numcount++;

                        #endregion
                    }

                    #region ��J�ǥ͸��

                        //�N�ǥ͸�ƶ�J�A�����m��
                        wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@�Ǹ�").InnerText);
                        wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@�m�W").InnerText);
                        wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@�����Ҹ�").InnerText);
                        wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + st.SelectSingleNode("@�Ƭd�帹").InnerText);
                        wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@���ʥN��").InnerText);
                        wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@��]�Ψƶ�").InnerText);
                        if (st.SelectSingleNode("@�s�Ǹ�").InnerText == "")
                        {
                            wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
                        }
                        else
                        {
                            wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@�s�Ǹ�").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
                        }
                            //wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�Ƶ�").InnerText);
                    if(st.SelectSingleNode("@�S������N�X")!=null)
                        wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�S������N�X").InnerText);

                    #endregion

                    i++;
                    rowj++;

                    //�^���i��
                    ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
                }

                #endregion

                #region �Y�ӼƬ�20���ơA�B�z��@����

                if (x == true)
                {

                    #region �ƻs�˦�-�氪�B�d��

                    //�ƻs�˪��e28�� Row(�氪)
                    //for (int m = 0; m < 28; m++)
                    //{
                    //    /*
                    //     * �ƻs template���Ĥ@�� Sheet����m�� Row
                    //     * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
                    //     */
                    //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
                    //}

                    /*
                     * �ƻsStyle(�]�t�x�s��X�֪���T)
                     * ����CreateRange()����n�ƻs��Range("A1", "R28")
                     * �A��CopyStyle�ƻs�t�@��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 ��J�Ǯո��

                    //�N�Ǯո�ƶ�J�A�����m��
                    wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
                    wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
                    wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").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("@��O").InnerText);

                    #endregion

                    if (j > 0)
                    {
                        //���J����(�bi��i+1�����AO��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 + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
                    numcount++;

                    #endregion
                }

                #endregion

                #region �έp�H��

                //��J�έp�H��
                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("�X  �p " + count.ToString() + " �W");

                #endregion

                wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);

                #region �]�w�ܼ�

                //�վ�s�M��Ҩϥ��ܼ�
                numcount = 1;
                rowj = (28 * j) - 2;
                rowi = (28 * j);
                x = false;

                #endregion
            }

            Worksheet mdws = wb.Worksheets[1];
            mdws.Name = "�q�l�榡";

            int mdws_index = 0;
            foreach (XmlElement record in source.SelectNodes("�M��/���ʬ���"))
            {
                mdws_index++;
                // �����~�Ǧ~��
                mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("�����~�Ǧ~��"));
                mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("�Z�O"));
                mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("��O�N��"));
                mdws.Cells[mdws_index, 3].PutValue("");

                mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("�Ǹ�"));
                mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("�m�W"));
                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("�ʧO�N��"));
                mdws.Cells[mdws_index, 9].PutValue((BL.Util.ConvertDate1(record.GetAttribute("�X�ͦ~���"))));
                mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("�S������N�X")); //�쬰�����������
                mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("���ʥN��"));
                mdws.Cells[mdws_index, 12].PutValue(BL.Util.ConvertDate1(record.GetAttribute("���ʤ��")));

                mdws.Cells[mdws_index, 13].PutValue(BL.Util.ConvertDate1(record.GetAttribute("�Ƭd���")));

                mdws.Cells[mdws_index, 14].PutValue(BL.Util.GetDocNo_Doc(record.GetAttribute("�Ƭd�帹")));
                mdws.Cells[mdws_index, 15].PutValue(BL.Util.GetDocNo_No(record.GetAttribute("�Ƭd�帹")));
                mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("�Ƶ�"));
            }

            mdws.AutoFitColumns();
            mdws.Cells.SetColumnWidth(5, 8.5);
            mdws.Cells.SetColumnWidth(11, 20);

            wb.Worksheets.ActiveSheetIndex = 0;

            //�x�s Excel
            wb.Save(location, FileFormatType.Excel2003);
        }
Пример #46
0
 public static bool GetPicturesFromExcelFile(string filepath, out Pictures[] pictures, out string error)
 {
     error = "";
     pictures = null;
     try
     {
         if (File.Exists(filepath) == false)
         {
             error = "文件不存在";
             pictures = null;
             return false;
         }
         Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
         workbook.Open(filepath);
         pictures = new Pictures[workbook.Worksheets.Count];
         for (int i = 0; i < workbook.Worksheets.Count; i++)
         {
             //pictures.Add();
             pictures[i] = workbook.Worksheets[i].Pictures;
         }
         return true;
     }
     catch (System.Exception e)
     {
         error = e.Message;
         return false;
     }
 }
        private void buttonX1_Click(object sender, EventArgs e)
        {
            this.textBoxX2.Text = "";

            this.addMsg(" ==== 開始匯入課程 ===");

            if (string.IsNullOrWhiteSpace(this.textBoxX1.Text))
                return;

            //1. 讀取所有科目 (供比對出科目代碼)
            this.GetAllSubjects();

            //2. 讀取某學年度學期的所有課程 (決定要新增或修改)
            this.GetAllCourses();

            //3. 開始讀取Excel 上的課程資料,
            Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(this.textBoxX1.Text);
            Worksheet ws = wb.Worksheets[1];   //課程
            int rowIndex = 1;
            while (ws.Cells[rowIndex, 0].Value != null)
            {
                string courseCode = GetCellValue(ws.Cells[rowIndex, 1].Value);
                string classCode = GetCellValue(ws.Cells[rowIndex, 2].Value);
                if (classCode.Length > 2)
                    classCode = classCode.Substring(1, 2);
                string credit = GetCellValue(ws.Cells[rowIndex, 3].Value);
                string courseName = GetCellValue(ws.Cells[rowIndex, 4].Value);
                string semester = GetCellValue(ws.Cells[rowIndex, 5].Value);
                string schoolyear = GetCellValue(ws.Cells[rowIndex, 6].Value);

                string subjID = "0";
                if (!this.dicSubjects.ContainsKey(courseCode))
                    this.addMsg( string.Format("匯入課程時找不到 subject ID, code : {0}, class name :{1} , course name : {2}, schoolyear: {3}, semester :{4}  ",
                                                                                                                    courseCode, classCode, courseName, schoolyear, semester));
                else
                {
                    subjID = this.dicSubjects[courseCode].UID;

                    string key = string.Format("{0}_{1}", courseCode, classCode);
                    //檢查資料庫中是否已有存在?如果存再就修改,否則新增。
                    bool isCourseExisted = false;
                    if (this.dicCourseExts.ContainsKey(key))
                    {
                        UDT.CourseExt cExt = this.dicCourseExts[key];
                        if (this.dicCourses.ContainsKey(cExt.CourseID.ToString()))
                            isCourseExisted = true;
                    }

                    if (isCourseExisted)
                    {
                        UDT.CourseExt cExt = this.dicCourseExts[key];
                        cExt.ClassName = classCode;
                        cExt.SubjectCode = courseCode;
                        cExt.SubjectID = int.Parse(subjID);
                        List<ActiveRecord> recs = new List<ActiveRecord>();
                        recs.Add(cExt);
                        (new AccessHelper()).UpdateValues(recs);

                        SHCourseRecord course = this.dicCourses[cExt.CourseID.ToString()];
                        course.Credit = decimal.Parse(credit);
                        course.Name = string.Format("{0} {1}", courseName, classCode);
                        course.SchoolYear = int.Parse(schoolyear);
                        course.Semester = int.Parse(semester);
                        SHCourse.Update(course);
                    }
                    else
                    {
                        SHCourseRecord course = new SHCourseRecord();
                        course.Credit = decimal.Parse(credit);
                        course.Name = string.Format("{0} {1}", courseName, classCode);
                        course.SchoolYear = int.Parse(schoolyear);
                        course.Semester = int.Parse(semester);
                        string newID = SHCourse.Insert(course);

                        UDT.CourseExt cExt = new UDT.CourseExt();
                        cExt.CourseID = int.Parse(newID);
                        cExt.ClassName = classCode;
                        cExt.SubjectCode = courseCode;
                        cExt.SubjectID = int.Parse(subjID);
                        List<ActiveRecord> recs = new List<ActiveRecord>();
                        recs.Add(cExt);
                        (new AccessHelper()).InsertValues(recs);

                    }
                } //if find subject id
                rowIndex += 1;
                this.lblStatus.Text = rowIndex.ToString() ;
                Application.DoEvents();

            } // while loop

            //匯入修課學生
            this.ImportSCAttendRecords();
        }
        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);
        }
Пример #49
0
        public int ProcessDocument()
        {
            AccessHelper helper = new AccessHelper(); //helper物件用來取得ischool相關資料。
            //建立Workbook物件
            SpecialStudentBook = new Aspose.Cells.Workbook();

            Aspose.Cells.Worksheet SpecialStudentSheet = SpecialStudentBook.Worksheets[0];
            //取得使用者所選取的班級。
            List<ClassRecord> classes = helper.ClassHelper.GetSelectedClass();

            //德行加減分Helper
            SmartSchool.Evaluation.AngelDemonComputer computer = new SmartSchool.Evaluation.AngelDemonComputer(); ;

            int row;
            decimal Score;
            int period;
            Boolean SpecialStudent;
            RewardStatistics RS;
            string description = "";
            SpecialStudentSheet.Cells[0, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolChineseName);
            if (mconfig.Semester == true && mconfig.MequialD == false)
                description = "(在學期間累計)";
            if (mconfig.MequialD == true && mconfig.Semester == true)
                description = "(在學期間累計且功過相抵)";
            if (mconfig.MequialD == true && mconfig.Semester == false)
                description = "(功過相抵)";
            switch (mconfig.ReportType)
            {
                case 1:

                    SpecialStudentSheet.Cells[1, 0].PutValue("小過三支以上" + description);
                    break;
                case 2:
                    SpecialStudentSheet.Cells[1, 0].PutValue("出缺勤扣分超過21分者");
                    break;
                case 3:
                    SpecialStudentSheet.Cells[1, 0].PutValue("德行成績不及格");
                    break;
                case 4:
                    SpecialStudentSheet.Cells[1, 0].PutValue("滿三大過" + description);
                    break;
                case 5:
                    SpecialStudentSheet.Cells[1, 0].PutValue("全學期缺席節次超過" + mconfig.Period + "節");
                    break;
                case 6:
                    SpecialStudentSheet.Cells[1, 0].PutValue("留校查看名單");
                    break;
            }

            SpecialStudentSheet.Cells[2, 0].PutValue("班級");
            SpecialStudentSheet.Cells[2, 1].PutValue("學號");
            SpecialStudentSheet.Cells[2, 2].PutValue("座號");
            SpecialStudentSheet.Cells[2, 3].PutValue("姓名");
            SpecialStudentSheet.Cells[2, 4].PutValue("大功");
            SpecialStudentSheet.Cells[2, 5].PutValue("小功");
            SpecialStudentSheet.Cells[2, 6].PutValue("嘉獎");
            SpecialStudentSheet.Cells[2, 7].PutValue("大過");
            SpecialStudentSheet.Cells[2, 8].PutValue("小過");
            SpecialStudentSheet.Cells[2, 9].PutValue("警告");
            for (int i = 0; i <= 9; i++)
            {
                SpecialStudentSheet.Cells.Merge(2, i, 2, 1);
                SpecialStudentSheet.Cells[2, i].Style.HorizontalAlignment = TextAlignmentType.Center;
                SpecialStudentSheet.Cells[2, i].Style.VerticalAlignment = TextAlignmentType.Center;
            }
            //取得缺曠類別

            #region 建立字典&範本

            Dictionary<string, int> AttList = new Dictionary<string, int>();
            Dictionary<string, string> dicPeriodType = new Dictionary<string, string>();
            List<string> PeriodType = new List<string>();
            List<string> AbsenceC = new List<string>();
            int test1 = 10;
            //SmartSchool.Feature.Basic.Config.GetPeriodList().GetContent();
            foreach (XmlElement var in SmartSchool.Feature.Basic.Config.GetPeriodList().GetContent().GetElements("Period"))
            {
                if (!dicPeriodType.ContainsKey(var.GetAttribute("Name")))
                    dicPeriodType.Add(var.GetAttribute("Name"), var.GetAttribute("Type"));
                //string name = var.GetAttribute("Name"); //節次名稱
                //string type = var.GetAttribute("Type"); //類型
            }

            SmartSchool.Customization.Data.SystemInformation.getField("Absence");
            XmlElement absenceXml = SmartSchool.Customization.Data.SystemInformation.Fields["Absence"] as XmlElement;

            foreach (XmlElement absence in absenceXml.SelectNodes("Absence"))
                AbsenceC.Add(absence.GetAttribute("Name"));

            SmartSchool.Customization.Data.SystemInformation.getField("Period");
            absenceXml = SmartSchool.Customization.Data.SystemInformation.Fields["Period"] as XmlElement;

            foreach (XmlElement absence in absenceXml.SelectNodes("Period"))
                if (!PeriodType.Contains(absence.GetAttribute("Type")))
                    PeriodType.Add(absence.GetAttribute("Type"));

            //foreach (SmartSchool.Evaluation.AngelDemonComputer.UsefulPeriodAbsence var in computer.UsefulPeriodAbsences)
            //{
            //    if (!PeriodType.Contains(var.Period))
            //        PeriodType.Add(var.Period);
            //    if (!AbsenceC.Contains(var.Absence))
            //        AbsenceC.Add(var.Absence);
            //    if (!AttList.ContainsKey (var.Period + var.Absence))
            //        AttList.Add(var.Period + var.Absence,0);
            //}
            foreach (string p in PeriodType)
            {
                SpecialStudentSheet.Cells[2, test1].PutValue(p);
                foreach (string a in AbsenceC)
                {
                    SpecialStudentSheet.Cells[3, test1].PutValue(a);
                    AttList.Add(p + a, 0);
                    test1++;
                }
                SpecialStudentSheet.Cells.Merge(2, test1 - AbsenceC.Count, 1, AbsenceC.Count);
                SpecialStudentSheet.Cells[2, test1 - AbsenceC.Count].Style.HorizontalAlignment = TextAlignmentType.Center;
                SpecialStudentSheet.Cells[2, test1 - AbsenceC.Count].Style.VerticalAlignment = TextAlignmentType.Center;
            }

            #endregion
            if (mconfig.ReportType == 3)
            {
                SpecialStudentSheet.Cells[2, test1].PutValue("德行");
                SpecialStudentSheet.Cells.Merge(2, test1, 2, 1);
                SpecialStudentSheet.Cells[2, test1].Style.HorizontalAlignment = TextAlignmentType.Center;
                SpecialStudentSheet.Cells[2, test1].Style.VerticalAlignment = TextAlignmentType.Center;
                test1 += 1;
            }
            //循訪班級記錄
            row = 4;
            foreach (ClassRecord crecord in classes)
            {
                //將班級學生填入獎懲記錄
                helper.StudentHelper.FillReward(crecord.Students);

                //將班級學生填入缺曠記錄
                if (mconfig.Semester == true)
                    helper.StudentHelper.FillAttendance(crecord.Students);
                else
                    helper.StudentHelper.FillAttendance(intSchoolYear.Value, (cboSemester.Text == "上學期" ? 1 : 2), crecord.Students);
                //填入學生學期分項成績
                helper.StudentHelper.FillSemesterEntryScore(false, crecord.Students);
                //取得該班學生資料,要排序不能使用 crecord.Students,改用Students
                List<StudentRecord> Students = crecord.Students;
                //將學生依學號排序
                if (mconfig.SortKey == "StudentNumber")
                    Students.Sort(CompareSNum);

                //循訪每位學生記錄,並建立SpecialStudent清單來產生報表

                foreach (StudentRecord student in Students)
                {
                    Score = 0;
                    SpecialStudent = false;
                    period = 0;
                    //計算獎懲次數
                    if (mconfig.Semester == true)
                        RS = new RewardStatistics(student.RewardList, 0, mconfig);
                    else
                        if (chkDate.Checked)
                            RS = new RewardStatistics(student.RewardList, 2, mconfig);
                        else
                            RS = new RewardStatistics(student.RewardList, 1, mconfig);
                    switch (mconfig.ReportType)
                    {
                        case 1:  //小過三支以上
                            if (mconfig.MequialD == true)
                                if ((RS.FaultACount * 9 + RS.FaultBCount * 3 + RS.FaultCCount - RS.AwardACount * 9 - RS.AwardBCount * 3 - RS.AwardCCount) >= 9)
                                    SpecialStudent = true;
                                else
                                    SpecialStudent = false;
                            else
                                if ((RS.FaultACount * 9 + RS.FaultBCount * 3 + RS.FaultCCount) >= 9)
                                    SpecialStudent = true;
                                else
                                    SpecialStudent = false;
                            break;

                        case 2:   //出缺勤扣分超過21分者
                            //計算缺曠節次
                            foreach (AttendanceInfo w in student.AttendanceList)
                            {
                                if (w.PeriodType == "")
                                {
                                    if (dicPeriodType.ContainsKey(w.Period))
                                        if (AttList.ContainsKey(dicPeriodType[w.Period] + w.Absence))
                                            AttList[dicPeriodType[w.Period] + w.Absence]++;
                                }
                                else
                                    if (AttList.ContainsKey(w.PeriodType + w.Absence))
                                        AttList[w.PeriodType + w.Absence]++;
                            }
                            // 計算缺曠扣分
                            foreach (string p in PeriodType)
                                foreach (string a in AbsenceC)
                                    Score = Score + computer.ComputeAttendanceScore(p, a, AttList[p + a]);
                            if (Score <= -21)
                                SpecialStudent = true;
                            else
                            {
                                SpecialStudent = false;
                                foreach (string p in PeriodType)
                                    foreach (string a in AbsenceC)
                                        AttList[p + a] = 0;
                            }
                            break;
                        case 3:   //德行成績不及格
                            Score = 0;

                            List<SemesterEntryScoreInfo> SemScore = new List<SemesterEntryScoreInfo>();
                            SemScore = student.SemesterEntryScoreList;
                            if (SemScore.Count > 0)
                                foreach (SemesterEntryScoreInfo si in SemScore)
                                    if (si.SchoolYear == intSchoolYear.Value && si.Semester == (cboSemester.Text == "上學期" ? 1 : 2) && si.Entry == "德行")
                                        Score = si.Score;
                            //helper.StudentHelper.FillField("本學期德行表現成績", student);
                            //XmlElement element = student.Fields["本學期德行表現成績"] as XmlElement;
                            //if (element != null && decimal.TryParse (element.GetAttribute("Score"),out Score))
                            //    Score = decimal.Parse(element.GetAttribute("Score"));
                            if (Score < 60)
                                SpecialStudent = true;
                            else
                                SpecialStudent = false;

                            break;
                        case 4:    //大過三支以上
                            if (mconfig.MequialD == true)
                                if ((RS.FaultACount * 9 + RS.FaultBCount * 3 + RS.FaultCCount - RS.AwardACount * 9 - RS.AwardBCount * 3 - RS.AwardCCount) >= 27)
                                    SpecialStudent = true;
                                else
                                    SpecialStudent = false;
                            else
                                if ((RS.FaultACount * 9 + RS.FaultBCount * 3 + RS.FaultCCount) >= 27)
                                    SpecialStudent = true;
                                else
                                    SpecialStudent = false;
                            break;
                        case 5: //全學期缺席節次超過指定節次
                            foreach (AttendanceInfo w in student.AttendanceList)
                                if (mconfig.AbsenceList.Contains(w.Absence) == true)
                                    if (mconfig.Date)
                                    {
                                        if (w.OccurDate >= mconfig.StartDate && w.OccurDate <= mconfig.EndDate)
                                            period++;
                                    }
                                    else
                                        period++;
                            if (period > mconfig.Period)
                                SpecialStudent = true;
                            else
                                SpecialStudent = false;
                            break;
                        case 6: //留校查看
                            List<RewardInfo> RewardInfo = new List<RewardInfo>();
                            RewardInfo = student.RewardList;
                            SpecialStudent = false;
                            int SchoolYear = intSchoolYear.Value;
                            int Semester = (cboSemester.Text == "上學期" ? 1 : 2);
                            foreach (RewardInfo r in RewardInfo)
                            {
                                if (r.SchoolYear == SchoolYear && r.Semester == Semester && r.UltimateAdmonition == true)
                                {
                                    if (mconfig.Date)
                                    {
                                        if (r.OccurDate >= mconfig.StartDate && r.OccurDate <= mconfig.EndDate)
                                            SpecialStudent = true;
                                    }
                                    else
                                        SpecialStudent = true;
                                }

                            }
                            break;

                    }
                    //符合特殊學生資格
                    if (SpecialStudent == true)
                    {
                        SpecialStudentSheet.Cells[row, 0].PutValue(student.RefClass.ClassName);
                        SpecialStudentSheet.Cells[row, 1].PutValue(student.StudentNumber);
                        SpecialStudentSheet.Cells[row, 2].PutValue(student.SeatNo);
                        SpecialStudentSheet.Cells[row, 3].PutValue(student.StudentName);
                        SpecialStudentSheet.Cells[row, 4].PutValue(RS.AwardACount);
                        SpecialStudentSheet.Cells[row, 5].PutValue(RS.AwardBCount);
                        SpecialStudentSheet.Cells[row, 6].PutValue(RS.AwardCCount);
                        SpecialStudentSheet.Cells[row, 7].PutValue(RS.FaultACount);
                        SpecialStudentSheet.Cells[row, 8].PutValue(RS.FaultBCount);
                        SpecialStudentSheet.Cells[row, 9].PutValue(RS.FaultCCount);
                        #region 處理缺曠內容
                        //計算缺曠節次
                        if (mconfig.ReportType != 2)
                        {
                            #region 2012/2/4號,dylan修改
                            foreach (AttendanceInfo w in student.AttendanceList)
                            {
                                //if (mconfig.Date)
                                //{
                                //    if (w.OccurDate >= mconfig.StartDate && w.OccurDate <= mconfig.EndDate)
                                //        if (AttList.ContainsKey(w.PeriodType + w.Absence))
                                //            AttList[w.PeriodType + w.Absence]++;
                                //}
                                //else
                                if (mconfig.Date) //如果勾選了"依日期區間累計"
                                {
                                    //判斷是否為日期區間內的日期
                                    if (w.OccurDate >= mconfig.StartDate && w.OccurDate <= mconfig.EndDate)
                                    {
                                        if (w.PeriodType == "")
                                        {
                                            if (dicPeriodType.ContainsKey(w.Period))
                                                if (AttList.ContainsKey(dicPeriodType[w.Period] + w.Absence))
                                                    AttList[dicPeriodType[w.Period] + w.Absence]++;
                                        }
                                        else
                                            if (AttList.ContainsKey(w.PeriodType + w.Absence))
                                                AttList[w.PeriodType + w.Absence]++;
                                    }
                                }
                                else //沒勾選為預設行為
                                {
                                    if (w.PeriodType == "")
                                    {
                                        if (dicPeriodType.ContainsKey(w.Period))
                                            if (AttList.ContainsKey(dicPeriodType[w.Period] + w.Absence))
                                                AttList[dicPeriodType[w.Period] + w.Absence]++;
                                    }
                                    else
                                        if (AttList.ContainsKey(w.PeriodType + w.Absence))
                                            AttList[w.PeriodType + w.Absence]++;
                                }
                            }
                            #endregion
                        }
                        int DicNum = 10;
                        //
                        foreach (string p in PeriodType)
                            foreach (string a in AbsenceC)
                            {
                                SpecialStudentSheet.Cells[row, DicNum].PutValue(AttList[p + a]);
                                DicNum++;
                            }
                        foreach (string p in PeriodType)
                            foreach (string a in AbsenceC)
                                AttList[p + a] = 0;
                        #endregion
                        if (mconfig.ReportType == 3)
                            SpecialStudentSheet.Cells[row, DicNum].PutValue(Score);
                        row++;
                    }

                }
            }
            if (mconfig.ReportType == 3)
            {
                SpecialStudentSheet.Cells.Merge(0, 0, 1, AttList.Count + 11);//合併儲存格
                SpecialStudentSheet.Cells.Merge(1, 0, 1, AttList.Count + 11);
            }
            else
            {
                SpecialStudentSheet.Cells.Merge(0, 0, 1, AttList.Count + 10);//合併儲存格
                SpecialStudentSheet.Cells.Merge(1, 0, 1, AttList.Count + 10);
            }
            SpecialStudentSheet.Cells[0, 0].Style.HorizontalAlignment = TextAlignmentType.Center;//水平置中
            SpecialStudentSheet.Cells[1, 0].Style.HorizontalAlignment = TextAlignmentType.Center;//水平置中
            SpecialStudentSheet.Cells[0, 0].Style.Font.Size = 18;
            SpecialStudentSheet.Cells[1, 0].Style.Font.Size = 18;
            SpecialStudentSheet.Cells.SetRowHeight(0, 24);
            SpecialStudentSheet.Cells.SetRowHeight(1, 24);
            if (B4.Checked == true)
                SpecialStudentSheet.PageSetup.PaperSize = PaperSizeType.PaperB4; //設定紙張
            else
                SpecialStudentSheet.PageSetup.PaperSize = PaperSizeType.PaperA4;

            SpecialStudentSheet.PageSetup.Orientation = PageOrientationType.Landscape;//設定橫向列印
            SpecialStudentSheet.PageSetup.PrintTitleRows = "$1:$4"; //設定跨頁標題
            SpecialStudentSheet.PageSetup.FitToPagesWide = 1;   //調整為一頁寬
            SpecialStudentSheet.PageSetup.FitToPagesTall = (row - (row % 35)) / 35 + 1;   //調整頁高
            //設定邊界
            SpecialStudentSheet.PageSetup.BottomMargin = 1;
            SpecialStudentSheet.PageSetup.TopMargin = 1;
            SpecialStudentSheet.PageSetup.LeftMargin = 1;
            SpecialStudentSheet.PageSetup.RightMargin = 1;
            //劃框線
            for (int i = 2; i < row; i++)
                for (int j = 0; j < test1; j++)
                {
                    SpecialStudentSheet.Cells[i, j].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                    SpecialStudentSheet.Cells[i, j].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                    SpecialStudentSheet.Cells[i, j].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                    SpecialStudentSheet.Cells[i, j].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                    SpecialStudentSheet.Cells[i, j].Style.Font.Size = 12;
                }
            //SpecialStudentSheet.AutoFitRows(); //自動調整列寬
            SpecialStudentSheet.AutoFitColumns(); //自動調整欄寬
            //調整功過欄寬,合併欄位無法自動調整
            for (int i = 4; i <= 9; i++)
                SpecialStudentSheet.Cells.SetColumnWidth(i, 6);
            for (int i = 2; i <= SpecialStudentSheet.Cells.MaxDataRow; i++)
                SpecialStudentSheet.Cells.SetRowHeight(i, 20);
            return 0;
        }
Пример #50
0
 public static bool ExportExcel(DataTable dt, string path)
 {
     bool succeed = false;
     if (dt != null)
     {
         try
         {
             Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
             Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
             //为单元格添加样式
             Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
             //设置居中
             style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
             //设置背景颜色
             style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
             //style.Pattern = BackgroundType.Solid;
             style.Font.IsBold = true;
             int rowIndex = 0;
             int colIndex = 0;
             int colCount = dt.Columns.Count;
             int rowCount = dt.Rows.Count;
             //列名的处理
             for (int i = 0; i < colCount; i++)
             {
                 cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
                 //cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
                 //cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
                 //cellSheet.Cells[rowIndex, colIndex].Style = style;
                 colIndex++;
             }
             rowIndex++;
             for (int i = 0; i < rowCount; i++)
             {
                 colIndex = 0;
                 for (int j = 0; j < colCount; j++)
                 {
                     cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                     colIndex++;
                 }
                 rowIndex++;
             }
             cellSheet.AutoFitColumns();
             path = Path.GetFullPath(path);
             workbook.Save(path);
             succeed = true;
         }
         catch (Exception ex)
         {
             succeed = false;
         }
     }
     return succeed;
 }
Пример #51
0
        public static bool ExportCSV(DataTable dt, string path)
        {
            bool succeed = false;
            if (dt != null)
            {
                try
                {
                    Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                    Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];

                    int rowIndex = 0;
                    int colIndex = 0;
                    int colCount = dt.Columns.Count;
                    int rowCount = dt.Rows.Count;

                    for (int i = 0; i < rowCount; i++)
                    {
                        colIndex = 0;
                        for (int j = 0; j < colCount; j++)
                        {
                            cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                            colIndex++;
                        }
                        rowIndex++;
                    }

                    //0-byte array
                    byte[] workbookData = new byte[0];

                    //Text save options. You can use any type of separator
                    TxtSaveOptions opts = new TxtSaveOptions();
                    opts.Separator = '\t';
                    opts.Encoding = System.Text.Encoding.Default;

                    //Copy each worksheet data in text format inside workbook data array
                    for (int idx = 0; idx < workbook.Worksheets.Count; idx++)
                    {
                        //Save the active worksheet into text format
                        MemoryStream ms = new MemoryStream();
                        workbook.Worksheets.ActiveSheetIndex = idx;
                        workbook.Save(ms, opts);

                        //Save the worksheet data into sheet data array
                        ms.Position = 0;
                        byte[] sheetData = ms.ToArray();

                        //Combine this worksheet data into workbook data array
                        byte[] combinedArray = new byte[workbookData.Length + sheetData.Length];
                        Array.Copy(workbookData, 0, combinedArray, 0, workbookData.Length);
                        Array.Copy(sheetData, 0, combinedArray, workbookData.Length, sheetData.Length);

                        workbookData = combinedArray;
                    }

                    //cellSheet.AutoFitColumns();
                    path = Path.GetFullPath(path);

                    //Save entire workbook data into file
                    File.WriteAllBytes(path, workbookData);

                    //workbook.Save(path,SaveFormat.CSV);
                    succeed = true;
                }
                catch (Exception ex)
                {
                    succeed = false;
                }
            }
            return succeed;
        }
Пример #52
0
        //导入excel,从流中读取
        private void improtExcel(System.IO.Stream excelStream)
        {
            Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(excelStream);
            wk.Open(excelStream);// 这儿是需要导入的文件

            DataTable dt = new DataTable();
            DataColumn dc1 = new DataColumn("col1", typeof(string));
            DataColumn dc2 = new DataColumn("col2", typeof(string));
            DataColumn dc3 = new DataColumn("col3", typeof(string));
            DataColumn dc4 = new DataColumn("col4", typeof(string));
            DataColumn dc5 = new DataColumn("col5", typeof(string));
            dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4, dc5 });

            int totalRowCount = wk.Worksheets[0].Cells.Rows.Count;
            for (int i = 0; i < totalRowCount; i++)//用于EXCEL数据的等号,可以自行固定如:149,也可以自行去读取它的等号;
            {
                DataRow dr = dt.NewRow();

                dr["col1"] = wk.Worksheets[0].Cells[i, 0].Value;//读取文件里面对应的信息
                dr["col2"] = wk.Worksheets[0].Cells[i, 1].Value;
                dr["col3"] = wk.Worksheets[0].Cells[i, 2].Value;
                dr["col4"] = wk.Worksheets[0].Cells[i, 3].Value;
                dr["col5"] = wk.Worksheets[0].Cells[i, 4].Value;

                dt.Rows.Add(dr);
            }

            GridView2.DataSource = dt;
            GridView2.DataBind();
        }
        protected override void Build(System.Xml.XmlElement source, string location)
        {
            #region 建立 Excel

            //從 Resources 將新生名冊template讀出來
            Workbook template = new Workbook();
            template.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), FileFormatType.Excel2003);

            //產生 excel
            Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), 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 異動紀錄

                // 所在地代碼對照
                Dictionary<string, string> gLocationCodeDict = new Dictionary<string, string>();

                foreach (XElement elm in BL.Get.JHSchoolList().Elements("學校"))
                {
                    string code=elm.Attribute("所在地代碼").Value;
                   if(!gLocationCodeDict.ContainsKey(code))
                       gLocationCodeDict.Add(code,elm.Attribute("所在地").Value);
                }

                //將xml資料填入至excel
                foreach (XmlNode st in list.SelectNodes("異動紀錄"))
                {
                    recCount++;

                    if (i % 20 == 0)
                    {
                        #region 複製樣式-欄高、範圍

                        //複製樣版中前27個 Row(欄高)
                        //for (int m = 0; m < 27; m++)
                        //{
                        //    /*
                        //     * 複製 template的第一個 Sheet的第m個 Row
                        //     * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
                        //     */
                        //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
                        //}

                        /*
                         * 複製Style(包含儲存格合併的資訊)
                         * 先用CreateRange()選取要複製的Range("A1", "R27")
                         * 再用CopyStyle複製另一個Range中的格式
                         */
                        Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
                        int t = j * 27;
                        wb.Worksheets[0].Cells.CreateRange(t, 27, 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)
                        {
                            //插入分頁(在 j * 27 跟 (j * 27) +1 中間,R跟S中間)
                            wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
                            rowj += 7;
                        }
                        else
                        {
                            rowj = 5;
                        }

                        rowi += 27;
                        j++;

                        #region 顯示頁數

                        //顯示頁數
                        if (x != true)
                        {
                            wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁,共" + Math.Ceiling((double)count / 20) + "頁");
                        }
                        else
                        {
                            wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 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, 5].PutValue(st.SelectSingleNode("@身分證號").InnerText);

                        try
                        {
                            wb.Worksheets[0].Cells[rowj, 6].PutValue(Convert.ToInt32(st.SelectSingleNode("@性別代號").InnerText));
                        }
                        catch (Exception)
                        {}
                        wb.Worksheets[0].Cells[rowj, 7].PutValue(st.SelectSingleNode("@性別").InnerText);
                        wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@出生年月日").InnerText);

                        string stra1 = "", stra2 = "";
                        if (st.SelectSingleNode("@畢業國中所在縣市代號") != null)
                            stra1 = st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;

                        if (st.SelectSingleNode("@入學資格代號") != null)
                            stra2 = st.SelectSingleNode("@入學資格代號").InnerText;

                        wb.Worksheets[0].Cells[rowj, 11].PutValue(stra1 + "\n" + stra2);

                        string data = "", data1 = "";
                        string uCode = "";
                        if (st.SelectSingleNode("@異動代號") == null)
                        {
                            if (st.SelectSingleNode("@異動代碼") != null)
                                uCode = st.SelectSingleNode("@異動代碼").InnerText;
                        }
                        else
                            uCode = st.SelectSingleNode("@異動代號").InnerText;

                        if (uCode == "001")
                                data1 = "畢業";

                        if (uCode == "003")
                                data1 = "結業";
                        if (uCode == "004")
                                data1 = "修滿";

                        if(st.SelectSingleNode("@畢業國中所在縣市代號")!=null)
                        if (!string.IsNullOrEmpty(st.SelectSingleNode("@畢業國中所在縣市代號").InnerText))
                        {
                            string code=st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;

                            if (gLocationCodeDict.ContainsKey(code))
                            {
                                data = gLocationCodeDict[code];
                            }

                        }

                            wb.Worksheets[0].Cells[rowj, 12].PutValue(data+st.SelectSingleNode("@畢業國中").InnerText+data1);
                        //wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@備註").InnerText);
                    if(st.SelectSingleNode("@特殊身份代碼")!=null)
                            wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);

                    #endregion

                    i++;
                    rowj++;

                    //回報進度
                    ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
                }

                #endregion

                #region 若個數為20倍數,處理單一頁面

                if (x == true)
                {

                    #region 複製樣式-欄高、範圍

                    //複製樣版前27個 Row(欄高)
                    //for (int m = 0; m < 27; m++)
                    //{
                    //    /*
                    //     * 複製 template的第一個 Sheet的第m個 Row
                    //     * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
                    //     */
                    //    wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
                    //}

                    /*
                     * 複製Style(包含儲存格合併的資訊)
                     * 先用CreateRange()選取要複製的Range("A1", "R27")
                     * 再用CopyStyle複製另一個Range中的格式
                     */
                    Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
                    int t= j * 27;
                    wb.Worksheets[0].Cells.CreateRange(t, 27, 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 * 27, 18);
                        rowj += 7;
                    }

                    rowi += 27;
                    j++;

                    #region 顯示頁數

                    //顯示頁數
                    wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁,共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
                    numcount++;

                    #endregion
                }

                #endregion

                #region 統計人數

                //填入統計人數
                wb.Worksheets[0].Cells[rowj, 1].PutValue("合  計 ");
                wb.Worksheets[0].Cells[rowj, 3].PutValue(count.ToString() + " 名");

                #endregion

                wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);

                #region 設定變數

                //調整新清單所使用變數
                numcount = 1;
                rowj = (27 * j) - 2;
                rowi = (27 * j);
                x = false;

                #endregion
            }

            #region 97中辦格式

            Worksheet mingdao = wb.Worksheets["電子格式99"];
            Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
            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;
            foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
            {
                mdws_index++;
                mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);

                mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("班別"));
                mdws.Cells[mdws_index, 1].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
                mdws.Cells[mdws_index, 2].PutValue("");
                mdws.Cells[mdws_index, 3].PutValue(record.GetAttribute("學號"));
                mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("姓名"));
                mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("身分證號"));
                mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("註1"));
                mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("性別代號"));
                mdws.Cells[mdws_index, 8].PutValue(GetBirthdateWithoutSlash(record.GetAttribute("出生年月日")));
                mdws.Cells[mdws_index, 9].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
                mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("入學資格代號"));
                //mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("畢業國中所在縣市代號"));
                string GradeSchoolCode = record.GetAttribute("畢業國中代碼");
                //if (GradeSchoolCode.Length > 3)
                //    mdws.Cells[mdws_index, 12].PutValue(GradeSchoolCode.Substring(2, 1));

            //    mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("畢業國中"));
                mdws.Cells[mdws_index, 11].PutValue(GradeSchoolCode);
                mdws.Cells[mdws_index, 12].PutValue(record.GetAttribute("入學資格證明文件"));

                mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("備註"));
            }

            mdws.AutoFitColumns();
            mdws.Cells.SetColumnWidth(5, 8.5);
            //mdws.Cells.SetColumnWidth(11, 20);

            wb.Worksheets.RemoveAt("電子格式99");
            wb.Worksheets.ActiveSheetIndex = 0;

            #endregion

            wb.Worksheets.ActiveSheetIndex = 0;

            //儲存 Excel
            wb.Save(location, FileFormatType.Excel2003);
        }
Пример #54
0
        public static void XuatDuLieuRaExcel(int iRowPara,
            int iColumnPara,
            string strSubHeaderPara,
            System.Data.DataTable tblBangDuLieuPara,
            string strTemplateNamePara)
        {
            //Đường dẫn file template
            string strSourceFilePri = string.Format("{0}{1}{2}",
              System.Windows.Forms.Application.StartupPath,
              PATH_TEMPLATES, strTemplateNamePara);

            SaveFileDialog saveFileDialogPri = new SaveFileDialog();
            saveFileDialogPri.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
            saveFileDialogPri.FilterIndex = 1;

            if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
            {
                FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);

                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                workbook.Open(streamTemp);
                workbook.Worksheets.Add();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                //Set cell store subHeader
                Aspose.Cells.Cells cellHeader = worksheet.Cells;
                //cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
                worksheet.Cells["A3"].PutValue(strSubHeaderPara);
                //worksheet.IsGridlinesVisible = false;
                worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);

                //Formatting for cells store database
                for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
                {
                    for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
                    {
                        Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
                        workbook.Styles.Add();
                        Aspose.Cells.Style style = cell.GetStyle();
                        style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                        style.Borders[BorderType.BottomBorder].Color = Color.Silver;
                        style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                        style.Borders[BorderType.TopBorder].Color = Color.Silver;
                        style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                        style.Borders[BorderType.LeftBorder].Color = Color.Silver;
                        style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                        style.Borders[BorderType.RightBorder].Color = Color.Silver;
                        cell.SetStyle(style);
                    }
                }
                //worksheet.AutoFitColumns();
                //Save excel file
                workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);

                MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
                    WorkingContext.LangManager.GetString("Message"),
                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                streamTemp.Close();

                if (File.Exists(saveFileDialogPri.FileName))
                    Process.Start(saveFileDialogPri.FileName);
            }
        }
Пример #55
0
        public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
        {
            error = "";
            try
            {
                if (datatable == null)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return false;
                }

                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
                Aspose.Cells.Cells cells = sheet.Cells;

                int nRow = 0;
                foreach (DataRow row in datatable.Rows)
                {
                    nRow++;
                    try
                    {
                        for (int i = 0; i < datatable.Columns.Count; i++)
                        {
                            if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
                            {
                                //------插入图片数据-------
                                System.Drawing.Image image = (System.Drawing.Image)row[i];
                                MemoryStream mstream = new MemoryStream();
                                image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
                                sheet.Pictures.Add(nRow, i, mstream);
                            }
                            else
                            {
                                cells[nRow, i].PutValue(row[i]);
                            }
                        }
                    }
                    catch (System.Exception e)
                    {
                        error = error + " DataTableToExcel: " + e.Message;
                    }
                }

                workbook.Save(filepath);
                return true;
            }
            catch (System.Exception e)
            {
                error = error + " DataTableToExcel: " + e.Message;
                return false;
            }
        }
        private void ImportSCAttendRecords()
        {
            this.addMsg(" ==== 開始匯入修課學生 ===");

            /*  取得目前所有的修課紀錄 */
            List<UDT.SCAttendExt> attRecs = (new AccessHelper()).Select<UDT.SCAttendExt>();
            Dictionary<string, Dictionary<string, UDT.SCAttendExt>> dicAttRecs = new Dictionary<string, Dictionary<string, UDT.SCAttendExt>>();
            foreach (UDT.SCAttendExt att in attRecs)
            {
                if (!dicAttRecs.ContainsKey(att.CourseID.ToString()))
                    dicAttRecs.Add(att.CourseID.ToString(), new Dictionary<string, UDT.SCAttendExt>());

                dicAttRecs[att.CourseID.ToString()].Add(att.StudentID.ToString(), att);
            }

            /* 取得所有課程 ,以便從課程代碼 及班及名稱,找出 課程系統編號 */
            this.GetAllCourses();

            List<UDT.CourseExt> allCourses = (new AccessHelper()).Select<UDT.CourseExt>();
            Dictionary<string, UDT.CourseExt> dicAllCourses = new Dictionary<string, UDT.CourseExt>();
            foreach (UDT.CourseExt course in allCourses)
            {
                if (this.dicCourses.ContainsKey(course.CourseID.ToString()))
                {
                    string key = string.Format("{0}_{1}", course.SubjectCode, course.ClassName);
                    dicAllCourses.Add(key, course);
                }
            }

            /* 取得所有學生資料,以便從學號找出學生編號  */
            List<K12.Data.StudentRecord> allStudents = K12.Data.Student.SelectAll();
            Dictionary<string, K12.Data.StudentRecord> dicAllStudents = new Dictionary<string, K12.Data.StudentRecord>();
            foreach (K12.Data.StudentRecord stud in allStudents)
            {
                if (!string.IsNullOrWhiteSpace(stud.StudentNumber))
                    dicAllStudents.Add(stud.StudentNumber, stud);
            }

            /* 讀取 Excel 資料  */
             Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(this.textBoxX1.Text);
            Worksheet ws = wb.Worksheets[0];   //修課紀錄
            int rowIndex = 1;
            while (ws.Cells[rowIndex, 3].Value != null)
            {
                string studNo = GetCellValue(ws.Cells[rowIndex, 3].Value);
                if (!dicAllStudents.ContainsKey(studNo))
                {
                    this.addMsg(string.Format("找不到學生,學號:{0}, rowNo: {1}  ", studNo, rowIndex.ToString()));
                }
                else
                {
                    string studID = dicAllStudents[studNo].ID ;
                    string courseCode = GetCellValue(ws.Cells[rowIndex, 7].Value);
                    string classCode = GetCellValue(ws.Cells[rowIndex, 8].Value);
                    if (classCode.Length > 2)
                        classCode = classCode.Substring(1, 2);
                    string key = string.Format("{0}_{1}", courseCode, classCode);
                    if (!dicAllCourses.ContainsKey(key))
                    {
                        this.addMsg(string.Format("找不到課程,課號:{0}, 班及:{1},  rowNo: {2}  ", courseCode, classCode,  rowIndex.ToString()));
                    }
                    else
                    {
                        string courseID = dicAllCourses[key].CourseID.ToString();
                        //判斷該生是否已經修課,若是,則 skip ,否則新增 !
                        if (dicAttRecs.ContainsKey(courseID) && dicAttRecs[courseID].ContainsKey(studID))
                        {
                            //do nothing
                            string msg = string.Format("學號:{0}  已修課程: 課號 = {1}, 班號 = {2},  rowindex ={3},故忽略不匯入!", studNo, courseCode, classCode, rowIndex.ToString());
                            this.addMsg(msg);
                        }
                        else
                        {
                            // 新增修課紀錄

                            UDT.SCAttendExt attRec = new UDT.SCAttendExt();
                            attRec.StudentID = int.Parse(studID);
                            attRec.CourseID = int.Parse(courseID);
                            List<ActiveRecord> recs = new List<ActiveRecord>();
                            recs.Add(attRec);
                            (new AccessHelper()).InsertValues( recs);
                        }
                    }
                }
                rowIndex += 1;
                this.lblStatus.Text = rowIndex.ToString();
                Application.DoEvents();
            }
        }
Пример #57
0
        public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
        {
            error = "";
            //----------Aspose变量初始化----------------
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
            Aspose.Cells.Cells cells = sheet.Cells;
            //-------------输入数据-------------
            int nRow = 0;
            sheet.Pictures.Clear();
            cells.Clear();
            foreach (IList list in lists)
            {

                for (int i = 0; i <= list.Count - 1; i++)
                {
                    try
                    {
                        System.Console.WriteLine(i.ToString() + "  " + list[i].GetType());
                        if (list[i].GetType().ToString() == "System.Drawing.Bitmap")
                        {
                            //插入图片数据
                            System.Drawing.Image image = (System.Drawing.Image)list[i];

                            MemoryStream mstream = new MemoryStream();

                            image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);

                            sheet.Pictures.Add(nRow, i, mstream);
                        }
                        else
                        {
                            cells[nRow, i].PutValue(list[i]);
                        }
                    }
                    catch (System.Exception e)
                    {
                        error = error + e.Message;
                    }

                }

                nRow++;
            }
            //-------------保存-------------
            workbook.Save(filepath);

            return true;
        }
Пример #58
0
        private void button3_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "上傳樣板";
            dialog.Filter = "Word檔案 (*.doc)|*.doc|Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                try
                {
                    if (dialog.FileName.IndexOf(".doc") > -1)
                    {
                        TemplateWord = new Aspose.Words.Document(dialog.FileName);
                        IsWord = true;
                    }
                    if (dialog.FileName.IndexOf(".xls") > -1)
                    {
                        TemplateExcel = new Aspose.Cells.Workbook();
                        TemplateExcel.Open(dialog.FileName);
                        IsWord = false;
                    }
                    //Template = new Aspose.Words.Document(dialog.FileName);
                    string[] fileNames = dialog.FileName.Split('\\');
                    textBox1.Text = fileNames[fileNames.Length - 1];
                }
                catch
                {
                    MessageBox.Show("樣板開啟失敗");

                }
            }
        }
        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);
        }
Пример #60
0
        private static bool SavePdfToStream(string inputFile, MemoryStream fileStream, MemoryStream documentStream, ref bool isLandscape)
        {
            var extension = Path.GetExtension(inputFile);
            if (string.IsNullOrEmpty(extension))
                throw new ArgumentException(inputFile);

            try
            {
                fileStream.Position = 0;
                switch (extension.ToUpperInvariant())
                {
                    case ".DOC":
                    case ".DOCX":
                    case ".RTF":
                    case ".DOT":
                    case ".DOTX":
                        var doc = new Aspose.Words.Document(fileStream);
                        if (doc.PageCount > 0)
                        {
                            var pageInfo = doc.GetPageInfo(0);
                            isLandscape = pageInfo.WidthInPoints > pageInfo.HeightInPoints;
                        }
                        doc.Save(documentStream, Aspose.Words.SaveFormat.Pdf);
                        break;
                    case ".XLS":
                    case ".XLSX":
                        var workbook = new Aspose.Cells.Workbook(fileStream);
                        for (var i = 0; i < workbook.Worksheets.Count; i++)
                        {
                            if (!workbook.Worksheets[i].IsVisible) continue;
                            isLandscape = workbook.Worksheets[i].PageSetup.Orientation == Aspose.Cells.PageOrientationType.Landscape;
                            break;
                        }
                        workbook.Save(documentStream, Aspose.Cells.SaveFormat.Pdf);
                        break;
                    //Microsoft Visio 
                    case ".VSD":
                    case ".VSS":
                    case ".VST":
                    case ".VSX":
                    case ".VTX":
                    case ".VDW":
                    case ".VDX":
                        var vsdDiagram = new Aspose.Diagram.Diagram(fileStream);
                        if (vsdDiagram.Pages.Count > 0)
                            isLandscape = vsdDiagram.Pages[0].PageSheet.PrintProps.PrintPageOrientation.Value == Aspose.Diagram.PrintPageOrientationValue.Landscape;
                        vsdDiagram.Save(documentStream, Aspose.Diagram.SaveFileFormat.PDF);
                        break;
                    //Microsoft Project
                    case ".MPP":
                        var project = new Aspose.Tasks.Project(fileStream);
                        project.Save(documentStream, Aspose.Tasks.Saving.SaveFileFormat.PDF);
                        isLandscape = true;
                        break;
                    //PowerPoint
                    case ".PPT":
                    case ".PPS":
                    case ".POT":
                        using (var pres = new Aspose.Slides.Presentation(fileStream))
                        {
                            isLandscape = pres.SlideSize.Size.Width > pres.SlideSize.Size.Height;
                            pres.Save(documentStream, Aspose.Slides.Export.SaveFormat.Pdf);
                        }
                        break;
                    case ".PPTX":
                    case ".PPSX":
                    case ".POTX":
                        //case ".XPS":
                        using (var presEx = new Aspose.Slides.Presentation(fileStream))
                        {
                            isLandscape = presEx.SlideSize.Orientation == Aspose.Slides.SlideOrienation.Landscape;
                            presEx.Save(documentStream, Aspose.Slides.Export.SaveFormat.Pdf);
                        }
                        break;

                    case ".PDF":
                        {
                            using (var pdf = new Aspose.Pdf.Document(fileStream))
                            {
                                var page = pdf.Pages.OfType<Aspose.Pdf.Page>().FirstOrDefault();
                                if (page != null && page.MediaBox != null)
                                {
                                    isLandscape = page.MediaBox.Width > page.MediaBox.Height;
                                }
                            }

                            fileStream.Seek(0, SeekOrigin.Begin);
                            fileStream.CopyTo(documentStream);
                        }

                        break;
                }
            }
            finally
            {
                fileStream.Close();
            }

            return true;
        }