乐趣区

关于c#:技术总结项目开端基础设施制造造轮子-自动生成sql-导入封装

因为这个我的项目没有应用框架,应用了 sqlite 数据库。前面的业务可想而知会应用多少拼接 sql,这就会相当麻烦 一旦数据库扭转 就要改很多货色。

所以根底的就是封装生成 sql。须要应用反射和自定义的个性。自定义个性在于“绑定数据库列名”和“绑定业务格局”

一、业务一 — 主动生成 sql
1. 首先是实体对应的表:[个性 –》TableNameAttr]
using System;
using System.Collections.Generic;
using System.Text;

namespace PublicLibrary.attr
{

// 数据库表名
[AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
public class TableNameAttr: Attribute{
    string value;

    public TableNameAttr(string value)
    {this.Value = value;}

    public string Value {get => value; set => this.value = value;}
}

}
2. 其次是实体字段对应的列名:[个性 –》TableFieldAttr]
using System;

namespace PublicLibrary.attr
{

// 查问 新增 批改的注解
[AttributeUsage(AttributeTargets.Field, AllowMultiple = true)]
public class TableFieldAttr:Attribute
{
    //bool 是 boolean 的类型
    // 是否存在的字段 ==> 针对查问
    private bool isExist =true;

    // 数据库列名
    private string columName;


    // 字符串类型
    private string jdbcType;

    // 默认值
    private string value;

/* // 新增 批改 选着性操作的字段

    private bool selective;*/

    // 须要自定义的数据库函数
    private bool definedFunc;

    // 是否主键
    private bool primaryKey =false;


    // 函数的表达式
    private string patternStr;

    // 是否更新 默认为 true
    private bool isUpdate =true;


    public TableFieldAttr(string columName)
    {this.ColumName = columName;}

    // 字段数据库不存在
    public TableFieldAttr(bool isExist)
    {this.IsExist = false;}

    // 行数据列名 默认值
    public TableFieldAttr(string columName, string value)
    {
        this.ColumName = columName;
        this.Value = value;
    }

    
    public TableFieldAttr(string columName, string value, string jdbcType,bool definedFunc, bool primaryKey, string patternStr)
    {
        this.ColumName = columName;
        this.DefinedFunc = definedFunc;
        this.JdbcType = jdbcType;
        this.DefinedFunc = definedFunc;
        this.PrimaryKey = primaryKey;
        this.PatternStr = patternStr;
        this.Value = value;
    }   
    
    public TableFieldAttr(string columName, string value, string jdbcType,bool definedFunc, bool primaryKey, string patternStr,bool isUpdate)
    {
        this.ColumName = columName;
        this.DefinedFunc = definedFunc;
        this.JdbcType = jdbcType;
        this.DefinedFunc = definedFunc;
        this.PrimaryKey = primaryKey;
        this.PatternStr = patternStr;
        this.Value = value;
        this.IsUpdate = isUpdate;
    }
  

    public bool IsExist {get => isExist; set => isExist = value;}
    public string ColumName {get => columName; set => columName = value;}
    public string JdbcType {get => jdbcType; set => jdbcType = value;}
    public string Value {get => value; set => this.value = value;}
    public bool DefinedFunc {get => definedFunc; set => definedFunc = value;}
    public bool PrimaryKey {get => primaryKey; set => primaryKey = value;}
    public string PatternStr {get => patternStr; set => patternStr = value;}
    public bool IsUpdate {get => isUpdate; set => isUpdate = value;}
}

}
3. 再就是创立 sql 的公共办法:
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using PublicLibrary.attr;
using PublicLibrary.serivce;
using PublicLibrary.serivce.impl;

namespace PublicLibrary
{

public  static class SQLiteSqlUtils
{

    /// <summary>
    /// 创立 查问语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"> 对象 </param>
    /// <param name="whereColums"> 查问出的字段, 如[id,name]</param>
    /// <returns></returns>
    public static string CreateSelectSql<T>(T t,string[] whereColums)
    {Type type = typeof(T);
        BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
        var startTime = DateTime.Now;
        TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
        string tableName = tableAttr != null ? tableAttr.Value : "";

        string sqlTemp = "SELECT #{colums} FROM" + tableName + "WHERE 1=1 #{whereColums}";

        Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>();

        StringBuilder colums = new StringBuilder();

        StringBuilder whereColumsTem = new StringBuilder("");

        FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

        PropertyInfo propertyInfo  = null;

        int countFlag = 0;
        IEnumerable<TableFieldAttr> enumerable = null;

        for (int i = 0; i < fieldInfos.Length; i++)
        {enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

            foreach (TableFieldAttr attr in enumerable){
                // 不是主键 和是数据库字段
                if (attr.IsExist)
                {fieldAttrDic.Add(fieldInfos[i].Name, attr);
                    colums.Append((countFlag == 0 ? "":",") + attr.ColumName);
                    countFlag++;
                }
            }
          
        }

        countFlag = 0;
        TableFieldAttr fieldAttr = null;
        if (whereColums != null)
        {for (int k = 0; k < whereColums.Length; k++)
            {propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags);
                if (propertyInfo != null)
                {fieldAttr = fieldAttrDic[whereColums[k]];
                    whereColumsTem.Append("and" + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'");
                    /*whereColumsTem.Append((countFlag == 0 ? "" :" and ") + tableName +"."+ (fieldAttr == null ?"NoField": fieldAttr.ColumName) +"='"+ propertyInfo.GetValue(t) +"'");
                    countFlag++;*/
                }

            }
        }
     

        string selectSql = sqlTemp.Replace("#{colums}", colums.ToString()).Replace("#{whereColums}", whereColumsTem.ToString());

        return selectSql;
    }


    /// <summary>
    /// 创立 删除语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"> 对象 </param>
    /// <param name="whereColums"> 查问出的字段, 如[id,name]</param>
    /// <returns></returns>
    public static string CreateDeleteSql<T>(T t, string[] whereColums)
    {Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>();
        Type type = typeof(T);
        BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
        var startTime = DateTime.Now;
        TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
        string tableName = tableAttr != null ? tableAttr.Value : "";

        string sqlTemp = "DELETE FROM" + tableName + "WHERE 1=1 #{whereColums}";

        StringBuilder whereColumsTem = new StringBuilder("");

        FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

        PropertyInfo propertyInfo = null;

        IEnumerable<TableFieldAttr> enumerable = null;

        int countFlag = 0;

        for (int i = 0; i < fieldInfos.Length; i++){enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

            foreach (TableFieldAttr attr in enumerable){
                // 不是主键 和是数据库字段
                if (attr.IsExist){fieldAttrDic.Add(fieldInfos[i].Name, attr);
                }
            }
        }

        TableFieldAttr fieldAttr = null;
        if (whereColums != null)
        {for (int k = 0; k < whereColums.Length; k++)
            {propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags);
                if (propertyInfo != null)
                {fieldAttr = fieldAttrDic[whereColums[k]];
                    whereColumsTem.Append("and" + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'");
                }

            }
        }

        string deleteSql = sqlTemp.Replace("#{whereColums}", whereColumsTem.ToString());
        return deleteSql;
    }

    public static string CreateInsertSql<T>(T t){List<T> list = new List<T>();
        list.Add(t);
        return CreateInsertSql<T>(list, new ColumServiceImpl());

    }

    public static string CreateInsertSql<T>(T t, ColumService columService){List<T> list = new List<T>();
        list.Add(t);
        return CreateInsertSql<T>(list, columService);

    }

    public static string CreateInsertSql<T>(List<T> list)
    {return CreateInsertSql<T>(list, new ColumServiceImpl());

    }

    // 新增 批量新增的 sql
    public static string CreateInsertSql<T>(List<T> list,ColumService columService)
    {Type type = typeof(T);
        BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
        var startTime = DateTime.Now;
        TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
        string tableName = tableAttr != null ? tableAttr.Value : "";


        string insertHead = "INSERT INTO" + tableName + "(#{colums}) VALUES\n";
        string valuesTemp = "(#{values})";


        FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

        Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>();

        Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>();

        //string iteminfo = "";
        PropertyInfo propertyinfo = null;

        TableFieldAttr tableFieldAttr = null;

        IEnumerable<TableFieldAttr> enumerable = null;

        string columsTemplet = "";
        string valuesTemplet = "";

        for (int i = 0; i < fieldInfos.Length; i++)
        {tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags));
            enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

            foreach (TableFieldAttr attr in enumerable)
            {table.Add(fieldInfos[i].Name, attr);
                attr.JdbcType = fieldInfos[i].FieldType.ToString();
                // 不是主键 和是数据库字段
                if (attr.IsExist)
                {
                    columsTemplet += "" + attr.ColumName +" ,";
                    valuesTemplet += "#{" + fieldInfos[i].Name + "} ,";
                }
            }
        }

        insertHead = insertHead.Replace("#{colums}", columsTemplet.Substring(0, columsTemplet.Length - 1));

        valuesTemp = valuesTemp.Replace("#{values}", valuesTemplet.Substring(0, valuesTemplet.Length - 1));

        string valuesInfo = "";
        string setValue = "";
        for (int k = 0; k < list.Count; k++)
        {

            object value = "";
            string currentValue = "";
            string rowInfo = valuesTemp;
            //rowInfo = ;
            foreach (string key in table.Keys)
            {propertyinfo = tablePropertys[key];

                tableFieldAttr = table[key];
                // 默认值 不进行函数转换
                if (tableFieldAttr.Value != null)
                {
                    value = tableFieldAttr.Value;
                    currentValue = value.ToString();
                    setValue =  currentValue;
                }
                else if (propertyinfo != null)
                {value = tablePropertys[key].GetValue(list[k], null);
                    currentValue = (value == null) ? "''":"'" + value.ToString() + "'";
                    setValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue;
                }

                
                rowInfo = rowInfo.Replace("#{" + key + "}", setValue);
            }

            valuesInfo += rowInfo + ",";

        }

        var endTime = DateTime.Now;
        TimeSpan ts = endTime.Subtract(startTime);
        Console.WriteLine("insert 语句生成耗时{0}ms.", ts.TotalMilliseconds);

        return insertHead + valuesInfo.Substring(0, valuesInfo.Length - 1);
    }

    /// <summary>
    ///  单条数据更新
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="whereColums"></param>
    /// <returns></returns>
    public static string CreateUpdateSql<T>(T t, string[] whereColums)
    {List<T> list = new List<T>();
        list.Add(t);
        return CreateUpdateSql<T>(list, whereColums, new ColumServiceImpl());
    }


    /// <summary>
    ///  更新的语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="whereColums">whereColums 必填  参考 新增和批改【id,name】</param>
    /// <param name="columService"></param>
    /// <returns></returns>
    public static string CreateUpdateSql<T>(T t, string[] whereColums, ColumService columService){List<T> list = new List<T>();
        list.Add(t);
        return CreateUpdateSql<T>(list, whereColums, columService);
    }

    /// <summary>
    /// 多条数据更新
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <param name="whereColums"></param>
    /// <returns></returns>
    public static string CreateUpdateSql<T>(List<T> list, string[] whereColums)
    {return CreateUpdateSql<T>(list, whereColums, new ColumServiceImpl());
    }

 
    /// <summary>
    /// 批改 批量批改的 sql  主语法
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <param name="whereColums"></param>
    /// <param name="columService"></param>
    /// <returns></returns>
    public static string CreateUpdateSql<T>(List<T> list, string[] whereColums, ColumService columService)
    {StringBuilder whereColumsTem = new StringBuilder("");
        List<string> whereColumTs = new List<string>();
        if (whereColums != null)
        {whereColumTs = new List<string>(whereColums);
        }

        var startTime = DateTime.Now;
        BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
        Type type = typeof(T);

        TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
        string tableName = tableAttr != null ? tableAttr.Value : "";

        string updateSql = "";

        string updateHead = "UPDATE" + tableName + "#{colums}";


        FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

        Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>();

        Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>();

        PropertyInfo propertyinfo = null;

        TableFieldAttr tableFieldAttr = null;
        IEnumerable<TableFieldAttr> enumerable = null;

     

        string setTemplet = "";
        for (int i = 0; i < fieldInfos.Length; i++)
        {tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags));
            enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

            foreach (TableFieldAttr attr in enumerable)
            {table.Add(fieldInfos[i].Name, attr);
                attr.JdbcType = fieldInfos[i].FieldType.ToString();
                // 不是主键 和是数据库字段
                if (attr.IsExist && !attr.PrimaryKey && attr.IsUpdate)
                {setTemplet += "" + attr.ColumName +"= #{"+ fieldInfos[i].Name +"} ,";
                }

                // 匹配
                if (whereColumTs.Contains(fieldInfos[i].Name))
                {whereColumsTem.Append("AND" + attr.ColumName + "= #{"+ fieldInfos[i].Name + "}");
                }

            }
        }
        setTemplet = "set" + setTemplet.Substring(0, setTemplet.Length - 1);



        if (whereColumTs.Count>0){setTemplet += "WHERE 1=1" + whereColumsTem.ToString();
        }

        //updateHead += setTemplet;

        string colum = "";
        object value = "";
        string currentValue = "";
        for (int k = 0; k < list.Count; k++)
        {
            colum = setTemplet;

            foreach (string key in table.Keys){propertyinfo = tablePropertys[key];

                tableFieldAttr = table[key];
                
                if (tableFieldAttr.Value != null)
                {
                    value = tableFieldAttr.Value;
                    // 默认值不做函数解决
                    currentValue = value.ToString();} else if (propertyinfo != null){value = tablePropertys[key].GetValue(list[k], null);
                    currentValue = (value == null) ? "''":"'" + value.ToString() + "'";
                    // 理论值才会做函数解决
                    currentValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue;
                }
                else{currentValue = "''";}
                colum = colum.Replace("#{" + key + "}", currentValue);

            }
            updateSql += updateHead.Replace("#{colums}", colum) + "\n;";
        }
        updateSql = updateSql.Substring(0, updateSql.Length - 1);

        var endTime = DateTime.Now;
        TimeSpan ts = endTime.Subtract(startTime);
        Console.WriteLine("update 语句生成耗时{0}ms.", ts.TotalMilliseconds);

        return updateSql;
    }

    /// <summary>
    ///  首字母大写
    /// </summary>
    /// <param name="str"></param>
    /// <returns></returns>
    public static string UpperCaseFirst(this string str)
    {if (string.IsNullOrWhiteSpace(str))
            return string.Empty;
        char[] s = str.ToCharArray();
        char c = s[0];
        if ('a' <= c && c <= 'z')
            c = (char)(c & ~0x20);
        s[0] = c;

        return new string(s);
    }
}

}

理论应用
针对实体:

办法调用示例:
List<OsZbSupplierProductInfo> record =new List<OsZbSupplierProductInfo>();

SQLiteSqlUtils.CreateInsertSql(record)

业务 2. 针对导入 excel
如法炮制 须要什么?须要列的下标 列内容非空验证 重复性验证 格局验证 长度验证

1. 自定义特效
using System;

namespace PublicLibrary.attr

{

// 导入的注解【特征值 / 注解】[AttributeUsage(AttributeTargets.Field, AllowMultiple = true)]
public class ColumnInfoAttr:Attribute{
    // 地位
    int index = -1;

    // 长度
    int length = 32;

    // 是否为空
    bool nullable = false;

    // 工夫格局
    string dateFormat = "";

    // 正则表单式 验证数据格式
    string pattern = "";

    public ColumnInfoAttr(int index, int length){
        this.index = index;
        this.length = length;
    }

    public ColumnInfoAttr(int index, int length, bool nullable)
    {
        this.index = index;
        this.length = length;
        this.nullable = nullable;
    }

    public ColumnInfoAttr(int index, int length, bool nullable,string dateFormat)
    {
        this.index = index;
        this.length = length;
        this.nullable = nullable;
        this.dateFormat = dateFormat;
    }

    public ColumnInfoAttr(int index, int length, bool nullable, string dateFormat, string pattern)
    {
        this.index = index;
        this.length = length;
        this.nullable = nullable;
        this.dateFormat = dateFormat;
        this.Pattern = pattern;
    }

    public int Index {get => index; set => index = value;}
    public int Length {get => length; set => length = value;}
    public bool Nullable {get => nullable; set => nullable = value;}
    public string DateFormat {get => dateFormat; set => dateFormat = value;}
    public string Pattern {get => pattern; set => pattern = value;}
}

}
2. 公共办法【注此局部没有校验重复性】
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using ess_zbfz_main.dto;
using ExcelDataReader;
using PublicLibrary;
using PublicLibrary.attr;

namespace ess_zbfz_main.util
{

// 导入工具类
public class ExcelUtil
{//, Dictionary<string, string> dbListDict, string[] keys
    public static MessageInfo<T> ReadExcel<T>(int firstIndex){MessageInfo<T> messageInfo = new MessageInfo<T>();
        FileStream fileStream =null;
        IExcelDataReader reader = null;
        DataSet result = null;
      
        try
        {
            // 小于 0 默认是第三行开始读取
            firstIndex = firstIndex < 0 ? 2 : firstIndex;

            OpenFileDialog fileImportDialog = new OpenFileDialog();
            fileImportDialog.Filter = "导入文件包(*.xlsx)|*.xlsx";// 扩展名
            fileImportDialog.FileName = "";
            if (fileImportDialog.ShowDialog() == DialogResult.OK)
            {
                string saveTempPath = @System.AppDomain.CurrentDomain.BaseDirectory + "data\\receiveData\\";// 长期寄存的门路
                /* if (!File.Exists(saveTempPath))
                 {File.Create(saveTempPath);
                 }*/
                string saveName = fileImportDialog.FileName.Substring(fileImportDialog.FileName.LastIndexOf("\\") + 1);
                string dataPath = saveTempPath + saveName;// 文件地址
                File.Copy(fileImportDialog.FileNames[0], dataPath, true);

                // 解析解决  start
                //stream = 
                fileStream = File.Open(fileImportDialog.FileName, FileMode.Open, FileAccess.Read);
                reader = ExcelReaderFactory.CreateReader(fileStream);

                object[] curObject = new object[10];

                result = reader.AsDataSet(new ExcelDataSetConfiguration()
                {ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = true,
                        ReadHeaderRow = (rowReader) =>
                        {
                            // 从第几行之后开始读取
                            int index = firstIndex;
                            if (index != rowReader.Depth)
                            {rowReader.Read();
                            }
                        }
                    }
                });
                DataTableCollection tableCollection = result.Tables;

                // 初步解决的数据
                messageInfo = GetmessageInfo<T>(tableCollection[0], firstIndex);

                //reader.Close();
                return messageInfo;

            }
        }
        catch(Exception ex) {Console.WriteLine(ex.Message);
            MessageBox.Show("导入文件错误信息:" + ex.Message);
        }
        finally{
            // 须要开释资源
            if (reader != null)
            {reader.Close();
            }   
            if (fileStream != null)
            {fileStream.Close();
            }
            if (result != null)
            {result.Clear();
            }
        }
        
        return messageInfo;
    }

    public static MessageInfo<T> GetmessageInfo<T>(DataTable dt, int firstIndex)
    {MessageInfo<T> messageInfo = new MessageInfo<T>();

        bool existError = false;

        int totalCount = dt.Rows.Count;

        int successCount = 0;

        // 错误信息
        StringBuilder errorSb = new StringBuilder();

        BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
        var list = new List<T>();
        Type type = typeof(T);

        ColumnInfoAttr columnInfoAttr = null;

        FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

        Dictionary<int, PropertyInfo> dictionAry = new Dictionary<int, PropertyInfo>();

        Dictionary<int, ColumnInfoAttr> columnDic = new Dictionary<int, ColumnInfoAttr>();

        // 行属性
        //PropertyInfo propertyInfo = type.GetProperty("CurExcelIndex");

        for (int p = 0; p < fieldInfos.Length; p++)
        {columnInfoAttr = fieldInfos[p].GetCustomAttribute<ColumnInfoAttr>();
            if (columnInfoAttr != null)
            {dictionAry.Add(columnInfoAttr.Index, type.GetProperty(SQLiteSqlUtils.UpperCaseFirst(fieldInfos[p].Name)));
                columnDic.Add(columnInfoAttr.Index, columnInfoAttr);
            }
        }

        PropertyInfo currentProp = null;

        // 实体
        T s;
        bool flag = false;

        int currentRow = firstIndex;

        foreach (DataRow item in dt.Rows){
            currentRow++;

            s = Activator.CreateInstance<T>();

            for (int i = 0; i < dt.Columns.Count; i++)
            {if (dictionAry.ContainsKey(i)){currentProp = dictionAry[i];
                    columnInfoAttr = columnDic[i];
                    object v = null;

                    if (currentProp.PropertyType.ToString().Contains("System.Nullable"))
                    {v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(currentProp.PropertyType));
                    }
                    else{v = Convert.ChangeType(item[i], currentProp.PropertyType);
                    }

                    // 不能够为空 ==> 非空验证
                    if (!columnInfoAttr.Nullable && (v == null || v.ToString().Trim().Length<=0))
                    {
                        //successCount++;
                        existError = true;
                        errorSb.Append("第" + currentRow + "行," + dt.Columns[i].ColumnName + "数据不得为空 \n");
                        flag = true;
                        //break;
                    }

                    // 不为空 超过了最大长度 ==> 长度验证
                    if (v != null && columnInfoAttr.Length < v.ToString().Length)
                    {
                        //successCount++;
                        existError = true;
                        errorSb.Append("第" + currentRow + "行," + dt.Columns[i].ColumnName + "数据长度不得超过" + columnInfoAttr.Length + "个字符 \n");
                        flag = true;
                        //break;
                    }

                    // 正则验证局部 ==> 数据格式验证
                    if (v != null && columnInfoAttr.Pattern!=null && columnInfoAttr.Pattern != "" && v.ToString().Trim().Length > 0){
                        // 不匹配正则
                        if(!Regex.IsMatch(v.ToString().Trim(), columnInfoAttr.Pattern))
                        {
                            existError = true;
                            errorSb.Append("第" + currentRow + "行," + dt.Columns[i].ColumnName + "数据格式不正确");
                            flag = true;
                        }
                    }

                    // 是否校验不合格
                    if (flag) {
                        flag = false;
                        successCount++;
                        break;
                    }
                    currentProp.SetValue(s, v, null);

                }

            }

            currentProp = dictionAry[-10];
            currentProp.SetValue(s, currentRow, null);// 设置 excel 的行列
            currentProp = null;

            list.Add(s);
        }
        // 返回的信息
        messageInfo.Record = list;
        messageInfo.ErrorInfo = errorSb.ToString();
        messageInfo.TotalCount = totalCount;
        messageInfo.ErrorCount = totalCount - successCount;
        messageInfo.SuccessCount = successCount;
        messageInfo.ExistError = existError;

        return messageInfo;
    }

}

}
3. 具体的应用

实体:

办法调用:

int firstIndex = 2;

MessageInfo<OsZbSupplierProductInfo> messageInfo = ExcelUtil.ReadExcel<OsZbSupplierProductInfo>(firstIndex);

本文来源于:宋文超 super,专属平台有 csdn、思否(SegmentFault)、简书、开源中国(oschina),转载请注明出处。

退出移动版