【winfrom】Excel文件加载

//Oledb 像我们连接数据库，数据加载DataTable
//Excel文件当作是我们要连接的数据库连接字符串

//03和07及以上版本的引擎不同，
//03是Microsoft.Jet.OLEDB.4.0 ，07及以上是Microsoft.ACE.OLEDB.12.0 。
//导入

  1 private void button4_Click(object sender, EventArgs e)
  2 {
  3 OpenFileDialog ofd = new OpenFileDialog();
  4 ofd.Title = "选择Excel文件";
  5 ofd.FileName = "";
  6 ofd.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
  7 if (ofd.ShowDialog() == DialogResult.OK)
  8 {
  9 string name = ofd.FileName;
 10 string ext = Path.GetExtension(name).ToLower();
 11 string strConn = "";
 12 if (ext == ".xls")
 13 {
 14 //如果是07以下（.xls）的版本的Excel文件
 15 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ofd.FileName + ";Extended Properties='Excel 8.0";
 16 }
 17 else
 18 {
 19 //如果是07以上(.xlsx)的版本的Excel文件
 20 strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + ofd.FileName + ";Extended Properties='Excel 12.0";//
 21 }
 22 //IMEX 0 Excel只能用作写入 1 只能用作读取 2 读取与写入
 23 strConn += ";HDR=Yes; IMEX=1'";//第一行是标题,支持读取
 24 //HDR No 表示第一行是数据，不是标题
 25 
 26 
 27 System.Data.DataTable dt = new System.Data.DataTable();
 28 using (OleDbConnection conn = new OleDbConnection(strConn))
 29 {
 30 conn.Open();
 31 System.Data.DataTable dtNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
 32 string shName = dtNames.Rows[0][2].ToString();
 33 string sql = string.Format("select * from [{0}]", shName);
 34 OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
 35 da.Fill(dt);
 36 }
 37 
 38 
 39 //先将部门信息存入表，再保存用户信息，用户信息表里的DeptId是对应的部门名称的编号
 40 //批量 失败 什么都没插入，成功，提交 事务来实现
 41 
 42 
 43 string sqlInsertDept = "insert into T_Department(DeptName) values(@deptName);select @@Identity";
 44 string sqlInsertUser = "insert into T_User(UserName,Age,DeptId) values(@userName,@age,@deptId)";
 45 string sqlExistDept = "select Id from T_Department where DeptName=@deptName";
 46 using (SqlConnection conn1 = new SqlConnection(connString))
 47 {
 48 conn1.Open();
 49 SqlTransaction tran = conn1.BeginTransaction();
 50 SqlCommand cmd = new SqlCommand("", conn1, tran);
 51 try
 52 {
 53 for (int i = 0; i < dt.Rows.Count; i++)
 54 {
 55 string deptName = dt.Rows[i]["DeptName"].ToString().Trim();
 56 string userName = dt.Rows[i]["UserName"].ToString().Trim();
 57 int age = int.Parse(dt.Rows[i]["Age"].ToString().Trim());
 58 
 59 SqlParameter[] paras ={
 60 new SqlParameter("@deptName",deptName)
 61 };
 62 cmd.Parameters.Clear();
 63 cmd.CommandText = sqlExistDept;
 64 cmd.Parameters.AddRange(paras);
 65 object oDId = cmd.ExecuteScalar();
 66 if(oDId !=null && Convert.ToInt32(oDId)>0)
 67 {
 68 SqlParameter[] parasUser ={
 69 new SqlParameter("@userName",userName),
 70 new SqlParameter("@age",age),
 71 new SqlParameter("@deptId",Convert.ToInt32(oDId))
 72 };
 73 cmd.Parameters.Clear();
 74 cmd.CommandText = sqlInsertUser;
 75 cmd.Parameters.AddRange(parasUser);
 76 cmd.ExecuteNonQuery();
 77 }
 78 else
 79 {
 80 cmd.Parameters.Clear();
 81 cmd.CommandText = sqlInsertDept;
 82 cmd.Parameters.AddRange(paras);
 83 object onewDId = cmd.ExecuteScalar();
 84 if(onewDId !=null && Convert.ToInt32(onewDId)>0)
 85 {
 86 SqlParameter[] parasUser ={
 87 new SqlParameter("@userName",userName),
 88 new SqlParameter("@age",age),
 89 new SqlParameter("@deptId",Convert.ToInt32(onewDId))
 90 };
 91 cmd.Parameters.Clear();
 92 cmd.CommandText = sqlInsertUser;
 93 cmd.Parameters.AddRange(parasUser);
 94 cmd.ExecuteNonQuery();
 95 }
 96 }
 97 }
 98 tran.Commit();
 99 }
100 catch (Exception ex)
101 {
102 tran.Rollback();
103 MessageBox.Show(ex.Message);
104 
105 }
106 finally
107 {
108 tran.Dispose();
109 conn1.Close();
110 }
111 }
112 
113 
114 }

View Code

public class SqlHelper
{
private readonly static string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

public static int ExecuteNonQuery(string sql,SqlParameter[] paras)
{
int count = 0;
using(SqlConnection conn=new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (paras != null)
cmd.Parameters.AddRange(paras);
conn.Open();
count = cmd.ExecuteNonQuery();
}
return count;
}

public static object ExecuteScalar(string sql, SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (paras != null)
cmd.Parameters.AddRange(paras);
conn.Open();
object o = cmd.ExecuteScalar();
return o;
}

}
}

View Code

秒客网

【winfrom】Excel文件加载

相关文章