Does anyone know how to open an Excel file when you don't know the name of the Excel sheet that the file contains?

I’m trying to open an Excel file with the ExcelFactory class. But ExcelFactory.getExcelData gives an error:

java.io.IOException: Cannot find the excel sheet. Please check your data source and try again.

ExcelFactory.getExcelDataWithDefaultSheet does work but the name of the sheet is with each download different. So does anyone know how to open an Excel file with the ExcelFactory class, when you don’t know the name of the Excel sheet?

1 Like

The sheet or the spreadsheet file? If the latter, how do you not know that file’s name?

this is something like:

  • how do I open a door without knowing where the door is…

provided you can actually open the file (house), i suppose with a bit of digging, you may be able to retrieve somehow the sheets (doors) available and knock at the right one…

If this were StackOverflow question, it would be downvoted to hell and back, and closed already !

That being said, I read the documentation to this Katalon class…and it asks for fullFilePath (why?! Relative file path should be enough!)

It seems like the Excel spreadsheet has timestamped or random name, and/or is generated dynamically at runtime… @Lukas_B is that true?

@bionel @mwarren04011990
I know the name of the Excel file, which is something like ''Run_" + timestamp + “.xls” , that is not difficult to do with regex. But the name of the sheet inside this file is also dynamically generated timestamp based. I cannot say open the Excel file with sheet “202312111833.*” can I?

Good, so now you have a way to open this file…

There is this library called Apache POI, that is designed to handle everything spreadshet related, including finding the Sheet.

Personally, I would use it, instead of this Katalon library.

You could use it like thus, assuming that you have an xlsx sheet:

import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory

final String timestamp = "202312111833"; // or whatever the file timestamp is

final String timestampRegex = /\d{10}/; // or whatever your timestamp regex is

WorkbookFactory.create(new File("./Run_${timestamp}.xlsx"))
	.withCloseable { Workbook workbook -> 
		final Sheet targetSheet = workbook.find { Sheet sheet -> return (sheet =~ timestampRegex).matches() }
		
		// TODO: anything you want to do with the Sheet here...
	}

You can even edit the spreadsheet with this library, just declare OutputStream withCloseable, and workbook.write(outputStream) when you’re done…!

Thanks @mwarren04011990 I will have a look.

1 Like

I suppose you want to find out the last-downloaded file into your $HOME\Downloads\ folder. Katalon Studio does not provide a out-of-box solution to this. You have to invent your solution.

How to?

Just take a list of files in the current Downloads folder, sort the entries by the update timestamp
in the descending order. Then the top entry will be the file downloaded last, which will the the Excel file that your test downloaded.

How can you get the list file contained files in a folder? See

How to sort the list of files by timestamp?

1 Like

How’d it end up going?