实体类写法,带上描述+验证规则
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");
}
}