寫入sql2005-vs2005中讀取EXCEL表格數據

vs2005中讀取EXCEL表格數據

環境如下，vs2005,sql2005,EXCEL表格一個，
我需要通過VS2005編寫一段C#代碼，來從EXCEL表格中讀取數據，再存入到sql2005表格中，哪位大神知道麻煩告訴下，代碼寫出來給我看，分數不吝啬，謝謝！

最佳回答：

這是以前做過的一個例子，你只看你需要的就可以了，這種方法首先要把數據庫的格式設計成excel的格式
private void insertSQL_Click(object sender, EventArgs e)
{

            comon mn = new comon(); 
            m = 0;
            string excelsql = string.Empty;
            string CarNum = string.Empty;
            string Tare = string.Empty;
            string name = string.Empty;
            string numb1 = string.Empty;
            string numb2 = string.Empty;
            string location = string.Empty;
            string vdate = string.Empty;
            string type = string.Empty;
            string remark = string.Empty;
            string id = string.Empty;
            string conn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + textPath.Text + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            OleDbConnection oleCon = new OleDbConnection(conn);
            oleCon.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
            DataSet ds = new DataSet();               
            mycommand.Fill(ds, "[Sheet1$]");
            oleCon.Close();
            int count = ds.Tables["[Sheet1$]"].Rows.Count;
            string[] st = new string[10] { "序號", "部門", "編號", "姓名", "登記號碼", "設備號", "位置", "簽到時間", "考勤類型", "備注" };
             bool bl = true;
             for (int i = 0; i < ds.Tables["[Sheet1$]"].Columns.Count; i++)
             {
                 if (st[i] != ds.Tables["[Sheet1$]"].Columns[i].ColumnName.ToString().Trim())
                 {
                     bl = false;
                     break;
                 }
             }
            if (bl)
            {                  
                for (int i = 0; i < count; i++)
                {
                    if (ds.Tables["[Sheet1$]"].Rows[i]["編號"].ToString() != "")
                    {
                        CarNum = ds.Tables["[Sheet1$]"].Rows[i]["部門"].ToString().Trim();
                        Tare = ds.Tables["[Sheet1$]"].Rows[i]["編號"].ToString().Trim();
                        name = ds.Tables["[Sheet1$]"].Rows[i]["姓名"].ToString().Trim();
                        numb1 = ds.Tables["[Sheet1$]"].Rows[i]["登記號碼"].ToString().Trim();
                        numb2 = ds.Tables["[Sheet1$]"].Rows[i]["設備號"].ToString().Trim();
                        location = ds.Tables["[Sheet1$]"].Rows[i]["位置"].ToString().Trim();
                        vdate = ds.Tables["[Sheet1$]"].Rows[i]["簽到時間"].ToString().Trim();
                        type = ds.Tables["[Sheet1$]"].Rows[i]["考勤類型"].ToString().Trim();
                        remark = ds.Tables["[Sheet1$]"].Rows[i]["備注"].ToString().Trim();
                        id = ds.Tables["[Sheet1$]"].Rows[i]["序號"].ToString().Trim();
                        string findExit = "select * from kaoqin where id='" + id + "'";
                        DataTable dt = common.GetDataTable(findExit);
                        if (vdate.Length > 0)
                        {
                            TimeSpan ts = Convert.ToDateTime(vdate.Substring(vdate.Length - 5, 5)).TimeOfDay.Subtract(Convert.ToDateTime("12:00").TimeOfDay);
                            if (ts.ToString().Substring(0, 1) == "-")
                            {
                                type = "上班";
                            }
                            else
                            {
                                type = "下班";
                            }
                        }
                        //MessageBox.Show(mn.selectdata1("select realname from staffinfo where unumber='" + Tare + "'").Rows[0][1].ToString().Trim());
                        name = mn.selectdata1("select realname from staffinfo where unumber='" + Tare + "'").Rows[0][0].ToString().Trim();
                        if (dt.Rows.Count > 0)
                        {
                            excelsql = string.Format("update kaoqin set Tare='" + Tare + "',name='" + name + "',numb1='" + numb1 + "',numb2='" + numb2 + "',location='" + location + "',vdate='" + vdate + "',type='" + type + "',remark='" + remark + "',CarNum='" + CarNum + "' where id='" + id + "'");
                        }
                        else
                        {
                            excelsql = string.Format("insert into kaoqin (id,CarNum, Tare,name,numb1,numb2,location,vdate,type,remark) values ('" + id + "','" + CarNum + "','" + Tare + "','" + name + "','" + numb1 + "','" + numb2 + "','" + location + "','" + vdate + "','" + type + "','" + remark + "')");
                        }
                        common.PostModify(excelsql);

                    }
                }
                dataGridView1.DataSource = common.GetDate(common.GetConnStr(), "select CarNum as 部門,Tare  as 編號,name as 姓名,numb1 as 登陸號碼,numb2 as 設備號,location as 位置,vdate as 簽到時間,type as 考勤類型,remark as 備注 from kaoqin");
                PlaySound("提示時奏幻想空間.WAV", 0, SND_ASYNC | SND_FILENAME);
                MessageBox.Show("導入數據已全部導入！");
            }
            else
            {
                MessageBox.Show("Excel結構不匹配，無法導入");
            }
            //SoundPlayer sp = new SoundPlayer(@"E:\學習文檔\c#2005\學籍管理\1.wav");
            //sp.PlayLooping();

            //SystemSounds.Beep.Play();
            //加載數據到datagridview
           //dataGridView1.DataSource = common.GetDate(common.GetConnStr(), "select * from kaoqin");
            //Application.DoEvents();               
        //}
        //catch
        //{
        //    MessageBox.Show("導入數據失敗！");
        //}
    }