//public Int32 DeterminePollTime() //{ // Int32 UpperSecondBound = 10; // Int32 LowerSecondBound = 1; // GlobalFunctions.DecayingQueue<Int32> newQueue = new GlobalFunctions.DecayingQueue<Int32>(updateCount.DecayMilliseconds); // Int32 ucCount = updateCount.queue.Count; // Int32 UpperPointBound = Math.Max(1, ucCount * 60); // Int32 AllPoints = 0; // Tuple<Int32, DateTime> queueObj = updateCount.Dequeue(); // DateTime referenceTime = DateTime.Now.AddMilliseconds(-1 * updateCount.DecayMilliseconds); // while (queueObj != null) // { // DateTime updateTime = queueObj.Item2; // Int32 pointVal = (Int32)Math.Round((updateTime - referenceTime).TotalSeconds); // AllPoints += Math.Max(pointVal, 0); // Tuple<Int32, DateTime> newQueueObj = new Tuple<Int32, DateTime>(1, updateTime); // newQueue.Enqueue(newQueueObj); // queueObj = updateCount.Dequeue(); // } // Double thisPointVal = (Double)(UpperPointBound - AllPoints) / (Double)UpperPointBound; // Int32 AddToLowerBound = (Int32)Math.Round(thisPointVal * (UpperSecondBound - LowerSecondBound), 0, MidpointRounding.AwayFromZero); // updateCount = newQueue; // return LowerSecondBound + AddToLowerBound; //} public Boolean TryConnectSheet() { try { GlobalFunctions.SetActiveWorkBookName(); String channelName; if (CheckConnection(out channelName)) { Globals.Ribbons.Ribbon1.buttonToggleConnect.Checked = true; Globals.Ribbons.Ribbon1.SetRibbonConnected(); Globals.Ribbons.Ribbon1.editBoxChannel.Text = channelName; EnableSync(); return(true); } else { DisableSync(); Globals.Ribbons.Ribbon1.SetRibbonDisconnected(); return(false); } } catch (Exception ex) { try { GlobalFunctions.ErrorLog(ex); DisableSync(); Globals.Ribbons.Ribbon1.SetRibbonDisconnected(); } catch (Exception) { } return(false); } }
private Boolean CheckConnection(out String channelName) { channelName = ""; try { SqlConnection dConn = GlobalFunctions.GetDBConnection(); String qry = "Select * from Channels"; GlobalFunctions.AppendEqualityQueryClause(ref qry, "User_Server"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "Workbook_Name"); SqlCommand cmd = new SqlCommand(qry, dConn); GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@User_Server", System.Environment.MachineName); GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Name", GlobalFunctions.ActiveWBName); //GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Path", ActiveWB.Path); DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels"); foreach (DataRow dr in ShareDT.Rows) { channelName = dr["Channel"].ToString(); channelID = dr["Channel_ID"].ToString(); Vars.UserID = "WORKBOOK_NAME:" + dr["Workbook_Name"].ToString() + ";USER_SERVER=" + dr["User_Server"].ToString() + ";ID:" + dr["ID"].ToString(); return(true); } return(false); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); return(false); } }
public void DisableSync() { try { Application.SheetChange -= Application_SheetChange; pollTimer.Enabled = false; pollTimer.Elapsed -= pollTimer_Elapsed; } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); } }
private List <CellUpdate> GetDBCells() { try { SqlCommand cmd = GetOthersChangesSelect(); GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.DateTime2, "@Change_Time", Vars.LatestUpdateTime.ToShortDateString() + " " + Vars.LatestUpdateTime.TimeOfDay.ToString()); GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@channel_id", channelID); GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Change_Author", Vars.SessionID); DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Changes"); return(ConvertToList(ShareDT)); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } }
//public void ClearNotifications() //{ // Excel.Worksheet ws = null; // try // { // foreach (KeyValuePair<String, WorksheetExt> kvp in GlobalFunctions.worksheetBounds) // { // WorksheetExt wext = kvp.Value; // ws = GlobalFunctions.findWorksheetByName(kvp.Key); // for (int i = 1; i <= wext.LastRow; i++) // { // for (int j = 1; j <= wext.LastCol; j++) // { // if (wext.CellStyles[i, j] != null) // { // CellStyleExt cext = wext.CellStyles[i, j]; // CellUpdate uc = new CellUpdate(i, j, kvp.Key, cext); // GlobalFunctions.ClearCell(uc, ws); // wext.CellStyles[i, j] = null; // } // } // } // } // } // catch (Exception ex) // { // GlobalFunctions.ErrorLog(ex); // } // finally // { // if (ws != null) Marshal.ReleaseComObject(ws); // } //} private void pollTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e) { try { pollTimer.Stop(); Globals.Ribbons.Ribbon1.LabelStatus.Label = "Syncing. . ."; GlobalFunctions.WaitForApplicationReady(); DoWork(); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); } finally { pollTimer.Start(); Globals.Ribbons.Ribbon1.LabelStatus.Label = "Sync Complete. . ."; pollTimer.Interval = MyRefreshTime; } }
private void ThisAddIn_Startup(object sender, System.EventArgs e) { try { GlobalFunctions.ConnectToDB(); // Workbook open event does not run when debugging from visual studop if (System.Diagnostics.Debugger.IsAttached) { TryConnectSheet(); } GlobalFunctions.InfoLog("Session Started ID: " + Vars.SessionID); Vars.LatestUpdateTime = Convert.ToDateTime(getDbTime()).AddSeconds(-5); Application.WorkbookOpen += Application_WorkbookOpen; } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); } }
private List <CellUpdate> ConvertToList(DataTable DT) { Int32 Count = DT.Rows.Count; List <CellUpdate> otherCells = new List <CellUpdate>(Count); try { for (int i = 0; i < Count; i++) { DataRow dr = DT.Rows[i]; CellUpdate newCell = CellUpdate.Create(dr); otherCells.Add(newCell); } return(otherCells); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } }
public static CellUpdate Create(DataRow dr) { try { Int32 iRow = Int32.Parse(dr["Excel_Row"].ToString()); Int32 iCol = Int32.Parse(dr["Excel_Column"].ToString()); String iWorksheet = dr["Worksheet"].ToString(); String ival = dr["Cell_Value"].ToString(); String iType = dr["Change_Type"].ToString(); String iChangeAuthor = dr["Change_Author"].ToString(); DateTime iChangeTime = (DateTime)dr["Change_Time"]; Enums.CellChangeType type = (iType == "Value" ? Enums.CellChangeType.Value : iType == "Comment" ? Enums.CellChangeType.Comment : Enums.CellChangeType.Value); return(new CellUpdate(iRow, iCol, iWorksheet, ival, type, iChangeAuthor, iChangeTime)); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } }
//public CellStyleExt[,] CellStyles; public WorksheetExt(String sheetName) { SheetName = sheetName; Excel.Worksheet ws = null; try { ws = GlobalFunctions.findWorksheetByName(SheetName); LastCol = ws.UsedRange.Columns.Count < 20 ? 40 : GetLastColumn(ws) + 20; LastRow = ws.UsedRange.Rows.Count < 20 ? 40 : GetLastRow(ws) + 20; //CellStyles = new CellStyleExt[LastRow + 1, LastCol + 1]; } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); if (ws != null) { Marshal.ReleaseComObject(ws); } throw; } }
private String getChannelID(String ChannelName) { try { SqlConnection dConn = GlobalFunctions.GetDBConnection(); String qry = "Select * from Channels Order By Channel_ID asc"; SqlCommand cmd = new SqlCommand(qry, dConn); DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels"); Int32 MaxChan = 0; if (ShareDT.Rows.Count > 0) { foreach (DataRow dr in ShareDT.Rows) { String ChannelID = dr["Channel_ID"].ToString(); String rChannelName = dr["Channel"].ToString(); MaxChan = Math.Max(1, Int32.Parse(ChannelID)); if (rChannelName == ChannelName) { return(ChannelID); } } return((MaxChan + 1).ToString()); } else { return(""); } } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } }
public CellUpdate(Excel.Range cell, Enums.CellChangeType type) { Excel.Comment comment = null; try { Row = cell.Row; Col = cell.Column; Worksheet = cell.Worksheet.Name.ToUpper(); this.TypeEnum = type; switch (TypeEnum) { case Enums.CellChangeType.Value: val = cell.Formula == null ? "" : cell.Formula.ToString(); break; case Enums.CellChangeType.Comment: comment = cell.Comment; val = comment == null ? "" : comment.Text(); break; } UpdateWSBounds(); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } finally { if (comment != null) { Marshal.ReleaseComObject(comment); } } }
private Boolean RefreshSheet(CellUpdate uc) { Excel.Worksheet ws = null; Excel.Range thisRange = null; Excel.Comment comment = null; try { ws = GlobalFunctions.findWorksheetByName(uc.Worksheet); if (ws == null) { throw new Exception("Worksheet not found: " + uc.Worksheet); } GlobalFunctions.WaitForApplicationReady(); thisRange = GlobalFunctions.createRange(ws, uc.Row, uc.Col); if (thisRange != null) { CellUpdate oldCell = new CellUpdate(thisRange, uc.TypeEnum); if (!uc.Equals(oldCell)) { switch (uc.TypeEnum) { case Enums.CellChangeType.Value: thisRange.Formula = uc.val; break; case Enums.CellChangeType.Comment: comment = thisRange.Comment; if (comment == null) { thisRange.AddComment(uc.val); } else { if (String.IsNullOrEmpty(uc.val)) { thisRange.ClearComments(); } else { comment.Text(uc.val); } } break; } } GlobalFunctions.InfoLog("Received", uc); //RefreshedCell rc = new RefreshedCell(thisRange, uc, oldCell.val); //RefreshedCell rc = new RefreshedCell(thisRange, uc, ""); Vars.LatestUpdateTime = GlobalFunctions.MaxDate(Vars.LatestUpdateTime, uc.changeTime.AddSeconds(-1)); } else { Marshal.ReleaseComObject(thisRange); } return(true); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex, uc); throw ex; } finally { if (thisRange != null) { Marshal.ReleaseComObject(thisRange); } if (ws != null) { Marshal.ReleaseComObject(ws); } } }