110,538
社区成员
发帖
与我相关
我的任务
分享
private void ImportXls()
{
CheckForIllegalCrossThreadCalls = false;
btnOK.Enabled = false;
this.Cursor = Cursors.WaitCursor;
var iUserCount = 0;
int i = 0;
DataSet ds = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strXlsPath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter da = new OleDbDataAdapter("select * from [sheet1$A:CV]", conn);
da.Fill(ds, "sheet1");
}
int RecCount = ds.Tables[0].Rows.Count;
progressBar1.Value = 0;
lblCount.Text = "0/" + RecCount.ToString();
progressBar1.Maximum = RecCount;
foreach (DataRow dr in ds.Tables[0].Rows)
{
String barCode = String.Empty;
String userChName = String.Empty;
String userENName = String.Empty;
String userCompany = String.Empty;
String userTableNo = String.Empty;
i++;
lblInfo.Text = "正在导入第:" + i.ToString() + "条数据";
lblCount.Text = i.ToString() + "/" + RecCount.ToString();
progressBar1.Value = i;
barCode = dr["邀请号"].ToString().Trim();
userChName = dr["中文姓名"].ToString().Trim();
userENName = dr["英文姓名"].ToString().Trim();
userCompany = dr["公司/机构名"].ToString().Trim();
userTableNo = dr["桌号"].ToString().Trim();
String sql = "insert into T_UserInfo(UserBarCode,UserCHName,UserENName,UserCompany,UserTableNO) values (@UserBarCode,@UserCHName,@UserENName,@UserCompany,@UserTableNO)";
SqlParameter[] para =
{
new SqlParameter("@UserBarCode",SqlDbType.NVarChar),
new SqlParameter("@UserCHName",SqlDbType.NVarChar),
new SqlParameter("@UserENName",SqlDbType.NVarChar),
new SqlParameter("@UserCompany",SqlDbType.NVarChar),
new SqlParameter("@UserTableNO",SqlDbType.NVarChar)
};
para[0].Value = barCode;
para[1].Value = userChName;
para[2].Value = userENName;
para[3].Value = userCompany;
para[4].Value = userTableNo;
iUserCount++;
try
{
DbHelperSQL.ExecuteSql(sql, para);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message+barCode);
}
}
lblInfo.Text = "完成";
lblCount.Text = "0/0";
progressBar1.Value = 0;
this.Cursor = Cursors.Default;
btnOK.Enabled = true;
MessageBox.Show("导入结束,共导入数据:" + iUserCount.ToString() + "条");
this.DialogResult = DialogResult.OK;
}