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();
}
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>
/// 导出待处理文件
/// </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);
}
}
/// <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);
}
}
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 static string GetExcelText(byte[] buffer)
{
var ms = new System.IO.MemoryStream(buffer);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(ms);
return(GetExcelText(workbook));
}
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);
}
private static string GetExcelText(Aspose.Cells.Workbook workbook)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
var sheet = workbook.Worksheets[i];
var cells = sheet.Cells;
if (cells.MaxDataRow < 0 || cells.MaxColumn < 0)
{
continue;
}
var dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
for (int k = 0; k < dt.Rows.Count; k++)
{
var _rowsb = new StringBuilder();
for (int h = 0; h < dt.Columns.Count; h++)
{
var str = dt.Rows[k][h].ToString();
if (!string.IsNullOrEmpty(str))
{
_rowsb.Append(str + "\t");
}
}
if (_rowsb.Length > 0)
{
sb.AppendLine(_rowsb.ToString());
}
}
}
return(sb.ToString());
}
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);
}
private void btnExport_Click(object sender, EventArgs e)
{
btnExport.Enabled = false;
if (lvData.Items.Count > 0)
{
DataTable dt = new DataTable();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
dt.Columns.Add("課程名稱");
dt.Columns.Add("授課教師");
dt.Columns.Add("課程成績");
foreach (ListViewItem lvi in lvData.Items)
{
DataRow dr = dt.NewRow();
dr["課程名稱"] = lvi.Text;
if (lvi.SubItems[1] != null)
{
dr["授課教師"] = lvi.SubItems[1].Text;
}
if (lvi.SubItems[2] != null)
{
dr["課程成績"] = lvi.SubItems[2].Text;
}
dt.Rows.Add(dr);
}
Utility.CompletedXls("課程成績輸入狀況", dt, wb);
}
btnExport.Enabled = true;
}
private bool CheckFile(string fileName)
{
bool result = false;
try
{
Aspose.Cells.Workbook workbookName = new Aspose.Cells.Workbook(fileName);
int sheetCount = workbookName.Worksheets.Count;
if (sheetCount == 1)
{
string sheetName = workbookName.Worksheets[0].Name;
if (sheetName == "批量导入词条模板")
{
Aspose.Cells.Cells cellsName = workbookName.Worksheets[0].Cells;
int minDataColumn = cellsName.MinDataColumn;
int maxDataColumn = cellsName.MaxDataColumn;
if (cellsName[0, minDataColumn].StringValue.Trim() == "词条" &&
cellsName[0, maxDataColumn].StringValue.Trim() == "解读")
{
result = true;
}
}
}
}
catch (Exception ex)
{ }
return(result);
}
public static string getExcelSheetName(IWebDriver driver, String filepath, String fileName, String Index)
{
String SheetName = null;
try
{
WriteLog(driver, filepath, "In GetSheetName");
string s = null;
Workbook wb = new Workbook(filepath + "\\" + fileName);
int index = Int32.Parse(Index);
int numberOfSheets = wb.Worksheets.Count;
WriteLog(driver, filepath, "Number of sheets in this workbook : " + numberOfSheets);
foreach (Worksheet worksheet in wb.Worksheets)
{
if (worksheet.Index == (index))
{
SheetName = worksheet.Name;
WriteLog(driver, filepath, "Sheet name for Index " + Index + " is " + SheetName);
}
}
}
catch (Exception e)
{
WriteLog(driver, filepath, " getExcelSheetName exception :" + e.Message);
}
return(SheetName);
}
/// <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)));
}
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);
}
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 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);
}
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);
}
}
/// <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);
}
/// <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);
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
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);
}
}
/// <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;
}
}
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 ExportData()
{
btnExport.Enabled = false;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
int colIdx = 0;
foreach (DataGridViewColumn dgvc in dgData.Columns)
{
wb.Worksheets[0].Cells[0, colIdx].PutValue(dgvc.HeaderText);
colIdx++;
}
int rowIdx = 1;
foreach (DataGridViewRow dgvr in dgData.Rows)
{
colIdx = 0;
foreach (DataGridViewCell dgvc in dgvr.Cells)
{
if (dgvc.Value != null)
{
wb.Worksheets[0].Cells[rowIdx, colIdx].PutValue(dgvc.Value.ToString());
}
colIdx++;
}
rowIdx++;
}
wb.Worksheets[0].AutoFitColumns();
Utility.CompletedXls("匯出局端備查資料", wb);
btnExport.Enabled = true;
}
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 UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
wb = null;
}
catch
{
throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。");
}
try
{
uploadData = Convert.ToBase64String(tempBuffer);
uploadIndex = true;
fs.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (upctrlPsn.UploadedFiles[0].IsValid && upctrlPsn.HasFile)
{
commonFunc.SaveFile(upctrlPsn, this);
string savePath = commonFunc.GetSavePath(upctrlPsn, this);
Aspose.Cells.Workbook xlsPsn = new Aspose.Cells.Workbook(savePath);
//xlsPsn.Open(savePath);
Aspose.Cells.Cells cellsPsn = xlsPsn.Worksheets[0].Cells;
var dtTemp = cellsPsn.ExportDataTable(0, 0, cellsPsn.MaxDataRow + 1, cellsPsn.MaxDataColumn + 1);
ListBox lstUpdated = new ListBox();
for (int i = 1; i < dtTemp.Rows.Count; i++)
{
if (commonFunc.VerifyNumber(dtTemp.Rows[i][0].ToString().Trim()))
{
lstUpdated.Items.Add(dtTemp.Rows[i][0].ToString().Trim());
}
}
foreach (ListItem item in lstUpdated.Items)
{
if (!xlstPerson.Items.Contains(item))
{
xlstPerson.Items.Add(item);
}
}
xlblPersonNumber.Text = xlstPerson.Items.Count.ToString();
JSHelper.Alert(UpdatePanel2, this, "上传成功！");
}
}
/// <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);
}
}
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);
}
}
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);
}
public static DataTable Aspose2Data(string filePath)
{
//Aspose.Cells.License li = new Aspose.Cells.License();
//li.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(filePath);
Aspose.Cells.Worksheet ws = wk.Worksheets[0];
return(ws.Cells.ExportDataTable(1, 0, ws.Cells.Rows.Count, ws.Cells.Columns.Count));
}
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.");
}
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);
}
/// <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 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:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(dataDir + "Testbook1.xlsx", new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
foreach (Worksheet ws in wb.Worksheets)
{
SheetRender sr = new SheetRender(ws, new ImageOrPrintOptions() { OnePagePerSheet = true, ImageFormat = ImageFormat.Jpeg });
sr.ToImage(0, dataDir + "Img_" + ws.Index + "_out.jpg");
}
// ExEnd:1
}
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);
}
}
/// <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("儲存失敗");
}
}
}
/// <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;
}
}
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;
}
}
private SandBox()
{
this.workbook = new Aspose.Cells.Workbook();
this.workbook.Worksheets.Add();
}
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;
}
}
private DataTable BuildDataTableFromFileExcel(System.IO.Stream dataStream)
{
var infoTb = BuildNewtable();
try
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook document = new Aspose.Cells.Workbook();
document.Open(dataStream);
if (document.Worksheets[0].Cells.Rows.Count <= 1) return infoTb;
for (var i = 1; i < document.Worksheets[0].Cells.Rows.Count; i++)
{
if (string.IsNullOrEmpty(document.Worksheets[0].Cells[i, 0].Value.ToString())) continue;
var row = infoTb.NewRow();
row["WorkingAccId"] = document.Worksheets[0].Cells[i, 3].Value;
row["PaymentMethod"] = document.Worksheets[0].Cells[i, 0].Value;
row["CreditAmount"] = String.Format(CultureInfo.InvariantCulture,
"{0:0,0}", document.Worksheets[0].Cells[i, 5].Value);
row["CreditAccount"] = string.Format("{0}-{1}-{2}", document.Worksheets[0].Cells[i, 4].Value, document.Worksheets[0].Cells[i, 3].Value, document.Worksheets[0].Cells[i, 2].Value);
row["Exist"] = string.Empty;
infoTb.Rows.Add(row);
}
UpdateDataInfo(infoTb);
}
catch (Exception ex)
{
labelNoResults.Text = "Wrong Selected Company Payment file";
}
return infoTb;
}
private void btnImport_Click(object sender, EventArgs e)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
wb.Open(this.textBoxX1.Text);
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
int rowIndex = 1;
int totalCount = 0;
while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString()))
{
rowIndex += 1;
totalCount += 1;
}
this.progressBarX1.Maximum = totalCount;
rowIndex = 1;
AccessHelper ah = new AccessHelper();
while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString()))
{
string ntu_sys_no = GetCellValue(ws.Cells[rowIndex, 0]);
string tea_name = GetCellValue(ws.Cells[rowIndex, 1]);
string tea_eng_name = GetCellValue(ws.Cells[rowIndex, 2]);
string tea_account = GetCellValue(ws.Cells[rowIndex, 7]);
string tea_email = GetCellValue(ws.Cells[rowIndex, 8]);
string tea_office_telno = GetCellValue(ws.Cells[rowIndex, 12]);
string unit = GetCellValue(ws.Cells[rowIndex, 15]);
//string emp_no = ws.Cells[rowIndex, 5].Value.ToString();
K12.Data.TeacherRecord tea = new K12.Data.TeacherRecord();
tea.Name = tea_name;
tea.TALoginName = tea_account;
tea.Email = tea_email;
string newTID = K12.Data.Teacher.Insert(tea);
//K12.Data.Teacher.Update(tea);
UDT.TeacherExtVO udtTe = new UDT.TeacherExtVO();
udtTe.EnglishName = tea_eng_name;
udtTe.TeacherID = int.Parse(newTID);
udtTe.NtuSystemNo = ntu_sys_no;
//udtTe.EmployeeNo = emp_no;
udtTe.OtherPhone = tea_office_telno;
udtTe.MajorWorkPlace = unit;
List<ActiveRecord> rec = new List<ActiveRecord>();
rec.Add(udtTe);
ah.SaveAll(rec);
rowIndex += 1;
this.labelX1.Text = string.Format("{0} / {1} ", rowIndex.ToString(), totalCount.ToString());
this.progressBarX1.Value = rowIndex;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private string TransferFileToBase64String(string fileName)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
fs.Close();
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
}
catch
{
throw new Exception("教學意見表樣版限用相容於 Excel 2003 檔案。");
}
try
{
return Convert.ToBase64String(tempBuffer);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <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;
}
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;
}
/// <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;
}
// 上傳模組
private void UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
wb = null;
}
catch
{
throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。");
}
try
{
uploadData = Convert.ToBase64String(tempBuffer);
uploadIndex = true;
fs.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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);
}
}
}
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 DataLayer.Model.FileReplace_File DoIt(DataLayer.Model.FileReplace_File model)
{
Stopwatch sw = new Stopwatch();
sw.Start();
int replaceCount = 0;
Regex reg = null;
List<string> strRemark = new List<string>();
model.IsDone = true;
bool isDefaultExt = defaultExt.Contains(model.ExtensionName);
bool isExcelExt = excelExt.Contains(model.ExtensionName);
bool isDocExt = docExt.Contains(model.ExtensionName);
//bool isPPTExt = pptExt.Contains(model.ExtensionName);
//bool isPdfExt = pdfExt.Contains(model.ExtensionName);
bool isTxtFile = XCLNetTools.FileHandler.ComFile.IsTextFile(model.Path);
bool isNeedCopy = !string.IsNullOrEmpty(this.txtOutPutPath.Text);
string realPath = model.Path;//被操作的文件实际路径，如果没有指定输出目录，则为原路径，如果指定了输出目录，则为copy到输出目录中后的路径
string filetitle = XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false);//文件名，不含扩展名
if (!System.IO.File.Exists(model.Path))
{
model.Remark = "文件不存在！";
model.ProcessState = DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理;
return model;
}
if (string.IsNullOrEmpty(model.ExtensionName))
{
model.Remark = "无法确认文件类型！";
model.ProcessState = DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理;
return model;
}
try
{
#region 先处理替换文件名
for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++)
{
var ruleModel = dataGridRuleConfig.Rows[ruleIndex].DataBoundItem as DataLayer.Model.FileReplace_RuleConfig;
if (null == ruleModel || !ruleModel.IsFileName)
{
continue;
}
#region 是否启用正则替换
if (ruleModel.IsRegex)
{
reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent);
}
else
{
string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent);
reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr);
}
#endregion 是否启用正则替换
#region 判断是否替换文件名
replaceCount = reg.Matches(filetitle).Count;
strRemark.Add(string.Format("规则【{0}】文件名替换【{1}】处；", ruleModel.Name, replaceCount));
filetitle = reg.Replace(filetitle, ruleModel.NewContent);
model.ProcessBlockCount += replaceCount;
#endregion 判断是否替换文件名
}
filetitle = string.Format("{0}{1}{2}", this.txtFileFirstName.Text, filetitle, this.txtFileLastName.Text);
if (!string.Equals(XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false), filetitle))
{
realPath = XCLNetTools.FileHandler.ComFile.GetFileFolderPath(model.Path) + "\\" + filetitle + "." + model.ExtensionName;
if (isNeedCopy)
{
realPath = realPath.Replace(this.openFileFolderPath.TrimEnd('\\'), this.txtOutPutPath.Text.TrimEnd('\\'));
XCLNetTools.FileHandler.ComFile.CopyFile(model.Path, realPath);
if (!System.IO.File.Exists(realPath))
{
model.Remark = "复制到输出目录执行失败！";
model.ProcessState = DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败;
return model;
}
}
else
{
this.pc.FileSystem.RenameFile(model.Path, filetitle + "." + model.ExtensionName);
}
}
#endregion 先处理替换文件名
#region 替换文件内容
Aspose.Cells.Workbook wb = null;
Aspose.Words.Document wordDocument = null;
string textContent = null;
for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++)
{
var ruleModel = dataGridRuleConfig.Rows[ruleIndex].DataBoundItem as DataLayer.Model.FileReplace_RuleConfig;
if (null == ruleModel || !ruleModel.IsFileContent)
{
continue;
}
#region 验证扩展名及是否为文本文件
if (!isDefaultExt && !isTxtFile && ruleModel.IsFileContent)
{
//非aspose能处理的文件，且非文本文件，则不能替换内容！
strRemark.Add(string.Format("规则【{0}】不支持替换该文件的内容！", ruleModel.Name));
continue;
}
#endregion 验证扩展名及是否为文本文件
#region 是否启用正则替换
if (ruleModel.IsRegex)
{
reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent);
}
else
{
string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent);
reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr);
}
#endregion 是否启用正则替换
#region 开始替换文件内容
if (isDefaultExt)
{
if (isExcelExt)
{
#region 处理excel文件
if (null == wb)
{
wb = new Aspose.Cells.Workbook(realPath);
}
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, ruleModel.NewContent);
currentCell.PutValue(cellValue);
}
}
}
}
#endregion 处理excel文件
}
else if (isDocExt)
{
#region 处理word
if (null == wordDocument)
{
//正则无法使用特殊正则，如\s带\的。
wordDocument = new Aspose.Words.Document(realPath);
}
replaceCount = wordDocument.Range.Replace(reg, ruleModel.NewContent);
#endregion 处理word
}
//else if (isPPTExt)
//{
// #region 处理PPT
// Aspose.Slides.Pptx.PresentationEx pptPres = new Aspose.Slides.Pptx.PresentationEx(realPath);
// #endregion
//}
//else if (isPdfExt)
//{
// #region 处理pdf文件
// Aspose.Pdf.Kit.PdfContentEditor pdfEditor = new Aspose.Pdf.Kit.PdfContentEditor();
// pdfEditor.BindPdf(realPath);
// pdfEditor.ReplaceText(this.txtOldValue.Text, this.txtNew.Text);
// pdfEditor.Save(realPath);
// #endregion
//}
}
else
{
#region 处理文本文件
if (null == textContent)
{
textContent = System.IO.File.ReadAllText(realPath, System.Text.Encoding.Default) ?? "";
}
replaceCount = reg.Matches(textContent).Count;
textContent = reg.Replace(textContent, ruleModel.NewContent);
#endregion 处理文本文件
}
strRemark.Add(string.Format("规则【{0}】文件内容替换【{1}】处；", ruleModel.Name, replaceCount));
#endregion 开始替换文件内容
this.SetTextLogValue(string.Format("正在处理文件【{0}】，应用规则【{1}】" + Environment.NewLine, model.FileName, ruleModel.Name));
model.ProcessBlockCount += replaceCount;
}
if (null != wb)
{
wb.Save(realPath);
}
if (null != wordDocument)
{
wordDocument.Save(realPath);
}
if (null != textContent)
{
System.IO.File.WriteAllText(realPath, textContent, System.Text.Encoding.Default);
}
#endregion 替换文件内容
if (strRemark.Count > 0)
{
model.Remark = string.Join("；", strRemark.ToArray());
}
model.ProcessState = DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理成功;
}
catch (Exception e)
{
model.ProcessState = DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败;
model.Remark = e.Message;
}
finally
{
sw.Stop();
model.ProcessDuration = (int)sw.Elapsed.TotalSeconds;
}
this.SetTextLogValue(string.Format("文件【{0}】处理完毕（{1}）" + Environment.NewLine, model.FileName, model.Remark));
return model;
}
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;
}
