/// <summary> /// Performs basic validation on the file being analyzed by this <see cref="FileAnalyzer" />. /// </summary> /// <returns>A <see cref="FileValidationResult" /> object representing the result of validation.</returns> public override FileValidationResult Validate() { try { using (ExcelApp app = new ExcelApp()) { app.Open(File); } return(FileValidationResult.Pass); } catch (COMException ex) { LogWarn("File failed to open: " + ex.Message); return(FileValidationResult.Fail(ex.Message)); } }
/// <summary> /// 模板格式下载,用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key) /// </summary> /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param> /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param> /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param> /// <param name="items">标签的键、值对</param> /// <returns>返回下载文件的链接地址(使用download.aspx)</returns> public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items) { ExcelApp excelApp = null; ExcelWorkbook workbook = null; ExcelWorksheet sheet = null; string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls"; string filePath = DownloadFolder + fileName; try { excelApp = new ExcelApp(); workbook = excelApp.Open(template, 0); workbook.SaveAs(filePath); sheet = workbook.Worksheets(1); ExcelRange range = sheet.Cells(); foreach (KeyValuePair <string, string> kv in items) { range.Replace(kv.Key, kv.Value, false); } workbook.Save(); } catch (Exception er) { throw er; } finally { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject); } if (workbook != null) { workbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject); } if (excelApp != null) { excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject); } } return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath)); }
/// <summary> /// Gets information about a document, such as page count, title, and author. /// </summary> /// <returns>A <see cref="DocumentProperties" /> object.</returns> public override DocumentProperties GetProperties() { DocumentProperties properties = new DocumentProperties(File); try { using (ExcelApp app = new ExcelApp()) { Workbook workbook = app.Open(File); // Calculate page count by summing the number of pages for each worksheet properties.Pages = (short)workbook.Sheets.OfType <Worksheet>().Sum(n => n.PageSetup.Pages.Count); // Get the workbook orientation switch (((Worksheet)workbook.ActiveSheet).PageSetup.Orientation) { case XlPageOrientation.xlPortrait: properties.Orientation = Orientation.Portrait; break; case XlPageOrientation.xlLandscape: properties.Orientation = Orientation.Landscape; break; } // Retrieve built-in workbook properties properties.Title = workbook.BuiltinDocumentProperties.Item["Title"].Value; properties.Author = workbook.BuiltinDocumentProperties.Item["Author"].Value; properties.Application = workbook.BuiltinDocumentProperties.Item["Application Name"].Value; } } catch (COMException ex) { // Log the error, then return whatever properties have been collected. LogWarn("Failure retrieving properties: " + ex.Message); } return(properties); }
/// <summary> /// 模板格式+简单格式下载,先用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key),再下载<paramref name="ds"/>的数据行 /// </summary> /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param> /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param> /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param> /// <param name="items">标签的键、值对</param> /// <param name="rowIndex">数据行的开始位置(1开始的索引,即Excel中的行号)</param> /// <param name="ds"></param> /// <returns>返回下载文件的链接地址(使用download.aspx)</returns> public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items, int rowIndex, IList <DownloadFormat> format, DataSet ds) { ExcelApp excelApp = null; ExcelWorkbook workbook = null; ExcelWorksheet sheet = null; string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls"; string filePath = DownloadFolder + fileName; try { excelApp = new ExcelApp(); workbook = excelApp.Open(template, 0); workbook.SaveAs(filePath); sheet = workbook.Worksheets(1); //标签替换 ExcelRange range = sheet.Cells(); if (items != null) { foreach (KeyValuePair <string, string> kv in items) { range.Replace(kv.Key, kv.Value, false); } } //数据行 int index = rowIndex; foreach (DataRow row in ds.Tables[0].Rows) { for (int i = 0; i < format.Count; i++) { DownloadFormat ft = format[i]; if (ft.ColumnIndex == null) { continue; } for (int j = 0; j < ft.ColumnIndex.Length; j++) { SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]); } } index++; } workbook.Save(); } catch (Exception er) { throw er; } finally { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject); } if (workbook != null) { workbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject); } if (excelApp != null) { excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject); } } return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath)); }
private void OpenApplication() { _wjaWorkbook = _excelApp.Open(@wjaReport_TextBox.Text); _eprInjectorWorkbook = _excelApp.Open(@eprInjectorReport_TextBox.Text); }
/// <summary> /// 模板格式+简单格式下载,先用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key),再下载<paramref name="ds"/>的数据行 /// </summary> /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param> /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param> /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param> /// <param name="items">标签的键、值对</param> /// <param name="rowIndex">数据行的开始位置(1开始的索引,即Excel中的行号)</param> /// <param name="ds"></param> /// <returns>返回下载文件的链接地址(使用download.aspx)</returns> public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items, int rowIndex, IList<DownloadFormat> format, DataSet ds) { ExcelApp excelApp = null; ExcelWorkbook workbook = null; ExcelWorksheet sheet = null; string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls"; string filePath = DownloadFolder + fileName; try { excelApp = new ExcelApp(); workbook = excelApp.Open(template, 0); workbook.SaveAs(filePath); sheet = workbook.Worksheets(1); //标签替换 ExcelRange range = sheet.Cells(); if (items != null) foreach (KeyValuePair<string, string> kv in items) range.Replace(kv.Key, kv.Value, false); //数据行 int index = rowIndex; foreach (DataRow row in ds.Tables[0].Rows) { for (int i = 0; i < format.Count; i++) { DownloadFormat ft = format[i]; if (ft.ColumnIndex == null) continue; for (int j = 0; j < ft.ColumnIndex.Length; j++) SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]); } index++; } workbook.Save(); } catch (Exception er) { throw er; } finally { if (sheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject); if (workbook != null) { workbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject); } if (excelApp != null) { excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject); } } return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath); }
/// <summary> /// 模板格式下载,用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key) /// </summary> /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param> /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param> /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param> /// <param name="items">标签的键、值对</param> /// <returns>返回下载文件的链接地址(使用download.aspx)</returns> public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items) { ExcelApp excelApp = null; ExcelWorkbook workbook = null; ExcelWorksheet sheet = null; string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls"; string filePath = DownloadFolder + fileName; try { excelApp = new ExcelApp(); workbook = excelApp.Open(template, 0); workbook.SaveAs(filePath); sheet = workbook.Worksheets(1); ExcelRange range = sheet.Cells(); foreach (KeyValuePair<string, string> kv in items) range.Replace(kv.Key, kv.Value, false); workbook.Save(); } catch (Exception er) { throw er; } finally { if (sheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject); if (workbook != null) { workbook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject); } if (excelApp != null) { excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject); } } return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath); }
protected void MagicItemCommand(object sender, MagicItemEventArgs e) { if (e.CommandName == "Save") { if (this.FileUpload1.FileName.Trim().Length <= 0) { WebUtil.ShowMsg(this, "请选择盘点结果文件"); return; } string fileName = this.FileUpload1.FileName; if (!fileName.EndsWith(".xls")) { WebUtil.ShowMsg(this, "请选择有效的Excel文件"); return; } string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls"); this.FileUpload1.SaveAs(filePath); IList<INVCheckLine> lines = new List<INVCheckLine>(); #region 读取文件 ExcelApp excelapp = null; ExcelWorkbook excelBook = null; ExcelWorksheet excelSheet = null; try { excelapp = new ExcelApp(); excelapp.DisplayAlerts = false; excelBook = excelapp.Open(filePath, 0); excelSheet = excelBook.Worksheets(1); int rowIndex = 2; string lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim(); decimal qty; while (lineNum.Length==4) { qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M); INVCheckLine line = new INVCheckLine(); line.LineNumber = lineNum; line.CurrentQty = qty; lines.Add(line); rowIndex++; lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim(); } } catch (Exception er) { WebUtil.ShowError(this, er.Message); return; } finally { if (excelSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject); if (excelBook != null) { excelBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject); } if (excelapp != null) { excelapp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject); } } #endregion using (ISession session = new Session()) { try { INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum")); if (head == null) { WebUtil.ShowError(this, "盘点单"+WebUtil.Param("ordNum")+"不存在"); return; } session.BeginTransaction(); head.ClearCheckQty(session); head.UpdateLines(session, lines); session.Commit(); } catch (Exception er) { session.Rollback(); WebUtil.ShowError(this, er.Message); return; } } this.Response.Redirect(WebUtil.Param("return")); } }
protected void MagicItemCommand(object sender, MagicItemEventArgs e) { if (e.CommandName == "Save") { if (this.FileUpload1.FileName.Trim().Length <= 0) { WebUtil.ShowMsg(this, "请选择盘点结果文件"); return; } string fileName = this.FileUpload1.FileName; if (!fileName.EndsWith(".xls")) { WebUtil.ShowMsg(this, "请选择有效的Excel文件"); return; } string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls"); this.FileUpload1.SaveAs(filePath); IList <INVCheckLine> lines = new List <INVCheckLine>(); #region 读取文件 ExcelApp excelapp = null; ExcelWorkbook excelBook = null; ExcelWorksheet excelSheet = null; try { excelapp = new ExcelApp(); excelapp.DisplayAlerts = false; excelBook = excelapp.Open(filePath, 0); excelSheet = excelBook.Worksheets(1); int rowIndex = 2; string lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim(); decimal qty; while (lineNum.Length == 4) { qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M); INVCheckLine line = new INVCheckLine(); line.LineNumber = lineNum; line.CurrentQty = qty; lines.Add(line); rowIndex++; lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim(); } } catch (Exception er) { WebUtil.ShowError(this, er.Message); return; } finally { if (excelSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject); } if (excelBook != null) { excelBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject); } if (excelapp != null) { excelapp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject); } } #endregion using (ISession session = new Session()) { try { INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum")); if (head == null) { WebUtil.ShowError(this, "盘点单" + WebUtil.Param("ordNum") + "不存在"); return; } session.BeginTransaction(); head.ClearCheckQty(session); head.UpdateLines(session, lines); session.Commit(); } catch (Exception er) { session.Rollback(); WebUtil.ShowError(this, er.Message); return; } } this.Response.Redirect(WebUtil.Param("return")); } }