昶達的部落格

string xlsPath = "d:\\wwwroot_hr\\WelfareFunds\\admin\\test999.xls";

string sheetName = "Sheet1";

//Excel的連線字串

//HDR(HeaDer Row):YES的表示第一列為標題列不讀取，NO則會讀取第一列

//IMEX:讀寫的模式，0:Export Mode(寫),1:Import Mode(讀),2:Linked Mode(讀/寫)，一般設定1

//xlsx格式不適用 

using (OleDbConnection conn_excel = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + xlsPath + "';Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1;'"))

{

conn_excel.Open();

OleDbCommand cmd_excel = new OleDbCommand("SELECT [EmployeeNo],[Cname],[Ename],[Travel],[Health] FROM [" + sheetName + "$];", conn_excel);

OleDbDataReader reader_excel = cmd_excel.ExecuteReader();

//SQL連線字串

using (SqlConnection cn_sql = new SqlConnection(@"server=192.168.0.1;uid=sa;pwd=password;database=testtbl"))

{

cn_sql.Open();

//宣告Transaction

SqlTransaction stran = cn_sql.BeginTransaction();

try

{

while (reader_excel.Read())

{

SqlCommand cmd_sql = new SqlCommand("insert into testtbl (EmployeeNo, Cname, Ename, Travel, Health) values ('" + reader_excel[0] + "','" + reader_excel[1] + "','" + reader_excel[2] + "','" + reader_excel[3] + "','" + reader_excel[4] + "')", cn_sql);

cmd_sql.Transaction = stran;

cmd_sql.ExecuteNonQuery();

}

//迴圈跑完並一次Insert

stran.Commit();

}

catch (SqlException ex)

{

Response.Write(ex.Message);

Response.Write(ex.Number);

stran.Rollback();

}

catch (OleDbException ex)

{

Response.Write(ex.Message);

stran.Rollback();

}

catch (Exception ex)

{

Response.Write(ex.Message);

stran.Rollback();

}

finally

{

cn_sql.Close();

conn_excel.Close();

reader_excel.Close();

}

}

}