一个简单的Excel导入数据库的方法

站长苏飞

先来看看OleDbHelper

[C#] 纯文本查看 复制代码

    /// <summary>
    /// oledb类型数据库操作类
    /// </summary>
    public sealed class OleDbHelper
    {
        /// <summary>  
        /// 执行查询语句，返回OleDbDataReader  
        /// </summary>  
        /// <param name="strSQL">查询语句"select * from [" + sheetName + "]"</param>  
        /// <returns>OleDbDataReader</returns>  
        public static OleDbDataReader ExecuteReader(string strSQL, string connectionString)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(strSQL, connection);
            try
            {
                connection.Open();
                OleDbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (OleDbException e)
            {
                throw new Exception(e.Message);
            }
        }
    }

使用继承类

[C#] 纯文本查看 复制代码

    /// <summary>
    /// excel操作类
    /// </summary>
    public abstract class ExcelBLL<T>
    {
        /// <summary>
        /// 配置要链接数据库的字符串，必须在子类中使用override重写
        /// </summary>
        protected abstract string ConnName
        {
            get;
        }

        /// <summary>
        ///根据Sql语句查询
        /// </summary>
        /// <param name="sql">t-sql select * from [" + sheetName + "]</param>
        /// <returns>返回集合</returns>
        public List<T> FindList(string path, string sql)
        {
            using (var reader = OleDbHelper.ExecuteReader(sql, string.Format(ConnName, path)))
            {
                List<T> list = new List<T>();
                var fields = GetReaderFieldNames(reader);
                while (reader.Read())
                {
                    list.Add(FillModelFromReader(reader, fields));
                }
                return list;
            }
        }
        private static string[] GetReaderFieldNames(OleDbDataReader reader)
        {
            var fields = new string[reader.FieldCount];
            for (var i = 0; i < reader.FieldCount; i++)
            {
                fields[i] = reader.GetName(i);
            }
            return fields;
        }
        public static bool HasFields(string field, params string[] fields)
        {
            if (fields == null || fields.Length == 0)
                return true;
            return fields.Contains(field);
        }
        protected abstract T FillModelFromReader(OleDbDataReader reader, params string[] fields);
    }

这样取到的就是一个完整的list<obj对象>

比如一个产品表导入方法如下

[C#] 纯文本查看 复制代码

  /// <summary>
    ///excel产品类
    /// </summary>
    public class Site_ProductExcelBLL : ExcelBLL<Site_Product>
    {
        /// <summary> 
        /// 数据库链接字符串
        /// </summary>
        protected override string ConnName
        {
            /// <summary>
            ///"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";  
            ///office2007之前 仅支持.xls  
            ///Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'
            ///支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
            ///</summary>
            get { return "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; }
        }
        /// <summary>
        /// 组织对象
        /// </summary>
        /// <param name="reader">reader</param>
        /// <param name="fields">fields</param>
        /// <returns>object</returns>
        protected override Site_Product FillModelFromReader(OleDbDataReader reader, params string[] fields)
        {
            var info = new Site_Product();

            if (HasFields("productid", fields)) info.productid = (int)reader["productid"];
            if (HasFields("lanmuid", fields)) info.lanmuid = (int)reader["lanmuid"];
            if (HasFields("keywords", fields)) info.keywords = reader["关键字"].ToString();
            if (HasFields("description", fields)) info.description = reader["描述"].ToString();
            if (HasFields("title", fields)) info.title = reader["产品名"].ToString();
            if (HasFields("link", fields)) info.link = reader["link"].ToString();
            if (HasFields("imgurl", fields)) info.imgurl = reader["imgurl"].ToString();
            if (HasFields("text", fields)) info.text = reader["text"].ToString();
            if (HasFields("context", fields)) info.context = reader["内容"].ToString();
            if (HasFields("sort", fields)) info.sort = (int)reader["sort"];
            if (HasFields("addtime", fields)) info.addtime = (DateTime)reader["addtime"];
            if (HasFields("reads", fields)) info.reads = (int)reader["reads"];

            return info;
        }
    }