DataTable LoadExcelSaveDataBase() { OpenFileDialog LoadFileDialog = new OpenFileDialog(); LoadFileDialog.Filter = "xls|*.xls"; DataTable CardDT = new DataTable(); CardDT.Columns.Add("ABA_num"); CardDT.Columns.Add("WEG_num"); CardDT.Columns.Add("Message"); CardDT.Columns.Add("Add"); CardDT.Columns.Add("Memo"); if (LoadFileDialog.ShowDialog() == DialogResult.OK) { System.IO.FileInfo LoadFile = new System.IO.FileInfo(LoadFileDialog.FileName); if (!LoadFile.Exists) { MessageBox.Show("檔案不存在"); return CardDT; } DataTable DT = null; string strConn = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" + LoadFileDialog.FileName + ";Extended Properties='Excel 8.0;IMEX = 1;'"; OleDbConnection Conn = new OleDbConnection(strConn); try { Conn.Open(); } catch (Exception ex) { MessageBox.Show(ex.Message); return CardDT; } Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel", true); object keyValue = key.GetValue("TypeGuessRows"); //try //{ key.SetValue("TypeGuessRows", 16); DataSet ds = new DataSet(); //clsDBComm commDB = new clsDBComm(); DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0) { dt.Rows[0]["TABLE_Name"] += "$"; } int RowIndex = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["TABLE_Name"].Equals("'20111022_門禁發卡編號$'")) { RowIndex = i; break; } } string strSelect = "Select * From [" + dt.Rows[RowIndex]["TABLE_Name"].ToString() + "] "; OleDbDataAdapter da = new OleDbDataAdapter(strSelect, strConn); da.Fill(ds, dt.Rows[RowIndex]["TABLE_Name"].ToString()); da.Dispose(); Conn.Close(); Conn.Dispose(); //ds.Tables[dt.Rows[0]["TABLE_Name"].ToString()].Rows.RemoveAt(0); dbroomEntities dbroom = new dbroomEntities(); //tblControllerConfig NewControll = new tblControllerConfig(); //NewControll.ControlID = "TEST-1"; //NewControll.ControlType = 1; //NewControll.ERId = 1; //dbroom.AddTotblControllerConfigs(NewControll); //tblEntranceGuardConfig newEgc = new tblEntranceGuardConfig(); //newEgc.EntranceCode = "TEST-1-1"; //newEgc.ERId = 1; //newEgc.Floor = 1; //newEgc.EntranceType = "1"; //newEgc.Memo = "TEST"; //dbroom.AddTotblEntranceGuardConfigs(newEgc); //tblCardReaderConfig newReader = new tblCardReaderConfig(); //newReader.ReaderID = 1; //newReader.EntranceCode ="TEST-1-1"; //newReader.ControlID = "TEST-1"; //newReader.ReaderType = 1; //newReader.IP = "0.0.0.0"; //newReader.Port = 6666; //dbroom.AddTotblCardReaderConfigs(newReader); DT = ds.Tables[dt.Rows[RowIndex]["TABLE_Name"].ToString()]; foreach (DataRow dr in DT.Rows) { if (dr[2] == DBNull.Value || dr[7] == DBNull.Value) continue; DataRow CardRow = CardDT.NewRow(); CardRow["ABA_num"] = dr[1].ToString() + dr[2].ToString(); CardRow["WEG_num"] = "00" + dr[3].ToString() + dr[4].ToString(); CardRow["Message"] = dr[6].Equals(DBNull.Value) ? dr[5] : dr[6].ToString(); CardRow["Add"] = dr[7]; CardRow["Memo"] = dr[8]; CardDT.Rows.Add(CardRow); int roleID; string GroupName = dr[5].Equals(DBNull.Value) ? "交控中心" : dr[5].ToString(); var data = from o in dbroom.tblSysRole where o.RoleName == GroupName select o; if (data.Count() == 0) { tblSysRole newRole = new tblSysRole(); int num = (from o in dbroom.tblSysRole select o).Count() + 1; newRole.RoleID = num; newRole.RoleName = dr[5].Equals(DBNull.Value) ? "交控中心" : dr[5].ToString(); dbroom.tblSysRole.Add(newRole); dbroom.SaveChanges(); roleID = num; } else { roleID = data.First().RoleID; } //tblSysUser newUser = new tblSysUser(); //dbroom.ExecuteFunction("delete from tblSysUsers;"); //int UserNum = (from o in dbroom.tblSysUsers select o).Count() + 1; //newUser.UserId = UserNum.ToString(); //newUser.UserName = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString(); //newUser.RoleId = roleID; //newUser.Enable = "Y"; //dbroom.AddTotblSysUsers(newUser); string aba = CardRow["ABA_num"].ToString(); var CardData = from o in dbroom.tblMagneticCard where o.ABA == aba select o; if (CardData.Count() > 0) { var oldCard = CardData.First(); oldCard.RoleID = roleID; oldCard.Name = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString(); oldCard.Company = dr[5].Equals(DBNull.Value) ? string.Empty : dr[5].ToString(); } else { tblMagneticCard newCard = new tblMagneticCard(); newCard.MagneticID = (from o in dbroom.tblMagneticCard select o).Count() + 1; newCard.ABA = aba; newCard.WEG1 = "00" + dr[3].ToString(); newCard.WEG2 = dr[4].ToString(); newCard.RoleID = roleID; newCard.Name = dr[6].Equals(DBNull.Value) ? string.Empty : dr[6].ToString(); newCard.Company = dr[5].Equals(DBNull.Value) ? string.Empty : dr[5].ToString(); newCard.Enable = "Y"; newCard.Memo = dr[8].ToString(); dbroom.tblMagneticCard.Add(newCard); } dbroom.SaveChanges(); } //} //catch (Exception ex) //{ // Conn.Close(); // Conn.Dispose(); // MessageBox.Show(ex.Message); //} //finally //{ key.SetValue("TypeGuessRows", keyValue); //} } else { this.Close(); return null; } return CardDT; }