protected void publish_import_upload_OnFileUploaded(object sender, FileUploadedEventArgs e) { var id = CurrentResource.Id; var file = e.File; var tempId = HomoryContext.Value.GetId(); var suffix = file.GetExtension().Replace(".", "").ToLower(); var path = string.Format("../Common/资源/{0}/{1}/{2}.{3}", CurrentUser.Id.ToString().ToUpper(), ResourceType.ToString(), tempId.ToString().ToUpper(), ResourceType == ResourceType.视频 ? suffix == "flv" ? suffix : "mp4" : "pdf"); var pathX = Server.MapPath(path); var source = string.Format("../Common/资源/{0}/{1}/{2}.{3}", CurrentUser.Id.ToString().ToUpper(), ResourceType.ToString(), tempId.ToString().ToUpper(), suffix); var sourceX = Server.MapPath(source); var cpic = path.Replace(".pdf", ".jpg").Replace(".flv", ".jpg").Replace(".mp4", ".jpg"); var cpicX = pathX.Replace(".pdf", ".jpg").Replace(".flv", ".jpg").Replace(".mp4", ".jpg"); var res = HomoryContext.Value.Resource.Single(o => o.Id == id); file.SaveAs(sourceX, true); switch (suffix) { case "doc": case "docx": case "txt": case "rtf": var docW = new Aspose.Words.Document(sourceX); docW.Save(pathX, Aspose.Words.SaveFormat.Pdf); docW.Save(cpicX, Aspose.Words.SaveFormat.Jpeg); res.Image = cpic; res.FileType = ResourceFileType.Word; res.Thumbnail = ((int)ResourceFileType.Word).ToString(); break; case "ppt": case "pptx": var docP = new Aspose.Slides.Presentation(sourceX); docP.Save(pathX, Aspose.Slides.Export.SaveFormat.Pdf); var tcdocp = new Aspose.Pdf.Document(pathX); using (var imageStream = new FileStream(cpicX, FileMode.Create)) { var resolution = new Resolution(300); var jpegDevice = new JpegDevice(resolution, 100); jpegDevice.Process(tcdocp.Pages[1], imageStream); imageStream.Close(); } res.Image = cpic; res.FileType = ResourceFileType.Powerpoint; res.Thumbnail = ((int)ResourceFileType.Powerpoint).ToString(); break; case "xls": case "xlsx": var docE = new Aspose.Cells.Workbook(sourceX); docE.Save(pathX, Aspose.Cells.SaveFormat.Pdf); var tcdoce = new Aspose.Pdf.Document(pathX); using (var imageStream = new FileStream(cpicX, FileMode.Create)) { var resolution = new Resolution(300); var jpegDevice = new JpegDevice(resolution, 100); jpegDevice.Process(tcdoce.Pages[1], imageStream); imageStream.Close(); } res.Image = cpic; res.FileType = ResourceFileType.Excel; res.Thumbnail = ((int)ResourceFileType.Excel).ToString(); break; case "pdf": var tcdoc = new Aspose.Pdf.Document(pathX); using (var imageStream = new FileStream(cpicX, FileMode.Create)) { var resolution = new Resolution(300); var jpegDevice = new JpegDevice(resolution, 100); jpegDevice.Process(tcdoc.Pages[1], imageStream); imageStream.Close(); } res.Image = cpic; res.FileType = ResourceFileType.Pdf; res.Thumbnail = ((int)ResourceFileType.Pdf).ToString(); break; case "avi": case "mpg": case "mpeg": case "flv": case "mp4": case "rm": case "rmvb": case "wmv": NReco.VideoConverter.FFMpegConverter c = new NReco.VideoConverter.FFMpegConverter(); c.GetVideoThumbnail(sourceX, cpicX, 2F); //if (!sourceX.EndsWith("flv", StringComparison.OrdinalIgnoreCase)) //{ // c.ConvertMedia(sourceX, pathX, NReco.VideoConverter.Format.flv); //} res.Image = cpic; res.FileType = ResourceFileType.Media; res.Thumbnail = ((int)ResourceFileType.Media).ToString(); break; } res.SourceName = file.GetName(); res.Title = file.GetNameWithoutExtension(); res.Source = source; res.Preview = path; res.Converted = true; HomoryContext.Value.SaveChanges(); }
/// <summary> /// Converts binary to blob pointer /// </summary> /// <param name="category">The file category.</param> /// <param name="data">The data.</param> /// <param name="name">The name.</param> /// <returns>Blob Pointer</returns> public static string ConvertToFile(string category, string name, byte[] data) { if (data == null) { return null; } using (Stream stream = new MemoryStream(data)) { // convert doc, xls -> docx, xlsx Stream streamToSave = new MemoryStream(); if (FileTypeDetector.IsDoc(data)) { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.RemoveMacros(); doc.Save(streamToSave, Aspose.Words.SaveFormat.Docx); } else if (FileTypeDetector.IsXls(data)) { Aspose.Cells.Workbook xls = new Aspose.Cells.Workbook(stream); xls.RemoveMacro(); xls.Save(streamToSave, Aspose.Cells.SaveFormat.Xlsx); } else { streamToSave = stream; } // save to file streamToSave.Position = 0; string result = BlobStoreProvider.Instance.PutBlob(category, name, streamToSave); return result; } }
/// <summary> /// 导出待处理文件 /// </summary> private void btnExportWaitFile_Click(object sender, EventArgs e) { var lst = new DataLayer.BLL.v_FileReplace_File_ForExport().GetAllList(); if (null == lst || lst.Count == 0) { DevExpress.XtraEditors.XtraMessageBox.Show("当前没有任何数据可供导出!", "系统提示"); return; } FolderBrowserDialog openFolder = new FolderBrowserDialog(); openFolder.Description = "请选择要存放的目录!"; if (openFolder.ShowDialog() != System.Windows.Forms.DialogResult.OK) { return; } string folderPath = openFolder.SelectedPath.TrimEnd('\\'); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Worksheets[0].Cells.ImportDataTable(XCLNetTools.DataSource.DataTableHelper.ToDataTable(lst), true, 0, 0); string filePath = string.Format(@"{0}\XCLNetFileRelace_{1:yyyyMMddHHmmssfff}.xlsx", folderPath, DateTime.Now); wb.Save(filePath, Aspose.Cells.SaveFormat.Xlsx); if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功,是否打开该文件?", "系统提示", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { System.Diagnostics.Process.Start(filePath); } }
public void MainExport(DataTable dt) { string filepath = ""; Aspose.Cells.Workbook xlBook = new Aspose.Cells.Workbook(); //清除默认sheet xlBook.Worksheets.Clear(); //设置第一个sheet和name xlBook.Worksheets.Add("日占比"); Aspose.Cells.Worksheet ws1 = xlBook.Worksheets[0]; RectangleExport(dt, ws1); //设置第一个sheet和name xlBook.Worksheets.Add("客户总量"); Aspose.Cells.Worksheet ws2 = xlBook.Worksheets[1]; RectangleExport(dt, ws2); //保存地址 if (dt.Rows.Count > 60000) { filepath = System.Windows.Forms.Application.StartupPath + "\\" + "牙模盒使用记录" + ".xlsx"; } else { filepath = System.Windows.Forms.Application.StartupPath + "\\" + "牙模盒使用记录" + ".xls"; } //保存 xlBook.Save(filepath); //打开 System.Diagnostics.Process.Start(filepath); }
public static void Run() { //Output directory string outputDir = RunExamples.Get_OutputDirectory(); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; int moduleIdx = workbook.VbaProject.Modules.Add(sheet); Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[moduleIdx]; module.Codes = "Sub ShowMessage()" + "\r\n" + " MsgBox \"Welcome to Aspose!\"" + "\r\n" + "End Sub"; Aspose.Cells.Drawing.Button button = sheet.Shapes.AddButton(2, 0, 2, 0, 28, 80); button.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating; button.Font.Name = "Tahoma"; button.Font.IsBold = true; button.Font.Color = System.Drawing.Color.Blue; button.Text = "Aspose"; button.MacroName = sheet.Name + ".ShowMessage"; workbook.Save(outputDir + "outputAssignMacroToFormControl.xlsm"); Console.WriteLine("AssignMacroToFormControl executed successfully."); }
public static void Run() { string dataDir = RunExamples.GetDataDir_Charts(); //ExStart:SetChartDataFromWorkBook 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", SaveFormat.Pptx); //ExEnd:SetChartDataFromWorkBook }
/// <summary> /// Excel格式转换(Excel文件不能为空,默认转成PDF) /// </summary> /// <param name="sourceStream">源文件</param> /// <param name="targetPath">目标路径</param> /// <param name="targetFileType">转换类型</param> /// <returns></returns> public static bool ExcelConvert(Stream sourceStream, string targetPath, Aspose.Cells.SaveFormat targetFileType = Aspose.Cells.SaveFormat.Pdf) { var xls = new Aspose.Cells.Workbook(sourceStream); xls.Save(@targetPath, targetFileType); return(true); }
private void ExportXLS(string docno, string caseID, string Bu, string Building) { string templatePath = string.Empty; Aspose.Cells.License lic = new Aspose.Cells.License(); string AsposeLicPath = System.Configuration.ConfigurationSettings.AppSettings["AsposeLicPath"].ToString(); lic.SetLicense(AsposeLicPath); templatePath = Page.Server.MapPath("~/Web/E-Report/ReportTemp.xlsx"); //Instantiate a new Workbook object. Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(templatePath); Aspose.Cells.Worksheet sheet1 = book.Worksheets[0]; //DFX Aspose.Cells.Worksheet sheet2 = book.Worksheets[1]; // PMFEA Aspose.Cells.Worksheet sheet3 = book.Worksheets[2]; // IssuesList Aspose.Cells.Worksheet sheet4 = book.Worksheets[3]; // CTQ // REPLACE PUBLIC FIELDS BindExcel(ref sheet1, caseID, Bu, Building, docno); BindPFMA(ref sheet2, caseID, Bu, Building, docno); BindIssuesList(ref sheet3, caseID, Bu, Building, docno); BindCTQ(ref sheet4, caseID, Bu, Building, docno); SetColumnAuto(ref sheet1); this.Response.Clear(); book.Save("NPI_Report.xls", Aspose.Cells.FileFormatType.Excel97To2003, Aspose.Cells.SaveType.OpenInExcel, this.Response, System.Text.Encoding.UTF8); }
/// <summary> /// 导出excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data">Ilist集合</param> /// <param name="filepath">保存的地址</param> public static bool Export <T>(IList <T> data, string filepath) { try { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = (Aspose.Cells.Worksheet)workbook.Worksheets[0]; PropertyInfo[] ps = typeof(T).GetProperties(); var colIndex = "A"; foreach (var p in ps) { sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称 要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称 int i = 2; foreach (var d in data) { sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null)); i++; } colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头 A1/A2/ } workbook.Save(filepath); GC.Collect(); return(true); } catch (Exception) { return(false); } }
/// <summary> /// Converts binary to blob pointer /// </summary> /// <param name="category">The file category.</param> /// <param name="data">The data.</param> /// <param name="name">The name.</param> /// <returns>Blob Pointer</returns> public static string ConvertToFile(string category, string name, byte[] data) { if (data == null) { return(null); } using (Stream stream = new MemoryStream(data)) { // convert doc, xls -> docx, xlsx Stream streamToSave = new MemoryStream(); if (FileTypeDetector.IsDoc(data)) { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.RemoveMacros(); doc.Save(streamToSave, Aspose.Words.SaveFormat.Docx); } else if (FileTypeDetector.IsXls(data)) { Aspose.Cells.Workbook xls = new Aspose.Cells.Workbook(stream); xls.RemoveMacro(); xls.Save(streamToSave, Aspose.Cells.SaveFormat.Xlsx); } else { streamToSave = stream; } // save to file streamToSave.Position = 0; string result = BlobStoreProvider.Instance.PutBlob(category, name, streamToSave); return(result); } }
/// <summary> /// Excel 生成 HTML 文件 /// </summary> /// <param name="excelPath">Excel 路径</param> /// <param name="htmlPath">Html 路径</param> /// <param name="sheetName">表单名称</param> public static void ExcelToHtmlFile(string excelPath, string htmlPath, string sheetName = "") { Aspose.Cells.HtmlSaveOptions htmlSaveOptions = new Aspose.Cells.HtmlSaveOptions(Aspose.Cells.SaveFormat.Html); Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook(excelPath); if (string.IsNullOrEmpty(sheetName)) { workBook.Save(htmlPath, htmlSaveOptions); } else { Aspose.Cells.Workbook newWorkBook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet newWorkSheet = newWorkBook.Worksheets[0]; newWorkSheet.Copy(workBook.Worksheets[sheetName]); newWorkBook.Save(htmlPath, htmlSaveOptions); } string directoryPath = string.Format("{0}/{1}_files", Path.GetDirectoryName(htmlPath), System.IO.Path.GetFileNameWithoutExtension(htmlPath)); string[] filePathList = Directory.GetFiles(directoryPath, "*.htm"); foreach (string filePath in filePathList) { TransformHTMLEncoding(filePath, string.Format("<script>\ndocument.write(\"<div style='color:red;font-size:10pt;font-family:Arial'>Evaluation Only. Created with Aspose.Cells for .NET.Copyright 2003 - 2018 Aspose Pty Ltd.</div>\");\n</script>")); } TransformHTMLEncoding(htmlPath, string.Format("<frame src=\"{0}_files/tabstrip.htm\" name=\"frTabs\" marginwidth=\"0\" marginheight=\"0\">", Path.GetFileNameWithoutExtension(htmlPath))); }
public void ConvertUsingAspose(string path) { Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(path); string newPath = string.Join(".", path.Split(".").SkipLast(1).Append("csv")); book.Save(newPath, Aspose.Cells.SaveFormat.CSV); }
private void SaveFile(MemoryStream stream, string filename, string format) { if (format == "xls") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003); } else if (format == "xlsx") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx); } else if (format == "doc") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Doc); } else if (format == "docx") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Docx); } }
private void ExportXLS(string caseID, string Bu, string FormNo) { string templatePath = string.Empty; Aspose.Cells.License lic = new Aspose.Cells.License(); string AsposeLicPath = System.Configuration.ConfigurationSettings.AppSettings["AsposeLicPath"].ToString(); lic.SetLicense(AsposeLicPath); templatePath = Page.Server.MapPath("~/Web/E-Report/TempPrelaunch .xlsx"); //Instantiate a new Workbook object. Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(templatePath); Aspose.Cells.Worksheet sheet1 = book.Worksheets[0]; Aspose.Cells.Worksheet sheet2 = book.Worksheets[1]; // REPLACE PUBLIC FIELDS BindHomePage(ref sheet1, caseID, Bu); BindCheckItem(ref sheet2, FormNo, Bu); this.Response.Clear(); book.Save("Prelaunch_Report.xls", Aspose.Cells.FileFormatType.Excel97To2003, Aspose.Cells.SaveType.OpenInExcel, this.Response, System.Text.Encoding.UTF8); }
public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); //Output directory string outputDir = RunExamples.Get_OutputDirectory(); //Certificate file and its password string certFileName = sourceDir + "AsposeDemo.pfx"; string password = "******"; //Load the workbook which is already digitally signed to add new digital signature Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(sourceDir + "sampleDigitallySignedByCells.xlsx"); //Create the digital signature collection Aspose.Cells.DigitalSignatures.DigitalSignatureCollection dsCollection = new Aspose.Cells.DigitalSignatures.DigitalSignatureCollection(); //Create new certificate System.Security.Cryptography.X509Certificates.X509Certificate2 certificate = new System.Security.Cryptography.X509Certificates.X509Certificate2(certFileName, password); //Create new digital signature and add it in digital signature collection Aspose.Cells.DigitalSignatures.DigitalSignature signature = new Aspose.Cells.DigitalSignatures.DigitalSignature(certificate, "Aspose.Cells added new digital signature in existing digitally signed workbook.", DateTime.Now); dsCollection.Add(signature); //Add digital signature collection inside the workbook workbook.AddDigitalSignature(dsCollection); //Save the workbook and dispose it. workbook.Save(outputDir + "outputDigitallySignedByCells.xlsx"); workbook.Dispose(); Console.WriteLine("AddDigitalSignatureToAnAlreadySignedExcelFile executed successfully.\r\n"); }
public override DocumentEntity ConvertToHtml(Common.Entities.DocumentEntity _docEntity) { if (!_docEntity.isConvert && String.IsNullOrEmpty(_docEntity.ConvertError)) { Aspose.Cells.Workbook xls = new Aspose.Cells.Workbook(_docEntity.FilePath); _docEntity.HtmlData = new HtmlParseContext(); _docEntity.HtmlData.PageNumber = xls.Worksheets.Count; foreach (Aspose.Cells.Worksheet item in xls.Worksheets) { Aspose.Cells.HtmlSaveOptions htmlsaveoption = new Aspose.Cells.HtmlSaveOptions(Aspose.Cells.SaveFormat.Html); htmlsaveoption.AttachedFilesDirectory = Path.Combine(_docEntity.ResourcesPath, "image"); htmlsaveoption.HtmlCrossStringType = Aspose.Cells.HtmlCrossType.Cross; using (MemoryStream htmlStream = new MemoryStream()) { try { xls.Worksheets.ActiveSheetIndex = item.Index; xls.Save(htmlStream, htmlsaveoption); _docEntity.HtmlData.HtmlContent.Add(Encoding.UTF8.GetString(htmlStream.ToArray())); _docEntity.isConvert = true; } catch (Exception e) { _docEntity.isConvert = false; _docEntity.ConvertError = e.Message; } } } } return(_docEntity); }
public static void Main(string[] args) { // The path to the documents directory. string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); if (!System.IO.Directory.Exists(dataDir)) { System.IO.Directory.CreateDirectory(dataDir); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; int moduleIdx = workbook.VbaProject.Modules.Add(sheet); Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[moduleIdx]; module.Codes = "Sub ShowMessage()" + "\r\n" + " MsgBox \"Welcome to Aspose!\"" + "\r\n" + "End Sub"; Aspose.Cells.Drawing.Button button = sheet.Shapes.AddButton(2, 0, 2, 0, 28, 80); button.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating; button.Font.Name = "Tahoma"; button.Font.IsBold = true; button.Font.Color = System.Drawing.Color.Blue; button.Text = "Aspose"; button.MacroName = sheet.Name + ".ShowMessage"; workbook.Save(dataDir + "Output.xlsm"); Console.WriteLine("File saved"); }
private void SaveFile(MemoryStream stream, string filename, string format) { if (format == "xls") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); // 2017/8/22 穎驊依據高雄小組專案 [03-05][04+] EXCEL匯入格式可否修正為xlsx也可匯入? 更改為新版 Aspose.Cells_201402 寫法 //wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003); wb.Save(filename, Aspose.Cells.SaveFormat.Excel97To2003); } else if (format == "xlsx") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx); } else if (format == "doc") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Doc); } else if (format == "docx") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Docx); } }
public void test() // 测试结果, 暂时无法在 Xamarin.Android 中授权, 能够读取Excel文件内容 { //string LData = "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4NCjxMaWNlbnNlPg0KICAgIDxEYXRhPg0KICAgICAgICA8TGljZW5zZWRUbz5pckRldmVsb3BlcnMuY29tPC9MaWNlbnNlZFRvPg0KICAgICAgICA8RW1haWxUbz5pbmZvQGlyRGV2ZWxvcGVycy5jb208L0VtYWlsVG8+DQogICAgICAgIDxMaWNlbnNlVHlwZT5EZXZlbG9wZXIgT0VNPC9MaWNlbnNlVHlwZT4NCiAgICAgICAgPExpY2Vuc2VOb3RlPkxpbWl0ZWQgdG8gMTAwMCBkZXZlbG9wZXIsIHVubGltaXRlZCBwaHlzaWNhbCBsb2NhdGlvbnM8L0xpY2Vuc2VOb3RlPg0KICAgICAgICA8T3JkZXJJRD43ODQzMzY0Nzc4NTwvT3JkZXJJRD4NCiAgICAgICAgPFVzZXJJRD4xMTk0NDkyNDM3OTwvVXNlcklEPg0KICAgICAgICA8T0VNPlRoaXMgaXMgYSByZWRpc3RyaWJ1dGFibGUgbGljZW5zZTwvT0VNPg0KICAgICAgICA8UHJvZHVjdHM+DQogICAgICAgICAgICA8UHJvZHVjdD5Bc3Bvc2UuVG90YWwgUHJvZHVjdCBGYW1pbHk8L1Byb2R1Y3Q+DQogICAgICAgIDwvUHJvZHVjdHM+DQogICAgICAgIDxFZGl0aW9uVHlwZT5FbnRlcnByaXNlPC9FZGl0aW9uVHlwZT4NCiAgICAgICAgPFNlcmlhbE51bWJlcj57RjJCOTcwNDUtMUIyOS00QjNGLUJENTMtNjAxRUZGQTE1QUE5fTwvU2VyaWFsTnVtYmVyPg0KICAgICAgICA8U3Vic2NyaXB0aW9uRXhwaXJ5PjIwOTkxMjMxPC9TdWJzY3JpcHRpb25FeHBpcnk+DQogICAgICAgIDxMaWNlbnNlVmVyc2lvbj4zLjA8L0xpY2Vuc2VWZXJzaW9uPg0KICAgIDwvRGF0YT4NCiAgICA8U2lnbmF0dXJlPlFYTndiM05sTGxSdmRHRnNMb1B5YjJSMVkzUWdSbUZ0YVd4NTwvU2lnbmF0dXJlPg0KPC9MaWNlbnNlPg=="; //System.IO.Stream stream = new System.IO.MemoryStream(Convert.FromBase64String(LData)); //stream.Seek(0, System.IO.SeekOrigin.Begin); //Aspose.Cells.License license = new Aspose.Cells.License(); //license.SetLicense(stream); try { string path = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Aspose.xlsx"); StringBuilder sb = new StringBuilder(); Aspose.Cells.Workbook wb = null; if (System.IO.File.Exists(path) == false) { wb = new Aspose.Cells.Workbook(); } else { wb = new Aspose.Cells.Workbook(path); } wb = new Aspose.Cells.Workbook(); if (wb != null) { bool isLicensed = wb.IsLicensed; if (isLicensed == false) { sb.AppendLine("Run Time {0} : isLicensed = false;".FormatWith(0)); } if (wb.Worksheets.Count > 1) { sb.AppendLine("Run Time {0} : Worksheets Count = {1};".FormatWith(0, wb.Worksheets.Count)); } var ws = wb.Worksheets[0]; Aspose.Cells.Cell cell0 = ws.Cells[0, 0]; string msg = "{0}".FormatWith(cell0.Value); System.Diagnostics.Debug.WriteLine(msg); cell0.Value = "A1 hello"; // 保存 string savePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Aspose_Save.xlsx"); wb.Save(savePath); } } catch (Exception ex) { string msg = "{0}".FormatWith(ex.GetInfo()); System.Diagnostics.Debug.WriteLine(msg); } }
/// <summary> /// Excel 转成 pdf 文件 /// </summary> /// <returns>错误信息,null 代表正常</returns> override protected string ExcelToPDF() { string result = null; Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(this._officeFileName); workbook.Save(this._pdfFileName); return(result); }
public string YLExcel(string originFilePath) { string ExcelHtml = originFilePath.Substring(0, originFilePath.LastIndexOf('.')) + ".html"; Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(originFilePath); workbook.Save(ExcelHtml, Aspose.Cells.SaveFormat.Html); return(ExcelHtml); }
static void Main(string[] args) { Console.WriteLine("Starting process."); var document = new Aspose.Cells.Workbook(@"C:\Users\devin\OneDrive\Documents\COI Report\OPD CSVs\2018\OP_DTL_GNRL_PGYR2018_P06302020 - Copy.csv"); document.Save(@"C:\Users\devin\OneDrive\Documents\COI Report\OPD CSVs\2018\OPD2018GNRL.tsv", Aspose.Cells.SaveFormat.TSV); Console.WriteLine("Process finsihed."); }
private Aspose.Cells.Workbook PrepareWorkbook(string filePath) { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Worksheets[0].Name = "Simulation Data"; workbook.Worksheets[0].Cells.ImportDataTable(simData, true, 0, 0); workbook.Save(filePath); return(workbook); }
private void btnExport_Click(object sender, EventArgs e) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Worksheets[0].Name = "評量輸入狀況檢視清單"; Aspose.Cells.Cells cs = wb.Worksheets[0].Cells; wb.Worksheets[0].Cells.Columns[0].Width = 40; wb.Worksheets[0].Cells.Columns[1].Width = 20; wb.Worksheets[0].Cells.Columns[2].Width = 10; wb.Worksheets[0].Cells.Columns[3].Width = 10; cs[0, 0].PutValue("課程名稱"); cs[0, 1].PutValue("授課老師"); cs[0, 2].PutValue("Midterm"); cs[0, 3].PutValue("Final"); int index = 1; foreach (DataGridViewRow row in dgv.Rows) { if (row.IsNewRow) { continue; } string course_name = row.Cells[colCourse.Index].Value + ""; string teacher_name = row.Cells[colTeacher.Index].Value + ""; string midterm = row.Cells[colExam1.Index].Value + ""; string final = row.Cells[colExam2.Index].Value + ""; cs[index, 0].PutValue(course_name); cs[index, 1].PutValue(teacher_name); cs[index, 2].PutValue(midterm); cs[index, 3].PutValue(final); index++; } //wb.Worksheets[0].AutoFitColumns(); SaveFileDialog save = new SaveFileDialog(); save.Title = "另存新檔"; save.FileName = "評量輸入狀況檢視清單.xls"; save.Filter = "Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*"; if (save.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { wb.Save(save.FileName, Aspose.Cells.SaveFormat.Excel97To2003); System.Diagnostics.Process.Start(save.FileName); } catch { MessageBox.Show("檔案儲存失敗"); } } }
/// <summary> /// 导出风险管控列表 /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult ExportExcel(string queryJson) { try { HttpResponse resp = System.Web.HttpContext.Current.Response; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); Pagination pagination = new Pagination(); pagination.p_kid = ""; pagination.p_fields = "'' num1,post,name,activitystep,case when isconventional=0 then '常规' else '非常规' end as isconventional,others as others1,b.fullname as dept1,to_char(a.createdate,'yyyy-MM-dd') as date1,'' num2,name as equname,areaname,case when isspecialequ=0 then '是' else '否' end as isspecialequ,others as others2,b.fullname as dept2,to_char(a.createdate,'yyyy-MM-dd') as date2,type"; pagination.p_tablename = "bis_baselisting a left join base_department b on a.createuserdeptcode=b.encode"; pagination.conditionJson = "1=1"; pagination.page = 1; pagination.rows = 100000; if (user.RoleName.Contains("公司级") || user.RoleName.Contains("厂级")) { pagination.conditionJson += " and a.CREATEUSERORGCODE ='" + user.OrganizeCode + "'"; } else { pagination.conditionJson += " and a.createuserdeptcode ='" + user.DeptCode + "'"; } var data = baselistingbll.GetPageListJson(pagination, queryJson); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/ExcelTemplate/作业活动及设备设施清单导出模板.xls")); if (data.Rows.Count > 0) { DataTable dt1 = data.Select("type=0").Count() > 0 ? data.Select("type=0").CopyToDataTable() : new DataTable(); if (dt1.Rows.Count > 0) { for (int i = 0; i < dt1.Rows.Count; i++) { dt1.Rows[i]["num1"] = i + 1; } wb.Worksheets[0].Cells.ImportDataTable(dt1, false, 1, 0, data.Rows.Count, 7); } DataTable dt2 = data.Select("type=1").Count() > 0 ? data.Select("type=1").CopyToDataTable() : new DataTable(); if (dt2.Rows.Count > 0) { dt2.Columns.Remove("post"); dt2.Columns.Remove("name"); dt2.Columns.Remove("activitystep"); dt2.Columns.Remove("isconventional"); dt2.Columns.Remove("others1"); dt2.Columns.Remove("dept1"); dt2.Columns.Remove("date1"); dt2.Columns.Remove("num1"); for (int i = 0; i < dt2.Rows.Count; i++) { dt2.Rows[i]["num2"] = i + 1; } wb.Worksheets[1].Cells.ImportDataTable(dt2, false, 1, 0, data.Rows.Count, 7); } } wb.Save(Server.UrlEncode("作业活动及设备设施清单.xls"), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp); return(Success("导出成功。")); } catch (Exception ex) { return(Error(ex.ToString())); } }
public override bool Convert(string sourceFilePath, string targetPdfPath) { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(sourceFilePath); Aspose.Cells.PdfSaveOptions saveOptions = new Aspose.Cells.PdfSaveOptions(); saveOptions.OnePagePerSheet = true; workbook.Save(targetPdfPath, saveOptions); return(File.Exists(targetPdfPath) && new FileInfo(targetPdfPath).Length > 0); }
public static bool DataTableToExcel2_sheet0(DataTable datatable, string filepath, out string error) { error = ""; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } //为单元格添加样式 Aspose.Cells.Style style = wb.Styles[wb.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; for (int i = 0; i < datatable.Columns.Count; i++) { DataColumn col = datatable.Columns[i]; string columnName = col.Caption ?? col.ColumnName; wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName); wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style); } rowIndex++; foreach (DataRow row in datatable.Rows) { for (int i = 0; i < datatable.Columns.Count; i++) { wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString()); } rowIndex++; } for (int k = 0; k < datatable.Columns.Count; k++) { wb.Worksheets[0].AutoFitColumn(k, 0, 150); } wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count); wb.Save(filepath); return(true); } catch (Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
public ActionResult Submit(AssetInventoryViewModel model) { string action = Request["Submit"]; if (action == "close") { if (model.InventoryId > 0) { this.AssetInventoryService.UpdateAssetInventoryStatus(model.InventoryId, AssetInventoryStatus.Closed); } } else if (action == "open") { if (model.InventoryId > 0) { this.AssetInventoryService.UpdateAssetInventoryStatus(model.InventoryId, AssetInventoryStatus.Open); } } else if (action == "exportNoScanInfo") { //导出某次盘点未扫描的资产信息 try { var assetInventory = this.AssetInventoryService.GetAssetInventory(model.InventoryId); var scanedAssetIds = new List <int>(); if (assetInventory != null && assetInventory.AssetInventoryRecords != null) { scanedAssetIds = assetInventory.AssetInventoryRecords.Where(it => it.AssetId.HasValue).Select(it => it.AssetId.Value).Distinct().ToList(); } var workbook = new Aspose.Cells.Workbook(); var descriptor = new ExportExcel <AssetModel>(); AssetSearchModel search = new AssetSearchModel() { BarCode = null, TypeId = null, BuyDate = null, UserName = null }; var assets = this.AssetService.List(search).Where(it => !scanedAssetIds.Contains(it.Id)).ToList(); descriptor.FillAssetData(assets, workbook, 0); var ms = new MemoryStream(); string fileName = assetInventory.Title + "(暂未扫描的资产信息)-" + DateTime.Now.ToString("yyyy-MM-dd") + ".xlsx"; workbook.Save(ms, Aspose.Cells.SaveFormat.Xlsx); return(File(ms.ToArray(), "application/vnd.ms-excel", fileName)); } catch (Exception ex) { ViewBag.Message = ex.Message; } } return(RedirectToAction("Index")); }
private void ExportDataSet(DataSet ds, string filePath, string fileName) { using (Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook()) { workbook.Worksheets[0].Name = fileName; workbook.Worksheets[0].Cells.ImportDataTable(ds.Tables[0], true, 0, 0, true, false); workbook.Save(filePath); } }
public static void Run() { // The path to the documents directory. string dataDir = RunExamples.GetDataDir_Shapes(); Presentation pres = new Presentation(dataDir+ "ChangeOLEObjectData.pptx"); ISlide slide = pres.Slides[0]; OleObjectFrame ole = null; // Traversing all shapes for Ole frame foreach (IShape shape in slide.Shapes) { if (shape is OleObjectFrame) { ole = (OleObjectFrame)shape; } } if (ole != null) { // Reading object data in Workbook Aspose.Cells.Workbook Wb; using (System.IO.MemoryStream msln = new System.IO.MemoryStream(ole.ObjectData)) { Wb = new Aspose.Cells.Workbook(msln); using (System.IO.MemoryStream msout = new System.IO.MemoryStream()) { // Modifying the workbook data Wb.Worksheets[0].Cells[0, 4].PutValue("E"); Wb.Worksheets[0].Cells[1, 4].PutValue(12); Wb.Worksheets[0].Cells[2, 4].PutValue(14); Wb.Worksheets[0].Cells[3, 4].PutValue(15); Aspose.Cells.OoxmlSaveOptions so1 = new Aspose.Cells.OoxmlSaveOptions(Aspose.Cells.SaveFormat.Xlsx); Wb.Save(msout, so1); // Changing Ole frame object data msout.Position = 0; ole.ObjectData = msout.ToArray(); } } } pres.Save(dataDir + "OleEdit_out.pptx", SaveFormat.Pptx); }
public static void Run() { //ExStart:ChangeOLEObjectData // The path to the documents directory. string dataDir = RunExamples.GetDataDir_Shapes(); Presentation pres = new Presentation(dataDir + "ChangeOLEObjectData.pptx"); ISlide slide = pres.Slides[0]; OleObjectFrame ole = null; // Traversing all shapes for Ole frame foreach (IShape shape in slide.Shapes) { if (shape is OleObjectFrame) { ole = (OleObjectFrame)shape; } } if (ole != null) { // Reading object data in Workbook Aspose.Cells.Workbook Wb; using (System.IO.MemoryStream msln = new System.IO.MemoryStream(ole.ObjectData)) { Wb = new Aspose.Cells.Workbook(msln); using (System.IO.MemoryStream msout = new System.IO.MemoryStream()) { // Modifying the workbook data Wb.Worksheets[0].Cells[0, 4].PutValue("E"); Wb.Worksheets[0].Cells[1, 4].PutValue(12); Wb.Worksheets[0].Cells[2, 4].PutValue(14); Wb.Worksheets[0].Cells[3, 4].PutValue(15); Aspose.Cells.OoxmlSaveOptions so1 = new Aspose.Cells.OoxmlSaveOptions(Aspose.Cells.SaveFormat.Xlsx); Wb.Save(msout, so1); // Changing Ole frame object data msout.Position = 0; ole.ObjectData = msout.ToArray(); } } } pres.Save(dataDir + "OleEdit_out.pptx", SaveFormat.Pptx); //ExEnd:ChangeOLEObjectData }
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); } }
internal static void ExportExcelFile(string filename) { try { var path = $"original\\{filename}"; Document doc = new Document(path); // Check for password protection var info = Aspose.Cells.FileFormatUtil.DetectFileFormat(path); if (info.IsEncrypted) { throw new Exception("The excel file is password protected..."); } Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(path); if (book.HasRevisions) { book.AcceptAllRevisions(); } // add footer to each sheet var footerText = string.Empty; footerText = String.Format("Διανομή μέσω 'ΙΡΙΔΑ' με UID: {0} στις {1}", filename, DateTime.Now.ToString("dd/MM/yy HH:mm")); var sheets = book.Worksheets; foreach (var sheet in sheets) { var pSetup = sheet.PageSetup; pSetup.SetFooter(1, footerText); } //set file metadata var properties = book.BuiltInDocumentProperties; properties["Title"].Value = "Δοκιμή Εγγράφου"; properties["Author"].Value = "Aspose Test Excel"; var extIndex = filename.LastIndexOf("."); filename = filename.Remove(extIndex); filename = filename.Insert(extIndex, ".pdf"); var outputPath = $"revised\\{filename}"; book.Save(outputPath, Aspose.Cells.SaveFormat.Pdf); Console.WriteLine("Converted excel document"); } catch (Exception exc) { Console.WriteLine(exc.Message); } }
public void outputSimulationData() { if (simData.Rows.Count > 0) { Aspose.Cells.Workbook workbook = openWorkbook(); int rowToStartAt = workbook.Worksheets[0].Cells.MaxDataRow + 1; workbook.Worksheets[0].Cells.ImportDataTable(simData, false, rowToStartAt, 0); string filePath = System.IO.Directory.GetCurrentDirectory(); filePath += "\\"; filePath += excelFileName; workbook.Save(filePath); simData.Rows.Clear(); } }
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path) { bool succeed = false; if (dt != null) { try { Aspose.Cells.License li = new Aspose.Cells.License(); string lic = @"<License> <Data> <SerialNumber>aed83727-21cc-4a91-bea4-2607bf991c21</SerialNumber> <EditionType>Enterprise</EditionType> <Products> <Product>Aspose.Total</Product> </Products> </Data> <Signature>CxoBmxzcdRLLiQi1kzt5oSbz9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1Fn9KgDAQ8B11psxIWvepKidw8ZmDmbk9kdJbVBOkuAESXDdtDEDZMB/zL7Y=</Signature> </License>"; Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic)); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = "Sheet1"; 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 = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); 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; }
private void 导出ToolStripMenuItem_Click(object sender, EventArgs e) { if (null == this.dgFiles.DataSource) { MessageBox.Show("当前没有任何数据可供导出!"); return; } FolderBrowserDialog openFolder = new FolderBrowserDialog(); openFolder.Description = "请选择要存放的目录!"; if (openFolder.ShowDialog() == System.Windows.Forms.DialogResult.OK) { string folderPath=openFolder.SelectedPath.TrimEnd('\\'); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); List<Model.FileInfo> lst = this.dgFiles.DataSource as List<Model.FileInfo>; wb.Worksheets[0].Cells.ImportDataTable(XCLNetTools.Generic.ListHelper<Model.FileInfo>.ToDataTable((IList<Model.FileInfo>)lst), true, 0, 0); string filePath=string.Format(@"{0}\XCLNetFileRelace_{1:yyyyMMddHHmmssfff}.xlsx", folderPath,DateTime.Now); wb.Save(filePath, Aspose.Cells.SaveFormat.Xlsx); if (MessageBox.Show("导出成功,是否打开该文件?", "系统提示", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { System.Diagnostics.Process.Start(filePath); } } }
/// <summary> /// 开始处理文件 /// </summary> private DataGridViewRow DoIt(DataGridViewRow dr) { DateTime dtStart = DateTime.Now; StringBuilder strRemark = new StringBuilder(); dr.Cells["是否已处理"].Value = "是"; try { #region 验证扩展名及是否为文本文件 string currentExt = dr.Cells["扩展名"].Value.ToString(); string[] defaultExt = { "xls", "xlsx","csv" , "doc", "docx"/*, "ppt", "pptx","pdf"*/ };//这些格式由aspose去处理 string[] excelExt = { "xls", "xlsx", "csv" }; string[] docExt = { "doc", "docx" }; //string[] pptExt = { "ppt", "pptx" }; //string[] pdfExt = { "pdf"}; bool isDefaultExt = defaultExt.Contains(currentExt); bool isExcelExt = excelExt.Contains(currentExt); bool isDocExt = docExt.Contains(currentExt); //bool isPPTExt = pptExt.Contains(currentExt); //bool isPdfExt = pdfExt.Contains(currentExt); bool isTxtFile = XCLNetTools.FileHandler.ComFile.IsTextFile(dr.Cells["路径"].Value.ToString()); if (!isDefaultExt && !isTxtFile && this.ckIsContent.Checked) { //非aspose能处理的文件,且非文本文件,则不能替换内容,只能替换文件名! dr.Cells["备注"].Value = "不支持替换该文件的内容!"; dr.Cells["是否处理成功"].Value = "否"; dr.DefaultCellStyle.ForeColor = System.Drawing.Color.Red; return dr; } #endregion Regex reg = null; #region 是否启用正则替换 if (this.ckIsRegexp.Checked) { reg = this.ckIsNotIgnoreLowerCase.Checked?new Regex(this.txtOldValue.Text):new Regex(this.txtOldValue.Text, RegexOptions.IgnoreCase); } else { string newExpStr = this.ckIsWhole.Checked ? string.Format(@"\b{0}\b",Regex.Escape(this.txtOldValue.Text)) :Regex.Escape(this.txtOldValue.Text); reg = this.ckIsNotIgnoreLowerCase.Checked ? new Regex(newExpStr) : new Regex(newExpStr, RegexOptions.IgnoreCase); } #endregion #region 复制到输出目录并判断是否替换文件名 string filePath = dr.Cells["路径"].Value.ToString(); filePath = filePath.Replace(this.openFileFolderPath.TrimEnd('\\'), this.txtOutPutPath.Text.TrimEnd('\\')); string filetitle = XCLNetTools.FileHandler.ComFile.GetFileName(filePath, false); #region 是否替换文件名 if (this.ckIsFileName.Checked) { strRemark.AppendFormat("文件名替换【{0}】处;", reg.Matches(filetitle).Count); filetitle = reg.Replace(filetitle, this.txtNew.Text); } #endregion filetitle = string.Format("{0}{1}{2}", this.txtFileFirstName.Text, filetitle, this.txtFileLastName.Text); filePath = XCLNetTools.FileHandler.ComFile.GetFileFolderPath(filePath) + "\\" + filetitle + "." + XCLNetTools.FileHandler.ComFile.GetExtName(filePath); XCLNetTools.FileHandler.ComFile.CopyFile(dr.Cells["路径"].Value.ToString(), filePath); if (!System.IO.File.Exists(filePath)) { dr.Cells["备注"].Value = "输出目录中的文件未找到!"; dr.Cells["是否处理成功"].Value = "否"; dr.DefaultCellStyle.ForeColor = System.Drawing.Color.Red; return dr; } #endregion #region 开始替换文件内容 if (this.ckIsContent.Checked) { int replaceCount = 0; if (isDefaultExt) { if (isExcelExt) { #region 处理excel文件 Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(filePath); for (int i = 0; i < wb.Worksheets.Count; i++) { Aspose.Cells.Cells sheetCells = wb.Worksheets[i].Cells; for (int cellsRowIndex = 0; cellsRowIndex < sheetCells.MaxDataRow+1; cellsRowIndex++) { for (int cellsColumn = 0; cellsColumn < sheetCells.MaxDataColumn+1; cellsColumn++) { Aspose.Cells.Cell currentCell = sheetCells[cellsRowIndex, cellsColumn]; string cellValue = Convert.ToString(currentCell.Value); if (!string.IsNullOrEmpty(cellValue)) { replaceCount += reg.Matches(cellValue).Count; cellValue = reg.Replace(cellValue, this.txtNew.Text); currentCell.PutValue(cellValue); } } } } if (replaceCount > 0) { wb.Save(filePath); } #endregion } else if (isDocExt) { #region 处理word //正则无法使用特殊正则,如\s带\的。 Aspose.Words.Document wordDocument = new Aspose.Words.Document(filePath); replaceCount= wordDocument.Range.Replace(reg, this.txtNew.Text); wordDocument.Save(filePath); #endregion } //else if (isPPTExt) //{ // #region 处理PPT // Aspose.Slides.Pptx.PresentationEx pptPres = new Aspose.Slides.Pptx.PresentationEx(filePath); // #endregion //} //else if (isPdfExt) //{ // #region 处理pdf文件 // Aspose.Pdf.Kit.PdfContentEditor pdfEditor = new Aspose.Pdf.Kit.PdfContentEditor(); // pdfEditor.BindPdf(filePath); // pdfEditor.ReplaceText(this.txtOldValue.Text, this.txtNew.Text); // pdfEditor.Save(filePath); // #endregion //} } else { #region 处理文本文件 string fileContent = System.IO.File.ReadAllText(filePath, System.Text.Encoding.Default); replaceCount = reg.Matches(fileContent).Count; fileContent = reg.Replace(fileContent, this.txtNew.Text); System.IO.File.WriteAllText(filePath, fileContent, System.Text.Encoding.Default); #endregion } strRemark.AppendFormat("文件内容替换【{0}】处;", replaceCount); } #endregion if (strRemark.Length > 0) { dr.Cells["备注"].Value = strRemark.ToString(); } dr.Cells["是否处理成功"].Value = "是"; dr.DefaultCellStyle.ForeColor = System.Drawing.Color.Green; } catch(Exception e) { dr.Cells["是否处理成功"].Value = "否"; dr.DefaultCellStyle.ForeColor = System.Drawing.Color.Red; dr.Cells["备注"].Value = e.Message; } DateTime dtEnd = DateTime.Now; dr.Cells["处理用时"].Value =Math.Round((decimal)(dtEnd.Subtract(dtStart).TotalMilliseconds/1000.0),1); return dr; }
void _BGWorker_DoWork(object sender, DoWorkEventArgs e) { StudentScore.SetClassMapping(); // 取得學生類別List List<K12.Data.StudentTagRecord> StudTagRecList = K12.Data.StudentTag.SelectAll(); // 過濾不排學生ID List<string> NonStudentIDList = DAL.DALTransfer.GetNonRankStudentIDFromUDTByStudentTag(StudTagRecList, JointAdmissionModule.DAL.DALTransfer.SchoolType.五專); foreach (string id in NonStudentIDList) if (StudentIDs.Contains(id)) StudentIDs.Remove(id); List<ReportStudent> PrintStudents = StudentIDs.ToReportStudent(); List<ReportStudent> RatingStudents = Util.GetStudentsDef(NonStudentIDList); RatingStudents.ToSC().ReadSemesterScore(this); RatingStudents.ToSC().ReadSemesterHistory(this); // 讀取三學期學期領域成績,填入ReportStudent foreach (ReportStudent stud in RatingStudents) { // 取得2,3年級學期歷程 List<SemesterData> semtList = (from xx in stud.SHistory where xx.GradeYear == 2 || xx.GradeYear == 8 || xx.GradeYear == 3 || xx.GradeYear == 9 select xx).ToList(); foreach (SemesterData sd in semtList) { foreach (SemesterScore ss in stud.SemestersScore) { // 二年級 if (sd.GradeYear == 2 || sd.GradeYear ==8) { if (sd.SchoolYear == ss.SchoolYear) { if (ss.Semester == 1) { if (ss.LearnDomainScore.HasValue) stud.LearnDomainScore81 = ss.LearnDomainScore.Value; else stud.LearnDomainScore81 = 0; } if (ss.Semester == 2) { if (ss.LearnDomainScore.HasValue) stud.LearnDomainScore82 = ss.LearnDomainScore.Value; else stud.LearnDomainScore82 = 0; } } } // 三上 if ((sd.GradeYear == 3 || sd.GradeYear == 9) && sd.Semester == 1) { if (sd.SchoolYear == ss.SchoolYear && ss.Semester == 1) { if (ss.LearnDomainScore.HasValue) stud.LearnDomainScore91 = ss.LearnDomainScore.Value; else stud.LearnDomainScore91 = 0; } } } } } List<IScoreParser<ReportStudent>> parsers = new List<IScoreParser<ReportStudent>>(); List<SLearningDomainParser> allsems = new List<SLearningDomainParser>(); List<int> semInt = new List<int>(); semInt.Add(3); semInt.Add(4); semInt.Add(5); parsers.Add(new LearningDomainParser(semInt)); allsems.Add(new SLearningDomainParser(1, 1)); allsems.Add(new SLearningDomainParser(1, 2)); allsems.Add(new SLearningDomainParser(2, 1)); allsems.Add(new SLearningDomainParser(2, 2)); allsems.Add(new SLearningDomainParser(3, 1)); allsems.Add(new SLearningDomainParser(3, 2)); allsems.Add(new SLearningDomainParser(7, 1)); allsems.Add(new SLearningDomainParser(7, 2)); allsems.Add(new SLearningDomainParser(8, 1)); allsems.Add(new SLearningDomainParser(8, 2)); allsems.Add(new SLearningDomainParser(9, 1)); allsems.Add(new SLearningDomainParser(9, 2)); List<string> DomainNameList = DAL.DALTransfer.GetDomainNameList(); //DomainNameList.Add("國語文"); //DomainNameList.Add("英語"); //DomainNameList.Add("數學"); //DomainNameList.Add("社會"); //DomainNameList.Add("自然與生活科技"); //DomainNameList.Add("藝術與人文"); //DomainNameList.Add("健康與體育"); //DomainNameList.Add("綜合活動"); // 放領域排名 List<DomainParser> AllDomainSems = new List<DomainParser>(); // 取得各領域三學期平均排名 foreach (string name in DomainNameList) { DomainParser dp = new DomainParser(name,100); AllDomainSems.Add(dp); //AllDomainSems.Add(new DomainParser(name)); } foreach (SLearningDomainParser each in allsems) parsers.Add(each); foreach(DomainParser each in AllDomainSems) parsers.Add(each); // 將學生加入年排名 List<RatingScope<ReportStudent>> scopes = RatingStudents.ToGradeYearScopes(); foreach (RatingScope<ReportStudent> each in scopes) foreach (IScoreParser<ReportStudent> parser in parsers) each.Rank(parser, PlaceOptions.Unsequence); Dictionary<string, StudentScore> DicPrintStudents = PrintStudents.ToSC().ToDictionary(); foreach (ReportStudent each in RatingStudents) { if (DicPrintStudents.ContainsKey(each.Id)) DicPrintStudents[each.Id] = each; } // 整理全部學生年排名 DAL.DALTransfer.StudRankScoreDict.Clear(); // 建立Key foreach (RatingScope<ReportStudent> scope in scopes) { DAL.DALTransfer.StudRankScoreDict.Add(scope.Name, new Dictionary<string, List<JointAdmissionModule.DAL.StudRankScore>>()); DAL.DALTransfer.StudRankScoreDict[scope.Name].Add("學期總平均", new List<JointAdmissionModule.DAL.StudRankScore>()); // 領域平均 foreach (SLearningDomainParser semsIndex in allsems) DAL.DALTransfer.StudRankScoreDict[scope.Name].Add(semsIndex.Name, new List<JointAdmissionModule.DAL.StudRankScore>()); // 各領域 foreach (DomainParser semsIdx in AllDomainSems) DAL.DALTransfer.StudRankScoreDict[scope.Name].Add(semsIdx.Name, new List<JointAdmissionModule.DAL.StudRankScore>()); } foreach (RatingScope<ReportStudent> scope in scopes) { // 學習領域 foreach (ReportStudent stud in (from xx in scope where xx.Places.NS("年排名").Contains("學習領域") select xx).ToList()) { DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); srs.StudentID = stud.StudentID; srs.Place = stud.Places.NS("年排名")["學習領域"]; DAL.DALTransfer.StudRankScoreDict[scope.Name]["學期總平均"].Add(srs); } // 領域平均 foreach (SLearningDomainParser semsIndex in allsems) { foreach (ReportStudent stud in (from xx in scope where xx.Places.NS("年排名").Contains(semsIndex.Name) select xx).ToList()) { DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); srs.StudentID = stud.StudentID; srs.Place = stud.Places.NS("年排名")[semsIndex.Name]; DAL.DALTransfer.StudRankScoreDict[scope.Name][semsIndex.Name].Add(srs); } } // 各領域 foreach (DomainParser semsIndex in AllDomainSems) { foreach (ReportStudent stud in (from xx in scope where xx.Places.NS("年排名").Contains(semsIndex.Name) select xx).ToList()) { DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); srs.StudentID = stud.StudentID; srs.Place = stud.Places.NS("年排名")[semsIndex.Name]; DAL.DALTransfer.StudRankScoreDict[scope.Name][semsIndex.Name].Add(srs); } } } //foreach (RatingScope<ReportStudent> scope in scopes) //{ // if (!DAL.DALTransfer.StudRankScoreDict.ContainsKey(scope.Name)) // DAL.DALTransfer.StudRankScoreDict.Add(scope.Name, new Dictionary<string, List<DAL.StudRankScore>>()); // foreach (ReportStudent stud in scope) // { // if (stud.GradeYear != scope.Name) continue; // // 學期總平均 // if (stud.Places.NS("年排名").Contains("學習領域")) // { // if (!DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) // DAL.DALTransfer.StudRankScoreDict.Add(stud.GradeYear, new Dictionary<string, List<DAL.StudRankScore>>()); // DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); // srs.StudentID = stud.StudentID; // srs.Place = stud.Places.NS("年排名")["學習領域"]; // if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey("學期總平均")) // { // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear]["學期總平均"].Add(srs); // } // else // { // List<DAL.StudRankScore> srsList = new List<JointAdmissionModule.DAL.StudRankScore>(); // srsList.Add(srs); // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].Add("學期總平均", srsList); // } // } // // 學期平均 // foreach (SLearningDomainParser semsIndex in allsems) // { // if (stud.GradeYear != semsIndex.Grade.ToString()) continue; // if (stud.Places.NS("年排名").Contains(semsIndex.Name)) // { // if (!DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) // DAL.DALTransfer.StudRankScoreDict.Add(stud.GradeYear, new Dictionary<string, List<DAL.StudRankScore>>()); // DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); // srs.StudentID = stud.StudentID; // srs.Place = stud.Places.NS("年排名")[semsIndex.Name]; // if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(semsIndex.Name)) // { // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][semsIndex.Name].Add(srs); // } // else // { // List<DAL.StudRankScore> srsList = new List<JointAdmissionModule.DAL.StudRankScore>(); // srsList.Add(srs); // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].Add(semsIndex.Name, srsList); // } // } // } // // 各領域 // foreach (DomainParser semsIdx in AllDomainSems) // { // if (stud.GradeYear != semsIdx.Grade.ToString()) continue; // if (stud.Places.NS("年排名").Contains(semsIdx.Name)) // { // if (!DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) // DAL.DALTransfer.StudRankScoreDict.Add(stud.GradeYear, new Dictionary<string, List<DAL.StudRankScore>>()); // DAL.StudRankScore srs = new JointAdmissionModule.DAL.StudRankScore(); // srs.StudentID = stud.StudentID; // srs.Place = stud.Places.NS("年排名")[semsIdx.Name]; // if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(semsIdx.Name)) // { // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][semsIdx.Name].Add(srs); // } // else // { // List<DAL.StudRankScore> srsList = new List<JointAdmissionModule.DAL.StudRankScore>(); // srsList.Add(srs); // DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].Add(semsIdx.Name, srsList); // } // } // } // } //} //// 排序對照年排名資料 debug 用 //for (int i = 1; i <= 9; i++) //{ // if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(i.ToString())) // { // string idx = i.ToString(); // foreach (SLearningDomainParser semsIndex in allsems) // { // if (DAL.DALTransfer.StudRankScoreDict[idx].ContainsKey(semsIndex.Name)) // { // var x = from xx in DAL.DALTransfer.StudRankScoreDict[idx][semsIndex.Name] orderby xx.Place.Score descending select xx; // DAL.DALTransfer.StudRankScoreDict[idx][semsIndex.Name] = x.ToList(); // } // } // } //} // 排序對照年排名資料 debug 用 Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); Dictionary<string, ReportStudent> students = RatingStudents.ToDictionary(x => x.Id); int wstCot = 0; foreach (RatingScope<ReportStudent> scope in scopes) { if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(scope.Name)) { string idx = scope.Name; string AvggName="學期總平均"; if (DAL.DALTransfer.StudRankScoreDict[idx].ContainsKey(AvggName)) { int row = 1; wb.Worksheets.Add(); wb.Worksheets[wstCot].Name = idx + AvggName; wb.Worksheets[wstCot].Cells[0, 0].PutValue("成績"); wb.Worksheets[wstCot].Cells[0, 1].PutValue("年排名"); wb.Worksheets[wstCot].Cells[0, 2].PutValue("分類"); wb.Worksheets[wstCot].Cells[0, 3].PutValue("年級"); wb.Worksheets[wstCot].Cells[0, 4].PutValue("SID"); wb.Worksheets[wstCot].Cells[0, 5].PutValue("StudCount"); foreach (JointAdmissionModule.DAL.StudRankScore xx in DAL.DALTransfer.StudRankScoreDict[idx][AvggName].OrderByDescending(x => x.Place.Score)) { wb.Worksheets[wstCot].Cells[row, 0].PutValue(xx.Place.Score); wb.Worksheets[wstCot].Cells[row, 1].PutValue(xx.Place.Percentage); wb.Worksheets[wstCot].Cells[row, 2].PutValue(AvggName); wb.Worksheets[wstCot].Cells[row, 3].PutValue(idx); wb.Worksheets[wstCot].Cells[row, 4].PutValue(xx.StudentID); wb.Worksheets[wstCot].Cells[row, 5].PutValue(xx.Place.Radix); wb.Worksheets[wstCot].Cells[row, 6].PutValue(students[xx.StudentID].GradeYear); row++; } wstCot++; } foreach (SLearningDomainParser semsIndex in allsems) { int row = 1; wb.Worksheets.Add(); wb.Worksheets[wstCot].Name = idx + semsIndex.Name.Replace(":", "-"); wb.Worksheets[wstCot].Cells[0, 0].PutValue("成績"); wb.Worksheets[wstCot].Cells[0, 1].PutValue("年排名"); wb.Worksheets[wstCot].Cells[0, 2].PutValue("分類"); wb.Worksheets[wstCot].Cells[0, 3].PutValue("年級"); wb.Worksheets[wstCot].Cells[0, 4].PutValue("SID"); wb.Worksheets[wstCot].Cells[0, 5].PutValue("StudCount"); if (DAL.DALTransfer.StudRankScoreDict[idx].ContainsKey(semsIndex.Name)) { foreach (JointAdmissionModule.DAL.StudRankScore xx in DAL.DALTransfer.StudRankScoreDict[idx][semsIndex.Name].OrderByDescending(x => x.Place.Score)) { wb.Worksheets[wstCot].Cells[row, 0].PutValue(xx.Place.Score); wb.Worksheets[wstCot].Cells[row, 1].PutValue(xx.Place.Percentage); wb.Worksheets[wstCot].Cells[row, 2].PutValue(semsIndex.Name); wb.Worksheets[wstCot].Cells[row, 3].PutValue(semsIndex.Grade); wb.Worksheets[wstCot].Cells[row, 4].PutValue(xx.StudentID); wb.Worksheets[wstCot].Cells[row, 5].PutValue(xx.Place.Radix); wb.Worksheets[wstCot].Cells[row, 6].PutValue(students[xx.StudentID].GradeYear); row++; } } wstCot++; } foreach (DomainParser semsIndex in AllDomainSems) { int row = 1; wb.Worksheets.Add(); wb.Worksheets[wstCot].Name = idx + semsIndex.Name.Replace(":", "-"); wb.Worksheets[wstCot].Cells[0, 0].PutValue("成績"); wb.Worksheets[wstCot].Cells[0, 1].PutValue("年排名"); wb.Worksheets[wstCot].Cells[0, 2].PutValue("分類"); wb.Worksheets[wstCot].Cells[0, 3].PutValue("年級"); wb.Worksheets[wstCot].Cells[0, 4].PutValue("SID"); wb.Worksheets[wstCot].Cells[0, 5].PutValue("StudCount"); if (DAL.DALTransfer.StudRankScoreDict[idx].ContainsKey(semsIndex.Name)) { foreach (JointAdmissionModule.DAL.StudRankScore xx in DAL.DALTransfer.StudRankScoreDict[idx][semsIndex.Name].OrderByDescending(x => x.Place.Score)) { wb.Worksheets[wstCot].Cells[row, 0].PutValue(xx.Place.Score); wb.Worksheets[wstCot].Cells[row, 1].PutValue(xx.Place.Percentage); wb.Worksheets[wstCot].Cells[row, 2].PutValue(semsIndex.Name); wb.Worksheets[wstCot].Cells[row, 3].PutValue(semsIndex.Grade); wb.Worksheets[wstCot].Cells[row, 4].PutValue(xx.StudentID); wb.Worksheets[wstCot].Cells[row, 5].PutValue(xx.Place.Radix); wb.Worksheets[wstCot].Cells[row, 6].PutValue(students[xx.StudentID].GradeYear); row++; } } wstCot++; } } } for (int i = 0; i < wb.Worksheets.Count; i++) { if (wb.Worksheets[i].Cells.MaxDataRow < 2) wb.Worksheets.RemoveAt(i); } try { wb.Save(Application.StartupPath + "\\五專成績debug.xls", Aspose.Cells.FileFormatType.Excel2003); //System.Diagnostics.Process.Start(Application.StartupPath + "\\五專成績debug.xls"); } catch (Exception ex) { } if (true) { List<ReportStudent> filteredStudent = new List<ReportStudent>(); foreach (ReportStudent each in DicPrintStudents.Values.ToSS()) { //看是否有「學習領域」的年排名。 if (each.Places.NS("年排名").Contains(LearningDomainParser.PlaceName)) { Place place = each.Places.NS("年排名")[LearningDomainParser.PlaceName]; filteredStudent.Add(each); } PrintStudents = filteredStudent; } } else PrintStudents = new List<ReportStudent>(DicPrintStudents.Values.ToSS()); PrintStudents.ToSC().ReadSemesterScore(this); PrintStudents.ToSC().ReadSemesterHistory(this); List<StudentScore> CalcStudents; if (PrintStudents.Count <= 0) { Feedback("", -1); //把 Status bar Reset... throw new ArgumentException("沒有任何學生資料可列印。"); } else { CalcStudents = PrintStudents.ToSC(); CalcStudents.ReadCalculationRule(this); //讀取成績計算規則。 PrintStudents.ReadUpdateRecordDate(this); e.Result = new Report(PrintStudents,txtReportTitle.Text); Feedback("列印完成", -1); } }
protected string ConvertDoc(string doc) { if (string.IsNullOrEmpty(doc)) return null; var file = new FileInfo(doc); if (!file.Exists) return null; var suffix = file.Extension.Replace(".", "").ToLower(); var path = string.Format("{0}.pdf", file.FullName.Substring(0, file.FullName.LastIndexOf('.'))); var newFile = new FileInfo(path); if (newFile.Exists) return path; switch (suffix) { case "doc": case "docx": case "txt": case "rtf": var docW = new Aspose.Words.Document(file.FullName); docW.Save(path, Aspose.Words.SaveFormat.Pdf); return path; case "ppt": case "pptx": var docP = new Aspose.Slides.Presentation(file.FullName); docP.Save(path, Aspose.Slides.Export.SaveFormat.Pdf); return path; case "xls": case "xlsx": var docE = new Aspose.Cells.Workbook(file.FullName); docE.Save(path, Aspose.Cells.SaveFormat.Pdf); return path; default: return null; } }
protected void publish_attachment_upload_OnFileUploaded(object sender, FileUploadedEventArgs e) { var aid = HomoryContext.Value.GetId(); var id = CurrentResource.Id; var file = e.File; var name = string.Format("../Common/资源/{2}/附件/{1}_{0}", file.FileName, aid, CurrentUser.Id.ToString().ToUpper()); var sourceX = Server.MapPath(name); var pathX = string.Format("../Common/资源/{2}/附件/{1}_{0}", file.GetNameWithoutExtension(), aid, CurrentUser.Id.ToString().ToUpper()); file.SaveAs(Server.MapPath(name), true); ResourceFileType type; switch (file.GetExtension().Replace(".", "")) { case "jpg": case "jpeg": case "png": case "gif": case "bmp": type = ResourceFileType.Image; break; case "rar": case "zip": case "7z": type = ResourceFileType.Zip; break; case "doc": case "docx": case "txt": case "rtf": type = ResourceFileType.Word; pathX += ".pdf"; pathX = Server.MapPath(pathX); var docW = new Aspose.Words.Document(sourceX); docW.Save(pathX, Aspose.Words.SaveFormat.Pdf); break; case "ppt": case "pptx": type = ResourceFileType.Powerpoint; pathX += ".pdf"; pathX = Server.MapPath(pathX); var docP = new Aspose.Slides.Presentation(sourceX); docP.Save(pathX, Aspose.Slides.Export.SaveFormat.Pdf); break; case "xls": case "xlsx": type = ResourceFileType.Excel; pathX += ".pdf"; pathX = Server.MapPath(pathX); var docE = new Aspose.Cells.Workbook(sourceX); docE.Save(pathX, Aspose.Cells.SaveFormat.Pdf); break; case "pdf": type = ResourceFileType.Pdf; break; case "mp3": case "wma": type = ResourceFileType.Audio; break; default: type = ResourceFileType.Media; break; } var ra = new ResourceAttachment { Id = aid, ResourceId = id, FileType = type, Title = file.GetName(), Remark = remarkTextbox.Text, Source = name, State = State.启用 }; HomoryContext.Value.ResourceAttachment.Add(ra); HomoryContext.Value.SaveChanges(); }
/// <summary> /// 產生匯入檔 /// </summary> private void ExportInputFile() { List<ReportStudentData> ExportData = new List<ReportStudentData>(); List<string> SIDList = (from x in Students select x.StudentID).ToList(); // 父母資料 List<JHParentRecord> ParentRecList = JHParent.SelectByStudentIDs(SIDList); // 地址 List<JHAddressRecord> AddressRecList = JHAddress.SelectByStudentIDs(SIDList); // 電話 List<JHPhoneRecord> PhoneRecList = JHPhone.SelectByStudentIDs(SIDList); List<SLearningDomainParser> allsems = new List<SLearningDomainParser>(); allsems.Add(new SLearningDomainParser(1, 1)); allsems.Add(new SLearningDomainParser(1, 2)); allsems.Add(new SLearningDomainParser(2, 1)); allsems.Add(new SLearningDomainParser(2, 2)); allsems.Add(new SLearningDomainParser(3, 1)); allsems.Add(new SLearningDomainParser(3, 2)); allsems.Add(new SLearningDomainParser(7, 1)); allsems.Add(new SLearningDomainParser(7, 2)); allsems.Add(new SLearningDomainParser(8, 1)); allsems.Add(new SLearningDomainParser(8, 2)); allsems.Add(new SLearningDomainParser(9, 1)); allsems.Add(new SLearningDomainParser(9, 2)); //// 取得 UDT 對照 //List<JointAdmissionModule.DAL.UserDefData_StudTypeWeight> udtList = DAL.UDTTransfer.GetDataFromUDT_StudTypeWeight().Where(x => x.SchoolType == "高中").ToList(); //// 建立特種身分代碼對照表 //Dictionary<string, string> specCodeDict = new Dictionary<string, string>(); //foreach (JointAdmissionModule.DAL.UserDefData_StudTypeWeight dd in udtList) // if (!specCodeDict.ContainsKey(dd.JoinStudType)) // specCodeDict.Add(dd.JoinStudType, dd.JoinStudTypeCode); // 資料轉換 foreach (ReportStudent stud in Students) { // 是否已有年排名資料 List<DAL.UserDefData> uddList = new List<JointAdmissionModule.DAL.UserDefData>(); if (_StudRankData.ContainsKey(stud.StudentID)) uddList = _StudRankData[stud.StudentID]; ReportStudentData rsd = new ReportStudentData(); // 班級 if (stud.Class != null) rsd.ClassName = stud.Class.Name; // 座號 rsd.SeatNo = stud.SeatNo; // 姓名 rsd.Name = stud.Name; // 家長姓名 foreach (JHParentRecord rec in ParentRecList.Where(x => x.RefStudentID == stud.StudentID)) { rsd.ParentName = rec.CustodianName; } foreach (JHPhoneRecord rec in PhoneRecList.Where(x => x.RefStudentID == stud.StudentID)) { // 電話 rsd.Phone = rec.Contact; // 手機 rsd.CellPhone = rec.Cell; } // 生日 DateTime dt; if (DateTime.TryParse(stud.Birthday, out dt)) { rsd.Birthday = dt; } // 性別 if (stud.Gender == "男") rsd.GenderCode = "1"; if (stud.Gender == "女") rsd.GenderCode = "2"; // 身分證字號 rsd.IDNumber = stud.IDNumber; foreach (JHAddressRecord rec in AddressRecList.Where(x => x.RefStudentID == stud.StudentID)) { // 郵區號 rsd.ZipCode = rec.MailingZipCode; // 聯絡地址 rsd.Address = rec.MailingCounty + rec.MailingTown + rec.MailingDistrict + rec.MailingArea + rec.MailingDetail; } //string code = ""; // 特種身分(原民未持1、原民認證2、身心障礙3、其他4) if (!string.IsNullOrEmpty(stud.SpcStudTypeName)) { rsd.SpceTypeCode = 4; if (stud.SpcStudTypeName == StudSpcName1) rsd.SpceTypeCode = 2; if (stud.SpcStudTypeName == StudSpcName2) rsd.SpceTypeCode = 1; if (stud.SpcStudTypeName == StudSpcName6) rsd.SpceTypeCode = 3; } PlaceCollection Places = stud.Places.NS("年排名"); // 這寫法主要判斷當名次在不同年級時可能會讀到錯誤資料 //if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } //else foreach (SLearningDomainParser semsIndex in allsems) { if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 1) { // 一上名次 // 一上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank11 = Places[placeKey].Level; rsd.RankPercent11 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1,Percentage=1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank11 = Level; rsd.RankPercent11 = Percentage; } } } } } } if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 2) { // 一下名次 // 一下名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank12 = Places[placeKey].Level; rsd.RankPercent12 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank12 = Level; rsd.RankPercent12 = Percentage; } } } } } } if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 1) { // 二上名次 // 二上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank21 = Places[placeKey].Level; rsd.RankPercent21 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.Rank21 = Level; rsd.RankPercent21 = Percentage; } } } } } if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 2) { // 二下名次 // 二下名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank22 = Places[placeKey].Level; rsd.RankPercent22 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank22 = Level; rsd.RankPercent22 = Percentage; } } } } } } if ((semsIndex.Grade == 3 || semsIndex.Grade == 9) && semsIndex.sm == 1) { // 三上名次 // 三上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank31 = Places[placeKey].Level; rsd.RankPercent31 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.Rank31 = Level; rsd.RankPercent31 = Percentage; } } } } } } if (Places.Contains("學習領域")) { string placeKey = "學習領域"; // 五學期名次 rsd.AvgRank5 = Places[placeKey].Level; // 五學期名次百分比 rsd.AvgRankPercent5 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { placeKey = "學期總平均"; if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.AvgRank5 = Level; rsd.AvgRankPercent5 = Percentage; } } } // 處理已有年排名(在UDT有存資料,填入 UDT 資料) if (uddList.Count > 0 && stud.LastEnterSemester.HasValue) { foreach (DAL.UserDefData udd in uddList) { // 七上 if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 1) { rsd.Rank11 = udd.GradeRank; rsd.RankPercent11 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank11 = udd.GradeRankAdd; rsd.RankPercent11 = udd.GradeRankPercentAdd; } } // 七下 if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 2) { rsd.Rank12 = udd.GradeRank; rsd.RankPercent12 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank12 = udd.GradeRankAdd; rsd.RankPercent12 = udd.GradeRankPercentAdd; } } // 八上 if ((udd.GradeYear==2 ||udd.GradeYear==8) && udd.Semester ==1) { rsd.Rank21 = udd.GradeRank; rsd.RankPercent21 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank21 = udd.GradeRankAdd; rsd.RankPercent21 = udd.GradeRankPercentAdd; } } // 八下 if ((udd.GradeYear == 2 || udd.GradeYear == 8) && udd.Semester == 2) { rsd.Rank22 = udd.GradeRank; rsd.RankPercent22 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank22 = udd.GradeRankAdd; rsd.RankPercent22 = udd.GradeRankPercentAdd; } } // 九上 if ((udd.GradeYear == 3 || udd.GradeYear == 9) && udd.Semester == 1) { rsd.Rank31 = udd.GradeRank; rsd.RankPercent31 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank31 = udd.GradeRankAdd; rsd.RankPercent31 = udd.GradeRankPercentAdd; } } } } // 判斷轉入生,清空之前名次 if (stud.LastEnterGradeYear.HasValue && stud.LastEnterSchoolyear.HasValue ) { int grYear = 0; // 先判斷年級 if (stud.LastEnterGradeYear >= 6) { grYear = stud.LastEnterGradeYear.Value - 6; } else { grYear = stud.LastEnterGradeYear.Value; } // 判斷udd有輸入資料 if (grYear == 3 && stud.LastEnterSemester == 1) { bool clear11 = true, clear12 = true, clear21 = true, clear22 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; if (udd.GradeYear == 2 && udd.Semester == 1) clear21 = false; if (udd.GradeYear == 2 && udd.Semester == 2) clear22 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } if (clear21) { rsd.Rank21 = null; rsd.RankPercent21 = null; } if (clear22) { rsd.Rank22 = null; rsd.RankPercent22 = null; } } if (grYear == 2 && stud.LastEnterSemester == 2) { bool clear11 = true, clear12 = true, clear21 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; if (udd.GradeYear == 2 && udd.Semester == 1) clear21 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } if (clear21) { rsd.Rank21 = null; rsd.RankPercent21 = null; } } if (grYear == 2 && stud.LastEnterSemester == 1) { bool clear11 = true, clear12 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } } if (grYear == 1 && stud.LastEnterSemester == 2) { bool clear11 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } } // 當沒有輸入全部清空 if (uddList.Count == 0) { if (grYear == 3 && stud.LastEnterSemester == 1) { rsd.Rank22 = null; rsd.Rank21 = null; rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent22 = null; rsd.RankPercent21 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 2 && stud.LastEnterSemester == 2) { rsd.Rank21 = null; rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent21 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 2 && stud.LastEnterSemester == 1) { rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 1 && stud.LastEnterSemester == 2) { rsd.Rank11 = null; rsd.RankPercent11 = null; } } } rsd.UpdateDate3 = stud.TransUpdateDateStr; ExportData.Add(rsd); } // 印報表 if (ExportData.Count > 0) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(Properties.Resources._100高雄高中多元入學匯入檔)); Aspose.Cells.Worksheet wst = wb.Worksheets[0]; int RowIdx = 1; foreach (ReportStudentData rsd in ExportData) { // 班級 0 wst.Cells[RowIdx, 0].PutValue(rsd.ClassName); // 座號 1 wst.Cells[RowIdx, 1].PutValue(string.Format("{0:00}",rsd.SeatNo)); // 姓名 2 wst.Cells[RowIdx, 2].PutValue(rsd.Name); // 家長姓名 3 wst.Cells[RowIdx, 3].PutValue(rsd.ParentName); if (rsd.Birthday.HasValue) { // 年 4 wst.Cells[RowIdx, 4].PutValue((rsd.Birthday.Value.Year -1911)); // 月 5 wst.Cells[RowIdx, 5].PutValue(rsd.Birthday.Value.Month); // 日 6 wst.Cells[RowIdx, 6].PutValue(rsd.Birthday.Value.Day); } // 性別 7 wst.Cells[RowIdx, 7].PutValue(rsd.GenderCode); // 身分證字號 8 wst.Cells[RowIdx, 8].PutValue(rsd.IDNumber); // 郵區號 9 wst.Cells[RowIdx, 9].PutValue(rsd.ZipCode); // 聯絡地址 10 wst.Cells[RowIdx, 10].PutValue(rsd.Address); // 電話 11 wst.Cells[RowIdx, 11].PutValue(rsd.Phone); // 手機 12 wst.Cells[RowIdx, 12].PutValue(rsd.CellPhone); // 特種身分(原民未持1、原民認證2、身心障礙3、其他4) 13 if(rsd.SpceTypeCode.HasValue) wst.Cells[RowIdx, 13].PutValue(rsd.SpceTypeCode.Value); else wst.Cells[RowIdx, 13].PutValue(""); // 一上名次 14 if(rsd.Rank11.HasValue) wst.Cells[RowIdx, 15].PutValue(rsd.Rank11.Value); // 一上名次百分比 15 if(rsd.RankPercent11.HasValue ) wst.Cells[RowIdx, 16].PutValue(rsd.RankPercent11.Value); // 一下名次 16 if(rsd.Rank12.HasValue) wst.Cells[RowIdx, 17].PutValue(rsd.Rank12.Value); // 一下名次百分比 17 if(rsd.RankPercent12.HasValue) wst.Cells[RowIdx, 18].PutValue(rsd.RankPercent12.Value); // 二上名次 18 if(rsd.Rank21.HasValue ) wst.Cells[RowIdx, 19].PutValue(rsd.Rank21.Value); // 二上名次百分比 19 if(rsd.RankPercent21.HasValue ) wst.Cells[RowIdx, 20].PutValue(rsd.RankPercent21.Value); // 二下名次 20 if(rsd.Rank22.HasValue) wst.Cells[RowIdx, 21].PutValue(rsd.Rank22.Value); // 二下名次百分比 21 if(rsd.RankPercent22.HasValue) wst.Cells[RowIdx, 22].PutValue(rsd.RankPercent22.Value); // 三上名次 22 if(rsd.Rank31.HasValue) wst.Cells[RowIdx, 23].PutValue(rsd.Rank31.Value); // 三上名次百分比 23 if(rsd.RankPercent31.HasValue ) wst.Cells[RowIdx, 24].PutValue(rsd.RankPercent31.Value); // 五學期名次 24 if(rsd.AvgRank5.HasValue ) wst.Cells[RowIdx, 25].PutValue(rsd.AvgRank5.Value); // 五學期名次百分比 25 if(rsd.AvgRankPercent5.HasValue ) wst.Cells[RowIdx, 26].PutValue(rsd.AvgRankPercent5.Value); // 是否是轉學生1是,否空白 if(!string.IsNullOrEmpty(rsd.UpdateDate3)) wst.Cells[RowIdx, 27].PutValue("1"); RowIdx++; } string FileName = "高中(高雄)用國中匯入檔.xls"; // 儲存檔案 try { string path = Path.Combine(System.Windows.Forms.Application.StartupPath, "Reports"); DirectoryInfo dir = new DirectoryInfo(path); if (!dir.Exists) dir.Create(); path = Path.Combine(path, FileName); if (File.Exists(path)) { int i = 1; while (true) { string newPath = Path.GetDirectoryName(path) + "\\" + Path.GetFileNameWithoutExtension(path) + (i++) + Path.GetExtension(path); if (!File.Exists(newPath)) { path = newPath; break; } } } wb.Save(path, Aspose.Cells.FileFormatType.Excel2003); if (MsgBox.Show(FileName + "產生完成,是否立刻開啟?", MessageBoxButtons.YesNo) == DialogResult.Yes) { System.Diagnostics.Process.Start(path); } } catch (Exception ex) { MsgBox.Show("儲存失敗"); } } }
public static bool ExportExcelWithAspose(DataSet ds, string path) { bool succeed = false; if (ds != null&&ds.Tables.Count>0) { try { Aspose.Cells.License li = new Aspose.Cells.License(); var lic = Resources.License; Stream s = new MemoryStream(lic); li.SetLicense(s); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); for (int tableCount = 0; tableCount < ds.Tables.Count; tableCount++) { workbook.Worksheets.Add(ds.Tables[tableCount].TableName); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[tableCount]; // cellSheet.Name = ds.Tables[tableCount].TableName; int rowIndex = 0; int colIndex = 0; int colCount = ds.Tables[tableCount].Columns.Count; int rowCount = ds.Tables[tableCount].Rows.Count; //列名的处理 for (int i = 0; i < colCount; i++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Columns[i].ColumnName); cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true; cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体"; colIndex++; } Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 10; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); cellSheet.Cells.ApplyStyle(style, styleFlag); rowIndex++; for (int i = 0; i < rowCount; i++) { colIndex = 0; for (int j = 0; j < colCount; j++) { cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].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 DataSetToExcel(System.Data.DataSet data, string filepath, out string error) { error = ""; try { if (data == null || data.Tables.Count < 1) { error = "DataTableToExcel:datatable 为空"; return false; } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Worksheets.RemoveAt(0); for (int i = 0; i < data.Tables.Count; i++) { var datatable = data.Tables[i]; Aspose.Cells.Worksheet sheet = workbook.Worksheets.Add(data.Tables[i].TableName); Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; try { for (int j = 0; j < datatable.Columns.Count; j++) { cells[nRow,j].PutValue(datatable.Columns[j].ColumnName); //Aspose.Cells.Style style = new Aspose.Cells.Style(); //style.Font.IsBold = true; //style.Font.Size = 11; //cells[nRow, j].SetStyle(style); } } catch (System.Exception e) { error = error + " DataSetToExcel: " + e.Message; } foreach (DataRow row in datatable.Rows) { nRow++; try { for (int j = 0; j < datatable.Columns.Count; j++) { if (row[j].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[j]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, j, mstream); } else if (row[j].GetType().ToString() == "System.DateTime") { var time = Convert.ToDateTime(row[j]); if (time == DateTime.MinValue || time == DateTime.MaxValue || time == DateTime.Parse("1900-01-01 00:00:00")) { cells[nRow, j].PutValue(string.Empty); } else { cells[nRow, j].PutValue(time.ToString("yyyy-MM-dd HH:mm:ss")); } //cells[nRow, j].PutValue(time); } else { cells[nRow, j].PutValue(row[j]); } } } catch (System.Exception e) { error = error + " DataSetToExcel: " + e.Message; } } } workbook.Save(filepath); return true; } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return false; } }
/// <summary> /// 将DataTable,导出为Excel /// </summary> /// <param name="dt">DataTable对象</param> /// <param name="path">存储路径</param> /// <param name="sheet">Sheet名称</param> /// <returns></returns> public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path, string sheet) { bool succeed = false; if (dt != null) { try { // Excel Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0]; cellSheet.Name = sheet; // 开始行、结束行;开始列、结束列 int colCount = dt.Columns.Count; int rowCount = dt.Rows.Count; // 列标题 for (int i = 0; i < colCount; i++) { Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()]; s.Font.IsBold = true; cellSheet.Cells[0, i].SetStyle(s); cellSheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName); } // 单元格数据 for (int i = 1; i <= rowCount; i++) { for (int j = 0; j < colCount; j++) { cellSheet.Cells[i, j].PutValue(dt.Rows[i - 1][j].ToString()); } } cellSheet.AutoFitColumns(); path = System.IO.Path.GetFullPath(path); workbook.Save(path); succeed = true; } catch (Exception ex) { MessageBox.Show(ex.ToString()); succeed = false; } } return succeed; }
/// <summary> /// DataTable 导出Excel /// </summary> /// <param name="datatable">DataTable</param> /// <param name="filepath">filepath</param> /// <param name="error">错误信息</param> /// <returns>bool</returns> public static bool DataTableToExcel(System.Data.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; try { for (int i = 0; i < datatable.Columns.Count; i++) { cells[nRow, i].PutValue(datatable.Columns[i].ColumnName); Aspose.Cells.Style style = new Aspose.Cells.Style(); style.Font.IsBold=true; style.Font.Size = 11; cells[nRow, i].SetStyle(style); } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } 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; } }