如何在C#中实现将Excel表的数据直接导入到SQL server中?

Question

如何在C#中实现将Excel表的数据直接导入到SQL server中?
能不能给一段源代码我看看?
谢谢!

Answer 1

这里有个范例，或许有用：遍历listbox中所指所有excel文件，逐个插入到sql server 对应的table 中。private void btnUpload_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;            string filepath = "";            progressBar1.Visible = true;
            progressBar1.Minimum = 1;
            progressBar1.Maximum = list.Items.Count;
            progressBar1.Value = 1;
            progressBar1.Step = 1;
             for (int i = 0; i < list.Items.Count;i++)
             {
                 filepath = list.Items[i].ToString();      
                 string FileName=GetFileName(filepath);    
                 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"   //excel 实际是就是一个数据源
               + "Data Source=" + filepath + ";"
               + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";                 try
                 {
                     oleConn = new OleDbConnection(strConn);
                     string strSQL = "select * from [Sheet1$]";     //对excel实施sql 作业
                     oleConn.Open();
                     OleDbDataAdapter oleAda = new OleDbDataAdapter(strSQL, oleConn);
                     DataSet ds = new DataSet();
                     oleAda.Fill(ds);                     UploadData(ds,FileName);              //参考下面方法                     progressBar1.PerformStep();
                     
                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show(ex.Message, "information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    
                 }
                 finally
                 {
                     oleConn.Close();
                 }
                     
             }
             Cursor.Current = Cursors.Default;             MessageBox.Show("information", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
//-----------
public void UploadData(DataSet ds,string filename)
        {
            //
            try
            {
                DAL dbOp = new DAL();                //--------------------------------------                if (filename == "XXX")
                {
                    string SqlDel = "delete from TableName ";  //清空历史数据
                       dbOp.ExecOpSql(SqlDel);
                    string MaterialNo = "";
                    string ComPoseMaterial = "";
                    double LosePer=0;
                    double Lose=0;                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 
                    {
                         MaterialNo = ds.Tables[0].Rows[i][0].ToString();
                         ComPoseMaterial = ds.Tables[0].Rows[i][1].ToString();
                         if (ds.Tables[0].Rows[i][2] != null)
                         {
                             Lose =Convert.ToDouble(ds.Tables[0].Rows[i][2]);
                         }
                         else
                         {
                             Lose= 0;
                         }
                         if (ds.Tables[0].Rows[i][3] != null )
                         {
                             LosePer =Convert.ToDouble(ds.Tables[0].Rows[i][3]);
                         }
                         else
                         {
                             LosePer = 0;
                         }
                   //插入最新数据
                         string sqlInsert = "insert into  TableName values('" + MaterialNo + "','" + ComPoseMaterial + "'," + Lose + "," + LosePer + ")";
                         dbOp.ExecOpSql(sqlInsert);   //封装的执行方法
                    }                  
                }
}

Answer 2

 private void button2_Click(object sender, EventArgs e)
        {            if (comboBox1.Text.Trim() != "")
            {
                DialogResult X = MessageBox.Show("你确定要导入:"+comboBox1.Text.Trim()+"应收金额 的数据吗吗?", "导入确认", MessageBoxButtons.YesNo);
                if (X == DialogResult.Yes)
                {
                    OleDbConnection conn = new OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+label5.Text.Trim()+";Extended Properties='Excel 8.0;IMEX=1'";
                    OleDbConnection olconn = new OleDbConnection(strConn);
                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [sheet1$]", olconn);
                    DataSet ds = new DataSet();
                    OleDbConnection oled = new OleDbConnection("provider=microsoft.jet.OLEDB.4.0;Data source=D:\\mysqlserver\\dgmk.mdb");
                    oled.Open();
                    try
                    {
                        oada.Fill(ds, "[sheet1$]");
                        OleDbCommand comm = new OleDbCommand("select * from 网吧名称", conn);
                        comm.CommandType = CommandType.StoredProcedure;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[0]["结算金额(元)"].ToString() != " ")
                            {
                                string o = comboBox1.Text.Trim() + "应收金额";
                                OleDbCommand oleddata = new OleDbCommand("Update cattle Set " + o + "='" + ds.Tables[0].Rows[i]["结算金额(元)"].ToString() + "' where 网吧名称='" + ds.Tables[0].Rows[i]["网吧名称"].ToString() + "'", oled);
                                oleddata.ExecuteNonQuery();
                            }
                        }
                        MessageBox.Show("导入完成");
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("excel表格格式不对，或者数据连接失败，检查后重试", "导入失败");
                    }
                }
            }
            else
            {
                MessageBox.Show("请选择数据导入月份.","月份选择");
            }
        }记得添加COM 引用：Microsoft excel 11.0 object library 

Answer 3

将xls导入到数据源中
然后在从数据源中读取数据，插入SQL SERVER 数据库
            //begin 1
            try
            {
                string FileName = "";
                if ((TxtPath.PostedFile != null) && (TxtPath.PostedFile.FileName.Length > 2))
                {                    //得到文件的序号
                    //string FileId=getNextSeq("clOAUpFile","FileId");
                    string[] FILE_NAME = TxtPath.PostedFile.FileName.Split('\\');
                    if (FILE_NAME.Length > 1)
                        FileName = FILE_NAME[FILE_NAME.Length - 1];
                    else
                        FileName = TxtPath.PostedFile.FileName;
                    Random random = new Random(System.DateTime.Now.Millisecond);
                    int values = random.Next(100, 999);
                    FileName = Session["USERNAME"].ToString() + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
                        + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString()
                        + DateTime.Now.Millisecond.ToString() + values.ToString() + FileName;
                    //string path =ConfigurationSettings.AppSettings["uploadExcelDir"].ToString()+"\\"+FileName;
                    string path = Server.MapPath(Request.ApplicationPath + "/main/exploit/upload/") + FileName;
                    string fileSize = TxtPath.PostedFile.ContentLength.ToString();                    TxtPath.PostedFile.SaveAs(path);                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                    OleDbConnection xlsConnection = new OleDbConnection(strConn);
                    OleDbCommand xlsCommand = new OleDbCommand("SELECT 部门,项目,分数 FROM [Sheet1$]", xlsConnection);
                    OleDbDataAdapter xlsDataAdapter = new OleDbDataAdapter(xlsCommand);
                    xlsDataAdapter.Fill(myDataSet);
                    dt = myDataSet.Tables[0];
                }
            }
            catch (Exception)
            {
                error = true;
                Response.Write("<SCRIPT language=JavaScript>alert('Excel文件错误，请核对!');location.href='exp_ExcelToOracle.aspx';</script>");
            }

Answer 4

直接用sql语句导入就行了,不用写那么多代码(OPENROWSET 或者opendatasource):
http://www.diybl.com/course/7_databases/sql/sqlServer/20071211/91775.html

Answer 5

dbOp是一个自定义类的对象啊，哥们儿