C# Excel 导入验证

实体类写法，带上描述+验证规则

public class Entity

    {

        [Requiring()]

        [Description("经销商代码")]

       public const int DEALER_CODE = 3;

 

        [Requiring(), ListRule("01,02,03", ",")]

        [Description("品牌")]

       public const int BRAND = 5;

 

        [Requiring(), DateOnlyRule()]

        [Description("申请时间")]

       public const int S01_DATE = 8;

}

 

调用Excel上传的写法

// Locate Sheet

                   var XlsxSheet = XlsxFile.Workbook.Worksheets[cboSheets.SelectedItem.ToString()];

 

                   // Checking

                    Logger.Clear();

                   "Checking Data...".AddLogTo(Logger);

                    CheckResilt = XlsxSheet.Check(Logger);

                   "Check Complete.".AddLogTo(Logger);

 

                    // Cancel if not pass.

                   if (!CheckResilt)

                    {

                       "Coversion Cancelled.".AddLogTo(Logger);

                       return;

                    }

相关判断规则和帮助方法

 

 

/// <summary>

   /// EPPlus Extension

   /// </summary>

   public static class EPPlusExtension

    {

 

       #region Check

 

       /// <summary>

       /// Check.

       /// </summary>

       /// <param name="Sheet">Sheet.</param>

       /// <param name="Logger">Logger.</param>

       /// <returns></returns>

       public static bool Check(this ExcelWorksheet Sheet, IList Logger)

        {

           int RowNo = 2; bool Result = true;

           while (Sheet.HasData(RowNo))

                Result &= Sheet.Check(RowNo++, Logger);

           return Result;

        }

 

       /// <summary>

       /// Check.

       /// </summary>

       /// <param name="Sheet">Excel Worksheet.</param>

       /// <param name="RowNo">Row No.</param>

       /// <param name="Logger">Logger.</param>

       /// <returns></returns>

       internal static bool Check(this ExcelWorksheet Sheet, int RowNo, IList Logger)

        {

           bool Result = true;

 

           foreach (var Col in GetOrderedColumns())

            {

               var Value = Sheet.Cells[RowNo, Col.GetColNo()].Text;

               var Req = Col.GetCustomAttribute<RequiringAttribute>();

               var Rule = Col.GetCustomAttribute<RuleAttribute>();

               var Desc = Col.GetCustomAttribute<DescriptionAttribute>();

 

               if (Value.IsNullOrEmpty())

                {

                   if (Req.IsRequired(ref Sheet, RowNo))

                        Result &= Req.GetErrorMessage(Col.GetCellName(RowNo), Desc.Description).AddLogTo(Logger, false);

                    continue;

                }

 

               if (Rule != null && !Rule.Check(Value))

                {

                    Result &= Rule.GetErrorMessage(Col.GetCellName(RowNo), Desc.Description).AddLogTo(Logger, false);

                   continue;

                }

            }

 

           return Result;

        }

 

       #endregion

 

       #region Export

 

       /// <summary>

       /// Export.

       /// </summary>

       /// <param name="Sheet">Sheet.</param>

       /// <param name="Stream">Stream.</param>

       /// <param name="Logger">Logger.</param>

       public static void Export(this ExcelWorksheet Sheet, ref Stream Stream, IList Logger)

        {

           int RowNo = 1; StreamWriter File = null;

           using (File = new StreamWriter(Stream, Encoding.UTF8))

               while (Sheet.HasData(RowNo))

                    Sheet.Export(RowNo++, ref File, Logger);

        }

 

       /// <summary>

       /// Export.

       /// </summary>

       /// <param name="Sheet">Sheet.</param>

       /// <param name="RowNo">Row No.</param>

       /// <param name="File">File.</param>

       /// <param name="Logger">Logger.</param>

       internal static void Export(this ExcelWorksheet Sheet, int RowNo, ref StreamWriter File, IList Logger)

        {

            List<string> Line = new List<string>();

 

            GetOrderedColumns().ForEach((Col) =>

            {

               var Value = Sheet.Cells[RowNo, Col.GetColNo()].Text;

               var Rule = Col.GetCustomAttribute<RuleAttribute>();

 

                Value = Value.NVL().ReplaceToEmpty("\r\n", "\n");

 

               if (RowNo != 1 && Rule is DigitsRuleAttribute)

                    Value = ((DigitsRuleAttribute)Rule).Format(Value).ReplaceToEmpty(",");

 

                Line.Add(Value);

            });

 

            File.WriteLine(Line.Join());

        }

 

       #endregion

 

       #region Columns

 

       /// <summary>

       /// Has Data

       /// </summary>

       /// <param name="Sheet">Excel Worksheet.</param>

       /// <param name="RowNo">Row No.</param>

       /// <returns></returns>

       public static bool HasData(this ExcelWorksheet Sheet, int RowNo)

        {

           return !Sheet.Cells[RowNo, RENDEF.REN_ID].Text.IsNullOrWhiteSpace();

        }

 

       /// <summary>

       /// Get Ordered Columns

       /// </summary>

       private static Func<List<FieldInfo>> GetOrderedColumns = () =>

           typeof(RENDEF).GetFields(BindingFlags.Static | BindingFlags.Public).ToList();

 

       /// <summary>

       /// Get Cell Name.

       /// </summary>

       /// <param name="Col"></param>

       /// <param name="RowNo"></param>

       /// <returns></returns>

       public static string GetCellName(this FieldInfo Col, int RowNo)

        {

            Func<int, string> GetColName = (n) =>

            {

               int m = 0; string s = string.Empty;

               while (n > 0) { m = n % 26; if (m == 0) m = 26; s = (char)(m + 64) + s; n = (n - m) / 26; }

               return s;

            };

 

           return GetColName((int)Col.GetRawConstantValue()) + RowNo.ToString();

        }

 

       /// <summary>

       /// Get Column Number.

       /// </summary>

       /// <param name="Col"></param>

       /// <returns></returns>

       public static int GetColNo(this FieldInfo Col)

        {

           return (int)Col.GetRawConstantValue();

        }

 

       /// <summary>

       /// Get Custom Attribute (Generic Version, For .NET Fx 4.0).

       /// </summary>

       /// <typeparam name="T">Type.</typeparam>

       /// <param name="Col"></param>

       /// <param name="inherit"></param>

       /// <returns></returns>

       public static T GetCustomAttribute<T>(this FieldInfo Col, bool inherit = false)

        {

           return (T)Col.GetCustomAttributes(typeof(T), inherit).FirstOrDefault();

        }

 

       #endregion

}

 

/// <summary>

   /// Description Attribute

   /// </summary>

   public class DescriptionAttribute : Attribute

    {

       /// <summary>

       /// Description

       /// </summary>

       public string Description;

 

       /// <summary>

       /// Description

       /// </summary>

       /// <param name="Description">Description</param>

       public DescriptionAttribute(string Description)

        {

           this.Description = Description;

        }

    }

 

 

/// <summary>

   /// Requiring Attribute

   /// </summary>

   public class RequiringAttribute : Attribute

    {

       /// <summary>

       /// Whether Required?

       /// </summary>

       /// <remarks>

       /// Set this value to null, to indicates Conditional Requirements.

       /// </remarks>

       public bool? YesOrNo = null;

 

       /// <summary>

       /// 条件性必填时的要判断的列。

       /// </summary>

       public int OnlyWhen;

 

       /// <summary>

       /// 条件性必填时的要判断的值。

       /// </summary>

       public string IsValue;

 

       /// <summary>

       /// 是否必填

       /// </summary>

       /// <param name="YesOrNo"></param>

       public RequiringAttribute(bool YesOrNo = true)

        {

           this.YesOrNo = YesOrNo;

        }

 

       /// <summary>

       /// 条件性必填

       /// </summary>

       /// <param name="OnlyWhen">条件列</param>

       /// <param name="IsValue">判断值</param>

       public RequiringAttribute(int OnlyWhen, string IsValue)

        {

           this.YesOrNo = null;

           this.OnlyWhen = OnlyWhen;

           this.IsValue = IsValue;

        }

 

       /// <summary>

       /// 是否需要

       /// </summary>

       /// <param name="Sheet"></param>

       /// <param name="rowno"></param>

       /// <returns></returns>

       public bool IsRequired(ref ExcelWorksheet Sheet, int rowno)

        {

           if (YesOrNo.HasValue) return YesOrNo.Value;

           else return Sheet.Cells[rowno, OnlyWhen].Value.Equals(IsValue);

        }

 

       /// <summary>

       /// Get Error Message

       /// </summary>

       /// <param name="Cell">Cell.</param>

       /// <param name="Desc">Description.</param>

       /// <returns></returns>

       public string GetErrorMessage(string Cell, string Desc)

        {

           return string.Format("单元格[{0}]的项目({1})为必填项。", Cell, Desc);

        }

    }

 

/// <summary>

   /// Rule Attribute (Abstract)

   /// </summary>

   public abstract class RuleAttribute : Attribute

    {

       /// <summary>

       /// Check

       /// </summary>

       /// <param name="Value">值</param>

       /// <returns></returns>

       public abstract bool Check(string Value);

 

       /// <summary>

       /// Get Error Message.

       /// </summary>

       /// <param name="Cell">Cell.</param>

       /// <param name="Desc">Description.</param>

       /// <returns></returns>

       public abstract string GetErrorMessage(string Cell, string Desc);

    }

 

 

   /// <summary>

   /// List Rule Attribute

   /// </summary>

   public class ListRuleAttribute : RuleAttribute

    {

       /// <summary>

       /// Items.

       /// </summary>

        List<string> Items = null;

 

       /// <summary>

       /// List Rule

       /// </summary>

       /// <param name="ItemList">Item List. (Default is "/", when spliting.)</param>

       public ListRuleAttribute(string ItemList) : this(ItemList, "/") { }

 

       /// <summary>

       /// List Rule

       /// </summary>

       /// <param name="ItemList">Item List.</param>

       /// <param name="Separator">Separator.</param>

       public ListRuleAttribute(string ItemList, params string[] Separator)

        {

            Items = ItemList.Split(Separator, StringSplitOptions.RemoveEmptyEntries).ToList();

        }

 

       /// <summary>

       /// Check.

       /// </summary>

       /// <param name="Value">Value.</param>

       /// <returns></returns>

       public override bool Check(string Value)

        {

           return Items.Contains(Value);

        }

 

       /// <summary>

       /// Get Error Message

       /// </summary>

       /// <param name="Cell">Cell.</param>

       /// <param name="Desc">Description.</param>

       /// <returns></returns>

       public override string GetErrorMessage(string Cell, string Desc)

        {

           return string.Format("单元格[{0}]中的项目({1})应该填入{{{2}}}中的值之一。", Cell, Desc, string.Join(",", Items));

        }

    }

 

   /// <summary>

   /// Date Only Rule Attribute

   /// </summary>

   public class DateOnlyRuleAttribute : RuleAttribute

    {

       string Format;

 

       /// <summary>

       /// Date Only Rule.

       /// </summary>

       /// <param name="Format"></param>

       public DateOnlyRuleAttribute(string Format = "yyyyMMdd")

        {

           this.Format = Format;

        }

 

       /// <summary>

       /// Check.

       /// </summary>

       /// <param name="Value">Value。</param>

       /// <returns></returns>

       public override bool Check(string Value)

        {

           var Value2 = string.Format("{0}/{1}/{2}", Strings.Left(Value, 4), Strings.Mid(Value, 5, 2), Strings.Mid(Value, 7, 2));

 

            DateTime Output;

           return DateTime.TryParse(Value2, out Output);

        }

 

       /// <summary>

       /// Get Error Message

       /// </summary>

       /// <param name="Cell">Cell.</param>

       /// <param name="Desc">Description.</param>

       /// <returns></returns>

       public override string GetErrorMessage(string Cell, string Desc)

        {

           return string.Format("单元格[{0}]中的项目({1})应该填入:YYYYMMDD格式的日期。", Cell, Desc);

        }

    }

 

   /// <summary>

   /// Digits Rule Attribute

   /// </summary>

   public class DigitsRuleAttribute : RuleAttribute

    {

       /// <summary>

       /// Digits

       /// </summary>

        public int Digits;

 

       /// <summary>

       /// Digits Rule

       /// </summary>

       /// <param name="Digits">Digits</param>

       public DigitsRuleAttribute(int Digits)

        {

           this.Digits = Digits;

        }

 

       /// <summary>

       /// Check

       /// </summary>

       /// <param name="Value">Value.</param>

       /// <returns></returns>

       public override bool Check(string Value)

        {

           if (Digits > 0)

            {

               decimal output;

               return decimal.TryParse(Value, out output);

            }

           else if (Digits == 0)

            {

               int output;

               return int.TryParse(Value, out output);

            }

           else

               throw new ArgumentException("Digits could not less than 0.", "Digits");

        }

 

       /// <summary>

       /// Get Error Message.

       /// </summary>

       /// <param name="Cell">Cell.</param>

       /// <param name="Desc">Description.</param>

       /// <returns></returns>

       public override string GetErrorMessage(string Cell, string Desc)

        {

           return string.Format("单元格[{0}]中的项目({1})应该填入:{2}位小数。", Cell, Desc, Digits);

        }

 

       /// <summary>

       /// Format.

       /// </summary>

       /// <param name="Value">Value.</param>

       /// <returns></returns>

       public string Format(string Value)

        {

           if (Value.IsNullOrWhiteSpace())

                Value = "0";

 

            if (Digits > 0)

            {

               decimal output;

               decimal.TryParse(Value, out output);

               return output.ToString("0." + new string('0', Digits));

            }

           else if (Digits == 0)

            {

                int output;

               int.TryParse(Value, out output);

               return output.ToString("0");

            }

           else

               throw new ArgumentException("Digits could not less than 0.", "Digits");

        }

    }