Författare: Lars Thunberg

In my current assignment / project we are running regression tests of text, csv and excelfiles. The system is based on .NET / C# and daily builds. Every night the checked in developed code is built and the system is built up from scratch. A number of output files should be the same as the day before i.e. regression test.

For C# there are a lot of ready solutions for comparing (diffs) excel files but we had no such at hands. We needed to built this on our own.

First I needed to convert excel files to csv files in order to make an easy compare/diff. For this I’m using a Visual Basic script that has been published on many sites.

XlsToCsv.vbs

if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

To use it just ”Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>”. You can call it from C# in the same way as you diff files (see below).

In order to diff files I use the FC tool in windows and call this from C# with Process.start. Here is a sample code:

string strCmdText = "/C c:/Windows/system32/fc /L ";                
Process P = Process.Start("CMD.exe", strCmdText + fileno1todiff + fileno2todiff);
P.WaitForExit();

//Exit codes from the differ
// -1 – Your syntax is incorrect.
// 0 – Both files are identical.
// 1 – The files are different.
// 2 – At least one of the files can’t be found.

int result = P.ExitCode;
//Make an assert check
Assert.AreEqual(0, result);

So this is a simple way to regression test excel files in C# with simple scripts.