/// <summary>
/// 格式化DataTable表头
/// </summary>
/// <param name="ieDto"></param>
/// <param name="dt"></param>
/// <param name="isEnToCh"></param>
public static void FormatterHead(ExcelIEDto ieDto, DataTable dt, bool isEnToCh = false)
{
if (dt.Columns.Contains(ExcelIEConsts.PrimarkKey))
{
dt.Columns.Remove(ExcelIEConsts.PrimarkKey);
}
if (dt.Columns.Contains(ExcelIEConsts.RowNumber))
{
dt.Columns.Remove(ExcelIEConsts.RowNumber);
}
foreach (var item in ieDto.FieldList)
{
if (dt.Columns.Contains(item.FieldEnName))
{
if (!ieDto.ExportObj.ContainsKey(item.FieldEnName))
{
ieDto.ExportObj.Add(new JProperty(item.FieldEnName, item.FieldChName));
}
if (isEnToCh)
{
dt.Columns[item.FieldEnName].ColumnName = item.FieldChName;
}
}
}
}
public async Task <string> PushExcelExportMsg(ExcelIEDto dto)
{
try
{
dto.LocalUrl = this.Request.GetLocalExportUrl();
await _excelService.PushExcelExportMsg(dto);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
return(ex.Message);
}
return(string.Empty);
}
/// <summary>
/// 消息发送：ExcelIE消息发送与较验
/// </summary>
/// <param name="ieDto"></param>
/// <returns></returns>
public async Task <string> PushExcelExportMsg(ExcelIEDto ieDto)
{
string error = string.Empty;
if (string.IsNullOrEmpty(ieDto.TemplateCode))
{
error = "模板编码不能为空！";
}
else
{
// 切换数据库连接
await _dbService.ChangeConnectionString(ieDto);
var templateRedisKey = ExcelIEConsts.ExcelIERedisPre + ieDto.TemplateCode + ":" + ieDto.TenantId.ToString();
var templateStr = await _redis.StringGetAsync(templateRedisKey);
if (string.IsNullOrEmpty(templateStr))
{
var template = await _excelIEDomainService.GetFirstExcelModelAsync(o => o.TemplateCode == ieDto.TemplateCode);
if (template.Id == Guid.Empty)
{
return(error = "模板不存在！");
}
else
{
await _redis.StringSetAsync(templateRedisKey, JsonHelper.ToJsonString(template), TimeSpan.FromMinutes(50));
ieDto.Template = template;
}
}
else
{
ieDto.Template = JsonHelper.ToJson <CoExcelExportSql>(templateStr);
}
//消息发送(导出)
ieDto.TemplateLog.ExportParameters = JsonHelper.ToJsonString(ieDto);
ieDto.TemplateLog.ParentId = ieDto.Template.Id;
ieDto.TemplateLog.TemplateSql = ieDto.Template.ExecSql;
ieDto.TemplateLog.CreateTime = DateTime.Now;
ieDto.TemplateLog.TenantId = ieDto.TenantId;
ieDto.TemplateLog.CreateUserId = ieDto.UserId;
ieDto.TemplateLog.CreateUser = ieDto.UserName;
ieDto.TemplateLog.Id = _excelIEDomainService.NewGuid();
await _excelIEDomainService.AddAsyncExcelLogModel(ieDto.TemplateLog);
await _capPublisher.PublishAsync(MqConst.ExcelIETopicName, ieDto);
}
return(error);
}
/// <summary>
/// 分页递归装载数据DataTable
/// </summary>
/// <param name="ieDto"></param>
/// <param name="dt"></param>
/// <param name="rowNum"></param>
/// <returns></returns>
private async Task <DataTable> GetDataBySql(ExcelIEDto ieDto, DataTable dt, int rowNum = 0)
{
string execSql = ieDto.TemplateLog.ExportSql + string.Format("And {0} > {1}", ExcelIEConsts.RowNumber, rowNum);
var dtItem = await _excelIEDomainService.GetDataTableBySqlAsync(execSql);
dt.Merge(dtItem);
if (dtItem.Rows.Count == ieDto.Template.ExecMaxCountPer)
{
var tempDt = await GetDataBySql(ieDto, dt, Convert.ToInt32(dt.AsEnumerable().Last <DataRow>()[ExcelIEConsts.RowNumber]));
dt.Merge(tempDt);
}
return(dt);
}
/// <summary>
/// 修改数据库连接字符串
/// 只有saas版需要切换，非saas版读取本地连接
/// </summary>
/// <param name="name">方法/key名称</param>
/// <returns></returns>
public async Task ChangeConnectionString(ExcelIEDto ieDto)
{
if (ieDto.TenantId == Guid.Empty)
{
throw new Exception("输入的tntid错误");
}
var tntKey = ExcelIEConsts.ExcelIERedisPre + ieDto.TenantId.ToString();
var tntStr = await _redis.StringGetAsync(tntKey);
if (string.IsNullOrEmpty(tntStr))
{
var tnt = await _amUnit.GetRepository <AmTenant>().GetFirstOrDefaultAsync(m => m.Id == ieDto.TenantId);
if (tnt == null)
{
throw new Exception($"未找到对应的租户配置，请检查输入的tntid：{ieDto.TenantId}是否有误");
}
if (!string.IsNullOrEmpty(tnt.TntDbStr) && !string.IsNullOrEmpty(tnt.TntCode))
{
tntStr = JsonHelper.ToJsonString(new
{
tnt.TntCode,
tnt.TntDbStr
});
ieDto.TenantCode = tnt.TntCode;
ieDto.TenantDBStr = tnt.TntDbStr;
await _redis.StringSetAsync(tntKey, tntStr, TimeSpan.FromMinutes(30));
await _scmUnit.ChangeConnectionStringAsync(tnt.TntDbStr);
}
}
else
{
var dbDto = JsonHelper.ToJson <dynamic>(tntStr);
await _scmUnit.ChangeConnectionStringAsync(Convert.ToString(dbDto.TntDbStr));
}
}
/// <summary>
/// 消息消费者：具体导出操作
/// </summary>
/// <param name="ieDto"></param>
/// <returns></returns>
public async Task <string> ExcelExport(ExcelIEDto ieDto)
{
string downLoadUrl = string.Empty, excelFilePath = string.Empty;
var dataTable = new DataTable();
var fileInfo = new ExportFileInfo();
//获取配置信息
var sysConfig = ConfigHelper.GetValue <SysConfig>();
var ossConfig = ConfigHelper.GetValue <OssConfig>();
ieDto.TaskWatch.Start();
try
{
//切换数据库连接
await _dbService.ChangeConnectionString(ieDto);
_logger.LogInformation("开始导入！");
#region 保存路径和模板路径初始化和处理
var root = Directory.GetCurrentDirectory() + "\\";
var rootPath = root + ExcelIEConsts.ExcelIESufStr;
var fileName = ieDto.Template.TemplateName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ExcelIEConsts.ExcelSufStr;
var excelPath = rootPath + ExcelIEConsts.ExportSufStr;
excelFilePath = excelPath + fileName;
//模板文件路径
var excelTemplatePath = rootPath + ExcelIEConsts.TemplateSufStr + ieDto.Template.TemplateName + ExcelIEConsts.ExcelSufStr;
//拷备模板路径
var excelTemplateCopyPath = rootPath + ExcelIEConsts.TemplateSufStr + fileName;
//导出前删除文件
if (File.Exists(excelFilePath))
{
File.Delete(excelFilePath);
}
//创建文件夹
if (!Directory.Exists(excelPath))
{
Directory.CreateDirectory(excelPath);
}
#endregion
#region 导出记录数据收集
var templateLog = await _excelIEDomainService.GetFirstExcelLogModelAsync(o => o.Id == ieDto.TemplateLog.Id);
if (templateLog.Id != Guid.Empty)
{
ieDto.TemplateLog = templateLog;
}
ieDto.TemplateLog.ExecCount++;
if (ieDto.TemplateLog.ExecCount >= 3)
{
ieDto.TemplateLog.Status = 2;
}
#endregion
//构造导出Sql语句
GetExportSql(ieDto);
//收集导出数据
ieDto.QueryWatch.Start();
dataTable = await GetDataBySql(ieDto, new DataTable());
ieDto.QueryWatch.Stop();
#region 导出excel数据
//默认为0Magicodes.IE插件（分sheet导出:默认10000）
if (ieDto.Template.TemplateType > 0)
{
ieDto.ExportType = (ExportTypeEnum)ieDto.Template.TemplateType;
}
if (ieDto.ExportType == ExportTypeEnum.MagicodesCommon)
{
//格式DataTable表头
FormatterHead(ieDto, dataTable, true);
//导出数据
ieDto.WriteWatch.Start();
fileInfo = await _iExcelExporter.Export(excelFilePath, dataTable, maxRowNumberOnASheet : ieDto.Template.ExecMaxCountPer);
ieDto.WriteWatch.Stop();
}
//模板导出自定义表头：支持图片
else if (ieDto.ExportType == ExportTypeEnum.MagicodesImgByTemplate)
{
//格式DataTable表头
FormatterHead(ieDto, dataTable);
var jarray = JArray.FromObject(dataTable);
if (!ieDto.ExportObj.ContainsKey("DataList"))
{
ieDto.ExportObj.Add(new JProperty("DataList", jarray));
}
//复制模板，解决资源共享占用问题
if (File.Exists(excelTemplatePath)) //必须判断要复制的文件是否存在
{
File.Copy(excelTemplatePath, excelTemplateCopyPath, true); //三个参数分别是源文件路径，存储路径，若存储路径有相同文件是否替换
}
//导出数据
ieDto.WriteWatch.Start();
fileInfo = await _iExcelExporter.ExportByTemplate <JObject>(excelFilePath, ieDto.ExportObj, excelTemplateCopyPath);
ieDto.WriteWatch.Stop();
//删除拷备模板文件
if (File.Exists(excelTemplateCopyPath))
{
File.Delete(excelTemplateCopyPath);
}
}
//MiniExcel导出
else if (ieDto.ExportType == ExportTypeEnum.MiniExcelCommon)
{
//格式DataTable表头
FormatterHead(ieDto, dataTable, true);
//导出数据
ieDto.WriteWatch.Start();
MiniExcel.SaveAs(excelFilePath, dataTable);
ieDto.WriteWatch.Stop();
}
//判断是本地还是远程部署
if (sysConfig.ExcelEDownLoad.DeployType == 0)
{
downLoadUrl = ieDto.LocalUrl + ExcelIEConsts.ExcelIEDownUrlSuf;
}
else
{
string ossFilePathName = string.Format(@"{0}{1}",
string.IsNullOrEmpty(sysConfig.ExcelEDownLoad.UrlSuf) ? (ExcelIEConsts.ExcelIEDownUrlSuf + ieDto.TenantCode + "/") : sysConfig.ExcelEDownLoad.UrlSuf,
fileName);
downLoadUrl = string.Format(@"https://{0}.{1}/{2}",
ossConfig.BucketName, ossConfig.Endpoint, string.IsNullOrEmpty(sysConfig.ExcelEDownLoad.UrlSuf) ? (ExcelIEConsts.ExcelIEDownUrlSuf + ieDto.TenantCode + "/") : sysConfig.ExcelEDownLoad.UrlSuf);
var ossResult = _oss.PutObject(ossConfig.BucketName, ossFilePathName, excelFilePath);
if (File.Exists(excelFilePath))
{
File.Delete(excelFilePath);
}
}
#endregion
#region 导出记录数据收集保存
ieDto.TemplateLog.FileSize = CountSize(GetFileSize(excelFilePath));
ieDto.TemplateLog.Status = 1;
ieDto.TemplateLog.FileName = fileName;
ieDto.TemplateLog.DownLoadUrl = downLoadUrl;
#endregion
_logger.LogInformation("导入成功！");
}
catch (Exception ex)
{
ieDto.TemplateLog.Status = 2;
ieDto.TemplateLog.ExportMsg = "导出失败：" + ex.Message + ":";
_logger.LogError("导入失败：" + ex.Message);
throw;
}
finally
{
ieDto.TemplateLog.ModifyTime = DateTime.Now;
ieDto.TemplateLog.ModifyUser = ieDto.UserName;
ieDto.TemplateLog.ExportCount = dataTable.Rows.Count;
if (sysConfig.ExcelEDownLoad.DeployType != 0 && File.Exists(excelFilePath))
{
File.Delete(excelFilePath);
}
ieDto.TaskWatch.Stop();
ieDto.TemplateLog.ExportDurationTask = Convert.ToDecimal(ieDto.TaskWatch.Elapsed.TotalSeconds);
ieDto.TemplateLog.ExportDurationQuery = Convert.ToDecimal(ieDto.QueryWatch.Elapsed.TotalSeconds);
ieDto.TemplateLog.ExportDurationWrite = Convert.ToDecimal(ieDto.WriteWatch.Elapsed.TotalSeconds);
if (ieDto.TemplateLog.Status == 1)
{
ieDto.TemplateLog.ExportMsg = "导出成功：" + ieDto.TaskWatch.Elapsed.TotalSeconds.ToString("0.00") + "秒";
}
if (ieDto.TemplateLog.Status == 2)
{
ieDto.TemplateLog.ExportMsg += ieDto.TaskWatch.Elapsed.TotalSeconds.ToString("0.00") + "秒";
}
await _excelIEDomainService.EditAsyncExcelLogModel(ieDto.TemplateLog, true);
}
return(string.Empty);
}
/// <summary>
/// 获取导出查询sql
/// </summary>
/// <param name="ieDto"></param>
/// <returns></returns>
public static void GetExportSql(ExcelIEDto ieDto)
{
string tempName = string.Empty, tempValue = string.Empty, exportSql = string.Empty;
StringBuilder selectFields = new StringBuilder(), mainSql = new StringBuilder(ieDto.TemplateLog.TemplateSql);
if (string.IsNullOrEmpty(ieDto.Template.ExportHead))
{
throw new Exception("导出模板列头不能为空！");
}
else
{
if (ieDto.TemplateLog.ExportSql is null || ieDto.TemplateLog.ExportSql.Contains($"#{ExcelIEConsts.SelectSql}#"))
{
ieDto.FieldList = JsonHelper.ToJson <List <FieldHeads> >(ieDto.Template.ExportHead);
ieDto.FieldList.ForEach(o =>
{
if (Convert.ToInt32(o.IsHide) == 0)
{
selectFields.AppendLine(string.Format("{0} AS {1},", ieDto.ReplaceFields.Keys.Contains(o.FieldEnName) ? ieDto.ReplaceFields[o.FieldEnName] : o.FieldDbName, o.FieldEnName));
}
});
selectFields.AppendLine(string.Format("ROW_NUMBER() OVER (ORDER BY {0}.{1} {2}) AS RowNum ",
string.IsNullOrEmpty(ieDto.Template.MainTableSign) ? ExcelIEConsts.MainTableSign : ieDto.Template.MainTableSign,
string.IsNullOrEmpty(ieDto.Template.OrderField) ? ExcelIEConsts.PrimarkKey : ieDto.Template.OrderField,
Convert.ToBoolean(ieDto.Template.Sort) ? ExcelIEConsts.SortDesc : ExcelIEConsts.SortAsc));
}
}
if (ieDto.TemplateLog.ExportSql is null || (ieDto.TemplateLog.ExportSql.Contains($"#{ExcelIEConsts.MainSql}#") && ieDto.TemplateLog.ExportSql.Contains($"#{ExcelIEConsts.SelectSql}#")))
{
mainSql.AppendLine(" where 1=1 ");
var type = typeof(ExcelIEDto);
var properties = type.GetProperties().Where(o => o.PropertyType.Name == ExcelIEConsts.PropertitySignName).ToList();
foreach (var propertity in properties)
{
var listItem = propertity.GetValue(ieDto, null) as List <ExcelEItemDto>;
var listFieldName = propertity.Name;
if (listItem == null)
{
continue;
}
foreach (var item in listItem)
{
if (item.FieldName.Count > 0 && item.FieldValue.Count > 0)
{
tempName = item.FieldName.First().FieldName; tempValue = item.FieldValue.First();
if (string.IsNullOrEmpty(tempName))
{
continue;
}
if (listFieldName == ExcelIEConsts.Equal)
{
mainSql.AppendLine(string.Format("And {0}='{1}' ", tempName, tempValue));
}
if (listFieldName == ExcelIEConsts.NotEqual)
{
mainSql.AppendLine(string.Format("And {0}<>'{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.Greater)
{
mainSql.AppendLine(string.Format("And {0}>'{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.GreaterEqual)
{
mainSql.AppendLine(string.Format("And {0}>='{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.Less)
{
mainSql.AppendLine(string.Format("And {0}<'{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.LessEqual)
{
mainSql.AppendLine(string.Format("And {0}<='{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.In)
{
var multInValue = new List <string>();
item.FieldValue.ForEach(o =>
{
multInValue.Add("'" + o + "'");
});
mainSql.AppendLine(string.Format("And {0} In ({1}) ", item.FieldName.First().FieldName, string.Join(',', multInValue.ToArray())));
}
else if (listFieldName == ExcelIEConsts.NotIn)
{
var multInValue = new List <string>();
item.FieldValue.ForEach(o =>
{
multInValue.Add("'" + o + "'");
});
mainSql.AppendLine(string.Format("And {0} Not In ({1}) ", item.FieldName.First().FieldName, string.Join(',', multInValue.ToArray())));
}
else if (listFieldName == ExcelIEConsts.Like)
{
mainSql.AppendLine(string.Format("And {0} Like '%{1}%' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.NotLike)
{
mainSql.AppendLine(string.Format("And {0} Not Like '%{1}%' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.CommonLike)
{
var likeValue = tempValue;
mainSql.Append("And ( ");
foreach (var like in item.FieldName)
{
if (item.FieldName.IndexOf(like) == item.FieldName.Count - 1)
{
mainSql.AppendLine(string.Format("{0} like '%{1}%' ", like.FieldName, likeValue));
}
else
{
mainSql.AppendLine(string.Format("{0} like '%{1}%' Or ", like.FieldName, likeValue));
}
}
mainSql.Append(" ) ");
}
else if (listFieldName == ExcelIEConsts.StartWith)
{
mainSql.AppendLine(string.Format("And {0} Like '{1}%' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.EndWith)
{
mainSql.AppendLine(string.Format("And {0} Like '%{1}' ", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.FitNULL)
{
mainSql.AppendLine(string.Format("And {0} Is Null", tempName, tempValue));
}
else if (listFieldName == ExcelIEConsts.NotFitNULL)
{
mainSql.AppendLine(string.Format("And {0} Is Not Null ", tempName, tempValue));
}
}
}
}
if (!string.IsNullOrEmpty(ieDto.WhereSql))
{
mainSql.AppendLine(ieDto.WhereSql);
}
if (!string.IsNullOrEmpty(ieDto.FromSql))
{
mainSql = mainSql.Replace($"#{ExcelIEConsts.FromSql}#", ieDto.FromSql);
}
exportSql = ExcelIEConsts.WithSql
.Replace($"#{ExcelIEConsts.TopCount}#", (Convert.ToInt32(ieDto.Template.ExecMaxCountPer) > 0 ? ieDto.Template.ExecMaxCountPer : ExcelIEConsts.ExecMaxCountPer).ToString())
.Replace($"#{ExcelIEConsts.OrderBy}#", string.Format("Order By {0}.{1} {2}",
string.IsNullOrEmpty(ieDto.Template.MainTableSign) ? ExcelIEConsts.MainTableSign : ieDto.Template.MainTableSign,
string.IsNullOrEmpty(ieDto.Template.OrderField) ? ExcelIEConsts.PrimarkKey : ieDto.Template.OrderField,
Convert.ToBoolean(ieDto.Template.Sort) ? ExcelIEConsts.SortDesc : ExcelIEConsts.SortAsc))
.Replace($"#{ExcelIEConsts.MainSql}#", mainSql.ToString())
.Replace($"#{ExcelIEConsts.SelectSql}#", selectFields.ToString());
ieDto.TemplateLog.ExportSql = Regex.Replace(exportSql, @"[\r\n\t]", "");
}
}
