private void UpdateWSBounds()
{
Dictionary <String, WorksheetExt> worksheetBounds = GlobalFunctions.worksheetBounds;
if (worksheetBounds.Keys.Contains(Worksheet))
{
WorksheetExt wsBoundary = worksheetBounds[Worksheet];
Int32 rowBound = Row + 20;
Int32 colBound = Col + 20;
if ((rowBound) > wsBoundary.LastRow)
{
wsBoundary.UpdateLastRow(rowBound);
}
if ((colBound) > wsBoundary.LastCol)
{
wsBoundary.UpdateLastCol(colBound);
}
}
}
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));
}
}