.Net com组件操作excel(不建议采用Com组件操作excel)

添加"Microsoft Office 12.0 Object Library" com组件
  1 using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

namespace Test
{
    public class DataAccess_Excel
    {
        private string GetConnectionString(string path)
        {
            string extension = Path.GetExtension(path).ToUpper();
            if (".XLS".Equals(extension))
                return GetXLSConnStr(path);
            else if (".XLSX".Equals(extension))
                return GetXLSXConnStr(path);
            else
                return null;
        }

        /// <summary>
        /// "HDR=Yes:"用于指示将Excel表格中的第一行作为标题，此时在查询语句中可以将标题作为数据表的字段名使用"
        /// "HDR= No:"则表示将Excel表格中的所有行都作为数据内容而不包含标题"
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private string GetXLSConnStr(string path)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
            sb.Append(path);
            sb.Append(";Extended Properties='Excel 8.0;HDR=YES;'");
            return sb.ToString();
        }

        private string GetXLSXConnStr(string path)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Provider=Microsoft.ACE.OLEDB.12.0;");
            sb.Append("Data Source=" + path + ";");
            sb.Append("Properties='Excel 12.0;HDR=YES'");
            return sb.ToString();
        }

        /// <summary>
        /// 用来获取数据连接
        /// </summary>
        /// <param name="path">excel文件的路径</param>
        /// <returns>返回一个OleDbConnection对象</returns>
        private OleDbConnection GetConnection(string path)
        {
            string conString = GetConnectionString(path);
            OleDbConnection oleConnection = new OleDbConnection(conString);
            try
            {
                oleConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return oleConnection;
        }

        /// <summary>
        /// 从EXCEL中获取第一个SHEET的名称
        /// 注意Excel wooksheet的名字必须以"$"结尾并且包含在方括号中。列名如有需要也应当包含在方括号中（如列名中包含有空格其它特殊字符等）。
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private string GetSheetName(string path)
        {
            try
            {
                string tableName = string.Empty;
                DataTable dt = null;
                using (OleDbConnection oleConnection = GetConnection(path))
                {
                    dt = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
                if (dt.Rows.Count > 0)
                {
                    tableName = dt.Rows[0][2].ToString().Trim();
                    if (!tableName.Contains("$"))
                    {
                        tableName += "$";
                    }
                    tableName = "[" + tableName + "]";
                }
                return tableName;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <returns>sql语句</returns>
        public int ExecuteNonQuery(string path, string sql)
        {
            int i = -1;
            try
            {
                using (OleDbConnection oleConnection = GetConnection(path))
                {
                    OleDbCommand oleCommand = oleConnection.CreateCommand();
                    oleCommand.CommandText = sql;
                    oleCommand.CommandType = CommandType.Text;
                    i = oleCommand.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return i;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <returns>sql语句</returns>
        public DataTable ExecuteQuery(string path, string sql)
        {
            DataTable dt = null;
            try
            {
                dt = new DataTable();
                dt.TableName = GetSheetName(path);
                using (OleDbConnection oleConnection = GetConnection(path))
                {
                    OleDbCommand oleCommand = oleConnection.CreateCommand();
                    oleCommand.CommandText = sql;
                    oleCommand.CommandType = CommandType.Text;
                    OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
                    oleAdapter.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 获取excel文件中的数据，默认从sheet1中获取数据，如果sheet1中没有数据，则从nicelabel中获取数据
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable GetDataFromExcelFile(string path)
        {
            string sheetName = GetSheetName(path);
            string sql = string.Format(@"Select * From {0}", sheetName);
            return ExecuteQuery(path, sql);
        }

        /// <summary>
        /// 创建表并插入数据
        /// </summary>
        /// <param name="dgv">DataGridView对象</param>
        /// <param name="path">excel 文件路径</param>
        /// <param name="row">表示哪一行</param>
        public void CreateTableAndInsertValue(DataTable dt, string path, int? row = null)
        {
            CreateTable(dt, path);
            InsertData(dt, path, row);
        }

        /// <summary>
        /// 创建表单
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="path"></param>
        public void CreateTable(DataTable dt, string path)
        {
            StringBuilder createSql = new StringBuilder();
            createSql.Append(string.Format("Create Table {0}",dt.TableName));
            createSql.Append(" ( ");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                createSql.Append("[");
                createSql.Append(dt.Columns[i].ColumnName);
                createSql.Append("]");
                createSql.Append(" memo");
                if (i != dt.Columns.Count - 1)
                    createSql.Append(",");
            }
            createSql.Append(" )");
            ExecuteNonQuery(path, createSql.ToString());
        }

        /// <summary>
        /// 插入表数据
        /// </summary>
        /// <param name="dgv">表单</param>
        /// <param name="path">文件路径</param>
        /// <param name="row">如果ROW为NULL,则插入所有数据，否则只插入特定行的数据</param>
        public void InsertData(DataTable dt, string path, int? row = null)
        {
            int rowFrom = 0;
            int rowEnd = 0;
            if (row == null)
            {
                rowFrom = 0;
                rowEnd = dt.Rows.Count;
            }
            else
            {
                rowFrom = row.Value;
                rowEnd = rowFrom + 1;
            }
            for (int rowIndex = rowFrom; rowIndex < rowEnd; rowIndex++)
            {
                string insertSql = GetInsertSql(dt.Rows[rowIndex]);
                ExecuteNonQuery(path, insertSql);
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        public int InsertData(DataRow dr, string path)
        {
            string insertSql = GetInsertSql(dr);
            return ExecuteNonQuery(path, insertSql);
        }

        private string GetInsertSql(DataRow dr) 
        {
            StringBuilder insertSql = new StringBuilder();
            insertSql.Append(string.Format("Insert Into {0} Values ",dr.Table.TableName));
            insertSql.Append(" ( ");
            int columnCount = dr.Table.Columns.Count;
            for (int j = 0; j < columnCount; j++)
            {
                if (dr.Field<string>(j) != null)
                {
                    insertSql.Append("'");
                    insertSql.Append(dr.Field<string>(j).Replace("'", "''"));
                    insertSql.Append("'");
                }
                else
                {
                    insertSql.Append("''");
                }
                if (j != columnCount - 1)
                    insertSql.Append(",");
            }
            insertSql.Append(")");
            return insertSql.ToString();
        }

            //使用OLEDB无法直接删除excel中的数据，需要先删除原数据文件，然后重新CreateTable
    }
}