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.
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.
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:
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.
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
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.
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
Here, we instruct Git to use the “excel” differ for any file names matching the
*.xls* pattern (feel free to refine the wildcard pattern).
You can find a simple repository https://github.com/ZoomerAnalytics/git-spreadsheetcompare which might be useful getting up and running in a minute.
The repository comes with
xldiff.bat and the workbook
After cloning the repository:
xldiff.batpoints to Spreadsheet Compare (Step 1)
.git/config(Step 2) and specify the full path to
xldiff.batinside your repository
Book1.xlsx in Excel, do some edits and hit Save.
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?
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.
Zoomer Analytics GmbH
© 2018 Zoomer Analytics LLC. All rights reserved.