vs2005中读取EXCEL表格数据

Question

环境如下，vs2005,sql2005,EXCEL表格一个，
我需要通过VS2005编写一段C#代码，来从EXCEL表格中读取数据，再存入到sql2005表格中，哪位大神知道麻烦告诉下，代码写出来给我看，分数不吝啬，谢谢！

Answer 1

这是以前做过的一个例子，你只看你需要的就可以了，这种方法首先要把数据库的格式设计成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("导入数据失败！");
        //}
    }