private void btnGo_Click(object sender, EventArgs e)
{
try
{
this.DTCList.Clear();
//Inilize DTCDatabase
string DTCstrCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = GMRDB.xls ;Extended Properties=Excel 8.0";
OleDbConnection GMRDBConnection = new OleDbConnection(DTCstrCon);
GMRDBConnection.Open();
string strCom = " SELECT * FROM [Ref_DTC$]";
OleDbDataAdapter GMRDBCommand = new OleDbDataAdapter(strCom, GMRDBConnection);
System.Data.DataSet dsDTC = new DataSet();
GMRDBCommand.Fill(dsDTC, "Ref_DTC");
dtDTC = dsDTC.Tables[0];
//Inilize FaultyType
strCom = " SELECT * FROM [Ref_DTCFaults$]";
GMRDBCommand = new OleDbDataAdapter(strCom, GMRDBConnection);
System.Data.DataSet dsFaultType = new DataSet();
GMRDBCommand.Fill(dsFaultType, "Ref_DTCFaults");
dtFaultType = dsFaultType.Tables[0];
//Close GMRDB
GMRDBConnection.Close();
//Get DTC List
XmlDocument SnapShot = new XmlDocument();
SnapShot.Load(this.tbxSnapshot.Text);
XmlNodeList SnapShotDTCs = SnapShot.GetElementsByTagName("dtc");
foreach (XmlNode q in SnapShotDTCs)
{
if (q != null && q.Attributes["valuehex"].Value != "")
{
DTC theDTC = new DTC();
theDTC = RequestDTCInfo(q.Attributes["display"].Value, q.ChildNodes[0].Attributes["faulttype"].Value, q.ChildNodes[0].Attributes["status"].Value, q.ChildNodes[0].Attributes["statuscat"].Value);
theDTC.Hex = q.Attributes["valuehex"].Value;
XmlNode Module = q.ParentNode;
theDTC.ECU = Module.Attributes["name"].Value;
DTCList.Add(theDTC);
}
}
FileInfo Model = new FileInfo("Model");
FileInfo Target = new FileInfo("DTCList.xls");
if (Target.Exists)
{
File.Delete(Target.FullName);
File.Copy(Model.FullName, Target.FullName);
}
else
{
File.Copy(Model.FullName, Target.FullName);
}
//XLS Operation
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(System.Environment.CurrentDirectory + "\\DTCList.xls");
Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Worksheets["DTCList"];
int i = 2;
foreach (DTC q in DTCList)
{
xlSheet.get_Range("A" + i.ToString()).Value = q.ECU;
xlSheet.get_Range("B" + i.ToString()).Value = q.Hex;
xlSheet.get_Range("C" + i.ToString()).Value = q.Sub;
xlSheet.get_Range("D" + i.ToString()).Value = q.Status;
xlSheet.get_Range("E" + i.ToString()).Value = q.Code;
xlSheet.get_Range("F" + i.ToString()).Value = q.Desc;
xlSheet.get_Range("G" + i.ToString()).Value = q.Sub_Desc;
xlSheet.get_Range("H" + i.ToString()).Value = q.Status_Desc;
i++;
}
xlApp.Visible = true;
/*
//SQL Operation;
string targetConString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = DTCList.xls ;Extended Properties=Excel 8.0";
OleDbConnection TargetConnection = new OleDbConnection(targetConString);
TargetConnection.Open();
OleDbCommand WriteCmd = new OleDbCommand("", TargetConnection);
foreach (DTC q in DTCList)
{
WriteCmd.CommandText = "INSERT INTO [DTCList$] VALUES("
+ "'" + q.ECU + "', "
+ "'" + q.Hex + "', "
+ "'" + q.Sub + "', "
+ "'" + q.Status + "', "
+ "'" + q.Code + "', "
+ "'" + q.Desc + "', "
+ "'" + q.Sub_Desc + "', "
+ "'" + q.Status_Desc + "')";
WriteCmd.ExecuteNonQuery();
}
TargetConnection.Close();
//Save Result
if (sfdSaveFile.ShowDialog() == DialogResult.OK)
{
FileInfo SaveFile = new FileInfo(sfdSaveFile.FileName);
if (SaveFile.Exists)
{
File.Delete(SaveFile.FullName);
}
File.Copy(Target.FullName, SaveFile.FullName);
}
*/
}
catch
{
MessageBox.Show("DTC XML Loaded Failed");
}
}
public DTC RequestDTCInfo(string Code, string Sub, string Status, string Status_Desc)
{
DTC Result = new DTC();
Result.Code = Code;
Result.Sub = Sub;
Result.Status = Status;
Result.Status_Desc = Status_Desc;
//Check Description
DataRow[] dtResult = dtDTC.Select("Display='" + Code + "'");
if (dtResult.Length != 0)
Result.Desc = dtResult[0]["Description"].ToString();
dtResult = dtFaultType.Select("Id='" + Sub + "'");
if (dtResult.Length != 0)
Result.Sub_Desc = dtResult[0]["Description"].ToString();
return Result;
}
