public async Task SyncNewRowsToKimai(IKimaiServices service, IList <KimaiDotNet.Models.TimesheetCollection> timesheets) { try { //find a row with no id to post for (int i = timesheets.Count; i < 10000; i++) { dynamic id = ((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.IdColumnIndex]).Value2; dynamic oADate = ((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.DateColumnIndex]).Value2; if (id is null && (oADate is int || oADate is double)) { int duration = (int)((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.DurationColumnIndex]).Value2; string customerName = (string)((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.CustomerColumnIndex]).Value2; int customerId = Globals.ThisAddIn.GetCustomerByName(customerName).Id.Value; string projectName = (string)((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.ProjectColumnIndex]).Value2; int projectId = Globals.ThisAddIn.GetProjectByName(projectName, customerId).Id.Value; string activityName = (string)((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.ActivityColumnIndex]).Value2; int activityId = Globals.ThisAddIn.GetActivityByName(activityName, projectId).Id.Value; string description = (string)((Range)Worksheet.Cells[i, ExcelAddin.Constants.Sheet1.DescColumnIndex]).Value2; if (oADate is Double)//this is really probably an OADate // https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=net-5.0 { DateTime date = DateTime.FromOADate(oADate).Date; //make sure any time is ignored, it shouldn't be there int addMinutes = GetNextAvailableTimeInMinutes(date); var timesheet = await service.PostTimesheet(new KimaiDotNet.Models.TimesheetEditForm() { Project = projectId, Activity = activityId, Begin = date.AddMinutes(addMinutes).ToUniversalTime(), End = date.AddMinutes(addMinutes).AddMinutes(duration).ToUniversalTime(), User = Globals.ThisAddIn.CurrentUser.Id.Value, Description = description, }).ConfigureAwait(false); Globals.ThisAddIn.Timesheets.Add(new KimaiDotNet.Models.TimesheetCollection() { Id = timesheet.Id, Begin = timesheet.Begin, End = timesheet.End, Activity = timesheet.Activity, Project = timesheet.Project, Duration = duration, User = timesheet.User, Description = timesheet.Description, Tags = timesheet.Tags, }); UpdateNewTimesheetRecordAfterServerSync(i, timesheet); } else { break; } } } } catch (Exception ex) { MessageBox.Show(ex.Message); ExcelAddin.Globals.ThisAddIn.Logger.LogWarning("Failed to sync new rows to kimai", ex); } }
public async Task SyncTimesheetsAsync() { var mockWorksheet = Globals.ThisAddIn.Application.Worksheets.Cast <Worksheet>() .SingleOrDefault(w => string.Equals(w.Name, "Mock", StringComparison.OrdinalIgnoreCase)); IKimaiServices services = !(string.Equals(ConfigurationManager.AppSettings["UseMocks"], "true", StringComparison.OrdinalIgnoreCase) || mockWorksheet is Worksheet) ? (IKimaiServices) new KimaiServices() : new MockKimaiServices(); var projects = await services.GetProjects().ConfigureAwait(false); Globals.ThisAddIn.Projects = projects.ToList(); Sheets.ProjectWorksheet.Instance.CreateOrUpdateProjectsOnSheet(projects); var customers = await services.GetCustomers().ConfigureAwait(false); Globals.ThisAddIn.Customers = customers.ToList(); Sheets.CustomerWorksheet.Instance.CreateOrUpdateCustomersOnSheet(customers); var activities = await services.GetActivities().ConfigureAwait(false); Globals.ThisAddIn.Activities = activities.ToList(); Sheets.ActivityWorksheet.Instance.CreateOrUpdateActivitiesOnSheet(activities); // https://docs.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-display-a-string-in-a-worksheet-cell?view=vs-2019 // https://stackoverflow.com/questions/856196/vsto-write-to-a-cell-in-excel // https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/automate-excel-using-visual-c-fill-data Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; if (Globals.ThisAddIn.Timesheets != null) { await Sheets.Sheet1.Instance.SyncNewRowsToKimai(services, Globals.ThisAddIn.Timesheets).ConfigureAwait(false); } var timesheets = await services.GetTimesheets().ConfigureAwait(false); Globals.ThisAddIn.Timesheets = timesheets.ToList(); // https://social.msdn.microsoft.com/Forums/vstudio/en-US/f89fe6b3-68c0-4a98-9522-953cc5befb34/how-to-make-a-excel-cell-readonly-by-c-code?forum=vsto sheet.Unprotect(); Globals.ThisAddIn.Application.Cells.Locked = false; sheet.Change -= new Microsoft.Office.Interop.Excel. DocEvents_ChangeEventHandler(changesRange_Change); Sheets.Sheet1.Instance.SetupTimesheetsHeaderRow(); Sheets.Sheet1.Instance.WriteTimesheetRows(timesheets); Sheets.KimaiWorksheet.Instance.SetSyncDate(); //https://stackoverflow.com/questions/2414591/how-to-create-validation-from-name-range-on-another-worksheet-in-excel-using-c //https://docs.microsoft.com/en-us/visualstudio/vsto/how-to-add-namedrange-controls-to-worksheets?view=vs-2019 //https://stackoverflow.com/questions/10373561/convert-a-number-to-a-letter-in-c-sharp-for-use-in-microsoft-excel //https://stackoverflow.com/a/10373827 AddDataValidationToColumnByRange(ExcelAddin.Constants.CustomersSheet.CustomersSheetName, ExcelAddin.Constants.CustomersSheet.NameColumnIndex, ExcelAddin.Constants.Sheet1.CustomerColumnIndex); sheet.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(changesRange_Change); var activitiesFlatList = string.Join(ExcelAddin.Constants.FlatListDelimiter, activities.Select(i => i.Name)); AddDataValidationToColumnWithFlatList(activitiesFlatList, ExcelAddin.Constants.Sheet1.ActivityColumnIndex); sheet.Unprotect(); // https://social.msdn.microsoft.com/Forums/vstudio/en-US/f89fe6b3-68c0-4a98-9522-953cc5befb34/how-to-make-a-excel-cell-readonly-by-c-code?forum=vsto if (Globals.ThisAddIn.Application.Cells.Locked is bool && (bool)Globals.ThisAddIn.Application.Cells.Locked) { Globals.ThisAddIn.Application.Cells.Locked = false; } Globals.ThisAddIn.Application.get_Range("A1", $"I{timesheets.Count + 1}").Locked = true; sheet.Protect(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); }