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