public static LogErrorToTextFile ( string filePath, string errorMessage ) : void | ||
filePath | string | Full path of error file |
errorMessage | string | Error message to be logged |
리턴 | void |
/// <summary> /// This function reads Excel file and returns dictionary /// </summary> /// <param name="filePath">Full path to Excel file</param> /// <param name="sheetName">Sheet name containing configurations</param> /// <returns>Dictionary of configurations</returns> public static Dictionary <string, string> ReadFromExcel(string filePath, string sheetName) { if (!(string.IsNullOrWhiteSpace(filePath) || string.IsNullOrWhiteSpace(sheetName))) { Dictionary <string, string> myList = new Dictionary <string, string>(); try { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = myDoc.WorkbookPart; IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { throw new ArgumentException("Message: Provided Sheet name is incorrect"); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (Row row in sheetData.Elements <Row>()) { List <string> rowValue = new List <string>(); rowValue = ReadRow(row, workbookPart).ToList(); if (null != rowValue && 0 < rowValue.Count()) // Check count to avoid adding of blank row entry which having meta data { myList.Add(rowValue[0].Trim(), rowValue[1].Trim()); } } } } catch (FileNotFoundException exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message); } catch (Exception exception) { if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1) { ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel"); } ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message); } return(myList); } else { throw new ArgumentException("Message: FilePath or Sheet name missing"); } }
/// <summary> /// Function is used to return all the values that are read from Sheet /// </summary> /// <param name="filePath">File path of the Excel</param> /// <param name="sheetName">Sheet to be read from Excel</param> /// <returns>Two-dimensional list with all values from Sheet</returns> public static Collection <Collection <string> > ReadSheet(string filePath, string sheetName) { Collection <Collection <string> > myList = new Collection <Collection <string> >(); Collection <string> rowList = new Collection <string>(); try { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = myDoc.WorkbookPart; IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { ErrorLogger.DisplayErrorMessage("Provided sheet name is invalid!"); return(myList); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (Row row in sheetData.Elements <Row>()) { rowList = ReadRow(row, workbookPart); if (null != rowList && 0 < rowList.Count()) // Check count to avoid adding of blank row entry which having meta data { myList.Add(rowList); } } } } catch (FileNotFoundException exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message); } catch (Exception exception) { if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1) { ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel"); } ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message); } return(myList); }
/// <summary> /// Function is used to read row values for the provided excel sheet /// </summary> /// <param name="row">Row from the excel sheet</param> /// <param name="workbookPart">WorkbookPart object</param> /// <returns>List of values in row</returns> private static Collection <string> ReadRow(Row row, WorkbookPart workbookPart) { string value = string.Empty; int rowSize = row.Elements().Count(), emptyValue = 0; Collection <string> rowValue = new Collection <string>(); try { foreach (Cell cell in row.Elements <Cell>()) { if (null != cell) { CellValue cellValue = cell.Descendants <CellValue>().FirstOrDefault(); if (null != cellValue) { value = cellValue.Text; if (null != cell.DataType) { switch (cell.DataType.Value) { case CellValues.SharedString: var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); if (null != stringTable) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value, CultureInfo.InvariantCulture)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } else { value = string.Empty; } value = value.Trim(); if (string.IsNullOrWhiteSpace(value)) { emptyValue++; } if (string.Equals("NA", value, StringComparison.OrdinalIgnoreCase)) { value = string.Empty; } rowValue.Add(value); } } } catch (Exception exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception occurred while reading row value: " + exception.Message); } if (emptyValue == rowSize) { return(null); } return(rowValue); }