private static void ReadXlsxFileIntoList() { Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started"); StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started"); StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()"); StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Any line containing 'DefaultXX' will be ignored, as will all headers"); string[] worksheetsTypes = { Constants.Duration, Constants.Capped, Constants.Pulse }; List <string> workSheetsNotUsed = new List <string>(); List <string> discardedLines = new List <string>(); List <string> workSheetsUsed = new List <string>(); SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(StaticVariable.InputFile); foreach (string wksheet in worksheetsTypes) { try { SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet]; SpreadsheetGear.IRange cells = worksheet.Cells; workSheetsUsed.Add(wksheet); } catch (Exception) { workSheetsNotUsed.Add(wksheet); } } foreach (string wksheet in workSheetsUsed) { SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet]; SpreadsheetGear.IRange cells = worksheet.Cells; var currentColumn = 0; for (currentColumn = 0; currentColumn < cells.ColumnCount; currentColumn++) { if (cells[0, currentColumn].Text.ToUpper().Equals(Constants.FinalColumnName)) { currentColumn++; break; } } var maximumNumberOfColumns = currentColumn; try { foreach (SpreadsheetGear.IRange row in worksheet.UsedRange.Rows) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < maximumNumberOfColumns; i++) { sb.Append(row[0, i].Value + "\t"); //0.0400 being chopped to 0.04. } string sAdjustSb = sb.ToString().TrimEnd('\t'); if (sAdjustSb.Contains(";") && !DiscardHeaderLine(sAdjustSb)) { discardedLines.Add("- " + sAdjustSb.Substring(0, sAdjustSb.IndexOf('\t'))); } else if (!string.IsNullOrEmpty(sAdjustSb) && !DiscardHeaderLine(sAdjustSb)) { ValidateData.CheckForCommasInLine(sAdjustSb); StaticVariable.InputXlsxFileDetails.Add(ValidateData.CapitaliseWord(sAdjustSb)); } } } catch (Exception e) { StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()"); StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Error in reading in XLSX line into list. Is there any data? "); StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + e.Message); } } workbook.Close(); if (workSheetsNotUsed.Any()) { StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()"); foreach (var entry in workSheetsNotUsed) { StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are not being used. Delete this worksheet"); } } foreach (var entry in workSheetsUsed) { StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are being used. "); } if (discardedLines.Any()) { StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()"); StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Customer destinations discarded."); discardedLines.Sort(); foreach (var entry in discardedLines) { StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry); } } StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList()-- completed"); Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished"); StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished"); }