public ReportingItem(ReportingItem itemToCopy)
{
Project = itemToCopy.Project;
Date = itemToCopy.Date;
Reporter = itemToCopy.Reporter;
Category = itemToCopy.Category;
}
public static void ExtractTaskOutOfDescription(ReportingItem reportingItem)
{
if (reportingItem.Description != null)
{
//string[] taskIds = new string[] { "Story", "Task", "Test Case", "US", "User Story", "Bug", "Defect" };
string description = reportingItem.Description.Trim();
//bool isTaskCouldBeDefined = taskIds.Any(s => description.StartsWith(s));
//if (isTaskCouldBeDefined)
//{
char[] arrayOfSeparators = { ' ', ':' };
int index = description.IndexOf(':');
if (index > 0)
{
if (digits.Contains(description[index - 1]) ||
arrayOfSeparators.Contains(description[index - 1]) && index > 1 && digits.Contains(description[index - 2]))
{
reportingItem.Task = description.Substring(0, index).Trim();
reportingItem.Description = description.Substring(index + 1).Trim();
}
}
//}
}
}
public static void ExtractHoursAtTheEndingOfString(string taskComment, ReportingItem toUpdateByParsing)
{
int length = taskComment.Length;
int index;
string timeToParse = "";
char[] digits = new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
char[] allowedForDoubleValue = new char [] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.', ',' };
if (length > 0)
{
for (index = length - 1; index > -1; index--)
{
if (!allowedForDoubleValue.Contains(taskComment[index]))
{
break;
}
else
{
timeToParse = taskComment[index] + timeToParse;
}
}
if (index < 0 || index + 1 >= length)
{
toUpdateByParsing.Hours = null;
return;
}
bool containsAnyDigit = timeToParse.IndexOfAny(digits) > -1;
if (containsAnyDigit)
{
double parsedValue = double.Parse(timeToParse, CultureInfo.InvariantCulture);
toUpdateByParsing.Hours = parsedValue;
toUpdateByParsing.Description = taskComment.Substring(0, index).TrimEnd(':', '-').TrimEnd();
}
else
{
toUpdateByParsing.Description = taskComment;
toUpdateByParsing.Hours = null;
}
}
else
{
toUpdateByParsing.Description = null;
toUpdateByParsing.Hours = null;
}
}
public static List <ReportingItem> ParseComment(string comment, ReportingItem baseReportingItem)
{
//Story 123: lorem ipsum
//Story - 123: lorem ipsum
//Task 123: lorem ipsum
//Task - 123: lorem ipsum
//Test Case 123: lorem ipsum
//Test Case-123: lorem ipsum
//Test Case 22234: lorem ipsum
//US 123: lorem ipsum
//US - 123: lorem ipsum
//User Story 22566: lorem ipsum
//Bug 22565: lorem ipsum
//Task 22777: lorem ipsum
//Defect 456: lorem ipsum
//Defect - 456: lorem ipsum
//Bug 456: lorem ipsum
//Bug - 456: lorem ipsum
comment = comment.Trim();
int length = comment.Length;
List <ReportingItem> result = new List <ReportingItem>();
string[] stringSeparators = { "\n\r", "\n" };
int last_match_index = 0;
List <string> tasks = new List <string>();
for (int i = 0; i < length; i++)
{
foreach (var separator in stringSeparators)
{
string commentSubracted = "";
if (i + separator.Length <= length)
{
string s = comment.Substring(i, separator.Length);
if (s.Equals(separator, StringComparison.InvariantCultureIgnoreCase))
{
commentSubracted = comment.Substring(last_match_index, i - last_match_index);
commentSubracted = commentSubracted.Trim().TrimStart('\n', '\r');
last_match_index = i + separator.Length;
if (commentSubracted.Length > 0)
{
tasks.Add(commentSubracted);
}
}
}
else
{
commentSubracted = comment.Substring(last_match_index);
commentSubracted = commentSubracted.Trim().TrimStart('\n', '\r');
if (commentSubracted.Length > 0)
{
tasks.Add(commentSubracted);
}
}
}
}
if (!tasks.Any())
{
var reportingItemToAdd = new ReportingItem(baseReportingItem);
if (comment != null)
{
ExtractActivitiesFromPartOfComment(comment, reportingItemToAdd);
if (reportingItemToAdd.Description != null)
{
ExtractTaskOutOfDescription(reportingItemToAdd);
}
result.Add(reportingItemToAdd);
}
}
foreach (var task in tasks)
{
var reportingItemToAdd = new ReportingItem(baseReportingItem);
reportingItemToAdd.Hours = null;
if (task != null)
{
ExtractActivitiesFromPartOfComment(task, reportingItemToAdd);
if (reportingItemToAdd.Description != null)
{
ExtractTaskOutOfDescription(reportingItemToAdd);
}
result.Add(reportingItemToAdd);
}
}
return(result);
}
private static void ExtractActivitiesFromPartOfComment(string commentToParse, ReportingItem itemToUpdate)
{
string[] timeMarkers = { "h", "hour", "hours" };
commentToParse = commentToParse.TrimEnd(' ', '\r', '\n');
foreach (string timeMarker in timeMarkers)
{
if (commentToParse.EndsWith(timeMarker))
{
commentToParse = commentToParse.Remove(commentToParse.Length - timeMarker.Length).TrimEnd();
itemToUpdate.Description = commentToParse;
ExtractHoursAtTheEndingOfString(itemToUpdate.Description, itemToUpdate);
return;
}
}
itemToUpdate.Description = commentToParse;
return;
}
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();
}