Finding all changes in the workbook

Aspose.Cells Product Family
1

Hi,


I have formulas in multiple cells in my Workbook/Worksheet.
I want to find out which all cells’ value changed when I updated a value or inserted a row. (In short, I want to find out which all cells’ value changed after an event).
What is the easiest and fastest way to do that? (Comparing for each and every cell will be too slow I believe).

Thanks and Regards,
Yatish
2

Hi,

I guess, this feature is not available.

However, please list down steps using Ms-Excel 2010 to illustrate this feature and help us with screenshots.

We will create a new feature request for this issue.

3

Thanks Shakeel for a very quick response.

I am attaching 2 screenshots for illustration
1) A Normal Sheet with 3 formulas.JPG
2) Changed 3 Cell Values in the sheet.JPG

1st image shows the initial sheet with 3 formulas (marked with red circle).
It can be noticed that when I updated values of 3 cells (marked with green circle in 2nd image), values of 2 cells changed (marked with red circles) whereas for the 3rd one it did not (marked with blue circle.
I quickly want to find out which cells’ value changed after updating values of 3 cells (The diff of 1st and 2nd image). I can do so by a brute force method by comparing each and every cell, but I am looking for some quicker and more elegant solution.

I want a similar functionality for cross-sheet formulas.
I want a similar functionality to check for auto-updated formulas (instead of values) in case of insertion of row/column etc.

But let us take one question at a time and concentrate on the 1st question for now.

Please let me know if you need further information on the stated problem.

Thanks and Regards,
Yatish
4

Hi,

Thanks for illustrating the problem.

Actually, I wanted to confirm that if this feature of finding changes is available in Ms-Excel, if it is, then list down the steps.

Aspose.Cells support the subset of features of Ms-Excel, means if some feature is not available, then it will also be not available in Aspose.Cells.

In case, this feature is not available, then you are looking for an algorithm, you can search for such algorithms on internet or may be make your own.

Algorithm Advice
I think, you can save your worbook in csv format, it will save your file this way
12,34,56
12,4,0
2,3,5

Then after you make change, again save the workbook in csv format, suppose this time you get this
12,34,56
12,1,0
2,3,5

What you will do is that you will read first file and second file line by line as string and compare them.
e.g

Iteration 1
str1 = “12,34,56”;
str2="12,34,56"

if(str1==str2)
skip

Iteration 2
then read next line
str1="12,4,0"
str2="12,1,0"

this time str1 not equal to str2, it means, cell’s values have changed in row 2. so you will note down row index 2

then you will split the str1 and str2 on with comma separator, and find the column index by comparing,

Finally, you will get the change row index and column indices, same process will go for all rows.

Hopefully you got my point.


5

Thanks a lot Shakeel.

By any chance, can I get some handles to events like ‘OnCellChange’ etc, so that these cells can automatically inform me if something was changed?

Thanks and Regards,
Yatish
6

Hi,

I am afraid, there is no such events in Aspose.Cells for .NET.