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;
}
