读取Excel中的数据

来源：互联网发布：java orm框架性能比较编辑：程序博客网时间：2024/06/02 17:31

///在按钮点击事件里

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("没有找到数据库！");
            }

        }