using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Web;
using log4net;
using Excel = Microsoft.Office.Interop.Excel;
namespace TMS.IMEX
{
public class IMEXImportBase
{
private static readonly ILog m_log = LogManager.GetLogger(typeof(IMEXImportBase));
#region 处理上传的文件
/// <summary>
/// 处理上传的文件
/// </summary>
/// <param name="fileData"></param>
/// <returns></returns>
protected string ProcessUploadFile(HttpPostedFileBase fileData)
{
m_log.Debug("IMEXImportBase->ProcessUploadFile Start");
try
{
// 绝对路径文件名
string fullFileName = string.Empty;
// 原始文件名
string oldFilename = Path.GetFileNameWithoutExtension(fileData.FileName);
// 获得文件扩展名
string fileExtension = Path.GetExtension(fileData.FileName);
// 新文件名
string newFileName = oldFilename + "_" + DateTime.Now.ToString("yyyyMMddHHmmssffffff") + fileExtension;
// 保存的物理路径
string phyPath = ConfigurationSettings.AppSettings["uploadFilePath"];
// 如果物理路径不存在,则创建路径
if (!Directory.Exists(phyPath))
{
Directory.CreateDirectory(phyPath);
}
fullFileName = phyPath + "\\" + newFileName;
fileData.SaveAs(fullFileName);
return fullFileName;
}
catch (Exception ex)
{
m_log.Error("IMEXImportBase->ProcessUploadFile Error:", ex);
throw new Exception(ex.Message);
}
finally
{
m_log.Debug("IMEXImportBase->ProcessUploadFile End");
}
}
#endregion
#region 读取Excel数据
/// <summary>
/// 读取Excel数据
/// </summary>
/// <param name="fullFileName"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
protected DataTable ReadExcelData(string fullFileName, string sheetName)
{
m_log.Debug("IMEXImportBase->ReadExcelData Start");
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;IMEX=1';" + "data source=" + fullFileName;
OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, sheetName);
m_log.Debug("IMEXImportBase->ReadExcelData End");
return ds.Tables[sheetName];
}
#endregion
#region 读取Excel数据
/// <summary>
/// 读取Excel数据
/// </summary>
/// <param name="fullFileName"></param>
/// <returns></returns>
protected object[,] ReadExcelData(string fullFileName)
{
Excel.Application app = null;
Excel.Workbook book = null;
Excel.Worksheet sheet = null;
try
{
m_log.Debug("IMEXImportBase->ReadExcelData Start");
app = new Excel.Application();
book =
(Excel.Workbook)
app.Workbooks.Open(fullFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
sheet = (Excel.Worksheet)book.Sheets[1];
var data = sheet.UsedRange.Value[Missing.Value] as object[,];
return data;
}
catch (Exception ex)
{
m_log.Error("IMEXExportBase->ReadExcelData" + ex.Message);
throw new Exception("IMEXExportBase->ReadExcelData():Failed", ex);
}
finally
{
//关闭Excel进程
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
if (book != null)
{
book.Close(false, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
if (app != null)
{
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
GC.Collect();
m_log.Debug("IMEXImportBase->ReadExcelData End");
}
}
#endregion
#region 处理上传的文件
/// <summary>
/// 处理上传的文件
/// </summary>
/// <param name="fileData"></param>
/// <param name="fileName">保存的文件名</param>
/// <param name="phyPath">保存的物理路径</param>
/// <returns></returns>
protected string ProcessUploadImg(HttpPostedFileBase fileData, string fileName, string phyPath)
{
m_log.Debug("IMEXImportBase->ProcessUploadImg Start");
try
{
// 获得文件扩展名
string fileExtension = Path.GetExtension(fileData.FileName);
// 新文件名
string newFileName = fileName + fileExtension;
// 如果物理路径不存在,则创建路径
if (!Directory.Exists(phyPath))
{
Directory.CreateDirectory(phyPath);
}
// 绝对路径文件名
string fullFileName = phyPath + "\\" + newFileName;
// 删除文件
DeleteFile(phyPath, fileName);
fileData.SaveAs(fullFileName);
return fullFileName;
}
catch (Exception ex)
{
m_log.Error("IMEXImportBase->ProcessUploadImg Error:", ex);
throw new Exception(ex.Message);
}
finally
{
m_log.Debug("IMEXImportBase->ProcessUploadImg End");
}
}
#endregion
#region 删除文件
/// <summary>
/// 删除文件
/// </summary>
/// <param name="filePath"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public void DeleteFile(string filePath, string fileName)
{
var directory = new DirectoryInfo(@filePath);
var files = directory.GetFiles(fileName + ".*");
foreach (var file in files)
{
file.Delete();
}
}
#endregion
}
}