后台Excel导入与导出

一：导入Excel数据

  [Area("Pumch")]
    public class UserController :  BaseController
    {
		protected IUserService m_UserService= IocProxyFactory.GetService<IUserService>();

        public JsonResult Import()
        {
            var jObject = ParseRawData().GetAwaiter().GetResult();

            var commandName = jObject["Command"].ToString();

            var path = Convert.ToString(jObject["Data"]["SitePath"]);

            if (string.IsNullOrEmpty(path))
            {
                throw new ArgumentException("请上传Excel路径SitePath");
            }

            return Json(Exec("User", jObject));
        }
		
        IDictionary<string, IDictionary<string, AuthMethod>> m_MethodDic = null;
		
		protected override AuthMethod GetMethod(string actionName, string commandName)
        {
            if (m_MethodDic == null)
            {
                m_MethodDic = new Dictionary<string, IDictionary<string, AuthMethod>>()
                {
                    {"User", new Dictionary<string, AuthMethod>(){
                        {"Import", new AuthMethod(){Method= m_UserService.Import}}
                    }}
                };
            }

            m_MethodCommandDic = m_MethodDic;

            return base.GetMethod(actionName, commandName);
        }
	}

public Result Import(JObject jObject)
		{
			var path = AppDomain.CurrentDomain.BaseDirectory + "wwwroot" + Convert.ToString(jObject["SitePath"]);

			try
			{
				var dataList = ExcelParse.Parse<UserEntity>(path, new Dictionary<string, string>()
			{
				 {"a", "RealName"}
				,{"b", "UserName"}
				,{"c", "Identity"}
			}, 1);

				var newList = new List<UserEntity>();

				foreach (var i in dataList)
				{
					if (newList.SingleOrDefault(y => y.UserName == i.UserName) == null)
					{
						newList.Add(i);
					}
				}

				lock (UserToolsSingleton.LockUser(LockEnum.用户导入.ToString()))
				{
					var userNameList = newList.Select(i => i.UserName).ToList();
					var identityList = newList.Select(i => i.Identity).ToList();

					var userDic = m_Dao.GetListAll(i => i.UserID, i => userNameList.Contains(i.UserName) && identityList.Contains(i.Identity) && i.Enable == 1, true).ToDictionary(i => i.UserName, i => i);

					foreach (var i in newList)
					{
						if (userDic.ContainsKey(i.UserName))
						{
							continue;
						}
						
						i.UserPassWord = PasswordHelper.ConverToPassword("666666");
						i.Enable = 1;
						i.CreateTime = DateTime.Now;

						m_Dao.CreateNotSave(i);
					}

					m_Dao.Save();
				}
			}
			catch (Exception ex)
			{
				LogHelper.Info("导出Excel异常:" + ex.ToString() + ",Path:" + path);

				return Result.GetError("格式错误请尝试对EXCEL内容全选,并进行\"清除格式\"操作");
			}

			return Result.GetSuccess();
		}
		

二:导出Excel数据

1.需要XML模板，excel打开模板另存为xml，然后对需要导出的数据字段进行绑定。

  public void ExportFinanceRecordUser(string shoppingOrderNo, string insuranceOrderNo, string telephone, DateTime? startTime, DateTime? endTime, byte? type, byte? isFrozen)
        {
            var ret = m_FinanceRecordService.ExportFinanceRecord(shoppingOrderNo, insuranceOrderNo, telephone, startTime, endTime, type, isFrozen);

            string fileName = DateTime.Today.ToString("yyyy-MM-dd") + "财务记录.xls";

            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString());
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.Write(ret);
            System.Web.HttpContext.Current.Response.End();
        }

  public string ExportFinanceRecord(string shoppingOrderNo, string insuranceOrderNo, string telephone, DateTime? startTime, DateTime? endTime, byte? type, byte? isFrozen)
        {
            var rawData = @"{Command:'', 
                                Data:{
                                       Query:
                                            {ShoppingOrderNo:'" + shoppingOrderNo + "',InsuranceOrderNo: '" + insuranceOrderNo + "',Telephone: '" + telephone + "',StartTime: '" + startTime + "',EndTime: '" + endTime + "',Type: '" + type + "',IsFrozen: '" + isFrozen + "'},Index:1,Pagesize:10000,Asc:false}}";

            var jObject = JObject.Parse(rawData);

            var data = (JObject)jObject["Data"];

            var list = GetList(data);

            var seralizeList = Newtonsoft.Json.JsonConvert.DeserializeObject<IDictionary<string, object>>(Newtonsoft.Json.JsonConvert.SerializeObject(list.Data));

            var newList = SerializeHelper.SerializeToList(seralizeList["List"]);

            for (int i = 0; i < newList.Count; i++)
            {
                var _d = newList[i];

                _d["CreateTime"] = Convert.ToDateTime(_d["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss");

                var financeRecordType = Convert.ToByte(_d["Type"]);

                switch (financeRecordType)
                {
                    case 1: _d["Type"] = "下单充值"; break;
                    case 2: _d["Type"] = "下单支付"; break;
                    case 3: _d["Type"] = "团队成员商城下单返利"; break;
                    case 4: _d["Type"] = "团队成员保险下单返利"; break;
                    case 5: _d["Type"] = "保险下单返利"; break;
                    case 6: _d["Type"] = "提现冻结"; break;
                    case 7: _d["Type"] = "提现审核拒绝"; break;
                    case 8: _d["Type"] = "提现审核成功"; break;
                    case 9: _d["Type"] = "红包消费"; break;
                    case 10: _d["Type"] = "商城下单返利"; break;
                    case 11: _d["Type"] = "红包赠送"; break;
                }

                var pointRecordIsFrozen = Convert.ToByte(_d["IsFrozen"]);

                switch (pointRecordIsFrozen)
                {
                    case 0: _d["IsFrozen"] = "已解冻"; break;
                    case 1: _d["IsFrozen"] = "未解冻"; break;
                    case 3: _d["IsFrozen"] = "已取消"; break;
                }

                var orderType = Convert.ToByte(_d["OrderType"]);

                switch (orderType)
                {
                    case 1: _d["OrderType"] = "实物订单"; break;
                    case 2: _d["OrderType"] = "虚拟订单"; break;
                    case 0: _d["OrderType"] = "无"; break;
                }
            }

            var context = new TemplateContext()
            {
                String = FileHelper.ReadFile(HttpContext.Current.Server.MapPath("/Export/ExportFinanceRecord.xml"), Encoding.UTF8),
                TemplateType = "preview",
                Source = new
                {
                    List = ScanRecordService.DicToInvariantCultureIgnoreCase(newList),
                    Length = newList.Count() + 1
                }
            };

            TemplateEngine.Current.Run(context);

            return context.Result();
        }