asp.net操作Excel助手

    public partial class ExcelHelper : IDisposable
    {
        #region Fileds
        private string _excelObject = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR={4};IMEX={5}\"";
        private string _filepath = string.Empty;
        private string _hdr = "No";
        private string _imex = "1";
        private OleDbConnection _con = null;
        #endregion

        #region Ctor
        public ExcelHelper(string filePath)
        {
            this._filepath = filePath;
        }
        #endregion

        #region Properties
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                string result = string.Empty;
                if (String.IsNullOrEmpty(this._filepath))
                    return result;

                //检查文件格式
                FileInfo fi = new FileInfo(this._filepath);
                if (fi.Extension.Equals(".xls"))
                {
                    result = string.Format(this._excelObject, "Jet", "4.0", this._filepath, "8.0", this._hdr, this._imex);
                }
                else if (fi.Extension.Equals(".xlsx"))
                {
                    result = string.Format(this._excelObject, "Ace", "12.0", this._filepath, "12.0", this._hdr, this._imex);
                }
                return result;
            }
        }

        /// <summary>
        /// 获取连接
        /// </summary>
        public OleDbConnection Connection
        {
            get
            {
                if (_con == null)
                {
                    this._con = new OleDbConnection();
                    this._con.ConnectionString = this.ConnectionString;
                }
                return this._con;
            }
        }

        /// <summary>
        /// HDR
        /// </summary>
        public string Hdr
        {
            get { return this._hdr; }
            set { this._hdr = value; }
        }

        /// <summary>
        /// IMEX
        /// </summary>
        public string Imex
        {
            get { return this._imex; }
            set { this._imex = value; }
        }
        #endregion

        #region Methods
        /// <summary>
        /// Gets a schema
        /// </summary>
        /// <returns>Schema</returns>
        public DataTable GetSchema()
        {
            DataTable dtSchema = null;
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            dtSchema = this.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            return dtSchema;
        }

        private string GetTableName()
        {
            string tableName = string.Empty;
            DataTable dt = GetSchema();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tableName += dt.Rows[i][2].ToString().Trim();
            }
            return tableName.Substring(0, tableName.Length - 1);
        }

        public DataTable ReadTable()
        {
            return this.ReadTable(GetTableName(), ExcelHelperReadTableMode.ReadFromWorkSheet);
        }

        /// <summary>
        /// Read all table rows
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <returns>Table</returns>
        public DataTable ReadTable(string tableName)
        {
            return this.ReadTable(tableName, ExcelHelperReadTableMode.ReadFromWorkSheet);
        }

        /// <summary>
        /// Read table
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="mode">Read mode</param>
        /// <returns>Table</returns>
        public DataTable ReadTable(string tableName, ExcelHelperReadTableMode mode)
        {
            return this.ReadTable(tableName, mode, "");
        }

        /// <summary>
        /// Read table
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="mode">Read mode</param>
        /// <param name="criteria">Criteria</param>
        /// <returns>Table</returns>
        public DataTable ReadTable(string tableName, ExcelHelperReadTableMode mode, string criteria)
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();
            }
            string cmdText = "Select * From [{0}]";
            if (!string.IsNullOrEmpty(criteria))
            {
                cmdText += " Where " + criteria;
            }
            string tableNameSuffix = string.Empty;
            if (mode == ExcelHelperReadTableMode.ReadFromWorkSheet)
                tableNameSuffix = "$";

            OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName + tableNameSuffix));
            cmd.Connection = this.Connection;
            OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);

            DataSet ds = new DataSet();

            adpt.Fill(ds, tableName);

            if (ds.Tables.Count >= 1)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// Drop table
        /// </summary>
        /// <param name="tableName">Table Name</param>
        public void DropTable(string tableName)
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();

            }
            string cmdText = "Drop Table [{0}]";
            using (OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName), this.Connection))
            {
                cmd.ExecuteNonQuery();

            }
            this.Connection.Close();
        }

        /// <summary>
        /// Write table
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="tableDefinition">Table Definition</param>
        public void WriteTable(string tableName, Dictionary<string, string> tableDefinition)
        {
            using (OleDbCommand cmd = new OleDbCommand(this.GenerateCreateTable(tableName, tableDefinition), this.Connection))
            {
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Add new row
        /// </summary>
        /// <param name="dr">Data Row</param>
        public void AddNewRow(DataRow dr)
        {
            string command = this.GenerateInsertStatement(dr);
            ExecuteCommand(command);
        }

        /// <summary>
        /// Execute new command
        /// </summary>
        /// <param name="command">Command</param>
        public void ExecuteCommand(string command)
        {
            using (OleDbCommand cmd = new OleDbCommand(command, this.Connection))
            {
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Generates create table script
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="tableDefinition">Table Definition</param>
        /// <returns>Create table script</returns>
        private string GenerateCreateTable(string tableName, Dictionary<string, string> tableDefinition)
        {

            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("CREATE TABLE [{0}](", tableName);
            firstcol = true;
            foreach (KeyValuePair<string, string> keyvalue in tableDefinition)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;
                sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value);
            }

            sb.Append(")");
            return sb.ToString();
        }

        /// <summary>
        /// Generates insert statement script
        /// </summary>
        /// <param name="dr">Data row</param>
        /// <returns>Insert statement script</returns>
        private string GenerateInsertStatement(DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName);


            foreach (DataColumn dc in dr.Table.Columns)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;

                sb.Append(dc.Caption);
            }

            sb.Append(") VALUES(");
            firstcol = true;
            for (int i = 0; i <= dr.Table.Columns.Count - 1; i++)
            {
                if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int)))
                {
                    sb.Append("'");
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                    sb.Append("'");
                }
                else
                {
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                }
                if (i != dr.Table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }

        /// <summary>
        /// Dispose [实现IDispose接口]
        /// </summary>
        public void Dispose()
        {
            if (this._con != null && this._con.State == ConnectionState.Open)
                this._con.Close();
            if (this._con != null)
                this._con.Dispose();
            this._con = null;
            this._filepath = string.Empty;
        }
        #endregion
    }

转载于:https://www.cnblogs.com/cancer_xu/archive/2010/10/12/1848966.html