public static WebResponseContent ReadToDataTable <T>(string path, List <string> ignoreTemplate)
{
WebResponseContent responseContent = new WebResponseContent();
FileInfo file = new FileInfo(path);
if (!file.Exists)
{
return(responseContent.Error("未找到上传的文件,请重新上传"));
}
List <T> entities = new List <T>();
using (ExcelPackage package = new ExcelPackage(file))
{
if (package.Workbook.Worksheets.Count == 0 ||
package.Workbook.Worksheets.FirstOrDefault().Dimension.End.Row <= 1)
{
return(responseContent.Error("未导入数据"));
}
List <CellOptions> cellOptions = GetExportColumnInfo(typeof(T).GetEntityTableName(), false, false);
//设置忽略的列
if (ignoreTemplate != null)
{
cellOptions = cellOptions
.Where(x => !ignoreTemplate.Select(s => s.ToLower()).Contains(x.ColumnName.ToLower()))
.ToList();
}
ExcelWorksheet sheetFirst = package.Workbook.Worksheets.FirstOrDefault();
for (int j = sheetFirst.Dimension.Start.Column, k = sheetFirst.Dimension.End.Column; j <= k; j++)
{
string columnCNName = sheetFirst.Cells[1, j].Value?.ToString()?.Trim();
if (!string.IsNullOrEmpty(columnCNName))
{
CellOptions options = cellOptions.Where(x => x.ColumnCNName == columnCNName).FirstOrDefault();
if (options == null)
{
return(responseContent.Error("导入文件列[" + columnCNName + "]不是模板中的列"));
}
if (options.Index > 0)
{
return(responseContent.Error("导入文件列[" + columnCNName + "]不能重复"));
}
options.Index = j;
}
}
if (cellOptions.Exists(x => x.Index == 0))
{
return(responseContent.Error("导入文件列必须与导入模板相同"));
}
PropertyInfo[] propertyInfos = typeof(T).GetProperties()
.Where(x => cellOptions.Select(s => s.ColumnName).Contains(x.Name))
.ToArray();
ExcelWorksheet sheet = package.Workbook.Worksheets.FirstOrDefault();
for (int m = sheet.Dimension.Start.Row + 1, n = sheet.Dimension.End.Row; m <= n; m++)
{
T entity = Activator.CreateInstance <T>();
for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
{
string value = sheet.Cells[m, j].Value?.ToString();
CellOptions options = cellOptions.Where(x => x.Index == j).FirstOrDefault();
PropertyInfo property = propertyInfos.Where(x => x.Name == options.ColumnName).FirstOrDefault();
if (options.Requierd && string.IsNullOrEmpty(value))
{
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,不能为空。"));
}
//验证字典数据
if (!string.IsNullOrEmpty(options.DropNo))
{
string key = options.KeyValues.Where(x => x.Value == value)
.Select(s => s.Key)
.FirstOrDefault();
if (key == null)//&& options.Requierd
{
//小于20个字典项，直接提示所有可选value
string values = options.KeyValues.Count < 20 ? (string.Join(',', options.KeyValues.Select(s => s.Value))) : options.ColumnCNName;
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,必须是字典数据中[{values}]的值。"));
}
//将值设置为数据字典的Key,如果导入为是/否字典项，存在表中应该对为1/0
value = key;
}
//验证导入与实体数据类型是否相同
(bool, string, object)result = property.ValidationProperty(value, options.Requierd);
if (!result.Item1)
{
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,{result.Item2}"));
}
property.SetValue(entity, value.ChangeType(property.PropertyType));
}
entity.SetCreateDefaultVal();
entities.Add(entity);
}
}
return(responseContent.OK(null, entities));
}
public static void ComparisonSign(HttpRequest request, string ApiParameters, WebResponseContent responseData)
{
//ApiPostParameters PostParameters;
////try
////{
//// PostParameters = JsonConvert.DeserializeObject<ApiPostParameters>(ApiParameters);
////}
////catch (Exception ex)
////{
//// // ApiResponseData.SetResponse(responseData, ApiResponseEnum.ParametersError);
//// return;
////}
//DateTime? timestamp = DateTime.Now;//PostParameters.timestamp.GetDateTime();
////if (timestamp == null || (DateTime.Now - Convert.ToDateTime(timestamp)).TotalMinutes > 30)
////{
//// EnumToResponse.SetResponse(responseData, ResponseEnum.TimeStampInvalid);
//// return;
////}
////if (string.IsNullOrWhiteSpace(PostParameters.biz_content.ToString()))
////{
//// responseData.return_code = "107";
//// responseData.return_msg = "biz_content必填";
//// return;
////}
//if (string.IsNullOrWhiteSpace(PostParameters.v))
//{
// // ApiResponseData.SetResponse(responseData, ApiResponseEnum.VersionEmpty);
// return;
//}
//Dictionary<string, string> dicParameters = new Dictionary<string, string>();
////foreach (PropertyInfo property in PostParameters.GetType().GetProperties())
////{
//// object Val = property.GetValue(PostParameters);
//// if ((Val == null || Val.ToString() == "") && property.Name.ToLower() != "biz_content")
//// {
//// EnumToResponse.SetResponse(responseData, ResponseEnum.Other, property.Name + "无效");
//// return;
//// }
//// if (property.Name == "sign")
//// {
//// continue;
//// }
//// if (Val != null)
//// {
//// dicParameters.Add(property.Name, Val.ToString());
//// }
////}
////SqlParameter[] Paras = new SqlParameter[] {
//// new SqlParameter("@apiKey", PostParameters.apiKey)
////};
////object private_key = new SqlDatabase().ExecuteScalar(CommandType.Text,
//// "SELECT TOP 1 private_key FROM ApiUser WHERE apiKey=@apiKey", Paras);
////if (private_key == null || private_key.ToString() == "")
////{
//// EnumToResponse.SetResponse(responseData, ResponseEnum.ApiKeyInvalid);
//// return;
////}
//return;
//////验证加密码签名
////string Sign = Signature(dicParameters, private_key.ToString());
////if (PostParameters.sign != Sign)
////{
//// EnumToResponse.SetResponse(responseData, ResponseEnum.SignInvalid);
//// return;
////}
}
/// <summary>
/// 导入模板(仅限框架导出模板使用)(202.05.07)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="path"></param>
/// <param name="exportColumns">指定导出的列</param>
/// <param name="ignoreColumns">忽略不导出的列(如果设置了exportColumns,ignoreColumns不会生效)</param>
/// <returns></returns>
public static WebResponseContent ReadToDataTable <T>(string path, Expression <Func <T, object> > exportColumns = null, List <string> ignoreTemplate = null)
{
WebResponseContent responseContent = new WebResponseContent();
FileInfo file = new FileInfo(path);
if (!file.Exists)
{
return(responseContent.Error("未找到上传的文件,请重新上传"));
}
List <T> entities = new List <T>();
using (ExcelPackage package = new ExcelPackage(file))
{
if (package.Workbook.Worksheets.Count == 0 ||
package.Workbook.Worksheets.FirstOrDefault().Dimension.End.Row <= 1)
{
return(responseContent.Error("未导入数据"));
}
//2020.08.11修复获取表结构信息时，表为别名时查不到数据的问题
//typeof(T).GetEntityTableName()
List <CellOptions> cellOptions = GetExportColumnInfo(typeof(T).Name, false, false, columns: exportColumns?.GetExpressionToArray());
//设置忽略的列
if (exportColumns != null)
{
cellOptions = cellOptions
.Where(x => exportColumns.GetExpressionToArray().Select(s => s.ToLower()).Contains(x.ColumnName.ToLower()))
.ToList();
}
else if (ignoreTemplate != null)
{
cellOptions = cellOptions
.Where(x => !ignoreTemplate.Select(s => s.ToLower()).Contains(x.ColumnName.ToLower()))
.ToList();
}
ExcelWorksheet sheetFirst = package.Workbook.Worksheets.FirstOrDefault();
for (int j = sheetFirst.Dimension.Start.Column, k = sheetFirst.Dimension.End.Column; j <= k; j++)
{
string columnCNName = sheetFirst.Cells[1, j].Value?.ToString()?.Trim();
if (!string.IsNullOrEmpty(columnCNName))
{
CellOptions options = cellOptions.Where(x => x.ColumnCNName == columnCNName).FirstOrDefault();
if (options == null)
{
return(responseContent.Error("导入文件列[" + columnCNName + "]不是模板中的列"));
}
if (options.Index > 0)
{
return(responseContent.Error("导入文件列[" + columnCNName + "]不能重复"));
}
options.Index = j;
}
}
if (cellOptions.Exists(x => x.Index == 0))
{
return(responseContent.Error("导入文件列必须与导入模板相同"));
}
PropertyInfo[] propertyInfos = typeof(T).GetProperties()
.Where(x => cellOptions.Select(s => s.ColumnName).Contains(x.Name))
.ToArray();
ExcelWorksheet sheet = package.Workbook.Worksheets.FirstOrDefault();
for (int m = sheet.Dimension.Start.Row + 1, n = sheet.Dimension.End.Row; m <= n; m++)
{
T entity = Activator.CreateInstance <T>();
for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
{
string value = sheet.Cells[m, j].Value?.ToString();
CellOptions options = cellOptions.Where(x => x.Index == j).FirstOrDefault();
PropertyInfo property = propertyInfos.Where(x => x.Name == options.ColumnName).FirstOrDefault();
//2021.06.04优化判断
if (string.IsNullOrEmpty(value))
{
if (options.Requierd)
{
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,不能为空。"));
}
continue;
}
//验证字典数据
//2020.09.20增加判断数据源是否有值
if (!string.IsNullOrEmpty(options.DropNo) && !string.IsNullOrEmpty(value))
{
if (options.KeyValues == null)
{
return(responseContent.Error($"[{options.ColumnCNName}]字段数字典编号[{options.DropNo}]缺失,请检查字典配置"));
}
string key = options.KeyValues.Where(x => x.Value == value)
.Select(s => s.Key)
.FirstOrDefault();
if (key == null)//&& options.Requierd
{
//小于20个字典项，直接提示所有可选value
string values = options.KeyValues.Count < 20 ? (string.Join(',', options.KeyValues.Select(s => s.Value))) : options.ColumnCNName;
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,必须是字典数据中[{values}]的值。"));
}
//将值设置为数据字典的Key,如果导入为是/否字典项，存在表中应该对为1/0
value = key;
}
else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
{
//2021.06.04增加日期格式处理
if (value.Length == 5 && int.TryParse(value, out int days))
{
property.SetValue(entity, new DateTime(1900, 1, 1).AddDays(days - 2));
}
else
{
property.SetValue(entity, value.ChangeType(property.PropertyType));
}
continue;
}
//验证导入与实体数据类型是否相同
(bool, string, object)result = property.ValidationProperty(value, options.Requierd);
if (!result.Item1)
{
return(responseContent.Error($"第{m}行[{options.ColumnCNName}]验证未通过,{result.Item2}"));
}
property.SetValue(entity, value.ChangeType(property.PropertyType));
}
entity.SetCreateDefaultVal();
entities.Add(entity);
}
}
return(responseContent.OK(null, entities));
}
public static void GetContentResult(FilterContext context, IActionResult actionResult, WebResponseContent responseData)
{
responseData = responseData ?? new WebResponseContent();
responseData.Set(ResponseType.ServerError);
if (context.HttpContext.IsAjaxRequest())
{
actionResult = new ContentResult()
{
Content = JsonConvert.SerializeObject(responseData),
ContentType = ApplicationContentType.JSON,
StatusCode = (int)HttpStatusCode.InternalServerError
};
}
else
{
string desc = Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(responseData.Message));
actionResult = new ContentResult()
{
Content = $@"<html><head><title></title></head><body>{desc}</body></html>",
ContentType = "text/html",
StatusCode = (int)HttpStatusCode.InternalServerError
};
}
//writelog
}
