/// <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"));
}
}