/// <summary> /// Before the workbook is saved, finalize the elapsed time, write the ElapsedTime CustomDocumentProperty /// </summary> /// <param name="Wb"></param> /// <param name="Cancel"></param> public void WorkbookBeforeSaveHandler(Excel.Workbook Wb) { log.Info("WorkbookBeforeSaveHandler - Start"); // Checks that the Workbook is being timed if (!timers.ContainsKey(Wb.FullName)) { return; } // Save the current Workbook FullName as previous name CustomDocumentProperty // We will check this value after the save to update the timers Dictionary if (CustomDocumentPropertiesContain(Wb, PREVFULLNAME)) { // Delete existing CustomDocumentProperty if it exists Wb.CustomDocumentProperties(PREVFULLNAME).Delete(); } // Add a new CustomDocumentProperty saving the current FullName of the workbook Wb.CustomDocumentProperties.Add( Name: PREVFULLNAME, LinkToContent: false, Type: Office.MsoDocProperties.msoPropertyTypeString, Value: Wb.FullName); double elapsedSeconds; // Finalize the elasped time // First treat as a SheetChange and make sure the timer is up to date WorkbookChangeHandler(Wb); elapsedSeconds = timers[Wb.FullName].SaveSession(); // Write the elapsed time to a the CustomDocumentProperty Wb.CustomDocumentProperties(ELAPSEDTIME).Value = elapsedSeconds; log.Info("WorkbookBeforeSaveHandler - Finish"); }
/// <summary> /// Initializes the Workbook timers upon opening or creating a new workbook /// </summary> /// <param name="Wb"></param> private void Application_WorkbookOpenOrNew(Excel.Workbook Wb) { try { log.Info(String.Format("Application_WorkbookOpenOrNew: <{0}>", Wb.Name)); // Check if the workbook name is blacklisted (or in XLSTART) if (blacklist.Contains(Wb.Name.ToLower()) || Wb.Path == Application.StartupPath) { return; } double elapsedMinutes; // Try to get the stored elapsed time for this Workbook, or initialize it to zero if it doesn't exist if (CustomDocumentPropertiesContain(Wb, ELAPSEDTIME)) { elapsedMinutes = Wb.CustomDocumentProperties(ELAPSEDTIME).Value; } else { // The CustomDocumentProperty does not exist, so created it and initialize to zero elapsedMinutes = 0; Wb.CustomDocumentProperties.Add( Name: ELAPSEDTIME, LinkToContent: false, Type: Office.MsoDocProperties.msoPropertyTypeNumber, Value: elapsedMinutes); } // Create a new WorkbookTimers instance for the opened Workbook if (!timers.ContainsKey(Wb.FullName)) { timers.Add(Wb.FullName, new WorkbookTimers(elapsedMinutes)); } else { // The timer for this Workbook already existed. This should never happen, but if so, just overwrite previous timer. timers.Remove(Wb.FullName); timers.Add(Wb.FullName, new WorkbookTimers(elapsedMinutes)); } log.Info("Application_WorkbookOpenOrNew - Finish"); } catch (Exception ex) { log.Error(ex); } }
public void WorkbookAfterSaveHandler(Excel.Workbook Wb, bool success) { log.Info("WorkbookAfterSaveHandler - Start"); if (!CustomDocumentPropertiesContain(Wb, PREVFULLNAME)) { return; } string prevFullName; prevFullName = Wb.CustomDocumentProperties(PREVFULLNAME).Value; // Check if any timer was counting for the file with the previous name // If so, change the Dictionary key to be the new Workbook's FullName if (timers.ContainsKey(prevFullName)) { WorkbookTimers tempWorkbookTimers = timers[prevFullName]; timers.Remove(prevFullName); timers.Add(Wb.FullName, tempWorkbookTimers); } // Delete the PrevFullName CustomDocumentProperty Wb.CustomDocumentProperties(PREVFULLNAME).Delete(); log.Info("WorkbookAfterSaveHandler - Finish"); }