How to create test cases in order to test columns in an excel file?

Question

I have to test an excel file which generated by the system. This file has around 40 columns in it. Do I have to create test cases for each column separately in order to test?

File contains payment records. Bank details,account numbers,amount etc.

Can't i check them in a single test case or multiple without having test cases for each column? Please explain

Answer 1

It would be better if you write scenarios for each column at least one. The reason beyond is the export file contains the banking pieces of information so there must be some condition and rules to display the data format in the excel.

Ex: Amount columns may contain the decimal values so it needs to be represented in such format.

Cover some multiple test cases for most highlighted fields like account no, amount etc..

Sometimes the special characters will display in the format of junk

Answer 2

If you deal with any point of error (exceptions) that the checking of each column can raise, you can use Soft Asserts to avoid failures to stop the test.

softAssert = new SoftAssert();
systemFile = openSystemFile();
expectedFile = openExpectedFile();
columns = listOfColumns();

for (column in columns) {
    softAssert.assertEquals(systemFile[column], expectedFile[column]);
}

softAssert.assertAll();

The softAssert object holds all the failures and release all of them when you call assertAll().

Answer 3

I've done the same in one of my project where i need to verify export functionality and validate the data. You can refer below checklist for reference

• Check whether file has been exported with valid file name and extension. Meaning full file name should be given. e.g. there is contact list and button to export in xls file then preferred filename could be contact_details.xls instead of xyz.xls.
• Check whether user is able to open the exported file.
• Check weather data format in excel is consistent or not ( font size, type etc)

• Check exported file have the proper header related to data that user can easily understand. Lets say it a bank details file then first row header should have the headers like

  Bank Name____Branch Address____IFSC Code____Account Number____Account Type

• Make sure data is exported in correct format and readable (sometime can garbage if wrong format selected)

• You can consider the file size as well and check whether its in acceptable range
• You have to pick some records from your application and have to match in excel sheet whether all details for that particular records are correct in excel file and in same row as it should.