Vsto Study Notes (9) Comparison of Excel content

When talking about file content comparison, we may first think of professional comparison software such as ultracompare, which has powerful functions and can quickly and accurately compare text-based file content, detailed difference reports are available for easy analysis. In fact, various version control software also contains more or less comparison functions, such as TFS, CVS, SVN, etc. However, if the file to be compared is not based on the text type, nothing can be done. Today, I will talk about the Excel Comparison Method and Its Features. I will also discuss it with you. If you have a better method, please share it with us.

 

I. Excel file architecture

Excel file structure. an Excel file is a workbook that can contain several worksheets. This structure makes it difficult to compare the content in Excel, especially when the amount of data in the worksheet is large, conventional comparison software is powerless.

 

2. Three comparison methods

In the following three ways, I will compare the content in two Excel files. First, I will prepare two test Excel files. For the sake of simplicity, both Excel files only contain one worksheet with some numbers:

Figure 12.161.xlsx

 

Figure 22.162.xlsx

 

1. First, I wrote a pieceProgram, Compare cells one by one (assuming that the worksheet names and numbers in the two Excel files are identical ):

 

 

Code

Private   Void Fnexcelcompare ( String V_strsourcepath, String V_strdestpath)
{
This . _ Int content, number of different cells =   0 ;
This . _ DIC content is different. Clear ();

Excel. Application app =   New Excel. Application ();
App. displayalerts =   False ;
Excel. Workbook srcbook = App. workbooks. Open (v_strsourcepath );
Excel. Workbook destbook = App. workbooks. Open (v_strdestpath );
Filestream log =   New Filestream (system. Windows. Forms. application. startuppath +   @" \ Logs \ reportcheck. Log " , Filemode. openorcreate, fileaccess. readwrite );
Streamwriter =   New Streamwriter (log );
String MSG =   String . Empty;

Writer. writeline ( " **************************************** **************************************** * \ N " );
Foreach (Excel. worksheet Sheet In Srcbook. worksheets)
{
For ( Int I =   1 ; I <= Sheet. usedrange. Rows. Count; I ++ )
{
For ( Int J =   1 ; J <= Sheet. usedrange. Columns. Count; j ++ )
{
String SRC = Sheet. cells [I, j]. value2 =   Null   ?   String . Empty: sheet. cells [I, j]. value2.tostring ();
String Dest = Destbook. worksheets [sheet. Name]. cells [I, j]. value2 =   Null   ?   String . Empty: destbook. worksheets [sheet. Name]. cells [I, j]. value2.tostring ();

If (SRC ! = DEST)
{
MSG = Datetime. Now. tostring () +   " ------ "   + Sheet. Name +   " 【 "   +   This . Fngetexceladdress (I, j) +   " ] The content in the cell is different \ n " ;
This . _ Int content, number of different cells ++ ;
This . _ DIC content is different. Add ( This . _ Int number of different cells, MSG );
Writer. writeline (MSG );
}
}
}
}
Writer. writeline ( " **************************************** **************************************** * \ N " );

Srcbook. Save ();
Destbook. Save ();

App. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (APP );
System. runtime. interopservices. Marshal. releasecomobject (srcbook );
System. runtime. interopservices. Marshal. releasecomobject (destbook );
App =   Null ;
Srcbook =   Null ;
Destbook =   Null ;
GC. Collect ();
}

 

 

2. Use openxml SDK 2.0.

PreviousArticleThe simple usage of openxml SDK and related tools can also be used to compare excel. Of course, it is limited to excel 2007 and Excel 2010.

If you have not installed openxml SDK 2.0, you canHereDownload.

After the download is complete, you can use it after step-by-step installation.

1) enable productivity tool:

 

2) make some simple configuration for this tool:

You can select to display row numbers, ignore namespaces, and ignore declarations:

Select the Excel version to be compared. For the Excel 2010 I used, select the second one:

 

3) click Compare files, select two Excel files to be compared, and click OK ]:

 

4) you can see that the Excel file is split into parts, and the marked green content is different:

 

5) Select a part marked as green and click View par diff to View Details:

This interface is very similar to the interface in the traditional comparison software, and you can easily see the differences.

The disadvantage of this method is that the comparison results are not intuitive enough to obtain comparative summary results, especially when the data volume is large.

 

3. Use spreadshcompare

Spreadshcompare is a VBA plug-in written by foreign users. It is open-source for free and can be downloaded from SourceForge. The latest version is 1.15. It supports Excel 2003 and Excel 2007. It does not support Excel 2010x64. I am using Excel 2010x64, so I am currently testing in a virtual machine.

I used hyper-V to install Windows Server 2003 R2 and Office 2007.

1) After downloading and installing, you can see a small balance icon in "add-on" of Excel:

 

2) open two Excel files to be compared: 1.xlsx1_2.xlsx, select two Excel files to be compared, and click [next ]:

 

3) Select cell-by-cell comparison to keep the case sensitive. Other options are default:

 

4) Select the worksheet to be compared and click [next ]:

 

5) Select and compare all regions and click [next ]:

 

6) Select the generate summary table option to keep the default value:

 

7) set the worksheet order, and then click [compare ]:

 

8) First, a brief summary is provided:

 

9) Click OK to generate two workbooks, one for recording all the differences, and the other for cell comparison and analysis:

 

 

10) at the same time, the source file is also marked with color, yellow indicates different:

 

After a large amount of data tests, the plug-in has high performance and high speed. I will try to modify this plug-in laterSource codeAnd strive to support Excel 2010x64.

Others include compare spreadsheet for Excel (commercial software), Excel compare (commercial software), and synkronizer for Excel (commercial software.

 

Iii. Summary

We have discussed the Excel comparison and provided three solutions. The three solutions have their own advantages and disadvantages, but they are applicable in different scenarios. Please make a selection based on your needs.