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