public static Homologation GetHomologation(string filePath, string sheetName, string failureName) { Homologation result = null; XSSFWorkbook hssfwb; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfwb = new XSSFWorkbook(file); } var sheet = hssfwb.GetSheet(sheetName); var rowHeaderIndex = int.Parse(ConfigurationManager.AppSettings["InputFile3_Header_RowIndex"]); var colFailureNameIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile3_Column_Exigences"]); var colMILndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile3_Column_MIL"]); var colFailingDiagCodeIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile3_Column_DrivingCycles"]); if (colFailureNameIndex == -1 || colMILndex == -1 || colFailingDiagCodeIndex == -1) { Logger.Log(string.Format("Input file '{0}' column(s) not found. Check the column names in config file!", filePath)); return(null); } var rowsCol0 = ExcelUtils.GetRowsByColumnIndexAndCellValue(sheet, colFailureNameIndex, failureName); if (rowsCol0 != null && rowsCol0.Count > 0 && rowsCol0[0] != null) { var data = rowsCol0[0]; result = new Homologation(); result.FailureName = failureName; result.FailingDiagCode = data.Cells[colFailingDiagCodeIndex].CellType == CellType.String ? data.Cells[colFailingDiagCodeIndex].StringCellValue : data.Cells[colFailingDiagCodeIndex].CellType == CellType.Numeric ? data.Cells[colFailingDiagCodeIndex].NumericCellValue.ToString() : ""; result.MIL = data.Cells[colMILndex].CellType == CellType.String ? data.Cells[colMILndex].StringCellValue : data.Cells[colMILndex].CellType == CellType.Numeric ? data.Cells[colMILndex].NumericCellValue.ToString() : ""; } return(result); }
private void btnSelectFile_Click(object sender, EventArgs e) { // Create an instance of the open file dialog box. OpenFileDialog dlg = new OpenFileDialog(); // Set filter options and filter index. dlg.Filter = "Text Files (.xls)|*.xlsx|All Files (*.*)|*.*"; dlg.FilterIndex = 1; dlg.Multiselect = true; // Call the ShowDialog method to show the dialog box. var userClickedOK = dlg.ShowDialog(); // Process input if the user clicked OK. if (userClickedOK == DialogResult.OK) { FileSdF_Path = dlg.FileName; txtFile1.Text = FileSdF_Path.Substring(FileSdF_Path.LastIndexOf('\\') + 1); var shortfileName = txtFile1.Text.Substring(0, txtFile1.Text.LastIndexOf(".")); File1_Path_Output = FileSdF_Path.Replace(shortfileName, shortfileName + "_out"); var exigencesColumn = -1; int.TryParse(ConfigurationManager.AppSettings["InputFile1_Column_Exigences"], out exigencesColumn); //ExcelUtils.GetColumnIndexByText(FileSdF_Path, ConfigurationManager.AppSettings["InputFile1_Column_Exigences"]); if (exigencesColumn == -1) { MessageBox.Show("Incorrect starting index set for applicability columns!"); return; } var nextColumns = ExcelUtils.GetColumnsAfterIndex(FileSdF_Path, exigencesColumn, int.Parse(ConfigurationManager.AppSettings["InputFile1_Header_RowIndex"])); cbApplications.DataSource = new BindingSource(nextColumns, null); cbApplications.DisplayMember = "Value"; cbApplications.ValueMember = "Key"; } }
public static void ProcessFailure(Microsoft.Office.Interop.Excel.Application excelApplication, string templateFilePath, string outputFilePath, ExigenceDetails exigence, Failure failure, Homologation homologation, int indexRecordProcessed) { ExcelUtils.WriteFailureDataToExcel(excelApplication, templateFilePath, outputFilePath, exigence, failure, homologation, indexRecordProcessed); }
public static List <ExigenceDetails> GetExigences(string File1_Path, string sheetName, int exigencesColIndex) { var results = new List <ExigenceDetails>(); try { #region read input file columns indexes XSSFWorkbook hssfwb; using (FileStream file = new FileStream(File1_Path, FileMode.Open, FileAccess.Read)) { hssfwb = new XSSFWorkbook(file); } var sheet = hssfwb.GetSheet(ConfigurationManager.AppSettings["InputFile1_SheetName"]); var rowHeaderIndex = int.Parse(ConfigurationManager.AppSettings["InputFile1_Header_RowIndex"]); var colFailureNameIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile1_Column_FailureName"]); var colDegradationModeIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile1_Column_DegradationMode"]); var colGIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile1_Column_G"]); var colGEEIndex = ExcelUtils.GetColumnIndexByText(sheet, rowHeaderIndex, ConfigurationManager.AppSettings["InputFile1_Column_GEE"]); // check if columns FailureNameIndex and GIndex exists and return null if they're missing. // Obs: column GEEIndex is not mandatory if (colFailureNameIndex == -1 || colGIndex == -1) { Logger.Log(string.Format("Input file '{0}' column(s) not found. Check the column names in config file!", File1_Path)); return(null); } #endregion var colValueForSearch = "x"; // get exicences from File1_Input var rowsDiags = ExcelUtils.GetRowsByColumnIndexAndCellValue(File1_Path, sheetName, exigencesColIndex, colValueForSearch); foreach (var rowDiag in rowsDiags) { var exigence = new ExigenceDetails(); var isNewExicence = true; try { exigence.FailureName = rowDiag.Cells[colFailureNameIndex].StringCellValue; // check if failure already exists [update 4 feb 2018] if (results.FirstOrDefault(ex => ex.FailureName == exigence.FailureName) != null) { //continue; exigence = results.FirstOrDefault(ex => ex.FailureName == exigence.FailureName); isNewExicence = false; } var degModes = rowDiag.Cells[colDegradationModeIndex].StringCellValue; if (degModes != null) { if (degModes.Contains('\n')) { var newDegModes = degModes.Split('\n'); foreach (var degMode in newDegModes) { if (exigence.DegradationMode.FirstOrDefault(d => d == degMode) != null) { continue; } if (degMode != ConfigurationManager.AppSettings["InputFile1_NoDegradationMode"]) { exigence.DegradationMode.Add(degMode); } } //exigence.DegradationMode.AddRange(degModes.Split('\n')); } else { if (exigence.DegradationMode.FirstOrDefault(d => d == degModes) == null && degModes != ConfigurationManager.AppSettings["InputFile1_NoDegradationMode"]) { exigence.DegradationMode.Add(degModes); } } } var voyant = rowDiag.Cells[colGIndex].StringCellValue; if (!string.IsNullOrEmpty(voyant)) { exigence.G1 = (voyant == "G1"); exigence.G2 = (voyant == "G2"); //rowDiag.Cells[colG2Index].StringCellValue; //exigence.GEE = (voyant == "GEE"); //rowDiag.Cells[colGEEIndex].StringCellValue; } if (colGEEIndex != -1) { var gee = rowDiag.Cells[colGEEIndex].StringCellValue; exigence.GEE = gee.ToUpper().Contains("BATTWARNREQ"); } if (isNewExicence) { results.Add(exigence); } } catch (Exception ex) { Logger.Log(ex, string.Format("Error extracting exigence details from file {0} at line {1}", File1_Path, rowDiag.RowNum)); continue; } } } catch (Exception ex) { Logger.Log(ex); } return(results); }
public static Failure GetFailureByName(string name, string sheetActivate, string sheetDeactivate, string sheetDiagEna, string sheetInitialConditions) { Failure result = null; try { result = new Failure(name); var filePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\" + ConfigurationManager.AppSettings["InputFile2_Name"]; var rowsFailureDetails = ExcelUtils.GetRowsByColumnIndexAndCellValue(filePath, sheetActivate, 0, name); var rowsFailureDetails_Deactivate = ExcelUtils.GetRowsByColumnIndexAndCellValue(filePath, sheetDeactivate, 0, name); var rowsFailureDetails_DiagEna = ExcelUtils.GetRowsByColumnIndexAndCellValue(filePath, sheetDiagEna, 0, name); var rowsFailureDetails_InitialConditions = ExcelUtils.GetRowsByColumnIndexAndCellValue(filePath, sheetInitialConditions, 0, name); #region rowsFailureDetails processing if (rowsFailureDetails != null && rowsFailureDetails.Count > 0) { foreach (var row in rowsFailureDetails) { var detail = new FailureDetail(); detail.ToRealize = row.Cells[1].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[1].StringCellValue : row.Cells[1].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[1].NumericCellValue.ToString() : ""; detail.Step = row.Cells[2].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[2].StringCellValue : row.Cells[2].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[2].NumericCellValue.ToString() : ""; detail.CANFrame = row.Cells[3].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[3].StringCellValue : row.Cells[3].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[3].NumericCellValue.ToString() : ""; detail.CANMessage = row.Cells[4].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[4].StringCellValue : row.Cells[4].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[4].NumericCellValue.ToString() : ""; detail.ValueToBeGiven = row.Cells[5].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[5].StringCellValue : row.Cells[5].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[5].NumericCellValue.ToString() : ""; detail.VariableToCheck = row.Cells[6].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[6].StringCellValue : row.Cells[6].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[6].NumericCellValue.ToString() : ""; detail.ValueToBeChecked = row.Cells[7].CellType == NPOI.SS.UserModel.CellType.String ? row.Cells[7].StringCellValue : row.Cells[7].CellType == NPOI.SS.UserModel.CellType.Numeric ? row.Cells[7].NumericCellValue.ToString() : ""; //add detail to parent result.FailureDetails_Activate.Add(detail); } } #endregion #region rowsFailureDetails_Deactivate processing if (rowsFailureDetails_Deactivate != null && rowsFailureDetails_Deactivate.Count > 0) { foreach (var row in rowsFailureDetails_Deactivate) { var detail = new FailureDetail(); detail.ToRealize = row.Cells[1].CellType == CellType.String ? row.Cells[1].StringCellValue : row.Cells[1].CellType == CellType.Numeric ? row.Cells[1].NumericCellValue.ToString() : ""; detail.Step = row.Cells[2].CellType == CellType.String ? row.Cells[2].StringCellValue : row.Cells[2].CellType == CellType.Numeric ? row.Cells[2].NumericCellValue.ToString() : ""; detail.CANFrame = row.Cells[3].CellType == CellType.String ? row.Cells[3].StringCellValue : row.Cells[3].CellType == CellType.Numeric ? row.Cells[3].NumericCellValue.ToString() : ""; detail.CANMessage = row.Cells[4].CellType == CellType.String ? row.Cells[4].StringCellValue : row.Cells[4].CellType == CellType.Numeric ? row.Cells[4].NumericCellValue.ToString() : ""; detail.ValueToBeGiven = row.Cells[5].CellType == CellType.String ? row.Cells[5].StringCellValue : row.Cells[5].CellType == CellType.Numeric ? row.Cells[5].NumericCellValue.ToString() : ""; detail.VariableToCheck = row.Cells[6].CellType == CellType.String ? row.Cells[6].StringCellValue : row.Cells[6].CellType == CellType.Numeric ? row.Cells[6].NumericCellValue.ToString() : ""; detail.ValueToBeChecked = row.Cells[7].CellType == CellType.String ? row.Cells[7].StringCellValue : row.Cells[7].CellType == CellType.Numeric ? row.Cells[7].NumericCellValue.ToString() : ""; //add detail to parent result.FailureDetails_Deactivate.Add(detail); } } #endregion #region rowsFailureDetails_DiagEna processing if (rowsFailureDetails_DiagEna != null && rowsFailureDetails_DiagEna.Count > 0) { foreach (var row in rowsFailureDetails_DiagEna) { var detail = new FailureDetail(); detail.ToRealize = row.Cells[1].CellType == CellType.String ? row.Cells[1].StringCellValue : row.Cells[1].CellType == CellType.Numeric ? row.Cells[1].NumericCellValue.ToString() : ""; detail.Step = row.Cells[2].CellType == CellType.String ? row.Cells[2].StringCellValue : row.Cells[2].CellType == CellType.Numeric ? row.Cells[2].NumericCellValue.ToString() : ""; detail.CANFrame = row.Cells[3].CellType == CellType.String ? row.Cells[3].StringCellValue : row.Cells[3].CellType == CellType.Numeric ? row.Cells[3].NumericCellValue.ToString() : ""; detail.CANMessage = row.Cells[4].CellType == CellType.String ? row.Cells[4].StringCellValue : row.Cells[4].CellType == CellType.Numeric ? row.Cells[4].NumericCellValue.ToString() : ""; detail.ValueToBeGiven = row.Cells[5].CellType == CellType.String ? row.Cells[5].StringCellValue : row.Cells[5].CellType == CellType.Numeric ? row.Cells[5].NumericCellValue.ToString() : ""; detail.VariableToCheck = row.Cells[6].CellType == CellType.String ? row.Cells[6].StringCellValue : row.Cells[6].CellType == CellType.Numeric ? row.Cells[6].NumericCellValue.ToString() : ""; detail.ValueToBeChecked = row.Cells[7].CellType == CellType.String ? row.Cells[7].StringCellValue : row.Cells[7].CellType == CellType.Numeric ? row.Cells[7].NumericCellValue.ToString() : row.Cells[7].CellType == CellType.Boolean ? row.Cells[7].BooleanCellValue.ToString() : ""; //add detail to parent result.FailureDetails_DiagEna.Add(detail); } } #endregion #region rowsFailureDetails_InitialConditions processing if (rowsFailureDetails_InitialConditions != null && rowsFailureDetails_InitialConditions.Count > 0) { foreach (var row in rowsFailureDetails_InitialConditions) { var detail = new FailureDetail(); detail.ToRealize = row.Cells[1].CellType == CellType.String ? row.Cells[1].StringCellValue : row.Cells[1].CellType == CellType.Numeric ? row.Cells[1].NumericCellValue.ToString() : ""; detail.Step = row.Cells[2].CellType == CellType.String ? row.Cells[2].StringCellValue : row.Cells[2].CellType == CellType.Numeric ? row.Cells[2].NumericCellValue.ToString() : ""; detail.CANFrame = row.Cells[3].CellType == CellType.String ? row.Cells[3].StringCellValue : row.Cells[3].CellType == CellType.Numeric ? row.Cells[3].NumericCellValue.ToString() : ""; detail.CANMessage = row.Cells[4].CellType == CellType.String ? row.Cells[4].StringCellValue : row.Cells[4].CellType == CellType.Numeric ? row.Cells[4].NumericCellValue.ToString() : ""; detail.ValueToBeGiven = row.Cells[5].CellType == CellType.String ? row.Cells[5].StringCellValue : row.Cells[5].CellType == CellType.Numeric ? row.Cells[5].NumericCellValue.ToString() : ""; detail.VariableToCheck = row.Cells[6].CellType == CellType.String ? row.Cells[6].StringCellValue : row.Cells[6].CellType == CellType.Numeric ? row.Cells[6].NumericCellValue.ToString() : ""; detail.ValueToBeChecked = row.Cells[7].CellType == CellType.String ? row.Cells[7].StringCellValue : row.Cells[7].CellType == CellType.Numeric ? row.Cells[7].NumericCellValue.ToString() : row.Cells[7].CellType == CellType.Boolean ? row.Cells[7].BooleanCellValue.ToString() : ""; //add detail to parent result.FailureDetails_InitialConditions.Add(detail); } } #endregion } catch (Exception ex) { Logger.Log(ex, string.Format("Error reading failure '{0}' details from file '{1}'.", name, ConfigurationManager.AppSettings["InputFile2_Name"])); } return(result); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { ReportProgress("***************************************************************************", true, true); ReportProgress("Start processing file '" + FileSdF_Path, true, true); int exigencesColIndex = 0; // ((KeyValuePair<int, string>)cbApplications.SelectedItem).Key; string selectedExigenceText = ""; // ((KeyValuePair<int, string>)cbApplications.SelectedItem).Value; MethodInvoker mi = delegate { exigencesColIndex = ((KeyValuePair <int, string>)cbApplications.SelectedItem).Key; selectedExigenceText = ((KeyValuePair <int, string>)cbApplications.SelectedItem).Value; }; if (this.InvokeRequired) { this.Invoke(mi); } ReportProgress("Selected exigence: " + exigencesColIndex + " - " + selectedExigenceText, true, true); ReportProgress("***************************************************************************", true, true); ReportProgress("Start extracting exigences from input file.", true, true); var sheetName = ConfigurationManager.AppSettings["InputFile1_SheetName"]; var exigences = AppManager.GetExigences(FileSdF_Path, sheetName, exigencesColIndex); // exicencesColIndex + nb of blank columns at the start of the file ReportProgress("End extracting exigences from input file.", true, true); ReportProgress("***************************************************************************", true, true); if (exigences == null) { ReportProgress("No exigences found in your input file. Please check application log for details.", true, false); MessageBox.Show("No exigences found in your input file. Please check application log for details."); return; } #region Create the output file based on the template var appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var templateFilePath = appPath + "\\" + ConfigurationManager.AppSettings["OutputFile_Template"]; var outputFilePath = appPath + "\\" + "Output_" + Logger.ExecutionTimeString + ".xlsx"; File.Copy(templateFilePath, outputFilePath); // remove template lines from output file (let only the header) & copy sheet with gasoline/diesel string sheetToDelete = rdGasoline.Checked ? ConfigurationManager.AppSettings["InputFile2_SheetName_MDS_Diesel"] : ConfigurationManager.AppSettings["InputFile2_SheetName_MDS_Gazoline"]; ExcelUtils.InitiateOutputFile(outputFilePath, sheetToDelete); #endregion var noDegrdationModeExigences = new StringBuilder(); var noProcedureAvailableExigences = new StringBuilder(); var excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelApplication.Application.DisplayAlerts = false; int noDegradationModeCount = 0; int noProcedureAvailableCount = 0; int noExigencesProcessed = 0; int noItemProcessed = 0; #region Process exigences foreach (var exigence in exigences) { if (backgroundWorker1.CancellationPending == true) { // close the excelApplication instance ReportProgress("The process has been cancelled by the user!", true, true, true); excelApplication.Quit(); DisplayProcessingSummary(exigences.Count, noDegradationModeCount, noProcedureAvailableCount, noExigencesProcessed); Process.Start(outputFilePath); // re-enable the button 'New' mi = delegate { btnNew.Enabled = true; }; if (this.InvokeRequired) { this.Invoke(mi); } // cancel and exit worker e.Cancel = true; return; } noItemProcessed++; backgroundWorker1.ReportProgress(noItemProcessed * 100 / exigences.Count); if (exigence.DegradationMode.Count == 1 && exigence.DegradationMode[0] == ConfigurationManager.AppSettings["InputFile1_NoDegradationMode"]) { noDegradationModeCount++; noDegrdationModeExigences.AppendLine(exigence.FailureName); //continue; } var failure = AppManager.GetFailureByName(exigence.FailureName, ConfigurationManager.AppSettings["InputFile2_SheetName_Activate"], ConfigurationManager.AppSettings["InputFile2_SheetName_Deactivate"], ConfigurationManager.AppSettings["InputFile2_SheetName_DiagEna"], ConfigurationManager.AppSettings["InputFile2_SheetName_InitialConditions"]); if (failure.FailureDetails_Activate.Count == 0 || (failure.FailureDetails_Activate.Count == 1 && failure.FailureDetails_Activate[0].Step == "")) { noProcedureAvailableCount++; noProcedureAvailableExigences.AppendLine(exigence.FailureName); continue; } ReportProgress(string.Format("Failure details: {0}, {1}", failure.Chapter, failure.FailureDetails_Activate.Count), false, true); #region read homologations Homologation homologation = null; if (chKHomologation.Checked) { homologation = AppManager.GetHomologation(FileHomologation_Path, ConfigurationManager.AppSettings["InputFile3_SheetName"], failure.Chapter); } #endregion ReportProgress("Start processing failure " + failure.Chapter, true, false); AppManager.ProcessFailure(excelApplication, templateFilePath, outputFilePath, exigence, failure, homologation, noExigencesProcessed); noExigencesProcessed++; ReportProgress("End processing failure. Remaining items to be processed: " + (exigences.Count - noItemProcessed), true, false); } #endregion // finalise processing - add end line to output ExcelUtils.FinishOutputFile(outputFilePath); // close the excelApplication instance excelApplication.Quit(); ReportProgress("End processing.", true, true); DisplayProcessingSummary(exigences.Count, noDegradationModeCount, noProcedureAvailableCount, noExigencesProcessed); if (noDegrdationModeExigences.Length > 0) { Logger.Log(string.Format("The following failures do not trigger any degraded mode: {0} {1}", Environment.NewLine, noDegrdationModeExigences.ToString())); } if (noProcedureAvailableExigences.Length > 0) { Logger.Log(string.Format("The following failures do not have any procedure available: {0} {1}", Environment.NewLine, noProcedureAvailableExigences.ToString())); } // open the output excel file Process.Start(outputFilePath); // re-enable the button 'New' mi = delegate { btnNew.Enabled = true; btnCancel.Enabled = false; }; if (this.InvokeRequired) { this.Invoke(mi); } }