C# EXCEL 导入导出类（OLEDB的方式）

 

public static class ExcelHelper    {        #region 导入        /// <summary>        /// 导入EXCEL（默认的sheet）        /// </summary>        /// <param name="fileName"></param>        /// <returns></returns>        public static System.Data.DataTable ImpExcelDt(string fileName)         {            return ImpExcelDt(fileName, "Sheet1");        }        /// <summary>        /// excel 导入        /// </summary>        /// <param name="fileName"></param>        /// <param name="sheetName"></param>        /// <returns></returns>        public static System.Data.DataTable ImpExcelDt(string fileName, string sheetName)        {            try            {                if (!File.Exists(fileName))                 {                    return null;                }                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";                OleDbConnection myConn = new OleDbConnection(strCon);                string strCom = " SELECT * FROM [" + sheetName + "$] ";                myConn.Open();                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);                DataSet myDataSet = new DataSet();                myCommand.Fill(myDataSet, "[" + sheetName + "$]");                myConn.Close();                System.Data.DataTable dt = myDataSet.Tables[0];                return dt;            }            catch(Exception ex)            {                throw ex;            }        }        #endregion        #region 导出到EXCEL        /// <summary>        /// 将数据导出到指定的Excel文件中        /// </summary>        /// <param name="listView">System.Windows.Forms.ListView,指定要导出的数据源</param>        /// <param name="destFileName">指定目标文件路径</param>        /// <param name="tableName">要导出到的表名称</param>        /// <param name="overWrite">指定是否覆盖已存在的表</param>        /// <returns>导出的记录的行数</returns>        public static int ExportToExcel(System.Data.DataTable dt, string destFileName, string tableName)        {            if (File.Exists(destFileName))             {                File.Delete(destFileName);            }            //得到字段名            string szFields = "";            string szValues = "";            for (int i = 0; i < dt.Columns.Count; i++)            {                szFields += "[" + dt.Columns[i] + "],";            }            szFields = szFields.TrimEnd(',');            //定义数据连接            OleDbConnection connection = new OleDbConnection();            connection.ConnectionString = GetConnectionString(destFileName);            OleDbCommand command = new OleDbCommand();            command.Connection = connection;            command.CommandType = CommandType.Text;            //打开数据库连接            try            {                connection.Open();            }            catch            {                throw new Exception("目标文件路径错误。");            }            //创建数据库表            try            {                command.CommandText = GetCreateTableSql("[" + tableName + "]", szFields.Split(','));                command.ExecuteNonQuery();            }            catch (Exception ex)            {                //如果允许覆盖则删除已有数据                throw ex;            }            try            {                //循环处理数据------------------------------------------                int recordCount = 0;                for (int i = 0; i < dt.Rows.Count; i++)                {                    szValues = "";                    for (int j = 0; j < dt.Columns.Count; j++)                    {                            szValues += "'" + dt.Rows[i][j] + "',";                                           }                    szValues = szValues.TrimEnd(',');                    //组合成SQL语句并执行                    string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";                    command.CommandText = szSql;                    recordCount += command.ExecuteNonQuery();                }                connection.Close();                return recordCount;            }            catch (Exception ex)            {                throw ex;            }        }        //得到连接字符串        private static String GetConnectionString(string fullPath)        {            string szConnection;            szConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fullPath;            return szConnection;        }        //得到创建表的SQL语句        private static string GetCreateTableSql(string tableName, string[] fields)        {            string szSql = "CREATE TABLE " + tableName + "(";            for (int i = 0; i < fields.Length; i++)            {                szSql += fields[i] + " VARCHAR(200),";            }            szSql = szSql.TrimEnd(',') + ")";            return szSql;        }        #endregion    }