{"action":"created","issue":{"url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/524","repository_url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK","labels_url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/524/labels{/name}","comments_url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/524/comments","events_url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/524/events","html_url":"https://github.com/OfficeDev/Open-XML-SDK/issues/524","id":366714361,"node_id":"MDU6SXNzdWUzNjY3MTQzNjE=","number":524,"title":"Detect pending track changes in excel using open xml ","user":{"login":"smithapk","id":43845895,"node_id":"MDQ6VXNlcjQzODQ1ODk1","avatar_url":"https://avatars1.githubusercontent.com/u/43845895?v=4","gravatar_id":"","url":"https://api.github.com/users/smithapk","html_url":"https://github.com/smithapk","followers_url":"https://api.github.com/users/smithapk/followers","following_url":"https://api.github.com/users/smithapk/following{/other_user}","gists_url":"https://api.github.com/users/smithapk/gists{/gist_id}","starred_url":"https://api.github.com/users/smithapk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smithapk/subscriptions","organizations_url":"https://api.github.com/users/smithapk/orgs","repos_url":"https://api.github.com/users/smithapk/repos","events_url":"https://api.github.com/users/smithapk/events{/privacy}","received_events_url":"https://api.github.com/users/smithapk/received_events","type":"User","site_admin":false},"labels":[{"id":746926362,"node_id":"MDU6TGFiZWw3NDY5MjYzNjI=","url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/labels/request:%20feature","name":"request: feature","color":"d93f0b","default":false},{"id":746926366,"node_id":"MDU6TGFiZWw3NDY5MjYzNjY=","url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/labels/stack%20overflow","name":"stack overflow","color":"fbca04","default":false},{"id":746926375,"node_id":"MDU6TGFiZWw3NDY5MjYzNzU=","url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/labels/under%20investigation","name":"under investigation","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":{"login":"tomjebo","id":4644198,"node_id":"MDQ6VXNlcjQ2NDQxOTg=","avatar_url":"https://avatars1.githubusercontent.com/u/4644198?v=4","gravatar_id":"","url":"https://api.github.com/users/tomjebo","html_url":"https://github.com/tomjebo","followers_url":"https://api.github.com/users/tomjebo/followers","following_url":"https://api.github.com/users/tomjebo/following{/other_user}","gists_url":"https://api.github.com/users/tomjebo/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjebo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjebo/subscriptions","organizations_url":"https://api.github.com/users/tomjebo/orgs","repos_url":"https://api.github.com/users/tomjebo/repos","events_url":"https://api.github.com/users/tomjebo/events{/privacy}","received_events_url":"https://api.github.com/users/tomjebo/received_events","type":"User","site_admin":false},"assignees":[{"login":"tomjebo","id":4644198,"node_id":"MDQ6VXNlcjQ2NDQxOTg=","avatar_url":"https://avatars1.githubusercontent.com/u/4644198?v=4","gravatar_id":"","url":"https://api.github.com/users/tomjebo","html_url":"https://github.com/tomjebo","followers_url":"https://api.github.com/users/tomjebo/followers","following_url":"https://api.github.com/users/tomjebo/following{/other_user}","gists_url":"https://api.github.com/users/tomjebo/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjebo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjebo/subscriptions","organizations_url":"https://api.github.com/users/tomjebo/orgs","repos_url":"https://api.github.com/users/tomjebo/repos","events_url":"https://api.github.com/users/tomjebo/events{/privacy}","received_events_url":"https://api.github.com/users/tomjebo/received_events","type":"User","site_admin":false}],"milestone":null,"comments":0,"created_at":"2018-10-04T10:05:36Z","updated_at":"2018-10-05T01:19:24Z","closed_at":null,"author_association":"NONE","body":"# Before submitting an issue, please fill this out\r\n\r\nIs this a:\r\n\r\n- [x] Question on library usage -Yes\r\n\r\n\r\n\r\n**Description**\r\n\r\nRequirement :\r\n\r\n                               Read pending track changes from excel which are not yet accepted or rejected\r\n\r\nProblem Statement:\r\n\r\n                             There is no tag to identify directly whether a track revision is accepted or rejected  in revision log .\r\n                           \r\n\r\nAssessment\r\n          \r\n                               Reached in  below assessment after analyzing  revision log  xml files  and revision headers xml files\r\n       \r\n•\tThere will be revision log corresponding to each revision (eg : revisionLog1.xml,…. revisionLog6.xml)  \r\n         \r\n                                                  This contains revision Id (rId) and cell details(eg: A1) of each revision \r\n        \r\n•\tThere is a  revisionheadrs.xml file \r\nThere exists a revision header corresponding to each  revision log \r\n\r\nBy correlating these two xml files with “rId” value we will get a unique record \r\n\r\nLast child of this record will have  reviewed tag if it is accepted or rejected\r\n\r\n\r\n\r\n                                    But there are some cases  this logic is failing\r\n\r\n\r\n                                             I analyzed the xml files of failing excel sheet\r\n                                              \r\n•\tIf there are more than one values (current value and previous values ) in accept or reject window for a cell .\r\n                                             if it is listing 4 records and I am accepting 2nd one \r\n                                             After accepting if again I  click this cell to accept or reject it will show a message -    There are no more pending changes to review\r\n                                             In this case maximum revision id comparison for each cell will fail \r\n\r\n                                                     <header guid=\"{9C3108F2-0497-4372-915B-71E20B8531C3}\" dateTime=\"2018-09-28T18:55:28\" maxSheetId=\"2\" userName=\"Smitha Puzhamkunnu\" r:id=\"rId5\" minRId=\"4\"><sheetIdMap count=\"1\"><sheetId val=\"1\"/></sheetIdMap>\r\n                                            <reviewedList count=\"1\"><reviewed rId=\"4\"/></reviewedList></header>                                             \r\n                                           \r\n                                             In this scenario rId=\"4\" is the last node . but the max rev id is  \"rId5\"  .\r\n                                            So it is showing incorrect result   -    There are pending changes to review\r\n                                         \r\n•\tIf we select accept all   or if we accept the last text  of a particular cell from accept or reject window the above  logic will work\r\n\r\n\r\nImplementation details\r\n\r\n                                        List<XmlElement> xmlElmnt = new List<XmlElement>();\r\n                    List<string> xmlInnerList = new List<string>();\r\n                    List<int> dicindexlist = new List<int>();\r\n                    dicindexlist.Add(-1);\r\n                    foreach (DocumentFormat.OpenXml.Packaging.WorkbookRevisionLogPart logpart in document.WorkbookPart.WorkbookRevisionHeaderPart.WorkbookRevisionLogParts)\r\n                    {\r\n                        var innerxml = logpart.Revisions.InnerXml;\r\n                        innerxml = \"<xmlroot>\" + innerxml + \"</xmlroot>\";\r\n                        xmlInnerList.Add(innerxml);\r\n\r\n                    }\r\n                    foreach (string innerelement in xmlInnerList)\r\n                    {\r\n                        XmlDocument xdoc = new XmlDocument();\r\n                        xdoc.LoadXml(innerelement);\r\n                        int ridvalue = 0;\r\n                        for (int k = 0; k < xdoc.ChildNodes.Count; k++)\r\n                        {\r\n                            var node1 = xdoc.ChildNodes[k];\r\n                            for (int cnt = 0; cnt < node1.ChildNodes.Count; cnt++)\r\n                            {\r\n                                var innernode = node1.ChildNodes[cnt];\r\n                                if (innernode.LocalName == \"rcc\")\r\n                                {\r\n                                    var attributes = innernode.Attributes;\r\n                                    var ridval = attributes.GetNamedItem(\"rId\");\r\n                                    //ridvalue = ridval.LocalName + ridval.InnerText;\r\n                                    ridvalue = Convert.ToInt32(ridval.InnerText);\r\n                                }\r\n                                for (int x = 0; x < innernode.ChildNodes.Count; x++)\r\n                                {\r\n                                    var node2 = innernode.ChildNodes[x];\r\n                                    var node2Attributes = node2.Attributes;\r\n                                    var callidval = node2Attributes.GetNamedItem(\"r\");\r\n                                    if (!DicXmlValues.ContainsKey(ridvalue))\r\n                                    {\r\n                                        DicXmlValues.Add(ridvalue, callidval.InnerText);\r\n                                    }\r\n                                    break;\r\n                                }\r\n\r\n                            }\r\n\r\n                        }\r\n                    }\r\n                    Dictionary<int, string> dicval = new Dictionary<int, string>();\r\n                    foreach (KeyValuePair<int, string> xmlval in DicXmlValues.OrderBy(key => key.Value))\r\n                    {\r\n\r\n                        dicval.Add(xmlval.Key, xmlval.Value);\r\n                    }\r\n\r\n                    List<int> indexlist = new List<int>();\r\n\r\n                    for (int d = 0; d < dicval.Count - 1; d++)\r\n                    {\r\n                        if (dicval.ElementAt(d).Value != dicval.ElementAt(d + 1).Value)\r\n                        {\r\n                            dicindexlist.Add(d);\r\n\r\n                            indexlist.Add(dicval.ElementAt(d).Key);\r\n                        }\r\n                    }\r\n                    dicindexlist.Add(dicval.Count - 1);\r\n                    for (int index1 = 0; index1 < dicindexlist.Count - 1; index1++)\r\n                    {\r\n\r\n                        int endindex = dicval.Count - ((dicval.Count - dicindexlist[index1 + 1]));\r\n                        int startindex = endindex - (dicindexlist[index1 + 1] - (dicindexlist[index1] + 1));\r\n                        Dictionary<int, string> resultdicval = new Dictionary<int, string>();\r\n                        Dictionary<int, string> orderedresultdicval = new Dictionary<int, string>();\r\n                        for (int index2 = startindex; index2 <= endindex; index2++)\r\n                        {\r\n\r\n                            resultdicval.Add(dicval.ElementAt(index2).Key, dicval.ElementAt(index2).Value);\r\n\r\n                        }\r\n                        foreach (KeyValuePair<int, string> indxval in resultdicval.OrderBy(x => x.Key))\r\n                        {\r\n\r\n                            orderedresultdicval.Add(indxval.Key, indxval.Value);\r\n                        }\r\n                        int indxmaxvalue = orderedresultdicval.Keys.Max();\r\n                        string rlVal = \"rId\" + indxmaxvalue;\r\n\r\n                        foreach (DocumentFormat.OpenXml.Spreadsheet.Header hdr in document.WorkbookPart.WorkbookRevisionHeaderPart.Headers)\r\n                        {\r\n                            if (rlVal == hdr.Id)\r\n                            {\r\n                                headerrecored = hdr;\r\n                                var rec = headerrecored.LastChild;\r\n                                if (rec.LocalName != \"reviewedList\")\r\n                                {\r\n                                    pendingchanges = pendingchanges + 1;\r\n                                }\r\n                                break;\r\n                            }\r\n                        }\r\n                    }\r\n                    string msg = \"\";\r\n                    if (pendingchanges > 0)\r\n                    {\r\n                         msg = \"There are pending changes to review\";\r\n                    }\r\n                    else\r\n                    {\r\n                         msg = \"There are no more pending changes to review\";\r\n                    }\r\n                                         https://social.msdn.microsoft.com/Forums/office/en-US/253c2315-f954-4e39-b30b-e10fbddf685c/how-to-get-track-changes-which-are-not-accepted-or-rejected-in-excel?forum=oxmls\r\n\r\n                                         https://social.msdn.microsoft.com/Forums/office/en-US/2848801d-a8b2-4dc4-a4ce-39a7c74cabee/open-xml-excel-to-detect-track-changes?forum=oxmlsdk\r\n                                           \r\n                                         https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/cc797468(v=office.14)\r\n\r\n\r\nOpen Questions\r\n                              \r\n                    \r\n1.\tHow can we identify whether a revision is accepted or rejected directly from revision log in excel .\r\n2.\tHow can we get only the pending track changes which are not accepted or rejected \r\n\r\n\r\n**Information**\r\n\r\n.NET FRAMEWORK  4.5\r\nDocumentFormat.OpenXml Version  - 2.8.1.15\r\n\r\n\r\n``` \r\n\r\n**Observed**\r\nThere is no way to identify whether a particular track change is accepted ,rejected or pending for  review directly from program\r\n\r\n**Expected**\r\n\r\nNeed to get pending track changes which is not yet accepted or rejected "},"comment":{"url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/comments/427217788","html_url":"https://github.com/OfficeDev/Open-XML-SDK/issues/524#issuecomment-427217788","issue_url":"https://api.github.com/repos/OfficeDev/Open-XML-SDK/issues/524","id":427217788,"node_id":"MDEyOklzc3VlQ29tbWVudDQyNzIxNzc4OA==","user":{"login":"tomjebo","id":4644198,"node_id":"MDQ6VXNlcjQ2NDQxOTg=","avatar_url":"https://avatars1.githubusercontent.com/u/4644198?v=4","gravatar_id":"","url":"https://api.github.com/users/tomjebo","html_url":"https://github.com/tomjebo","followers_url":"https://api.github.com/users/tomjebo/followers","following_url":"https://api.github.com/users/tomjebo/following{/other_user}","gists_url":"https://api.github.com/users/tomjebo/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjebo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjebo/subscriptions","organizations_url":"https://api.github.com/users/tomjebo/orgs","repos_url":"https://api.github.com/users/tomjebo/repos","events_url":"https://api.github.com/users/tomjebo/events{/privacy}","received_events_url":"https://api.github.com/users/tomjebo/received_events","type":"User","site_admin":false},"created_at":"2018-10-05T01:19:24Z","updated_at":"2018-10-05T01:19:24Z","author_association":"COLLABORATOR","body":"@smithapk thanks for detailing this issue and requesting an attribute. I'll take a look. you might also want to check with StackOverflow."}}
{"actor":{"display_login":"tomjebo"}}