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.