Reputation: 16575

How do I create a readable diff of two spreadsheets using git diff?

We have a lot of spreadsheets (xls) in our source code repository. These are usually edited with gnumeric or openoffice.org, and are mostly used to populate databases for unit testing with dbUnit. There are no easy ways of doing diffs on xls files that I know of, and this makes merging extremely tedious and error prone.

I've tried to converting the spreadsheets to xml and doing a regular diff, but it really feels like it should be a last resort.

I'd like to perform the diffing (and merging) with git as I do with text files. How would I do this, e.g. when issuing git diff?

Upvotes: 190

Answers (22)

Bjoern Stiel

Reputation: 4161

I'm the co-author of a free, open-source Git extension:

https://github.com/xltrail/git-xl

It makes Git work with any Excel workbook file format without any workarounds.

Upvotes: 3

nmz787

Reputation: 2170

Here's what I ended up doing (requires openpyxl), based on how I previously approached git-diffing PowerPoint files. I also got it to work with BeyondCompare:



"""
Setup -- Add these lines to the following files:
--- .gitattributes
*.xlsx diff=xlsx

--- .gitconfig (or repo\.git\config    or your_user_home\.gitconfig) (change the path to point to your local copy of the script)
[diff "xlsx"]
    binary = true
    textconv = python C:/Users/myUserName/Miniconda3/Scripts/git-xlsx-textconv.py

[difftool "xlsx"]
    cmd = t1=`mktemp` && `python C:/Users/myUserName/Miniconda3/Scripts/git-xlsx-textconv.py $LOCAL >$t1` && t2=`mktemp` && `python C:/Users/myUserName/Miniconda3/Scripts/git-xlsx-textconv.py $REMOTE >$t2` && "C:/Program\\ Files/Beyond\\ Compare\\ 4/BCompare.exe $t1 $t2" && rm -f $t1 $t2

usage:
git diff your_excel.xlsx
git difftool --tool xlsx your_excel.xlsx

"""

import sys
from openpyxl import load_workbook


if __name__ == '__main__':
    if len(sys.argv) != 2:
        print("Usage: git-xlsx-textconv file.xslx")

    path_to_excel = sys.argv[1]

    wb = load_workbook(filename=path_to_excel, read_only=True)

    for sn in wb.sheetnames:
        ws = wb[sn]
        par_text = b'#<'
        par_text += sn.encode('utf-8')
        par_text += b'>#\n'
        rows = []
        for row in ws.rows:
            
            rowtext = b''
            is_empty = True
            for cell in row:
                s = '' if cell.value is None else str(cell.value).strip()
                s = s.replace("\\", "\\\\")
                s = s.replace("\n", " ")
                s = s.replace("\r", " ")
                s = s.replace("\t", " ")
                s = s.replace('\r\n', ' ')
                s = s.rstrip('\r\n')
                s = s.rstrip(r'')

                # Convert left and right-hand quotes from Unicode to ASCII
                # found http://stackoverflow.com/questions/816285/where-is-pythons-best-ascii-for-this-unicode-database
                # go here if more power is needed  http://code.activestate.com/recipes/251871/
                # or here                          https://pypi.python.org/pypi/Unidecode/0.04.1
                punctuation = { 0x2018:0x27, 0x2019:0x27, 0x201C:0x22, 0x201D:0x22 }
                s.translate(punctuation).encode('ascii', 'ignore')
                s = s.encode('utf-8')
                if s:
                    is_empty = False
                    rowtext += s
                rowtext += b'|'
            rows.append((rowtext, is_empty))
        rows_new = []
        non_emtpy_seen = False
        num_empties_at_end_of_sheet = 0
        # now figure out if there's a bunch of "empty" rows at the end of the sheet
        for r in reversed(rows):
            text, is_empty = r
            if is_empty and not non_emtpy_seen:
                num_empties_at_end_of_sheet+=1
                continue
            non_emtpy_seen = True
            rows_new.append(text)
        rows_new = list(reversed(rows_new))
        par_text += b'\n'.join(rows_new)
        if num_empties_at_end_of_sheet:
            par_text += f'\n<... and {num_empties_at_end_of_sheet} empty rows that are not shown...>\n'.encode('utf8')
        par_text += b'\n'
        print(par_text.decode('utf8'))

Upvotes: 0

shubhu

Reputation: 125

There is a library daff (short for data diff) which helps in comparing tables, producing a summary of their diffs, and using such a summary as a patch file.

It is written in Haxe, so it can be compiled in major languages.

I have made an Excel Diff Tool in Javascript with help of this library. It works well with numbers & small strings but the output is not ideal for long strings (e.g. a long sentence with with minor character change).

Upvotes: 5

Gremlin

Reputation: 238

Newer versions of MS Office come with Spreadsheet Compare, which performs a fairly nice diff in a GUI. It detects most kinds of changes.

Upvotes: 4

kokichi88

Reputation: 51

I got the problem like you so I decide to write small tool to help me out. Please check ExcelDiff_Tools. It comes with several key points:

Support xls, xlsx, xlsm.
With formula cell. It will compare both formula and value.
I try to make UI look like standard diff text viewer with : modified, deleted, added, unchanged status. Please take a look with image below for example:

Upvotes: 1

Shashank Singla

Reputation: 1837

You can try this free online tool - www.cloudyexcel.com/compare-excel/

It gives a good visual output online, in terms of rows added,deleted, changed etc.

enter image description here

Plus you donot have to install anything.

Upvotes: 15

na_ka_na

Reputation: 1658

We faced the exact same issue in our co. Our tests output excel workbooks. Binary diff was not an option. So we rolled out our own simple command line tool. Check out the ExcelCompare project. Infact this allows us to automate our tests quite nicely. Patches / Feature requests quite welcome!

Upvotes: 129

Chris B

Reputation: 5441

If you have TortoiseSVN then you can CTRL click the two files to select them in Windows Explorer and then right-click, TortoiseSVN->Diff.

This works particularly well if you are looking for a small change in a large data set.

Upvotes: 1

kbulgrien

Reputation: 4508

Use Altova DiffDog

Use diffdog's XML diff mode and Grid View to review the differences in an easy to read tabular format. Text diff'ing is MUCH HARDER for spreadsheets of any complexity. With this tool, at least two methods are viable under various circumstances.

Save As .xml

To detect the differences of a simple, one sheet spreadsheet, save the Excel spreadsheets to compare as XML Spreadsheet 2003 with a .xml extension.
Save As .xlsx

To detect the differences of most spreadsheets in a modularized document model, save the Excel spreadsheets to compare as an Excel Workbook in .xlsx form. Open the files to diff with diffdog. It informs you that the file is a ZIP archive, and asks if you want to open it for directory comparison. Upon agreeing to directory comparison, it becomes a relatively simple matter of double-clicking logical parts of the document to diff them (with the XML diff mode). Most parts of the .xslx document are XML-formatted data. The Grid View is extremely useful. It is trivial to diff individual sheets to focus the analysis on areas that are known to have changed.

Excel's propensity to tweak certain attribute names with every save is annoying, but diffdog's XML diff'ing capabilities include the ability to filter certain kinds of differences. For example, Excel spreadsheets in XML form contain row and c elements that have s attributes (style) that rename with every save. Setting up a filter like c:s makes it much easier to view only content changes.

diffdog has a lot of diff'ing capability. I've listed the XML diff modes only simply because I haven't used another tool that I liked better when it comes to differencing Excel documents.

Upvotes: 2

Toby

Reputation: 9803

If you're using Java, you could try simple-excel.

It'll diff spreadsheets using Hamcrest matchers and output something like this.

java.lang.AssertionError:
Expected: entire workbook to be equal
     but: cell at "C14" contained <"bananas"> expected <nothing>,
          cell at "C15" contained <"1,850,000 EUR"> expected <"1,850,000.00 EUR">,
          cell at "D16" contained <nothing> expected <"Tue Sep 04 06:30:00">
    at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)

I should qualify that we wrote that tool (like the ticked answer rolled their own).

Upvotes: 1

mattsmith321

Reputation: 6851

I know several responses have suggested exporting the file to csv or some other text format, and then comparing them. I haven't seen it mentioned specifically, but Beyond Compare 3 has a number of additional file formats that it supports. See Additional File Formats. Using one of the Microsoft Excel File Formats you can easily compare two Excel files without going through the export to another format option.

Upvotes: 3

Andres

Reputation: 101

I have found xdocdiff WinMerge Plugin. It is a plugin for WinMerge (both OpenSource and Freeware, you doesn't need to write a VBA nor save an excel to csv or xml). It works just for the celd's contains.

This plugin supports also:

.rtf Rich Text
.docx/.docm Microsoft WORD 2007(OOXML)
.xlsx/.xlsm Microsoft Excel 2007(OOXML)
.pptx/.pptm Microsoft PowerPoint 2007(OOXML)
.doc Microsoft WORD ver5.0/95/97/2000/XP/2003
.xls Microsoft Excel ver5.0/95/97/2000/XP/2003
.ppt Microsoft PowerPoint 97/2000/XP/2003
.sxw/.sxc/.sxi/.sxd OpenOffice.org
.odt/.ods/.odp/.odg Open Document
.wj2/wj3/wk3/wk4/123 Lotus 123
.wri Windows3.1 Write
.pdf Adobe PDF
.mht Web Archive
.eml Exported files from OutlookExpress

Regard, Andres

Upvotes: 10

Laurent

Reputation: 6205

Quick and easy with no external tools, works well as long as the two sheets you are comparing are similar:

Create a third spreadsheet
Type =if(Sheet1!A1 <> Sheet2!A1, "X", "") in the top left cell (or equivalent: click on the actual cells to automatically have the references inserted into the formula)
Ctrl+C (copy), Ctrl+A (select all), Ctrl+V (paste) to fill the sheet.

If the sheets are similar, this spreadsheet will be empty except for a few cells with X in them, highlighting the differences. Unzoom to 40% to quickly see what is different.

Upvotes: 117

thvo

Reputation: 1552

I've done a lot of comparing of Excel workbooks in the past. My technique works very well for workbooks with many worksheets, but it only compares cell contents, not cell formatting, macros, etc. Also, there's some coding involved but it's well worth it if you have to compare a lot of large files repeatedly. Here's how it works:

A) Write a simple dump program that steps through all worksheets and saves all data to tab-separated files. Create one file per worksheet (use the worksheet name as the filename, e.g. "MyWorksheet.tsv"), and create a new folder for these files each time you run the program. Name the folder after the excel filename and add a timestamp, e.g. "20080922-065412-MyExcelFile". I did this in Java using a library called JExcelAPI. It's really quite easy.

B) Add a Windows shell extension to run your new Java program from step A when right-clicking on an Excel file. This makes it very easy to run this program. You need to Google how to do this, but it's as easy as writing a *.reg file.

C) Get BeyondCompare. It has a very cool feature to compare delimited data by showing it in a nice table, see screenshot.

D) You're now ready to compare Excel files with ease. Right-click on Excel file 1 and run your dump program. It will create a folder with one file per worksheet. Right-click on Excel file 2 and run your dump program. It will create a second folder with one file per worksheet. Now use BeyondCompare (BC) to compare the folders. Each file represents a worksheet, so if there are differences in a worksheet BC will show this and you can drill down and do a file comparison. BC will show the comparison in a nice table layout, and you can hide rows and columns you're not interested in.

Upvotes: 13

graveDust

Reputation: 11

xdocdiff plugin for SVN

Upvotes: 1

patrick

Reputation: 16949

Convert to cvs then upload to a version control system then diff with an advanced version control diff tool. When I used perforce it had a great diff tool, but I forget the name of it.

Upvotes: 0

_mike

Reputation:

I found an openoffice macro here that will invoke openoffice's compare documents function on two files. Unfortunately, openoffice's spreadsheet compare seems a little flaky; I just had the 'Reject All' button insert a superfluous column in my document.

Upvotes: 1

Luke

Reputation:

Hmmm. From the Excel menu choose Window -> Compare side by side?

Upvotes: 6

anon

Reputation:

I would use the SYLK file format if performing diffs is important. It is a text-based format, which should make the comparisons easier and more compact than a binary format. It is compatible with Excel, Gnumeric, and OpenOffice.org as well, so all three tools should be able to work well together. SYLK Wikipedia Article

Upvotes: 3

Casper

Reputation: 1252

Do you use TortoiseSVN for doing your commits and updates in subversion? It has a diff tool, however comparing Excel files is still not really user friendly. In my environment (Win XP, Office 2007), it opens up two excel files for side by side comparison.

Right click document > Tortoise SVN > Show Log > select revision > right click for "Compare with working copy".

Upvotes: 5

lesscode

Reputation: 6361

I don't know of any tools, but there are two roll-your-own solutions that come to mind, both require Excel:

You could write some VBA code that steps through each Worksheet, Row, Column and Cell of the two Workbooks, reporting differences.
If you use Excel 2007, you could save the Workbooks as Open-XML (*.xlsx) format, extract the XML and diff that. The Open-XML file is essentially just a .zip file of .xml files and manifests.

You'll end up with a lot of "noise" in either case if your spreadsheets aren't structurally "close" to begin with.

Upvotes: 0

ConroyP

Reputation: 41886

Diff Doc may be what you're looking for.

Compare documents of MS Word (DOC, DOCX etc), Excel, PDF, Rich Text (RTF), Text, HTML, XML, PowerPoint, or Wordperfect and retain formatting

Choose any portion of any document (file) and compare it against any portion of the same or different document (file).

Upvotes: -1

How do I create a readable diff of two spreadsheets using git diff?

Answers (22)

Related Questions