Florencesoft

Although DiffEngineX is not currently exposed as a COM component or DLL class library, it is possible to invoke DiffEngineX programmatically from software, as well as from the Windows Command Prompt. DiffEngineX can be driven by command line arguments. A full list of arguments is given in the help file available from the DiffEngineX menu item Help-->Help Topics-->Command Line Arguments. The list is also available on our Internet help page.

A snippet of C# .NET source code showing how to do this is given below. Although the string assigned to .Arguments has been split across several lines here, ensure that your code is on a single one.

(The /outbook1 and /outbook2 arguments are optional, if you don't want to save the DiffEngineX created reports to your filesystem. As /show has been used, the /report argument is also optional in the example below.)

using System.Diagnostics;

namespace CallDiffEngineXExample
{
private void CallDiffEngineXProgrammatically1()
{
Process process = new Process();
try
{
string filename = @"C:\Program Files\Florencesoft\DiffEngineX\DiffEngineX.exe";
process.StartInfo.FileName = filename;
process.StartInfo.UseShellExecute = true;
process.StartInfo.Arguments = @"/inbook1:""C:\Users\Bob\test worksheets\original.xlsx""
/inbook2:""C:\Users\Bob\test worksheets\modified.xlsx""
/report:report1.xlsx
/outbook1:outbook1.xlsx
/outbook2:outbook2.xlsx
/compareexcelnames
/coloralternaterows
/addhyperlinks
/colordifferences
/show";
process.Start();
process.WaitForExit();
}
catch
{

}
finally
{
process.Close();
}
}
}

Version 2.14 of DiffEngineX can compare the Visual Basic for Applications (VBA) code and macros contained in Microsoft Excel spreadsheets. The new difference report is in HTML format and is viewed in your default web browser.

Version 2.01 of DiffEngineX allows you to specify a minimum percentage change between two numbers before they are flagged as different.

We have recently written a concise description of DiffEngineX and guide to its use. It is recommended reading for everyone who uses the software. It can be found at

How To Use DiffEngineX To Compare Excel Spreadsheets

DiffEngineX can now compare Excel cell comments. This new functionality has to be explicitly turned on via the Options dialog. The comment differences are listed in a new sheet, called Excel Comments Comparison, on the difference report.

If a workbook of interest is compared against a blank spreadsheet, this option will effectively create a list of all the comments.

DiffEngineX can now treat numeric values as equal if they differ less than a specified value. This adds to its previous ability of being able to round numbers to a specified number of decimal places before comparing them.

NOTE: If you wish to compare the calculated values of formulae, rather than the formulae text itself, you should select the option Their Calculated Values.

Add Hyperlinks To Aid Navigation has been added to the Options dialog. This connects the difference report to every cell found to differ between two spreadsheets. Please refer to the Hyperlinks Help Entry for details.

Users can now discover new DiffEngineX features and auto-update the software, if appropriate, via a new menu item called Check for Updates available from the Help menu.

An option has been added to allow DiffEngineX to compare Excel defined names.

Excel allows names with either workbook or worksheet-level scope to be created. Workbook-level scope names are only compared when Whole Workbooks is selected on the main part of the user-interface. Likewise if /sheets is supplied as a command-line argument, workbook-level scope names are not compared.

Ranges and formulae referred to by names are always reported in R1C1 notation regardless of DiffEngineX's A1 or R1C1 notation setting.

This is because a cell reference in R1C1 notation is self-contained. Cell references in A1 notation can only be understood with reference to what Excel regards is the Active Cell at a particular moment in time, if they have any part of them that is a relative rather than absolute reference.

For example the Excel defined name and definition of CellBelow = Sheet1!R[1]C unambiguously refers to the cell 1 row down and 0 columns across, regardless of what the Active Cell happens to be at the time

However in A1 notation the same Excel defined name and defintion is

CellBelow = Sheet1!D18 when the Active Cell is $D$17

and

Cell Below = Sheet1!E18 when the Active Cell is $E$17

and

CellBelow = Sheet1!J3 when the ActiveCell is $J$2.

As such, due to relative references, it is better to compare name definitions in R1C1 notation.

Extra Point:
If a workbook of interest is compared against a blank spreadsheet, this new option will effectively report on all the defined names.

We have tested DiffEngineX with the 32-bit and 64-bit Windows 7 Release Candidate and can confirm our software is compatible with it.

As the software had already undergone modifications to be compatible with Windows Vista it did not need any further changes.

The original purpose of the alignment algorithm was to align rows and columns that match up between two worksheets. It was not concerned with unmatched rows. The intent was to minimize differences by alignment before a cell-by-cell comparison.

Users that were concerned with spotting what rows had been inserted and deleted found that sometimes an unmatched row in sheet #1 was paired up with another unmatched row in sheet #2.

This meant that the color used to indicate a modification was used, instead of the colors selected for additions and deletions. DiffEngineX by default works at the cell level, not at the row level.

Our first attempt to solve this problem was the launch of the Interpret modified cell as deletion and addition... feature found on the Extras dialog. However this only changes the color for the unique identifier portion of a row as selected via the Align Rows dialog box.

Now we have a far better solution. We now recommend users turn on the Use Alignment Plus feature which makes sure that all unmatched rows are paired up with blank rows. This way the appropriate color for either a new or deleted row is used.

Use Alignment Plus also works for columns.

DiffEngineX could always highlight differences at the character level, when the option Color in red precisely the parts of... was turned on. However the character level differences were shown on the difference report. The difference report is a cell-by-cell listing of differences and is distinct from the color highlighted workbooks copies DiffEngineX produces. Typically the difference report is titled Sheet1.

A verbose report without context is sometimes difficult to interpret.

Now DiffEngineX can highlight character level differences directly on the spreadsheet copies it produces. The relevant option to turn on is called Highlight Character Level Differences.

By default DiffEngineX uses the color red for a deleted cell, green for an added cell and light orange for a modified cell. However when is a modified cell not really a modified cell, but rather part of a row that has been entirely deleted and replaced by another row that just happens to be occupying its old position?

When Align Rows is specified, you select a column or columns that specify the parts of a row which make up its unique identifier. If the unique identifier is present in one worksheet, but not the other then the row has either been deleted or it is a new one. As such coloring the identifying part of the row light orange is inappropriate, as what really has happened is that there is one deletion and one addition.

The Extras dialog now has an option called Interpret modified cell as deletion and addition... which lets you choose how DiffEngineX colors the unique parts of each row.

You may wish to get Excel to sort the color highlighted sheets afterwards on a color of your choice (only available in Excel 2007) to get all the deleted and added rows to line up. Remember to only sort on the unique parts of a row.

When determining whether a row has been added or deleted, it is helpful to only look at the unique parts of each row.

* This option is now obsolete. We strongly recommend the use of Use Alignment Plus instead of this option.

Last week DiffEngineX was used to compare two Excel 2007 spreadsheets each with at least 750, 000 rows. The comparison took approximately 4 hours 30 minutes to complete. The user confirmed they were pleased with the results. Align Rows was turned on. We generally recommend the data should be pre-sorted in Excel using functionality available from its Data tab or menu before a comparison, if necessary.

We have compared spreadsheets with a million rows ourselves and seen results in a couple of minutes. So why do some comparisons take hours and some only minutes? The answer is that DiffEngineX is much faster when the number of differences buried in a million rows is small, rather than large. With a large number of differences, more blank rows have to be inserted to get the data to line up and this is a time-consuming, Excel mediated operation.

Additionally DiffEngineX works best with Excel 2002 and above. Slower performance has been noted when DiffEngineX is used with old versions of Excel, such as Excel 2000.

This is the first report of DiffEngineX being used with such a large amount of data. The key points are that the comparison may take hours and that you should ensure the Align Rows feature is selected. Unless your data is guaranteed to be in sorted order, you should get Excel to sort it before a comparison. (This does not apply when comparing formulae based models.)

In the days of Excel 2003, one customer told us he used DiffEngineX to compare two worksheets containing 50, 000 rows a piece.

With such large amounts of data, it is probably difficult to spot the differences in the color highlighted worksheets. That is why we recommend using the Extra dialog to turn on the Hide Matching Rows feature.

Typically with standard sized spreadsheets (2 Mb per workbook) we have seen comparison results generated within less than a minute.

DiffEngineX's Options dialog contains several useful features.

The two most powerful are Color in red precisely the parts of formulae and text constants that differ and Compact like changes with contiguous.

The effects of turning the first option on are shown below.



The differences between two spreadsheet cells are highlighted using the color red on the difference report. Additionally cells will be made larger so that lengthy amounts of text can be easily read.

Compact like changes with contiguous is intended for workbooks that contain blocks of equivalent formulae. Equivalent formulae are different in the sense that they reference different cells, but the different cells are the same relative offsets away. Financial models often contain blocks of tens to hundreds of equivalent formulae which are modified as a whole unit. When this option is turned on, only one change will be reported on the difference report, instead of hundreds of individual changes.