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