public TimeReport GetTimeReport(string filePath)
{
var table = GetTimeReportTable(GetDataTableCollection(filePath, 11));
TimeReport timeReportResult = new TimeReport();
///Костыль тк мы не знаем
if (!table.Columns.Contains("#"))
{
table = GetTimeReportTable(GetDataTableCollection(filePath, 14));
}
timeReportResult.TimeReportRows = new List <TimeReportRow>();
timeReportResult.SetName(filePath);
foreach (DataRow row in table.Rows)
{
TimeReportRow timeReportRow = new TimeReportRow();
foreach (DataColumn column in table.Columns)
{
PutValueByTheCaptionTimeReport(row, column, timeReportRow);
}
timeReportResult.TimeReportRows.Add(timeReportRow);
}
/// удаляем последнюю строку с Total и все которые не заполнены в полях Task и Description
RemoveEmptyAndTotalRows(timeReportResult);
return(timeReportResult);
}
/// <summary>
/// Очень сильно подумать что можно сделать с файлом TimeReport (получилось сделать
/// </summary>
/// <param name="row"></param>
/// <param name="column"></param>
/// <param name="timeReportRow"></param>
private void PutValueByTheCaptionTimeReport(DataRow row, DataColumn column, TimeReportRow timeReportRow)
{
if (column.Caption.Contains("Project"))
{
timeReportRow.ProjectName = row[column].ToString();
}
if (column.Caption.Contains("Task"))
{
timeReportRow.Task = row[column].ToString();
}
if (column.Caption.Contains("Description"))
{
timeReportRow.Description = row[column].ToString();
}
if (column.Caption.Contains("Regular Labor") || column.Caption.Contains("Regular Time"))
{
decimal.TryParse(row[column].ToString(), out decimal result);
timeReportRow.RegularLabor = result;
}
if (column.Caption.Contains("Overtime Labor") || column.Caption.Contains("Overtime"))
{
decimal.TryParse(row[column].ToString(), out decimal result);
timeReportRow.OvertimeLabor = result;
}
if (column.Caption.Contains("Off hours"))
{
decimal.TryParse(row[column].ToString(), out decimal result);
timeReportRow.OffHours = result;
}
if (column.Caption.Contains("Date"))
{
DateTime.TryParse(row[column].ToString(), out DateTime result);
timeReportRow.Date = result;
}
}
public TimeReport GetTimeReportByProject(List <string> filesInPath, PaymentData paymentData)
{
TimeReport timeReportResult = new TimeReport();
///добавить фильтр по дате из названия файла
var reportFileByProject = filesInPath.Where(x => x.ToLower().Contains(paymentData.ProjectName.Trim().ToLower())).SingleOrDefault();
if (string.IsNullOrEmpty(reportFileByProject))
{
return(null);
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(@reportFileByProject);
// Keeping track
bool found = false;
// Loop through all worksheets in the workbook
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
{
string[] nameOfSheet = sheet.Name.ToString().Split(' ');
string[] employerSplitedName = paymentData.EmployerName.Split(' ');
// Check the name of the current sheet
if (nameOfSheet.Contains(employerSplitedName[0]) && nameOfSheet.Contains(employerSplitedName[1]))
{
found = true;
break; // Exit the loop now
}
}
wb.Close(SaveChanges: false);
if (found)
{
var table = GetDataTableCollectionByEmployeeName(reportFileByProject, paymentData.EmployerName, 11);
///Костыль тк мы не знаем
if (!table.Columns.Contains("#"))
{
table = GetDataTableCollectionByEmployeeName(reportFileByProject, paymentData.EmployerName, 14);
}
timeReportResult.TimeReportRows = new List <TimeReportRow>();
//timeReportResult.SetName(filePath);
foreach (DataRow row in table.Rows)
{
TimeReportRow timeReportRow = new TimeReportRow();
foreach (DataColumn column in table.Columns)
{
PutValueByTheCaptionTimeReport(row, column, timeReportRow);
}
timeReportResult.TimeReportRows.Add(timeReportRow);
}
/// удаляем последнюю строку с Total и все которые не заполнены в полях Task и Description
RemoveEmptyAndTotalRows(timeReportResult);
}
else
{
return(null);
}
timeReportResult.SetNameFromProject(paymentData.EmployerName);
return(timeReportResult);
}
