请教,EXCEL导入数据几个功能的实现

qq858136112 2013-10-31 12:21:36
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();
}

以上代码可以完成EXCEL文件数据插入到SQL数据库的表,请教以下几个问题?
1 如何实现不导入重复的数据,如果有重复的,只需要更新相关值

2 如何实现与数据比较后才更新相关列的值
3 遇到EXCEL表中错误的数据,提示是那条数据错了?
...全文
140 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
threenewbee 2013-10-31
  • 打赏
  • 举报
回复
用一个字典记录唯一字段,这样就可以很容易查询是该插入还是修改了。
qq858136112 2013-10-31
  • 打赏
  • 举报
回复
哎,求回答哎,求回答哎,求回答哎,求回答

110,499

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧