1

I am using office 365 and Excel online (Build 16.0.9403.1875).

and I am creating Microsoft Excel online Add-ins, using Excel javascript API.

How to find the dirty cell/cells from excel sheet using Excel Javascript API.

If a cell is edited by value / formula / format, that became dirty. So I need to find, what are all the cells are dirty(edited) from range of cells.

For Reference, please find calculate method in this link.

feetwet
  • 3,248
  • 7
  • 46
  • 84
Raghu
  • 49
  • 3

2 Answers2

0

There is no Excel JavaScript API that will return the dirty cells, but it's a good idea. Please suggest it at Office Developer Voice.

Rick Kirkham
  • 9,038
  • 1
  • 14
  • 32
0

Since Excel doesn't natively have a dirty flag, you'll need to produce one via one of two methods:

Catch the cell edit event and record a list of edited cells that are dirty.

Dim Dirty As New Collection

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim sheet As Worksheet
    Set sheet = Sh
    Dirty.Add (sheet.Name + "!" + Target.Address)
End Sub

OR

When you need the dirty flag, open the saved copy behind the scenes and compare the two to see what has changed.

Denise Skidmore
  • 2,286
  • 22
  • 51
  • Thanks for your reply, I just have an option to use only JavaScript. The second option is possible, but have to check all (value / formula / any format) for each cell to decide. – Raghu Jun 13 '18 at 07:40
  • Sorry, missed the Javascript API note. Can you catch the sheet/workbook events there? – Denise Skidmore Jun 13 '18 at 14:42
  • From the link you provided in the question, looks like you do have access to the onChanged event. – Denise Skidmore Jun 13 '18 at 14:43
  • https://dev.office.com/reference/add-ins/excel/worksheetchangedeventargs The syntax will be different in Java, but the algorithm provided should work. You might store a more complex object with the address and worksheet id rather than the concatenated string address. – Denise Skidmore Jun 13 '18 at 14:45
  • Thank you for your reply. - In what way this event object will help us to find the dirty cell?. - Event Object properties are very minimal and those properties of no use on my context. - as you mentioned, I collect cell information which are complex object and i should find the pristine cell(untouched cell), so i will just ignore it to reduce my object(data) size. – Raghu Jun 15 '18 at 05:37
  • If the event is fired, than the address within the event details is a cell becoming dirty. Keep a list of those addresses and you'll know all dirty cells. – Denise Skidmore Jun 15 '18 at 15:20
  • If you only care about specific cells and don't want to have a list of all dirty cells, you could use https://dev.office.com/reference/add-ins/excel/bindingdatachangedeventargs on the range object for that cell. – Denise Skidmore Jun 15 '18 at 15:24
  • Fine, What happens, if i copy and paste content from another excel sheet? How do i find and gather list of dirty and pristine(pure,untouched) cells? – Raghu Jun 21 '18 at 15:29
  • For Ref: https://stackoverflow.com/questions/39753324/how-to-identify-the-changed-cell-in-office-js-api-bindingdatachanged-event – Raghu Jun 21 '18 at 15:36