请教，EXCEL导入数据几个功能的实现
private void button1_Click(object sender, EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + "D:\\test.xls" + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(dt);
dataGridView1.DataSource = dt;
}
private void ssex()
{
openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
string filePath = fileInfo.FullName;
//string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "d:\\test.xsl" + ";Extended Properties=Excel 8.0;HDR=YES; IMEX=1'";
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
{
OleDbConnection oleDbConnection = new OleDbConnection(strConn);
oleDbConnection.Open();
//获取excel表
DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
string tableName = dataTable.Rows[0][2].ToString().Trim();
tableName = "[" + tableName.Replace("'", "") + "]";
//利用SQL语句从Excel文件里获取数据
//string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
string query = "SELECT 姓名 FROM " + tableName;
OleDbDataAdapter odda = new OleDbDataAdapter(query, oleDbConnection);
/*
DataTable dt = new DataTable();
odda.Fill(dt);
dataGridView1.DataSource = dt;
*/
DataSet dataSet = new DataSet();
//OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection);
//OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, strConn);
oleAdapter.Fill(dataSet, tableName);
//dataGrid1.DataSource = dataSet;
//dataGrid1.DataMember = tableName;
//dataGrid1.SetDataBinding(dataSet, "gch_Class_Info");
dataGridView1.DataSource = dataSet;
//从excel文件获得数据后，插入记录到SQL Server的数据表
//连接数据库
string sqlcon = "Data Source=192.168.36.253;Initial Catalog=db_MyQQData;Persist Security Info=True;User ID=test;Password=test";
string sqlcmd = "select name from dbo.tb_CurreneyUser";
SqlConnection con = new SqlConnection(sqlcon);
SqlDataAdapter sqlDA1 = new SqlDataAdapter(sqlcmd, con);
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
DataTable dataTable1 = new DataTable();
sqlDA1.Fill(dataTable1);
foreach (DataRow dataRow in dataSet.Tables[tableName].Rows)
{
DataRow dataRow1 = dataTable1.NewRow();
dataRow1["name"] = dataRow["姓名"];
//dataRow1["classPlace"] = dataRow["开课城市"];
//dataRow1["classTeacher"] = dataRow["讲师"];
//dataRow1["classTitle"] = dataRow["课程名称"];
//dataRow1["durativeDate"] = dataRow["持续时间"];
//MessageBox.Show(dataSet.Tables["K12016"].Rows[i].ToString());
dataTable1.Rows.Add(dataRow1);
}
MessageBox.Show("新插入 " + dataSet.Tables[tableName].Rows.Count.ToString() + " 条记录");
sqlDA1.Update(dataTable1);
dataTable1.Clear();
sqlDA1.Dispose();
oleDbConnection.Close();
con.Close();
}
