public override Boolean Equals(Object obj)
{
CellUpdate uCell = obj as CellUpdate;
if (uCell == null)
{
return(false);
}
return(uCell.Row == Row && uCell.Col == Col && uCell.Worksheet == Worksheet && uCell.val == val);
}
public static String GetCellDebug(CellUpdate cs)
{
String ret = String.Format("{0}!({1},{2})", cs.Worksheet, cs.Row, cs.Col);
ret += String.Format(" {0}: {1}", cs.Type, cs.val);
if (cs.changeTime != DateTime.MinValue)
{
ret += " [Change Time: " + cs.changeTime.ToString("dd MMM yyyy HH:mm:ss.fff") + "]";
}
return(ret);
}
private CellUpdate FindWithoutValue(IEnumerable <CellUpdate> searchSet, CellUpdate searchCell)
{
foreach (CellUpdate myNewCell in searchSet)
{
if (myNewCell.Col == searchCell.Col && myNewCell.Row == searchCell.Row && myNewCell.Worksheet == searchCell.Worksheet && myNewCell.Type == searchCell.Type)
{
return(myNewCell);
}
}
return(null);
}
private void CommitUpdates(CellUpdate oneCell)
{
SqlCommand cmd = GetSelectCommand();
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Channel_ID", channelID);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Excel_Row", oneCell.Row);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Excel_Column", oneCell.Col);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Change_Type", oneCell.Type);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Worksheet", oneCell.Worksheet);
using (SqlDataAdapter ShareDataAdapter = new SqlDataAdapter(cmd))
{
DataSet ShareDS = new DataSet();
ShareDataAdapter.Fill(ShareDS);
DataTable ShareDT = ShareDS.Tables[0];
DataColumn[] keyColumns = new DataColumn[4];
keyColumns[0] = ShareDT.Columns["Channel_ID"];
keyColumns[1] = ShareDT.Columns["Worksheet"];
keyColumns[2] = ShareDT.Columns["Excel_Row"];
keyColumns[3] = ShareDT.Columns["Excel_Column"];
ShareDT.PrimaryKey = keyColumns;
SqlCommandBuilder cb = new SqlCommandBuilder(ShareDataAdapter);
DataRow dr = ShareDT.Rows.Find(new object[] { channelID, oneCell.Worksheet, oneCell.Row, oneCell.Col });
if (dr == null)
{
//if (oneCell.val == "")
//{
// // ShareDataAdapter.DeleteCommand = cb.GetDeleteCommand();
// ShareDataAdapter.InsertCommand = GetInsertCommand(cb);
//}
//else
//{
//
//}
ShareDataAdapter.InsertCommand = GetInsertCommand(cb);
Insert(ShareDT, oneCell);
ShareDataAdapter.Update(ShareDT);
GlobalFunctions.InfoLog("Insert", oneCell);
}
else
{
String changeID = dr["ID"].ToString();
Update(changeID, oneCell);
GlobalFunctions.InfoLog("Update", oneCell);
}
}
SpeedUpRefresh();
}
private void Insert(DataTable ShareDT, CellUpdate oneCell)
{
DataRow dr = ShareDT.NewRow();
dr["Channel_ID"] = channelID;
dr["Worksheet"] = oneCell.Worksheet;
dr["Excel_Row"] = oneCell.Row;
dr["Excel_Column"] = oneCell.Col;
dr["Cell_Value"] = oneCell.val;
dr["Change_Author"] = Vars.SessionID;
dr["Change_Time"] = getDbTime();
dr["Change_Type"] = oneCell.Type;
ShareDT.Rows.Add(dr);
}
private void Update(String changeID, CellUpdate oneCell)
{
SqlCommand updateCmd = GetUpdateCommand();
updateCmd.Parameters.Add("@ID", SqlDbType.VarChar, 255);
updateCmd.Parameters["@ID"].Value = changeID;
updateCmd.Parameters.Add("@Cell_Value", SqlDbType.VarChar, 4095); //Increased size for value
updateCmd.Parameters["@Cell_Value"].Value = oneCell.val;
updateCmd.Parameters.Add("@Change_Author", SqlDbType.VarChar, 255);
updateCmd.Parameters["@Change_Author"].Value = Vars.SessionID;
updateCmd.Parameters.Add("@Change_Time", SqlDbType.DateTime);
updateCmd.Parameters["@Change_Time"].Value = getDbTime();
updateCmd.Connection.Open();
updateCmd.ExecuteNonQuery();
updateCmd.Connection.Close();
}
public static void ClearCell(CellUpdate uc, Excel.Worksheet ws)
{
Excel.Range thisRange = null;
try
{
WaitForApplicationReady();
thisRange = createRange(ws, uc.Row, uc.Col);
if (thisRange != null)
{
WaitForApplicationReady();
thisRange.ClearComments();
//dynamic oldFC = null;
//dynamic oldAppliesTo = null;
//if (thisRange.FormatConditions.Count > 0)
//{
// foreach (dynamic fc in thisRange.FormatConditions)
// {
// if (fc.Type != 4)
// {
// oldFC = fc;
// oldAppliesTo = fc.AppliesTo;
// }
// }
// thisRange.FormatConditions.Delete();
// oldFC.ModifyAppliesToRange(oldAppliesTo);
//}
}
}
catch (Exception ex)
{
ErrorLog(ex, uc);
throw;
}
finally
{
if (thisRange != null)
{
Marshal.ReleaseComObject(thisRange);
}
}
}
public static void ErrorLog(Exception incEx, CellUpdate debugObject = null)
{
String oDebug = "";
if (debugObject != null)
{
oDebug = GetCellDebug(debugObject);
}
StringBuilder ExceptionDesc = new StringBuilder();
ExceptionDesc.AppendLine("Exception @ " + DateTime.Now);
if (oDebug != "")
{
ExceptionDesc.AppendLine(oDebug);
}
ExceptionDesc.AppendLine(Vars.SessionID);
ExceptionDesc.AppendLine(incEx.Message + " " + incEx.StackTrace);
Log(ExceptionDesc.ToString(), errorLogFileName);
}
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;
}
}
private HashSet <CellUpdate> GetChangedCells()
{
HashSet <CellUpdate> AllMyCells = CellReader.GetAllCells();
HashSet <CellUpdate> myChanges = new HashSet <CellUpdate>(AllMyCells.Except(MySavedCells));
// Get all cells no longer being used that previously had a value
IEnumerable <CellUpdate> deletedCellsTemp = MySavedCells.Except(AllMyCells);
for (int i = 0; i < deletedCellsTemp.Count(); i++)
{
/* CELL BEFORE */
CellUpdate oldCell = deletedCellsTemp.ElementAt(i);
CellUpdate currCell = FindWithoutValue(AllMyCells, oldCell);
if (currCell == null)
{
CellUpdate newUC = new CellUpdate(oldCell.Row, oldCell.Col, oldCell.Worksheet, "", oldCell.TypeEnum, oldCell.changeAuthor, DateTime.Now);
myChanges.Add(newUC);
}
}
return(myChanges);
}
public static void InfoLog(String message, CellUpdate debugObject = null)
{
if (!EnableInfoLogging)
{
return;
}
StringBuilder infoDesc = new StringBuilder();
infoDesc.AppendLine("Info @ " + DateTime.Now);
infoDesc.AppendLine(message);
String oDebug = "";
if (debugObject != null)
{
oDebug = GetCellDebug(debugObject);
}
if (oDebug != "")
{
infoDesc.AppendLine(oDebug);
}
Log(infoDesc.ToString(), infoLogFileName);
}
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);
}
}
}
private void DoWork()
{
try
{
Application.Calculation = Excel.XlCalculation.xlCalculationManual;
DateTime SyncStart = DateTime.Now;
if (RefreshDelayCountdown == 0)
{
SlowDownRefresh();
}
else
{
if (RefreshDelayCountdown > 0)
{
RefreshDelayCountdown--;
}
}
List <CellUpdate> otherCells = GetDBCells();
HashSet <CellUpdate> myChanges = GetChangedCells();
foreach (CellUpdate myNewCell in myChanges)
{
//GlobalFunctions.worksheetBounds[myNewCell.Worksheet].CellStyles[myNewCell.Row, myNewCell.Col] = new CellStyleExt();
/* Remove if updating a saved cell */
CellUpdate savedCellOld = FindWithoutValue(MySavedCells, myNewCell);
CellUpdate OthersCellConflict = FindWithoutValue(otherCells, myNewCell);
if (OthersCellConflict != null)
{
if (OthersCellConflict.val == "")
{
otherCells.Remove(OthersCellConflict);
/* Other deleted, you win */
}
else
{
/* Both have values, you win */
otherCells.Remove(OthersCellConflict);
}
}
//Save update
CommitUpdates(myNewCell);
if (savedCellOld != null)
{
MySavedCells.Remove(savedCellOld);
}
if (myNewCell.val != "")
{
MySavedCells.Add(myNewCell);
}
try
{
if (string.IsNullOrEmpty(myNewCell.changeAuthor))
{
Globals.Ribbons.Ribbon1.label1.Label = "self";
}
Globals.Ribbons.Ribbon1.label2.Label = myNewCell.Worksheet.ToString() + ':' + myNewCell.Row.ToString() + '/' + myNewCell.Col.ToString();
Globals.Ribbons.Ribbon1.label3.Label = myNewCell.val.ToString();
}
catch (Exception)
{
}
}
foreach (CellUpdate otherNewCell in otherCells)
{
/* Remove if updating a saved cell */
CellUpdate savedCellOld = FindWithoutValue(MySavedCells, otherNewCell);
//Update my sheet
RefreshSheet(otherNewCell);
//GlobalFunctions.InfoLog("Update Received", otherNewCell);
if (savedCellOld != null)
{
MySavedCells.Remove(savedCellOld);
}
if (otherNewCell.val != "")
{
MySavedCells.Add(otherNewCell);
}
try
{
if (string.IsNullOrEmpty(otherNewCell.changeAuthor))
{
Globals.Ribbons.Ribbon1.label1.Label = "unknown!";
}
else
{
Globals.Ribbons.Ribbon1.label1.Label = otherNewCell.changeAuthor.ToString().Substring(1, 10);
}
Globals.Ribbons.Ribbon1.label2.Label = otherNewCell.Worksheet.ToString() + ':' + otherNewCell.Row.ToString() + '/' + otherNewCell.Col.ToString();
Globals.Ribbons.Ribbon1.label3.Label = otherNewCell.val.ToString();
}
catch (Exception)
{
}
}
//DateTime SyncEnd = DateTime.Now;
//TimeSpan SyncDuration = (SyncEnd - SyncStart);
//GlobalFunctions.InfoLog("Sync Duration: " + SyncDuration.TotalSeconds.ToString() + " seconds");
}
finally
{
Application.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
}
}
public static HashSet <CellUpdate> GetAllCells()
{
Excel.Worksheet currSheet = null;
Excel.Sheets sheets = null;
Excel.Workbook activeWb = null;
HashSet <CellUpdate> myCells = new HashSet <CellUpdate>();
//System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
//stopwatch.Start();
try
{
activeWb = GlobalFunctions.FindActiveWorkbook();
sheets = activeWb.Worksheets;
foreach (Excel.Worksheet ws in sheets)
{
currSheet = ws;
String wsName = currSheet.Name.ToUpper();
Int32 LastCol;
Int32 LastRow;
WorksheetExt wsBoundary;
if (GlobalFunctions.worksheetBounds.Keys.Contains(wsName))
{
wsBoundary = GlobalFunctions.worksheetBounds[wsName];
}
else
{
wsBoundary = new WorksheetExt(wsName);
GlobalFunctions.worksheetBounds.Add(wsName, wsBoundary);
}
LastCol = wsBoundary.LastCol;
LastRow = wsBoundary.LastRow;
Excel.Range range = ws.Range["A1", GlobalFunctions.GetExcelColumnName(LastCol) + LastRow];
// FOR LOOP ON VALUES
dynamic formulaArrayD = range.Formula;
GlobalFunctions.WaitForApplicationReady();
Object[,] formulaArray = formulaArrayD as Object[, ];
String[,] commentArray = GetComments(wsName, range, LastRow, LastCol);
if (formulaArray != null)
{
for (int i = 1; i <= LastRow; i++)
{
for (int j = 1; j <= LastCol; j++)
{
if (formulaArray[i, j] != null)
{
if (formulaArray[i, j].ToString() != "")
{
CellUpdate uc = new CellUpdate(i, j, wsName, formulaArray[i, j].ToString(), Enums.CellChangeType.Value, "", DateTime.MinValue);
myCells.Add(uc);
}
}
}
}
}
if (commentArray != null)
{
for (int i = 1; i <= LastRow; i++)
{
for (int j = 1; j <= LastCol; j++)
{
if (commentArray[i, j] != null)
{
if (commentArray[i, j] != "")
{
CellUpdate uc = new CellUpdate(i, j, wsName, commentArray[i, j].ToString(), Enums.CellChangeType.Comment, "", DateTime.MinValue);
myCells.Add(uc);
}
}
}
}
}
if (range != null)
{
Marshal.ReleaseComObject(range);
}
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
}
return(myCells);
}
catch (Exception ex)
{
if (ex.Message == "Incorrect ActiveWB Name")
{
Globals.ThisAddIn.DisableSync();
}
throw;
}
finally
{
if (currSheet != null)
{
Marshal.ReleaseComObject(currSheet);
}
if (sheets != null)
{
Marshal.ReleaseComObject(sheets);
}
if (activeWb != null)
{
Marshal.ReleaseComObject(activeWb);
}
//GlobalFunctions.InfoLog(String.Format("GetAllCells: {0}", stopwatch.Elapsed));
}
}
