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