/// <summary>
/// 将Excel中的内容转换为数据集合DataSet，调用数据库处理
/// </summary>
/// <param name="ExcelFilePathAll"></param>
/// <returns></returns>
public bool doExcelTextToDb(string ExcelFilePathAll, string GuidStr)
{
//处理Excel文件
//DataSet ds_result=new DataSet();
//DataSet ds_result=null;
//string ErrMsg = "";
try
{
logger.Error(string.Format("{0}:[{1}]:[{2}]", "doExcelTextToDb", ExcelFilePathAll, GuidStr));
//excel转换为dataset
//bool result = commonExcel.ExcelFileToDataSet(ExcelFilePathAll,GuidStr,out ds_result, out ErrMsg);
//if (result == true)
//{
// if (ds_result != null && ds_result.Tables.Count > 0)
// {
// //存在数据的情况下将数据保存到数据库中
// for (int i = 0; i < ds_result.Tables.Count; i++)
// {
// dataToDbFromDt(ds_result.Tables[i], GuidStr);
// }
// }
//}
//mq 2017-11-24上面操作语句注释，加入以下语句。
commonExcel commonexcel = new commonExcel();
DataSet ds = commonexcel.ExcelFileToDataSet(ExcelFilePathAll, GuidStr);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataTable dt in ds.Tables)
{
dataToDbFromDt(dt, GuidStr);
}
}
}
catch (Exception ex)
{
logger.Error(string.Format("{0}:[{1}]:[{2}]:[{3}]", "doExcelTextToDb", ExcelFilePathAll, "数据保存异常", ex.Message + ex.StackTrace));
DBLogger.Insert(DBLogger.GetLoggerInfo(ExcelFilePathAll, ex.Message + Environment.NewLine + ex.StackTrace, GuidStr, 0));
return(false);
}
return(true);
}
/// <summary>
/// 2017-08-14 mq 修改，方法比原方法加入yljgbm
/// 遍历目录下的所有文件，将每个文件内容保存到数据库的临时表中，然后立即导入正式表中后删除
/// </summary>
/// <param name="DirectoryPath">文件夹路径</param>
private bool DoFileToDb(string DirectoryPath, string GuidStr, string yljgbm, string clientUser)
{
bool isExistsFile = false;
int fileCount = 0;
string errorFile = "";
try
{
//备份已处理的文件的路径
string filePathBack = ConfigurationManager.AppSettings["ExcelUpFile"] + "backup\\";
string filePathBackError = ConfigurationManager.AppSettings["ExcelUpFile"] + "Error\\";
//处理文件内容到数据库中
DirectoryInfo DirectoryArray = new DirectoryInfo(DirectoryPath);
//获取该文件夹下的文件列表
FileInfo[] Files = DirectoryArray.GetFiles();
//获取该文件夹下的文件夹列表
DirectoryInfo[] Directorys = DirectoryArray.GetDirectories();
//逐个处理文件
foreach (FileInfo theFile in Files)
{
errorFile = theFile.Name;
//将数据处理到数据库中
bool result = doExcelTextToDb(theFile.FullName, GuidStr);
if (result == true) //为true时，导入临时表成功
{
DBLogger.Insert(DBLogger.GetLoggerInfo(theFile.Name, "导入临时表成功", GuidStr, 1));
//如果目录不存在创建目录
if (!Directory.Exists(filePathBack))
{
Directory.CreateDirectory(filePathBack);
}
//将处理的文件进行备份
if (File.Exists(filePathBack + Path.GetFileName(theFile.FullName)) == true)
{
File.Delete(filePathBack + Path.GetFileName(theFile.FullName));
}
if (File.Exists(theFile.FullName) == true)
{
File.Move(theFile.FullName, filePathBack + Path.GetFileName(theFile.FullName));
}
}
else
{
DBLogger.Insert(DBLogger.GetLoggerInfo(theFile.Name, "导入临时表失败", GuidStr, 0));
//如果目录不存在创建目录
if (!Directory.Exists(filePathBackError))
{
Directory.CreateDirectory(filePathBackError);
}
//将处理的文件进行备份
if (File.Exists(filePathBackError + Path.GetFileName(theFile.FullName)) == true)
{
File.Delete(filePathBackError + Path.GetFileName(theFile.FullName));
}
if (File.Exists(theFile.FullName) == true)
{
File.Move(theFile.FullName, filePathBackError + Path.GetFileName(theFile.FullName));
}
}
//在这里记录导入临时表的信息
if (result == true)
{
//如果文件导入成功，立即执行临时表到正式表操作
ExecuteProCreateData(clientUser, GuidStr, yljgbm);
//记录到正式表的信息
DBLogger.Insert(DBLogger.GetLoggerInfo(theFile.Name, "导入正式表成功", GuidStr, 1));
}
}
//逐个获取文件夹名称，并递归调用方法本身
foreach (DirectoryInfo Dir in Directorys)
{
bool result = copyDirectory(DirectoryPath + "\\" + Dir.Name, filePathBack + "\\" + Dir.Name);
if (result == true)
{
isExistsFile = true;
}
}
//删除处理完的文件目录
//Directory.Delete(DirectoryPath, false);
//只要存在要处理的文件就返回true
if (fileCount > 0)
{
isExistsFile = true;
}
}
catch (Exception ex)
{
logger.Error(string.Format("{0}:[{1}]:[{2}]", "DoFileToDb", "数据保存异常", ex.Message));
DBLogger.Insert(DBLogger.GetLoggerInfo(errorFile, ex.Message, GuidStr, 0));
return(false);
}
return(isExistsFile);
}
/// <summary>
/// 执行文件的处理
/// </summary>
/// <param name="threadid"></param>
public void DoFile(string clientUser, string GuidStr, string yljgbm)
{
TxtLogger.Debug(string.Format("{0}:[{1}]:[{2}]", "DoFile", clientUser, "文件处理开始"));
//取得本地的保存文件地址
fileDirAddress = ConfigurationManager.AppSettings["ExcelUpFile"];
string filePathAll = string.Format("{0}{1}", fileDirAddress, "upLoade");
string filePathAllTo = string.Format("{0}{1}", fileDirAddress, "excute");
try
{
//文件处理
if (copyDirectory(filePathAll, filePathAllTo) == true)
{
//将文件内容保存到数据库中
DoFileToDb(filePathAllTo, GuidStr, yljgbm, clientUser);
//处理完当前数据后再次扫描是否存在需要处理的文件
DoFile(clientUser, GuidStr, yljgbm);
}
else
{
//调用存储过程将临时表中的数据处理到正式表中
//ExecuteProCreateData(clientUser, GuidStr, yljgbm);
//ExecuteProCreateData(clientUser, GuidStr, yljgbm);
////处理程序执行结束，关闭处理程序
//string sql_update = string.Format("update T_JK_CXZT set CLCXZT='1' ,KSSJ='{0}' where CLCXZT='2' ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff"));
////更新数据库中数据状态
//DBOracle dboracle = new DBOracle();
//int insertResult = dboracle.ExecuteNonQuery_oracle(sql_update);
//return;
}
logger.Debug(string.Format("{0}:[{1}]:[{2}]", "DoFile", clientUser, "文件处理结束"));
DBLogger.Insert(new LoggerInfo(clientUser, "DoFile 文件处理结束", 1));
}
catch (Exception ex)
{
logger.Error(string.Format("{0}:[{1}]:[{2}]:[{3}]", "DoFile", clientUser, "文件处理异常", ex.Message));
DBLogger.Insert(new LoggerInfo(clientUser, string.Format("DoFile文件处理异常,{0}", ex.Message), 0));
throw ex;
//出错时增加错误处理
////处理程序执行结束，关闭处理程序
//string sql_update = string.Format("insert into ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff"));
////更新数据库中数据状态
//DBOracle dboracle = new DBOracle();
//int insertResult = dboracle.ExecuteNonQuery_oracle(sql_update);
//throw new Exception(string.Format("DoFile文件处理异常：{0}", ex.Message));
}
finally
{
//处理程序执行结束，关闭处理程序
string sql_update = string.Format("update T_JK_CXZT set CLCXZT='1' ,KSSJ='{0}' where CLCXZT='2' ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff"));
//更新数据库中数据状态
DBOracle dboracle = new DBOracle();
int insertResult = dboracle.ExecuteNonQuery_oracle(sql_update);
}
return;
}