Excel 数据导入到 sql中

  哥哥我弄了一天啊  终于弄出来了  

 要注意几点：

  1：在添参数时  要注意不要给参数的值设置小了 一定报错  我索性 就都给了一个max  但性能有点说不过去

    代码如下：

 

  using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;

 

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        MyCha();
        Inser();
      
    }

    string sql = "SELECT * FROM [Sheet1$]";
    DataSet ds = new DataSet();
 
    OleDbDataAdapter da = new OleDbDataAdapter();
   
    public void  MyCha()
    {
        string xlsPath = System.Web.HttpContext.Current.Server.MapPath("~/app_data/2010330.xls");
        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" + "data source=" + xlsPath;
        da = new OleDbDataAdapter(sql, connStr);
        da.Fill(ds);
       
     
       
    }

    public void Inser()
    {
       
        int num = 0;
        string conn = "server=.;database=ComData;uid=sa;pwd=01120817lhh";
        SqlConnection sqlConn = new SqlConnection(conn);
        try
        {
            for (int i = 0; i < ds.Tables.Count; i++)
            {

                DataSet dss = new DataSet();
                num += ds.Tables[i].Rows.Count;
                for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                {
                   
                    string str = "INSERT INTO Company_Information (公司序号,公司简称,公司全程,公司英文,公司省份,公司城市,公司详细地址,公司电话,公司邮编,公司网址,公司E_mail,公司简介,公司案例,公司系统资质等级,公司证书编号,批准备案日期,首次获奖日期,发证机关,资质) VALUES (@number,@Abbreviation,@Fname,@Ename,@Province,@City,@Address,@Phone,@ZipCode,@Website,@Email,@Introduction,@Case,@Grade,@CNumber,@ADate,@Fdata,@AOrgan,@Qualification)";
                    //SqlDataAdapter comm = new SqlDataAdapter(str, sqlConn);
                    SqlCommand comm = new SqlCommand(str, sqlConn);
                    comm.Parameters.Add("@number",SqlDbType.NVarChar,4000);
                    comm.Parameters["@number"].Value = ds.Tables[i].Rows[j]["序号"].ToString().Trim();
                    comm.Parameters.Add("@Abbreviation", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Abbreviation"].Value = ds.Tables[i].Rows[j]["简称"].ToString().Trim();
                    comm.Parameters.Add("@Fname", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Fname"].Value = ds.Tables[i].Rows[j]["全称"].ToString().Trim();
                    comm.Parameters.Add("@Ename", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Ename"].Value = ds.Tables[i].Rows[j]["英文"].ToString().Trim();
                    comm.Parameters.Add("@Province", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Province"].Value = ds.Tables[i].Rows[j]["简称"].ToString().Trim();
                    comm.Parameters.Add("@City", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@City"].Value = ds.Tables[i].Rows[j]["市"].ToString().Trim();
                    comm.Parameters.Add("@Address", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Address"].Value = ds.Tables[i].Rows[j]["详细地址"].ToString().Trim();
                    comm.Parameters.Add("@Phone", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Phone"].Value = ds.Tables[i].Rows[j]["电话"].ToString().Trim();
                    comm.Parameters.Add("@ZipCode", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@ZipCode"].Value = ds.Tables[i].Rows[j]["邮编"].ToString().Trim();
                    comm.Parameters.Add("@Website", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Website"].Value = ds.Tables[i].Rows[j]["网址"].ToString().Trim();
                    comm.Parameters.Add("@Email", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Email"].Value = ds.Tables[i].Rows[j]["e-mail"].ToString().Trim();
                    comm.Parameters.Add("@Introduction",SqlDbType.NVarChar,4000);
                    comm.Parameters["@Introduction"].Value = ds.Tables[i].Rows[j]["公司简介"].ToString().Trim();
                    comm.Parameters.Add("@Case",SqlDbType.NVarChar,4000);
                    comm.Parameters["@Case"].Value = ds.Tables[i].Rows[j]["案例"].ToString().Trim();
                    comm.Parameters.Add("@Grade", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Grade"].Value = ds.Tables[i].Rows[j]["系统集成资质等级"].ToString().Trim();
                    comm.Parameters.Add("@CNumber", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@CNumber"].Value = ds.Tables[i].Rows[j]["证书编号"].ToString().Trim();
                    comm.Parameters.Add("@ADate", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@ADate"].Value = ds.Tables[i].Rows[j]["批准(备案)日期"].ToString().Trim();
                    comm.Parameters.Add("@Fdata", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@Fdata"].Value = ds.Tables[i].Rows[j]["首次获证日期"].ToString().Trim();
                    comm.Parameters.Add("@AOrgan", SqlDbType.NVarChar, 4000);
                    comm.Parameters["@AOrgan"].Value = ds.Tables[i].Rows[j]["发证机关"].ToString().Trim();
                    comm.Parameters.Add("@Qualification",SqlDbType.NVarChar,4000);
                    comm.Parameters["@Qualification"].Value = ds.Tables[i].Rows[j]["资质"].ToString().Trim();
                   
                  

                    //SqlCommand sqComm = new SqlCommand(str, sqlConn);

                    if (sqlConn.State == ConnectionState.Closed)
                    {
                        sqlConn.Open();
                    }
                    comm.ExecuteNonQuery();

                    //sqComm.ExecuteNonQuery();

                }

            }
        }
        catch (Exception ep)
        {
            Response.Write(ep);
        }
        finally
        {
            sqlConn.Close();
        }
      
    }

 

  

 

}