读取Excel中的数据

 ///在按钮点击事件里

private void btnAddsource_Click(object sender, EventArgs e)
        {
                        
            if (DialogResult.OK == openFileDialog1.ShowDialog())
            {
                //获取Execle文件路径
                string filePath = openFileDialog1.FileName;              

                DataTable  dtExcel = ExcelToDateTable(filePath, "Sheet1");
                for (int i = 0; i < dtExcel.Rows.Count; i++)
                {

 

                  //调用自定义的方法

                    InsertDataToAccess(dtExcel.Rows[i][0].ToString());
                }
                this.lbxSourcefile.DataSource = null;

                //绑定ListBox控件

                this.lbxSourcefile.DataSource = GetSourceFilesAll();
                lbxSourcefile.DisplayMember = "Sfile";
            }
        }

 

//自定义方法读取Excel文件返回一个datatable

public static DataTable ExcelToDateTable(string strExcelFileName, string strSheetName)
        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            string strExcel = string.Format("select * from [{0}$]", strSheetName);
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                adapter.Fill(ds, strSheetName);
                conn.Close();
            }

            return ds.Tables[strSheetName];

        }

//自定义方法插入数据库

        public static void InsertDataToAccess(string _strPara)
        {
            try
            {
                SqlConnection sqlDbConn = GetConnection();
                sqlDbConn.Open();
                string strInsertString = "INSERT INTO SourceFile (sourcefile) VALUES (@strCollumn1)";
                SqlCommand sComm = new SqlCommand(strInsertString, sqlDbConn);
                sComm.Parameters.Add("@strCollumn1", SqlDbType.VarChar, 50);
                sComm.Parameters["@strCollumn1"].Value = _strPara;
                int result = sComm.ExecuteNonQuery();
                sqlDbConn.Close();

            }
            catch (Exception)
            {

                MessageBox.Show("没有找到数据库！");
            }
          
        }