I'm writing a JUnit test for some code that produces an Excel file (which is binary). I have another Excel file that contains my expected output. What's the easiest way to compare the actual file to the expected file?
Sure I could write the code myself, but I was wondering if there's an existing method in a trusted third-party library (e.g. Spring or Apache Commons) that already does this.
You might consider using my project simple-excel which provides a bunch of Hamcrest Matchers to do the job.
When you do something like the following,
assertThat(actual, WorkbookMatcher.sameWorkbook(expected));
You'd see, for example,
java.lang.AssertionError:
Expected: entire workbook to be equal
but: cell at "C14" contained <"bananas"> expected <nothing>,
cell at "C15" contained <"1,850,000 EUR"> expected <"1,850,000.00 EUR">,
cell at "D16" contained <nothing> expected <"Tue Sep 04 06:30:00">
at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
That way, you can run it from your automatted tests and get meaningful feedback whilst you're developing.
You can read more about it at this article on my site
Here's what I ended up doing (with the heavy lifting being done by DBUnit):
/**
* Compares the data in the two Excel files represented by the given input
* streams, closing them on completion
*
* @param expected can't be <code>null</code>
* @param actual can't be <code>null</code>
* @throws Exception
*/
private void compareExcelFiles(InputStream expected, InputStream actual)
throws Exception
{
try {
Assertion.assertEquals(new XlsDataSet(expected), new XlsDataSet(actual));
}
finally {
IOUtils.closeQuietly(expected);
IOUtils.closeQuietly(actual);
}
}
This compares the data in the two files, with no risk of false negatives from any irrelevant metadata that might be different. Hope this helps someone.
I needed to do something similar and was already using the Apache POI library in my project to create Excel files. So I opted to use the included ExcelExtractor interface to export both workbooks as a string of text and asserted that the strings were equal. There are implementations for both HSSF for .xls as well as XSSF for .xlsx.
Dump to string:
XSSFWorkbook xssfWorkbookA = ...;
String workbookA = new XSSFExcelExtractor(xssfWorkbookA).getText();
ExcelExtractor has some options for what all should be included in the string dump. I found it to have useful defaults of including sheet names. In addition it includes the text contents of the cells.
A simple file comparison can easily be done using some checksumming (like MD5) or just reading both files.
However, as Excel files contain loads of metadata, the files will probably never be identical byte-for-byte, as James Burgess pointed out. So you'll need another kind of comparison for your test.
I'd recommend somehow generating a "canonical" form from the Excel file, i.e. reading the generated Excel file and converting it to a simpler format (CSV or something similar), which will only retain the information you want to check. Then you can use the "canonical form" to compare with your expected result (also in canonical form, of course).
Apache POI might be useful for reading the file.
BTW: Reading a whole file to check its correctnes would generally not be considere a Unit test. That's an integration test...
The easiest way I find is to use Tika. I use it like this:
private void compareXlsx(File expected, File result) throws IOException, TikaException {
Tika tika = new Tika();
String expectedText = tika.parseToString(expected);
String resultText = tika.parseToString(result);
assertEquals(expectedText, resultText);
}
<dependency>
<groupId>org.apache.tika</groupId>
<artifactId>tika-parsers</artifactId>
<version>1.13</version>
<scope>test</scope>
</dependency>
You could use javaxdelta to check whether the two files are the same. It's available from here:
You may use Beyond Compare 3 which can be started from command-line and supports different ways to compare Excel files, including:
To test only content of the first sheets in Kotlin (easily can be converted to java).
private fun checkEqualityExcelDocs(doc : XSSFWorkbook, doc1 : XSSFWorkbook) : Boolean{
val mapOfCellDoc = doc.toList().first().toList().flatMap { row -> row.map { Pair(PivotExcelCreator.IndexInThePivotTable(it.rowIndex,it.columnIndex),it.stringCellValue) }}.toMap()
val mapOfCellDoc1 = doc1.toList().first().toList().flatMap { row -> row.map { Pair(PivotExcelCreator.IndexInThePivotTable(it.rowIndex,it.columnIndex),it.stringCellValue) }}.toMap()
if(mapOfCellDoc.size == mapOfCellDoc1.size){
return mapOfCellDoc.entries.all { mapOfCellDoc1.containsKey(it.key) && mapOfCellDoc[it.key] == mapOfCellDoc1[it.key]}
}
return false
}
data class IndexInThePivotTable(val row: Int, val col: Int)
and in your code add assert
assertTrue(checkEqualityExcelDocs(expected, actual), "Docs aren't equal!")
as you can see doc.toList().first() will take only the first sheet of document, if you need to compare each sheet respectively change code a little.
Also it is quite good idea to not take into account "" empty strings cells, I didn't need this functionality (As well, simply add this part, if you need).
also it can be useful information
//first doc I've got from outputstream such way
val out = ByteArrayOutputStream()
//some method which writes excel to outputstream
val firstDoc = XSSFWorkbook(ByteArrayInputStream(out.toByteArray()))
and second doc from file to compare with
val secondDoc = XSSFWorkbook(Test::class.java.getClassLoader().getResource("yourfile.xlsx").path)
Just found out there's something in commons-io's FileUtils. Thanks for the other answers.
Please, take a look at the site to compare the binary files, http://www.velocityreviews.com/forums/t123770-re-java-code-for-determining-binary-file-equality.html
Tiger
Maybe... compare MD5 digests of each file? I'm sure there are a lot of ways to do it. You could just open both files and compare each byte.
EDIT: James stated how the XLS format might have differences in the metadata. Perhaps you should use the same interface you used to generate the xls files to open them and compare the values from cell to cell?
