导入Excel通用

Ben Lin

2024-12-27 af5f1cebacbff5f03b77eed92425312857c83ed1

导入Excel通用

 Tiger.Business/Common/ImportExcel.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 ImportExcel : IImportExcel
    {
        /// <summary>
        /// å¯¼å…¥
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="newEntity"></param>
        /// <param name="jsonStr"></param>
        /// <returns></returns>
        public async Task<ApiAction> Import<T>(T newEntity, string jsonStr) where T : class, new()
        {
            var result = new ApiAction();
            try
            {

                List<T> list = JsonConvert.DeserializeObject<List<T>>(jsonStr);
                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, ImportEntityValidate 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.Controllers.System/Controllers/Base/BaseController.cs

@@ -574,5 +574,42 @@
        }

        #endregion

        #region å¯¼å…¥Excel
        [HttpPost]
        public async Task<IActionResult> ImportExcel([FromBody] ApiAction action)
        {
            ApiAction response;
            try
            {
                response = action.GetResponse(await DI.Resolve<IImportExcel>().Import(action.NewDataEntity(),action.Data?.ToString()));
            }
            catch (System.Exception ex)
            {
                response = action.GetResponse().CatchExceptionWithLog(ex);
            }
            return Ok(response);
        }

        /// <summary>
        /// å¯¼å…¥Excel前验证
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ValidateTableImport([FromBody] ApiAction<ImportEntityValidate> action)
        {
            ApiAction response;
            try
            {
                response = action.GetResponse(await DI.Resolve<IImportExcel>().ValidateTableImport(action.NewDataEntity(), action.Data));
            }
            catch (System.Exception ex)
            {
                response = action.GetResponse().CatchExceptionWithLog(ex);
            }
            return Ok(response);
        }
        #endregion
    }
}

 Tiger.IBusiness/Common/IImportExcel.cs

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,16 @@
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 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);
    }
}

 Tiger.Model.Net/Entitys/ImportEntity.cs

@@ -12,4 +12,11 @@
        public string DataTableStr { get; set; }
        public string[] PARAMS { get; set; }
    }

    public class ImportEntityValidate
    {
        public string EntityJson { get; set; }
        public string CheckJson { get; set; }
        public string where { get; set; }
    }
}

 Tiger.Model.Net/Entitys/MES/BIZ_MES_WO_BATCH.cs

@@ -158,8 +158,13 @@
        #region è™šæ‹Ÿå±žæ€§
        /*例子
        [SugarColumn(IsIgnore = true)]
        public string FieldName { get; set; }
        public string FieldName { get; set; } 
        */
        [SugarColumn(IsIgnore = true)]
        public string VALIDATION_TYPE { get; set; }

        [SugarColumn(IsIgnore = true)]
        public string VALIDATION_RESULT { get; set; }
        #endregion

        #region å¤–键属性