public ReportingItem(InputExcelRow input)
{
Project = ExtractShortProjectName(input.Project);
//long dateNum = long.Parse(input.Date);
//Date = DateTime.FromOADate(dateNum);
Date = input.Date;
Reporter = Regex.Replace(input.Worker, @" \(.*?\)", "");
Category = input.Task;
input.ReportedHours = input.ReportedHours?.TrimEnd('h', 'H');
double tmp;
double.TryParse(input.ReportedHours, NumberStyles.Any, CultureInfo.InvariantCulture, out tmp);
this.Hours = tmp;
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
//const string inputPath = @"C:\Users\ssurnin\Downloads\OneDrive_1_2-7-2020\Example - input.xlsx";
//const string inputPath = @"D:\Sources\ETL_For_COINS\OneDrive_1_07.02.2020\Example - input.xlsx";
string outputPathDirectory = Directory.GetCurrentDirectory();
string inputPathDirectory = Directory.GetCurrentDirectory();
//const string outputPath = @"D:\Sources\ETL_For_COINS\OneDrive_1_07.02.2020\Output.xlsx";
string[] files = Directory.GetFiles(inputPathDirectory, "Reported_Time_Details*.xlsx");
Console.WriteLine("The number of files with \"*.xlsx\" is {0}.", files.Length);
foreach (string file in files)
{
Console.WriteLine(file);
//read the Excel file as byte array
byte[] bin = File.ReadAllBytes(file);
//create a new Excel package in a memorystream
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
//load worksheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[0];
DateTime startDate = (DateTime)worksheet.Cells[4, 2].Value; //rows[3][1].Cast<DateTime>();
DateTime endDate = (DateTime)worksheet.Cells[5, 2].Value; //rows[4][1].Cast<DateTime>();
//DateTime startDate = Program.GetDatetimeFromCell(worksheet.Cells[4, 2].Value); //rows[3][1].Cast<DateTime>();
//DateTime endDate = Program.GetDatetimeFromCell(worksheet.Cells[5, 2].Value); //rows[4][1].Cast<DateTime>();
if (worksheet != null)
{
int startIndex = -1;
//loop all rows
for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++)
{
if ((string)worksheet.Cells[i, 1].Value == "Project")
{
startIndex = i + 1;
break;
}
}
List <InputExcelRow> readRows = new List <InputExcelRow>();
List <ReportingItem> reportedItems = new List <ReportingItem>();
for (int i = startIndex; i <= worksheet.Dimension.End.Row; i++)
{
string[] row = new string[9];
//loop all columns in a row
for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
{
//add the cell data to the List
if (worksheet.Cells[i, j].Value != null)
{
row[j - 1] = worksheet.Cells[i, j].Value.ToString();
}
}
var input = new InputExcelRow(row);
var reportingItem = new ReportingItem(input);
List <ReportingItem> parsedTasks = new List <ReportingItem>()
{
reportingItem
};
if (input.Comment != null)
{
parsedTasks = ReportingItem.ParseComment(input.Comment, reportingItem);
}
readRows.Add(input);
reportedItems.AddRange(parsedTasks);
}
//print rows here
foreach (var item in reportedItems)
{
string itemToPrint = item.ToString();
Console.WriteLine(itemToPrint);
Console.WriteLine();
Console.WriteLine();
}
string outputPath = outputPathDirectory + "\\" + $"COINS CCCA Teams Timesheets {startDate.ToString("MMM")}'{startDate.ToString("yy")}.xlsx";
ExcelWrite(outputPath, reportedItems, startDate, endDate);
Console.WriteLine($"The output file is '{outputPath}'");
}
}
}
Console.WriteLine("Execution is finished");
Console.ReadKey();
}