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