常用类-ExcelHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Utils
{
    /// <summary>
    /// Excel 辅助类
    /// </summary>
    public class ExcelHelper
    {
        public const string Excel2003 = ".xls";

        public const string Excel2007 = ".xlsx";

        public enum ConnectionMode
        {
            Read,
            Write
        }

        public static OleDbConnection GetConnection(string file, ConnectionMode mode)
        {
            if (File.Exists(file))
            {
                var extension = Path.GetExtension(file);
                var connectionString = "";
                if (ExcelHelper.Excel2003.Equals(extension, StringComparison.CurrentCultureIgnoreCase))
                {
                    connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=yes;{1}\";",
                        file,
                        (mode == ConnectionMode.Read ? "IMEX=1" : "IMEX=2")
                        );
                }
                else if (ExcelHelper.Excel2007.Equals(extension, StringComparison.CurrentCultureIgnoreCase))
                {
                    connectionString = String.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=\"{0}\";Extended Properties=\"Excel 12.0;HDR=yes;{1}\";",
                        file, (mode == ConnectionMode.Read ? "IMEX=1" : ""));
                }
                return new OleDbConnection(connectionString);
            }
            else
            {
                throw new FileNotFoundException();
            }

        }


        /// <summary>
        ///ConnectionMode is read
        /// </summary>
        /// <param name="file"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDatatable(string file, string commandText, params OleDbParameter[] cmdParameters)
        {
            return ExecuteDatatable(file, commandText, ConnectionMode.Read, cmdParameters);
        }


        public static DataTable ExecuteDatatable(string file, string commandText, ConnectionMode mode, params OleDbParameter[] cmdParameters)
        {
            using (OleDbConnection conn = GetConnection(file, mode))
            using (OleDbCommand cmd = new OleDbCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = commandText;
                if (cmdParameters != null && cmdParameters.Length > 0)
                {
                    foreach (OleDbParameter param in cmdParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }


        /// <summary>
        /// ConnectionMode is write
        /// </summary>
        /// <param name="file"></param>
        /// <param name="commandText"></param>
        /// <param name="cmdParameters"></param>
        public static void ExecuteNonQuery(string file, string commandText, params OleDbParameter[] cmdParameters)
        {
            ExecuteNonQuery(file, commandText, ConnectionMode.Write, cmdParameters);
        }

        public static void ExecuteNonQuery(string file, string commandText, ConnectionMode mode, params OleDbParameter[] cmdParameters)
        {
            using (OleDbConnection conn = GetConnection(file, mode))
            using (OleDbCommand cmd = new OleDbCommand())
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = commandText;
                if (cmdParameters != null && cmdParameters.Length > 0)
                {
                    foreach (OleDbParameter param in cmdParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
}