通用导入Excel更新

Ben Lin

2024-12-28 6e78892c440406c94fa1190d8daa04480620f13a

通用导入Excel更新

 Tiger.Api/Autofac/AutoFacContianer.cs

@@ -107,5 +107,10 @@
        {
            return AutoFacContainer.Instance.Resolve<T>();
        }

        public static dynamic Resolve(string typeFullName)
        {
            return AutoFacContainer.Instance.Resolve(System.Type.GetType(typeFullName, true));
        }
    }
}

 Tiger.Business.MES/BIZ/ImportWoPlan.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,123 @@
using Apache.NMS.ActiveMQ.Commands;
using Rhea.Common;
using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tiger.IBusiness;
using Tiger.Model.Minsun;
using Tiger.Model;
using Tiger.Model.Entitys.MES.U9C;
using System.Reflection;
using Dm.filter;
using System.Dynamic;

namespace Tiger.Business
{
    /// <summary>
    /// å·¥å•è®¡åˆ’导入Excel
    /// </summary>
    public class ImportWoPlan : IImportWoPlan
    {
        /// <summary>
        /// å¯¼å…¥
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity"></param>
        /// <param name="jsonStr"></param>
        /// <returns></returns>
        public async Task<ApiAction> Import<T>(T newEntity, ImportInput input) where T : class, new()
        {
            var result = new ApiAction();
            try
            {

                List<T> list = JsonConvert.DeserializeObject<List<T>>(input.EntityJson);
                DbClient db = Biz.Db;
                if (list.Any())
                {
                    if (list.Count > 100)
                    {
                        db.Utilities.PageEach(list, 100, pageList =>
                        {
                            var y = db.Storageable(pageList)
                                   .ToStorage();
                            y.AsInsertable.ExecuteCommand();
                            y.AsUpdateable.ExecuteCommand();
                        });
                    }
                    else
                    {
                        var s = db.Storageable(list)
                               .ToStorage();
                        s.AsInsertable.ExecuteCommand();
                        s.AsUpdateable.ExecuteCommand();
                    }
                }


            }
            catch (Exception ex)
            {
                result.CatchException(ex, $"验证导入异常");
            }
            return result;
        }

        /// <summary>
        /// å¯¼å…¥Excel前验证
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> ValidateTableImport<T>(T newEntity, ImportValidateInput input) {
            var result = new ApiAction<List<T>>();
            try
            {
                List<T> list = JsonConvert.DeserializeObject<List<T>>(input.EntityJson);
                List<string> chkColumns = JsonConvert.DeserializeObject<List<string>>(input.CheckJson);
                List<string> wheres = JsonConvert.DeserializeObject<List<string>>(input.where); //目前只有=，以后可以增加其他的查询条件
                Type entityType = typeof(T); //T泛型 
                //通过属性名获取属性值  â€œVALIDATION_TYPE”属性名称
                PropertyInfo typeInfo = entityType.GetProperty("VALIDATION_TYPE");
                //通过属性名获取属性值  â€œVALIDATION_RESULT”属性名称
                PropertyInfo retInfo = entityType.GetProperty("VALIDATION_RESULT");
                foreach (var item in list)
                {
                    string where = " 1=1 ";
                    typeInfo.SetValue(item, "新增");
                    retInfo.SetValue(item, "");
                    foreach (var col in chkColumns)
                    {
                        PropertyInfo colInfo = entityType.GetProperty(col);
                        if (!colInfo.IsNullOrEmpty() && colInfo.GetValue(item).IsNullOrEmpty()) {
                            typeInfo.SetValue(item, "数据异常");
                            retInfo.SetValue(item, "值为空或不存在");
                        }
                    }
                    foreach (var w in wheres)
                    {
                        PropertyInfo wInfo = entityType.GetProperty(w);
                        where += $"AND {w} = '{wInfo.GetValue(item)}'";
                    }
                    if (await Biz.Db.Queryable<T>().Where(where).AnyAsync())
                    {
                        typeInfo.SetValue(item, "修改");
                        retInfo.SetValue(item, "数据已经存在，使用更新");
                    }
                }
                result.Data = list;
            }
            catch (Exception ex)
            {
                result.CatchException(ex, $"验证导入异常");
            }
            return result;
        }
    }
}

 Tiger.Business/Common/ImportExcel.cs

@@ -21,22 +21,22 @@
    /// <summary>
    /// é€šç”¨å¯¼å…¥Excel
    /// </summary>
    public class ImportExcel : IImportExcel
    public class ImportExcel : ICommonImportExcel
    {
        /// <summary>
        /// å¯¼å…¥
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity"></param>
        /// <param name="jsonStr"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ApiAction> Import<T>(T newEntity, string jsonStr) where T : class, new()
        public async Task<ApiAction> Import<T>(T newEntity, ImportInput input) where T : class, new()
        {
            var result = new ApiAction();
            try
            {

                List<T> list = JsonConvert.DeserializeObject<List<T>>(jsonStr);
                List<T> list = JsonConvert.DeserializeObject<List<T>>(input.EntityJson);
                DbClient db = Biz.Db;
                if (list.Any())
                {
@@ -75,13 +75,13 @@
        /// <param name="newEntity"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ApiAction<List<T>>> ValidateTableImport<T>(T newEntity, ImportEntityValidate input) {
        public async Task<ApiAction<List<T>>> ValidateTableImport<T>(T newEntity, ImportValidateInput input) {
            var result = new ApiAction<List<T>>();
            try
            {
                List<T> list = JsonConvert.DeserializeObject<List<T>>(input.EntityJson);
                List<string> chkColumns = JsonConvert.DeserializeObject<List<string>>(input.CheckJson);
                List<string> wheres = JsonConvert.DeserializeObject<List<string>>(input.where);
                List<string> wheres = JsonConvert.DeserializeObject<List<string>>(input.where); //目前只有=，以后可以增加其他的查询条件
                Type entityType = typeof(T); //T泛型 
                //通过属性名获取属性值  â€œVALIDATION_TYPE”属性名称
                PropertyInfo typeInfo = entityType.GetProperty("VALIDATION_TYPE");

 Tiger.Controllers.System/Controllers/Base/BaseController.cs

@@ -577,12 +577,13 @@

        #region å¯¼å…¥Excel
        [HttpPost]
        public async Task<IActionResult> ImportExcel([FromBody] ApiAction action)
        public async Task<IActionResult> ImportExcel([FromBody] ApiAction<ImportInput> action)
        {
            ApiAction response;
            try
            {
                response = action.GetResponse(await DI.Resolve<IImportExcel>().Import(action.NewDataEntity(),action.Data?.ToString()));
                IImportExcel import = DI.Resolve(action.Data.typeFullName) as IImportExcel;
                response = action.GetResponse(await import.Import(action.NewDataEntity(),action.Data));
            }
            catch (System.Exception ex)
            {
@@ -597,12 +598,13 @@
        /// <param name="action"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ValidateTableImport([FromBody] ApiAction<ImportEntityValidate> action)
        public async Task<IActionResult> ValidateTableImport([FromBody] ApiAction<ImportValidateInput> action)
        {
            ApiAction response;
            try
            {
                response = action.GetResponse(await DI.Resolve<IImportExcel>().ValidateTableImport(action.NewDataEntity(), action.Data));
                IImportExcel import = DI.Resolve(action.Data.typeFullName) as IImportExcel;
                response = action.GetResponse(await import.ValidateTableImport(action.NewDataEntity(), action.Data));
            }
            catch (System.Exception ex)
            {

 Tiger.IBusiness.MES/BIZ/IImportWoPlan.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,17 @@
using Rhea.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tiger.Model;
using Tiger.Model.Entitys.MES.BizMesWo;
using Tiger.Model.Entitys.MES.BizMesWoBatch;

namespace Tiger.IBusiness
{
    public interface IImportWoPlan: IImportExcel
    {
    }
}

 Tiger.IBusiness/Common/ICommonImportExcel.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,14 @@
using Rhea.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tiger.Model;

namespace Tiger.IBusiness
{
    public interface ICommonImportExcel: IImportExcel
    {
    }
}

 Tiger.IBusiness/Common/IImportExcel.cs

@@ -10,7 +10,7 @@
{
    public interface IImportExcel
    {
        public Task<ApiAction> Import<T>(T newEntity, string jsonStr) where T : class, new();
        public Task<ApiAction<List<T>>> ValidateTableImport<T>(T newEntity, ImportEntityValidate input);
        public Task<ApiAction> Import<T>(T newEntity, ImportInput input) where T : class, new();
        public Task<ApiAction<List<T>>> ValidateTableImport<T>(T newEntity, ImportValidateInput input);
    }
}

 Tiger.Model.Net/Entitys/ImportEntity.cs

@@ -13,7 +13,17 @@
        public string[] PARAMS { get; set; }
    }

    public class ImportEntityValidate
    public class BaseImportInput
    {
        public string typeFullName { get; set; }
    }

    public class ImportInput : BaseImportInput
    {
        public string EntityJson { get; set; }
    }

    public class ImportValidateInput: BaseImportInput
    {
        public string EntityJson { get; set; }
        public string CheckJson { get; set; }