In this post, I’m going to demonstrate how to get a useful diff for binary Excel files that reside in a Git repository. I assume that you’re familiar with Git, and in that case you know that having binary files in a repository is generally not a good idea. Nonetheless, sometimes it’s useful—perhaps they contain test data, or import data, or base data for bootstrapping, or some other useful data that aren’t suitable for text form.
Diffing a binary file
For whatever reason you have an Excel file in your repository, finding a difference in
the file between two commits is by default not possible. Let’s say that you have
uncommitted changes to an Excel file called
document.xlsx. This is what happens if
you try to see what has changed:
$ git diff document.xlsx diff --git a/document.xlsx b/document.xlsx index b22099f..ebebbb9 100644 Binary files a/document.xlsx and b/document.xlsx differ
Since the file is binary rather than text, Git gives up and just prints that there is a difference. But that will soon change! :-)
Defining an Excel diff driver
First, download the ExcelCompare tool. Click
on Releases and get the latest one—I used
0.5.1 when testing. Unpack the ZIP file in a folder of your choosing, but make sure it’s in a
persistent location (i.e., unpacking it in a Downloads folder is probably a bad idea). I unpacked to
By now, it’s probably a good time to mention that I use Git Bash on Windows. That affects the path handling a bit, as you’ll see.
We will write a script to perform the actual diffing. To begin, edit the
.git/config file in
your repository and amend the following:
[diff "excel"] command = c:/apps/excelcompare/exceldiff.cmd
This registers a diff driver in your repository, telling Git which command to use when diffing
files of excel type. The
exceldiff.cmd script doesn’t
exist yet, but it will in a minute. If you want to place the script somewhere else, or name it
differently, feel free to do so.
Do you see the path oddity? The command path looks almost like a regular Windows path, except it has forward slashes instead of backward slashes. Using backward slashes, even if they are properly escaped, did not work for me.
Next, we must tell Git to associate certain files with the excel diff driver. To do so, edit
.gitattributes in the root of your repository and amend the following:
Feel free to add more file patterns (one per line) if you wish. ExcelCompare supports a number of different Excel file types.
.gitignore) must be committed
to have any effect, so add it to a commit at this point.
Finally, open up an editor and create
exceldiff.cmd in the correct location (as specified in
@echo off c:\apps\excelcompare\excel_cmp.bat %2 %5
(Adjust the path to
excel_cmp.bat so that it matches the location where you unpacked ExcelCompare.)
Why arguments two and five? It’s because Git passes seven arguments to a diff command, of which the second is the path to the old file (which likely is a temporary file) and the fifth is the path to the new file. For more detailed information, have a look at the Git documentation, specifically under GIT_EXTERNAL_DIFF.
Back to diffing
Once the script has been saved, we can attempt the diff again:
$ git diff document.xlsx DIFF Cell at Sheet1!A3 => 'X' v/s 'Y' ----------------- DIFF ------------------- Sheets: [Sheet1] Rows:  Cols: [A] ----------------- EXTRA WB1 ------------------- Sheets:  Rows:  Cols:  ----------------- EXTRA WB2 ------------------- Sheets:  Rows:  Cols:  ----------------------------------------- Excel files C:\...\AppData\Local\Temp\TRAkga_document.xlsx and document.xlsx differ
This is the raw output from ExcelCompare. As can be seen, the uncommitted change is apparently that I changed the value of cell A3 on sheet Sheet1 from X to Y (which happens to be exactly what I did).
And that’s it! Much, much more useful than before!
A small note
As can be seen from the example above, the diff output doesn’t have the same format as a “native” textual Git diff, which may look like this:
$ git diff foo diff --git a/foo b/foo index 9972d7e..8320b47 100644 --- a/foo +++ b/foo @@ -1,2 +1,2 @@ -bar +baz
Specifically, the initial metadata lines are not present, and the differences are
not in patch format. This means that an Excel diff cannot be used for patching an Excel
file. If you attempt to use
git format-patch, you’ll see that it just ignores the Excel diff.
In this post, I have shown how to configure a Git repository to use a custom command for showing the difference between different revisions of an Excel file. If you found it useful, please let me know in the comments!
- Fixed some typos.
- Fixed incorrect
- Added a paragraph about committing the