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 }
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); } }
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); }
/// <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); } }
/// <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); } }
/// <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); } }
/// <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); }
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; } } } } }
/// <summary> /// 打开指定路径excel /// </summary> /// <param name="path">excel 文件路径</param> public void Open(string path) { if (!string.IsNullOrEmpty(path)) { wb = new Aspose.Cells.Workbook(path); } }
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 }
/// <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); }
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); } }
/// <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(); } }
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 }
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 }
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); } }
/// <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 }
/// <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); }
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"); }
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); // } //} }
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; } }
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"); }
/// <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); } }
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(); }
/// <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(); }
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); }
//列印 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); }
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); }
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; }
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; }
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; }
//导入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); }
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); } }
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(); } }
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; }
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); }
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; }