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