This project has moved. For the latest updates, please go here.

Comparing Two Workbooks

Jun 25, 2012 at 11:28 PM

What's the best way to compare to XLWorkbooks? The equals method appears to have the default by-reference behavior.

Jun 26, 2012 at 2:50 PM

What would you be trying to compare?

You could take two workbooks and compare their respective worksheet collections to see if they had the same worksheet names.

Then you could take two worksheets with the same names and do a cell by cell comparison and output a report with the differences.

Jun 26, 2012 at 4:20 PM
Edited Jun 26, 2012 at 4:21 PM

Thanks for replying Robert. I should have been more clear. I want to know if the workbooks are identical. Identical for me would mean they have all the same contents. text, formatting... the whole nine yards. 

For my purposes, I wouldn't need to know the differences. I just need a yes or no answer.

Jun 28, 2012 at 5:19 PM
Edited Jun 28, 2012 at 5:23 PM

Well, "the whole nine yards" is a lot of yards. What you are suggesting is to examine every single property of every single object in the two workbooks and see if they are equal. In theory you could do that with reflection and recursively examine the collections of objects and their respective properties until you compared every single leaf on the tree. However, that task will never end because some objects contain references to the collections that hold them so the code would go into an infinite loop. To do properly you would have to somehow figure out how to avoid loops in the graph of connected objects. If you figure out how to do that, you could use it as your thesis paper to earn a PhD! Seriously!

In the language of objects, what you are trying to do is called a "deep object comparison". I googled "C# deep object compare" and got a lot of hits, including these from stack overflow.

http://stackoverflow.com/questions/506096/comparing-object-properties-in-c-sharp

http://stackoverflow.com/questions/1539989/c-sharp-implementation-of-deep-recursive-object-comparison-in-net-3-5

Reading through some of these discussions you can see that doing a deep object compare is very complicated.

Someone wrote a package on codeplex that claims to do deep object comparisons. You can check it out here,

http://comparenetobjects.codeplex.com/

Try it out. If it works, that would be great!!! My suspicion is that the ClosedXML workbooks are too complex to be able to work by using any sort of general purpose reflection techniques.

My suggestion would be to figure out which objects and properties you are most interested in comparing and then write it yourself. First compare the workbook properties, then see if the worksheets have the same name, and then compare each worksheet with the same name by range name of the cells used, then if the range of the cells used are the equal compare the values of each of the corresponding individual cells. Then compare the formatting, styles, formulas, colors, etc. Build it up one layer at a time. It's very dirty and ugly but it might work for you.