如何使用 git diff 创建两个电子表格的可读差异？

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

提问by neu242

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.

我们的源代码存储库中有很多电子表格 (xls)。这些通常使用 gnumeric 或 openoffice.org 进行编辑，并且主要用于填充数据库以使用dbUnit进行单元测试。据我所知，没有简单的方法可以对 xls 文件进行差异处理，这使得合并非常乏味且容易出错。

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.

我试图将电子表格转换为 xml 并进行常规差异，但它真的感觉应该是最后的手段。

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

我想像git处理文本文件一样执行差异（和合并）。我将如何做到这一点，例如发布时git diff？

采纳答案by na_ka_na

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!

我们在公司遇到了完全相同的问题。我们的测试输出 excel 工作簿。二进制差异不是一种选择。所以我们推出了我们自己的简单命令行工具。查看ExcelCompare 项目。事实上，这使我们能够很好地自动化我们的测试。非常欢迎补丁/功能请求！

回答by Laurent

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(Sheet1!A1 <> Sheet2!A1, "X", "")在左上方的单元格（或等效的：点击实际细胞自动已引用插入式）
• Ctrl+C（复制）、Ctrl+A（全选）、Ctrl+V（粘贴）以填充工作表。

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.

如果工作表相似，该电子表格将是空的，除了几个带有 X 的单元格，突出显示差异。取消缩放至 40% 以快速查看不同之处。

回答by thvo

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:

我过去做过很多 Excel 工作簿的比较。我的技术适用于包含许多工作表的工作簿，但它只比较单元格内容，而不是单元格格式、宏等。此外，还涉及一些编码，但如果您必须重复比较大量大文件，这是非常值得的。这是它的工作原理：

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.

A) 编写一个简单的转储程序，它可以遍历所有工作表并将所有数据保存到制表符分隔的文件中。为每个工作表创建一个文件（使用工作表名称作为文件名，例如“MyWorksheet.tsv”），并在每次运行程序时为这些文件创建一个新文件夹。以 Excel 文件名命名文件夹并添加时间戳，例如“20080922-065412-MyExcelFile”。我使用名为JExcelAPI的库在 Java 中完成了此操作。这真的很容易。

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.

B) 添加一个 Windows shell 扩展，以便在右键单击 Excel 文件时从步骤 A 运行您的新 Java 程序。这使得运行这个程序变得非常容易。你需要谷歌一下如何做到这一点，但这就像编写一个 *.reg 文件一样简单。

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

C) 获得BeyondCompare。它有一个非常酷的功能，可以通过在漂亮的表格中显示分隔数据来比较分隔数据，请参见屏幕截图。

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.

D) 您现在可以轻松比较 Excel 文件了。右键单击 Excel 文件 1 并运行您的转储程序。它将创建一个文件夹，每个工作表一个文件。右键单击 Excel 文件 2 并运行您的转储程序。它将创建第二个文件夹，每个工作表一个文件。现在使用 BeyondCompare (BC) 来比较文件夹。每个文件代表一个工作表，因此如果工作表中存在差异，BC 将显示这一点，您可以向下钻取并进行文件比较。BC 将以漂亮的表格布局显示比较，您可以隐藏您不感兴趣的行和列。

回答by Shashank Singla

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

您可以试用这个免费的在线工具 - www.cloudyexcel.com/compare-excel/

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

它在添加、删除、更改行等方面提供了良好的在线视觉输出。

Plus you donot have to install anything.

另外，您不必安装任何东西。

回答by Andres

I have found xdocdiff WinMerge Plugin. It is a plugin for WinMerge (both OpenSourceand 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.

我找到了xdocdiff WinMerge Plugin。它是 WinMerge 的插件（开源和免费软件，您不需要编写 VBA 或将 excel 保存到 csv 或 xml）。它仅适用于细胞的包含。

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

• .rtf 富文本
• .docx/.docm Microsoft WORD 2007(OOXML)
• .xlsx/.xlsm Microsoft Excel 2007(OOXML)
• .pptx/.pptm Microsoft PowerPoint 2007(OOXML)
• .doc 微软 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 打开文件
• .wj2/wj3/wk3/wk4/123 莲花 123
• .wri Windows3.1 写入
• .pdf Adob​​e PDF
• .mht 网络档案
• .eml 从 OutlookExpress 导出的文件

Regard, Andres

问候，安德烈斯

回答by Andres

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

嗯。从 Excel 菜单中选择窗口 -> 并排比较？

回答by Casper

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.

您是否使用 TortoiseSVN 在 subversion 中进行提交和更新？它有一个差异工具，但是比较 Excel 文件仍然不是真正用户友好的。在我的环境（Win XP、Office 2007）中，它打开两个 excel 文件进行并排比较。

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

右键单击文档 > Tortoise SVN > 显示日志 > 选择修订版 > 右键单击​​“与工作副本比较”。

回答by Gremlin

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

较新版本的 MS Office 带有Spreadsheet Compare，它在 GUI 中执行了相当不错的差异。它检测大多数类型的变化。

回答by shubhu

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.

有一个库daff（data diff 的缩写），它有助于比较表格、生成它们的差异摘要，并将这样的摘要用作补丁文件。

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

它是用 Haxe 编写的，因此可以用主要语言编译。

I have made an Excel Diff Toolin 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).

我在这个库的帮助下用 Javascript制作了一个Excel Diff 工具。它适用于数字和小字符串，但输出对于长字符串并不理想（例如，带有轻微字符变化的长句子）。

回答by mattsmith321

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.

我知道有几个回复建议将文件导出为 csv 或其他一些文本格式，然后比较它们。我没有看到它具体提到过，但是 Beyond Compare 3 有许多它支持的其他文件格式。请参阅其他文件格式。使用 Microsoft Excel 文件格式之一，您可以轻松比较两个 Excel 文件，而无需通过导出为另一种格式选项。