Last updated on March 23, 2018
Git XL: An open-source Git command-line extension for Excel files
Posted by Björn Stiel - Comments
Git can be tweaked to make it play nicely with Excel workbook files (that is, to be able to show diffs). Pre-commit hoooks can be set-up to automatically export the workbook’s VBA code as stand-alone .bas files on each commit. And Microsoft’s Spreadsheet Compare can be used as a custom differ for Excel workbook file changes.
However, these tweaks require some technical plumbing work which has to be repeated for each and every user and computer.
Introducing Git XL: An open-source Git command-line extension
In order to make Git Excel-ready out of the box, we created Git XL, an open-source, free Git extension. Git XL comes with a custom Excel workbook differ that understands the VBA code inside your workbook (handling spreadsheets is on the roadmap).
Also, Git XL takes care of the correct Git configuration so that all the Excel oddities (such as temporary files) are handled correctly. Currently, Git XL supports Windows only, but we are working on a Mac version.
To get started, download and run the latest installer. This installs the Git XL extension and the custom workbook differ on your computer, and configures your environment variables so that Git can find the extension (you can find more information here.
As soon as the installation process is complete, open a new command-line window and run git xl to display the help page:
C:\Users\Bjoern\Developer> git xl
git-xl/0.1.0 (windows; Python 3.6.4;)
git xl []
Git XL is a system for managing Excel workbook files in
association with a Git repository. Git xl:
* installs a special git-diff for Excel files
* makes Git ignore temporary Excel files via .gitignore
Commands
--------
* git xl install:
Install Git xl.
* git xl uninstall:
Uninstall Git xl.
* git xl version:
Report the version number.
Getting started with Git XL
Run git xl install once to make Git XL work across all your (existing and new) repositories. Alternatively, you can install Git XL on a per-repository basis. In the repository’s root folder, run: git xl install --local.
When installing Git XL in local mode, Git XL creates .gitattributes (or amends it if it already exists). Make sure .gitattributes is tracked as part of your repository.
Let’s have a look at what Git XL does (the examples are available at https://github.com/xlwings/git-xl-examples but you can really do it with any git repository that contains Excel workbook files).
C:\Users\Bjoern\Developer> git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1
diff --xl a/Book1.xlsb b/Book1.xlsb
--- /dev/null
+++ b/Book1.xlsb/VBA/Module2
+Option Explicit
+
+Function Count_once(Count_range As Range) As Long
+
+Dim strAddress As String
+Dim lMaxRow As Long, lEndCol As Long, lStartCol As Long
+Dim lColCount As Long
+Dim lLoop As Long, lArrElement As Long
+Dim lArray() As Long
+
+
+
+ lMaxRow = Rows.Count
+
+ lColCount = Count_range.Columns.Count
+
+ lEndCol = Count_range.Columns(lColCount).Column
+
+ ReDim lArray(lColCount)
+
+ lStartCol = Count_range.Columns(1).Column
+
+ For lLoop = lStartCol To lEndCol
+ lArray(lArrElement) = Cells(lMaxRow, lLoop).End(xlUp).Row
+ lArrElement = lArrElement + 1
+ Next lLoop
+
+ lMaxRow = WorksheetFunction.Max(lArray)
+
+ strAddress = Range(Count_range.Cells(1, 1), _
+ Cells(lMaxRow, lEndCol)).Address
+
+
+ Count_once = Evaluate("sumproduct((" & strAddress & "<>"""")/" _
+ & "countif(" & strAddress & "," & strAddress & "&""""))") - 1
+
+End Function
+
Play around with it by editing the VBA inside your working copy’s Book1.xlsb and/or the text file README.md in the repository and diff your working copy versus the head version:
C:\Users\Bjoern\Developer> git diff
Upcoming features
You are now in full control of your Excel VBA changes. What’s next for Git XL? We are planning to support MacOS and also to extend the diff functionality to worksheets. Merging is another topic on the roadmap.
If you would like to see a feature, you are very welcome to open an issue or contribute to the project.