3 steps to make Spreadsheet Compare work with git diff

For Git, Excel workbooks are just binary files. This means they cannot be diffed via git diff. In this blog post, we hook up Microsoft Office’s Spreadsheet Compare to git diff to create meaningful workbook comparisons.

What is Spreadsheet Compare?

Spreadsheet Compare has been part of Office Professional Plus since version 2013 and is also shipped with Office 365 ProPlus. Spreadsheet Compare is a stand-alone desktop application that lets us compare two Excel workbooks and create a report of the differences.

Depending on your Windows and Office versions, Spreadsheet Compare is located somewhere in the \Program Files\Microsoft Office\ folder. For example, on Windows 10 and Office 2016 (32-bit), it can be found in C:\Program Files (x86)\Microsoft Office\root\Office16\DCF.

Step 1 - Create xldiff.bat

In order to use Spreadsheet Compare as a Git differ, we need to be able to run it programmatically from the command line. We want to configure Git such that git diff <file> invokes Spreadsheet Compare whenever <file> is an Excel workbook.

We cannot call Spreadsheet Compare directly because the git diff command passes arguments that are incompatible with Spreadsheet Compare.

git diff passes seven arguments: path, old-file, old-hex, old-mode, new-file, new-hex, new-mode.

Spreadsheet Compare accepts only a single argument: The path to a text file which itself contains the paths to the two workbooks to be compared.

Create the batch file xldiff.bat to transform the git diff argument into the text file that Spreadsheet Compare expects to invoke the application. Make sure xldiff.bat is available globally on your computer (for instance, C:\Developer\xldiff.bat is a good choice).

@ECHO OFF
REM Create paths to original and current spreadsheets to store in tmp
set path2=%5
REM Change forward slash to back slash on all paths for the Excel tool
set path2=%path2:/=\%
ECHO %2 > tmp.txt
dir %path2% /B /S >> tmp.txt

"C:\Program Files (x86)\Microsoft Office\root\Office16\DCF\spreadsheetcompare" tmp.txt

This batch file captures the second (old-file) and fifth position argument (new-file), puts them into the text file and passes it to Spreadsheet Compare.

Make sure the above path to Spreadsheet Compare is correct for your Office installation. Instead of specifying the full path, you could also replace it with spreadsheetcompare and configure your PATH environment variables so it points to the correct folder.

Step 2 - Customise .git/config

Inside our Git repository, add the following line to the .git/config file (depending on your settings, the .git folder might be hidden from your Explorer).

[diff "excel"]
    command = C:/Developer/xldiff.bat

This defines our new custom differ “excel” which invokes our script located at C:\Developer\xldiff.bat (note the forward slashes in .git/config).

Again, make sure to either specify the full path to xldiff.bat or to add its directory to your PATH environment variables and specify the filename only in the command.

Step 3 - Create .gitattributes

The final step is to tell Git when to use our new custom Excel differ “excel”. In the root of our Git repository folder, we create the following .gitattributes file:

*.xls* diff=excel

Here, we instruct Git to use the “excel” differ for any file names matching the *.xls* pattern (feel free to refine the wildcard pattern).

A simple repository

You can find a simple repository https://github.com/xlwings/git-spreadsheetcompare which might be useful getting up and running in a minute.

The repository comes with xldiff.bat and the workbook Book1.xlsx.

After cloning the repository:

1. Ensure the path inside xldiff.bat points to Spreadsheet Compare (Step 1)
2. Configure .git/config (Step 2) and specify the full path to xldiff.bat inside your repository

Open Book1.xlsx in Excel, do some edits and hit Save.

A git status should now show that Book1.xlsx has been modified.

Let’s see what exactly changed: git diff Book1.xlsx. This time, we should get a meaningful diff in Spreadsheet Compare. How awesome is that?

What’s next?

Hooking up Spreadsheet Compare to Git allows us to see and validate our workbook changes before committing them, resulting in improved workbook quality and fewer errors.

While this is a great way to diff working copies of our Excel workboooks on our local computer, this approach does not work for a diff between two arbitrary commit hashes. We will cover that in the future in a separate blog post.

Also, Spreadsheet Compare is a desktop application, available only on Windows and thus does neither work server side (GitHub, BitBucket, GitLab etc) nor on any Mac computers.

If you are interested in server side Git/Excel integration, have a look at our commercial product xltrail.