Hi,
We have reporting module in web application in which we have different reports and have functionality to run report in Excel format.File gets downloaded from website to local machine.Can we verify the Excel sheet data with expected value using Katalon studio and if yes,then how?
Thanks
Yes, this is doable. You will need to import a third-party library thatâs capable of reading/writing excel data. I would recommend Apache POI: https://poi.apache.org/
If you need a tutorial on how to import any external library into your Katalon project, give this a read: https://docs.katalon.com/katalon-studio/tutorials/import_java_library.html
Finally, you may need to mess with the desired capabilities of whichever browser(s) that you use for testing. Namely, you may want to set a default location for files downloaded from the browser(s), so that when it comes time to pull the file into your script and validate, you know where to find it. Hereâs an example of how to set this up for Chrome:
1.) Open Project > Settings > Desired Capabilities > Web UI > Chrome:
2.) Click the âAddâ button, and set the name of the new property to âprefsâ, the type to âDictionaryâ:
3.) In the value column, click on the ellipses, and set name = âdownload.default_directoryâ, type = String, value = âmy/downlaod/directoryâ:
Let me know if you need any more details, Iâd be happy to help 
After configuration how can i create test case for verify the expected result with excel data?In my case, every time there would be new excel file generated from web application.So how can i pick the that dynamic file name and compare with expected result?
Thanks,
We have web application in which we want to achieve following functionality.
1.Login on web application.
2.Go to reporting module>>pick a report and run the report .
3.Report would downloaded in local machine with datetime(Everytime namename+datetime).
4.After that we want to verify excel sheet first row and last row data with our expected result.
Can you help me with the complete example ?In our case, we need test case where automatically pick the report with date time and then verify with expected result.
Thanks
Now weâre getting into a âgive a man a fish vs. teach a man to fishâ situation⌠
Iâve given you the tools that you need, but itâs up to you to do the work. I will give you an idea of how we solve this problem, but there are any number of ways to do this, and it really depends on the nature of your project in particular.
We use a utility class weâve called DownloadManager, which is tasked with handling files downloaded from the AUT:
public class DownloadManager {
private static final String downloadPath = "C:/katalon_test_downloads";
private DownloadManager() {}
public static File getLastDownloadedFile() {
File downloadDirectory = new File(downloadPath);
File[] downloadedFiles = downloadDirectory.listFiles();
if(downloadedFiles == null || downloadedFiles.length == 0) {
return null;
}
File lastModifiedFile = downloadedFiles[0];
for(int i = 1; i < downloadedFiles.length; i++) {
if(lastModifiedFile.lastModified() < downloadedFiles[i].lastModified()) {
lastModifiedFile = downloadedFiles[i];
}
}
return lastModifiedFile;
}
public static File getDownloadedFile(final String fileName) {
boolean downloaded = isFileDownloaded(fileName);
if(downloaded) {
return new File(downloadPath + File.separator + fileName);
}
else {
return null;
}
}
public static boolean isFileDownloaded(final String fileName) {
File downloadDirectory = new File(downloadPath);
File[] downloadedFiles = downloadDirectory.listFiles(new FilenameFilter() {
public boolean accept(File dir, String name) {
return name.equals(fileName);
}
});
}
public static void waitForDownload() {
File downloadDirectory = new File(downloadPath);
int fileCount = downloadDirectory.listFiles().length;
int expectedFileCount = fileCount + 1;
long startTime = System.currentTimeMillis();
while(fileCount < expectedFileCount && (System.currentTimeMillis() - startTime) < 300000) {
fileCount = downloadDirectory.listFiles().length;
Thread.sleep(1000);
}
}
}
In here, you have a bunch of useful methods that your script can call to retrieve files for processing. The general workflow in your script would look something like:
1.) Download your file from the app. If youâve followed my Desired Capabilities config from above, you know where this file will ultimately end up, regardless of which machine itâs running on.
2.) Wait for the download to be complete. We do this by calling the waitForDownload() method in the util class from above. All it does is wait for the number of files in the target directory to increment.
3.) Once youâre sure the file is done downloading, call either of the get methods to retrieve the file. Since you said the filename is dynamic in your case, I would use the getLastDownloadedFile() method, which just scans the directory for the file with the most recent âDate modifiedâ value and returns it.
4.) Your script should now have a File object to work with, which is where Apache POI comes in.
Caution: The DownloadManager utility is NOT thread safe! If youâre running tests in parallel, and any two tests are downloading files, you may not get the desired behaviorâŚ
Hi Dayal,
Please use the below custom keywords for verifying the text in the excel.
// Custom keywords to read and write excel
public class Excel_Operations {
@Keyword
//Read excel cell value by passing the path of the file
def Excel_Read(String FilePath) {
try {
// Specify the path of file
File src=new File(FilePath)
// load file
FileInputStream fis=new FileInputStream(src)
// Load workbook
XSSFWorkbook wb=new XSSFWorkbook(fis)
// Load sheet- Here we are loading first sheet only
XSSFSheet sh1= wb.getSheetAt(0)
// getRow() specify which row we want to read and getCell() specify which column to read.
// getStringCellValue() specify that we are reading String data.
GlobalVariable.VerifyExcelText = sh1.getRow(4).getCell(0).getStringCellValue()
System.out.println(GlobalVariable.VerifyExcelText)
// Exception handling if the cell value is empty
} catch (Exception e) {
System.out.println(e.getMessage())
}
WindowsUtils.killByName("EXCEL.exe")
}
@Keyword
//Write excel cell value by passing the path of the file
def Excel_Write(String FilePath, String TextValue) {
FileInputStream file = new FileInputStream (new File(FilePath))
XSSFWorkbook workbook = new XSSFWorkbook(file)
XSSFSheet sheet = workbook.getSheetAt(0)
//'Write data to excel'
sheet.getRow(1).createCell(1).setCellValue(TextValue)
file.close()
FileOutputStream outFile =new FileOutputStream(new File(FilePath))
workbook.write(outFile)
outFile.close()
}
}
Once you get the text in the global variable then you can verify with expected text. in the above example i am reading the value which i need.
OP didnât actually mention whether the document was in .xls or .xlsx format, so your specific use of XSSFWorkbook may not work. Also, youâre opening an input stream on a per-method basis, which is completely unnecessary. Finally, why create a global variable with the cell text? Just return the value to the caller!
Hereâs an amended version of the keyword that we use:
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
public class ExcelDocument {
private final Workbook workbook;
private Sheet currentSheet;
public ExcelDocument(final File file) {
this.workbook = WorkbookFactory.create(file);
this.currentSheet = workbook.getSheetAt(0);
}
public List<String> getSheets() {
List<String> sheets = new ArrayList<String>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheets.add(workbook.getSheetName(i));
}
return sheets;
}
public void switchToSheet(final String name) {
currentSheet = workbook.getSheet(name);
}
public void switchToSheet(final int index) {
currentSheet = workbook.getSheetAt(index);
}
public String getCellText(final int rowIndex, final int columnIndex) {
Row row = currentSheet.getRow(rowIndex);
Cell cell = row.getCell(columnIndex);
String text = cell.getStringCellValue();
return text;
}
}
Instances of this class are mutable: i.e. currentSheet initially references the first sheet in the workbook, but can be changed with the switchToSheet() methods. From there, you can get the text of any cell within the current sheet that you want. While we donât do any document writing, you could conceivably add a method to write data to a cell as well. This will work irrespective of the version of Excel the document was generated from.
Thanks Brandon.
I have used the amended version .What would be the keyword to verify with expected result.I have used keyword on following method
@Keyword
public String getCellText(final int rowIndex, final int columnIndex) {
Row row = currentSheet.getRow(rowIndex);
Cell cell = row.getCell(columnIndex);
String text = cell.getStringCellValue();
return text;
This work for me !
Hi,
How did you use the keyword to verify the excel data with expected one?Can you give more detail about it?
Thanks
Can anyone help me?
Using my above keywordâŚ
ExcelDocument excelDocument = new ExcelDocument(myFile);
assert excelDocument.getCellText(1, 1).equals(myExpectedText);
I have used your line of code into my test case.Getting the error.Am i doing wrong?
I would need to know which error you are getting⌠Just from looking at it, it looks like you need to import the ExcelDocument class.
Press ctrl + shift + o, and select your custom keyword.
The ExcelDocument class that Iâve shown takes a File object as a constructor argument, not a String. The DownloadManager class that I gave toward the beginning of this topic returns File objects, which you would pass to the ExcelDocument constructor.
Sorry for again disturbing you.I have created the object of DownloadManager class and used in ExcelDocument class.
DownloadManager dm = new DownloadManager()
File dmf= dm.getDownloadedFile(âmdCustomReportAutomation.xlsâ)
ExcelDocument excelDocument = new ExcelDocument(dmf)
This time got different error.
I would like to thank you for your support.
Please put a breakpoint on the third line of the above code, and run in debug. Iâm guessing that âdmfâ = null. Where does the âmdCustomReportAutomation.xlsâ file exist after it is downloaded?
Thank You very much Brandon.Issue got resolved now.
No problem, glad you are up and running. Please consider marking the reply that best solves your problem as a solution to this topic, so that others reading it may quickly find solutions. 
