C#winform中Excel电子表格导入数据库示例
做了一个数据录入程序,本打算一条条录入,可是后来发现当数据量比较庞大时,就十分不方便了,没办法在网上查了一些资料,结合我的实际做了个excel电子表格导入数据库的示例:
首先要保证将要导入数据库的excel表格中的数据和数据库字段相符,excel中不能存在数据表中不存在的字段。获取excel文档完整路径,并将其中的数据生成dataset对象:
private
DataSet xsldata(
string
filepath)
{
string strCon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + " ;Extended Properties='Excel 8.0;IMEX=1' " ;
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] " ;
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, " [Sheet1$] " );
dataGridView1.DataSource = ds.Tables[ 0 ];
Conn.Close();
return ds;
}
{
string strCon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + " ;Extended Properties='Excel 8.0;IMEX=1' " ;
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] " ;
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, " [Sheet1$] " );
dataGridView1.DataSource = ds.Tables[ 0 ];
Conn.Close();
return ds;
}
完成之后把dataset对象导入数据库中,这里说明一下excel的头一行系统会默认为列名,不对其做导入操作,所以最好做个处理,这里我添加了列名。
private
void
button1_Click(
object
sender, EventArgs e)
{
if (txtpath.Text == "" )
{
MessageBox.Show( " 请选择要导入的Excel文档! " , " 系统提示 " ,MessageBoxButtons.OK,MessageBoxIcon.Information);
return ;
}
string filepath = txtpath.Text;
SqlConnection conn = new SqlConnection(strcon); // 链接数据库
conn.Open();
try
{
DataSet ds = new DataSet();
{
if (txtpath.Text == "" )
{
MessageBox.Show( " 请选择要导入的Excel文档! " , " 系统提示 " ,MessageBoxButtons.OK,MessageBoxIcon.Information);
return ;
}
string filepath = txtpath.Text;
SqlConnection conn = new SqlConnection(strcon); // 链接数据库
conn.Open();
try
{
DataSet ds = new DataSet();
//
取得数据集
//调用上面的函数
ds = xsldata(filepath);
// dataGridView2.DataSource = ds.Tables[0];
int errorcount = 0 ; // 记录错误信息条数
int insertcount = 0 ; // 记录插入成功条数
int updatecount = 0 ; // 记录更新信息条数
for ( int i = 0 ; i < ds.Tables[ 0 ].Rows.Count; i ++ )
{
int cardtypeid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 0 ].ToString());
string cardnum = ds.Tables[ 0 ].Rows[i][ 1 ].ToString();
string cardpwd = ds.Tables[ 0 ].Rows[i][ 2 ].ToString();
int officeid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 3 ].ToString());
DateTime stdt = Convert.ToDateTime(ds.Tables[ 0 ].Rows[i][ 4 ].ToString());
string cardstate = ds.Tables[ 0 ].Rows[i][ 5 ].ToString();
int userid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 6 ].ToString());
if (cardtypeid != 0 && cardnum != "" && cardpwd != "" && userid != 0 )
{
SqlCommand selectcmd = new SqlCommand( " select count(*) from cardsinfo where CardNum=' " + cardnum + " 'and CardPwd=' " + cardpwd + " ' and CardState='1' and TypeID= " + cardtypeid, conn);
int count = Convert.ToInt32(selectcmd.ExecuteScalar());
if (count > 0 )
{
updatecount ++ ;
}
else
{
SqlCommand insertcmd = new SqlCommand( " insert into cardsinfo(TypeID,CardNum,CardPwd,OutByOffID,CardTime,CardState,CardByUser) values( " + cardtypeid + " ,' " +
cardnum + " ',' " + cardpwd + " ', " + officeid + " ,' " + stdt + " ','1', " + userid + " ) " , conn);
insertcmd.ExecuteNonQuery();
insertcount ++ ;
}
}
else
{
// MessageBox.Show("电子表格信息有错!");
errorcount ++ ;
;
}
}
MessageBox.Show( insertcount + " 条数据导入成功! " + updatecount + " 条数据重复! " + errorcount + " 条数据部分信息为空没有导入! " );
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
ds = xsldata(filepath);
// dataGridView2.DataSource = ds.Tables[0];
int errorcount = 0 ; // 记录错误信息条数
int insertcount = 0 ; // 记录插入成功条数
int updatecount = 0 ; // 记录更新信息条数
for ( int i = 0 ; i < ds.Tables[ 0 ].Rows.Count; i ++ )
{
int cardtypeid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 0 ].ToString());
string cardnum = ds.Tables[ 0 ].Rows[i][ 1 ].ToString();
string cardpwd = ds.Tables[ 0 ].Rows[i][ 2 ].ToString();
int officeid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 3 ].ToString());
DateTime stdt = Convert.ToDateTime(ds.Tables[ 0 ].Rows[i][ 4 ].ToString());
string cardstate = ds.Tables[ 0 ].Rows[i][ 5 ].ToString();
int userid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 6 ].ToString());
if (cardtypeid != 0 && cardnum != "" && cardpwd != "" && userid != 0 )
{
SqlCommand selectcmd = new SqlCommand( " select count(*) from cardsinfo where CardNum=' " + cardnum + " 'and CardPwd=' " + cardpwd + " ' and CardState='1' and TypeID= " + cardtypeid, conn);
int count = Convert.ToInt32(selectcmd.ExecuteScalar());
if (count > 0 )
{
updatecount ++ ;
}
else
{
SqlCommand insertcmd = new SqlCommand( " insert into cardsinfo(TypeID,CardNum,CardPwd,OutByOffID,CardTime,CardState,CardByUser) values( " + cardtypeid + " ,' " +
cardnum + " ',' " + cardpwd + " ', " + officeid + " ,' " + stdt + " ','1', " + userid + " ) " , conn);
insertcmd.ExecuteNonQuery();
insertcount ++ ;
}
}
else
{
// MessageBox.Show("电子表格信息有错!");
errorcount ++ ;
;
}
}
MessageBox.Show( insertcount + " 条数据导入成功! " + updatecount + " 条数据重复! " + errorcount + " 条数据部分信息为空没有导入! " );
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
这样简单的excel电子表格导入数据库就完成了