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