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    }

以上就是C# EXCEL 匯入匯出類（OLEDB的方式）的範例程式碼詳情的內容，更多相關內容請關注topic.alibabacloud.com（www.php.cn）！

