{"action":"created","issue":{"url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/38","repository_url":"https://api.github.com/repos/na-ka-na/ExcelCompare","labels_url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/38/labels{/name}","comments_url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/38/comments","events_url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/38/events","html_url":"https://github.com/na-ka-na/ExcelCompare/issues/38","id":170832266,"number":38,"title":"Development ideas","user":{"login":"jmacadie","id":1902025,"avatar_url":"https://avatars.githubusercontent.com/u/1902025?v=3","gravatar_id":"","url":"https://api.github.com/users/jmacadie","html_url":"https://github.com/jmacadie","followers_url":"https://api.github.com/users/jmacadie/followers","following_url":"https://api.github.com/users/jmacadie/following{/other_user}","gists_url":"https://api.github.com/users/jmacadie/gists{/gist_id}","starred_url":"https://api.github.com/users/jmacadie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jmacadie/subscriptions","organizations_url":"https://api.github.com/users/jmacadie/orgs","repos_url":"https://api.github.com/users/jmacadie/repos","events_url":"https://api.github.com/users/jmacadie/events{/privacy}","received_events_url":"https://api.github.com/users/jmacadie/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-08-12T09:25:40Z","updated_at":"2016-08-17T16:14:04Z","closed_at":null,"body":"Hi,\r\n\r\nI work building (big) Excel financial models and source control is a nightmare. I love git (for small personal coding projects) but it doesn't work with Excel, as we all know. I came across your project yesterday after following a link from [http://programmaticallyspeaking.com/git-diffing-excel-files.html](url) and it's a bit of a revelation for me.\r\n\r\nI've been giving some thought to how I'd approach making your tool more functional for the types of spreadsheet I work with and below are my high level thoughts on how the tool could be structured. The key idea being to track transformations first and then cell diff (hopefully) aligned cells. I know you have this on your to do list and to my way of thinking tracking cell transformations requires quite a different program structure, which as well as being difficult is possibly why you've not got round to it yet.\r\n\r\nI know nothing about java and am not a trained programmer but am happy to try to help out (day-job workload permitting) if you're keen. Alternately if the changes are too radical/not where you want to go I could just fork you and go my own way. Let me know.\r\n\r\nAnyway here's my thoughts:\r\n\r\n **Maintain 3 spreadsheets**\r\n- From spreadsheet (immutable)\r\n- To spreadsheet (immutable)\r\n- Working spreadsheet, a clone of the From spreadsheet to start with but is gradually edited by applying the changes identified in to two transform sections below\r\n\r\n**FIND TRANSFORMATIONS**\r\nidea is to repress high-volume cell difference reporting that can be easily explained by the single transformation that created the bulk changes\r\n\r\n_SHEET TRANSFORMATIONS_\r\n- (REPORT) Sheets re-ordered\r\n- (REPORT) Sheet renamed\r\n- (REPORT) Sheet copied\r\n- (REPORT) Sheet deleted\r\n\r\n_CELL TRANSFORMATIONS_\r\n- (REPORT) Rows added\r\n- (REPORT) Rows deleted\r\n- (REPORT) Rows cut & pasted (i.e. moved)\r\n- (REPORT) Rows copied & inserted (i.e. duplicated)\r\n- (REPORT) Columns added\r\n- (REPORT) Columns deleted\r\n- (REPORT) Columns cut & pasted (i.e. moved)\r\n- (REPORT) Columns copied & inserted (i.e. duplicated)\r\n- (REPORT) Cells deleted (shift left)\r\n- (REPORT) Cells deleted (shift up)\r\n- (REPORT) Cells inserted (shift left)\r\n- (REPORT) Cells inserted (shift down)\r\n- Possibly need to run this loop 2 (or more) times per sheet to catch instances where, for example, columns have been inserted and then rows\r\n\r\n**FIND CELL DIFFERENCES**\r\nCan now compare the cells in the Working spreadsheet to the To spreadsheet\r\n- Need a method to translate the cell address through the transformations to infer the original cell reference in the From spreadsheet (i.e. reverse apply the transformations) for reporting\r\n\r\n_FORMULAE CONDENSE_\r\n- Condense the sheet down to just unique formulae on the sheet and the blocks of cells that all share the same formula\r\n- This is important as in big Excel files it is common practice to fill formulae. For example a spreadsheet might have 500 columns and each row will have a filled-across formula. If the diff reports on each of the 500 differences (per edited row) then you'll spam the output and not be able to easily determine what's changed\r\n- In VBA you'd do this by comparing the FormulaR1C1 property of the cells for equivalence\r\n- Output should be a paired list of (block) cell references and the formula in the top, left cell of the block\r\n\r\n_CELL DIFFERENCES_\r\n- (REPORT) Where cell blocks with matching formulae have been extended or reduced\r\n- (REPORT) Where matching cell blocks have different formulae\r\n- (REPORT) Where non-matching cell blocks with different formulae (need to think about this one a bit)\r\n"},"comment":{"url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/comments/240463572","html_url":"https://github.com/na-ka-na/ExcelCompare/issues/38#issuecomment-240463572","issue_url":"https://api.github.com/repos/na-ka-na/ExcelCompare/issues/38","id":240463572,"user":{"login":"jmacadie","id":1902025,"avatar_url":"https://avatars.githubusercontent.com/u/1902025?v=3","gravatar_id":"","url":"https://api.github.com/users/jmacadie","html_url":"https://github.com/jmacadie","followers_url":"https://api.github.com/users/jmacadie/followers","following_url":"https://api.github.com/users/jmacadie/following{/other_user}","gists_url":"https://api.github.com/users/jmacadie/gists{/gist_id}","starred_url":"https://api.github.com/users/jmacadie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jmacadie/subscriptions","organizations_url":"https://api.github.com/users/jmacadie/orgs","repos_url":"https://api.github.com/users/jmacadie/repos","events_url":"https://api.github.com/users/jmacadie/events{/privacy}","received_events_url":"https://api.github.com/users/jmacadie/received_events","type":"User","site_admin":false},"created_at":"2016-08-17T16:14:04Z","updated_at":"2016-08-17T16:14:04Z","body":"I've been doing a bit of work on my ideas. Being a total newb to java I downloaded netbeans for an IDE and it's apparent you weren't using netbeans for the current application. I'm sure porting is pretty simple for anyone with much experience but I was struggling to get it to build with external libraries etc. I also found I was struggling to get my head round what all your code was doing. Finally my ideas for development are structurally quite different to your current project.\r\n\r\nFor all of the above reasons I found it easier to start from scratch with my own project. After (not much) thought I've come to appreciate just how hard detecting transformations is going to be. I've therefore been focusing on the formulae condense idea in my original thought dump, and had some success. The idea being to condense a given sheet down into a paired list of unique formulae and all the cells that share that same formula. This will be a real boon for reporting on effective differences within spreadsheets where fill down / right has been heavily used (as is the case on most of the files I work with).\r\n\r\nThe biggest issue I've had to deal with so far is that POI doesn't do R1C1 notation ([https://poi.apache.org/apidocs/org/apache/poi/ss/formula/OperationEvaluationContext.html](url) & search page for R1C1) and that's pretty much a pre-requisite for grouping like formulae, so I've had to roll my own.\r\n\r\nAnyways feel free to take a look: [https://github.com/jmacadie/ExcelCompare2](url) and ping me any thoughts. The project is pretty nascent as yet and doesn't get as far as actually doing a diff. Also any pointers on how to improve my super basic java writing style gratefully accepted.\r\n\r\nJames\r\n"}}
{"actor":{"display_login":"jmacadie"}}