数据导入?

Question

C#,如何将读取一个Excel的表格,并且把数据导入sql表格中?

Answer 1

        #region 读取EXCEL
        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        /// <returns>返回一个数据集</returns>
        /// http://dev.csdn.net/article/72/72658.shtm
        public static DataSet ExcelToDS(string Path) {
            if (!string.IsNullOrEmpty(Path)) {
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                string strConn = @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=YES'";
                //驱动下载地址:http://www.microsoft.com/downloads/details.aspx?familyid=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=zh-cn
                //by zhangbk 2008-7-16
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                string _table_name = "sheet1$";
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt != null
                    && dt.Rows.Count > 0
                    && dt.Columns.Count > 0) {
                    _table_name = dt.Rows[0][2].ToString();
                }                strExcel = string.Format("select * from [{0}]", _table_name);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds);
                return ds;
            }
            return null;
        }
        #endregion

Answer 2

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
        con.Open();
        string sql = @" INSERT   INTO   Table1   SELECT   *   FROM   OpenRowSet    ('microsoft.jet.oledb.4.0','Excel   8.0;   HDR=NO;IMEX=1;database=Products.xls;','select  *   from   [sheet1$]')   ";
        SqlCommand cmd = new SqlCommand(sql, con);
        int line= cmd.ExecuteNonQuery();
        if(line>0)
        {
            Response.Write("<script>alert('成功')</script>");
        }