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