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