Delete or remove data from an Excel worksheet without deleting the entire Excel file

Product Forums Katalon Studio
,
1

Hello Team,

Kindly assist me how to delete or remove data from an Excel worksheet without deleting the entire Excel file.

Regards,
Shiva

1 Like
2

Um…This sparks some questions…

Questions

  • Is this Excel spreadsheet also being tracked by Katalon’s Data Files feature? If so, you don’t want to programmatically touch it. Katalon already has a lock on any files that are marked as Data Files.
  • What exactly are you tryna delete? Are we talkin Rows, Cells, … ?
  • You say “Worksheet”. That isn’t a thing. Do you mean deleting from multiple Sheets in the Workbook, or just one

Assuming that you aren’t tryna delete an Excel file that you have set up as a Data File in Katalon Studio, and are just looking to delete a Row of data from the first Sheet, you can do the following:

Manipulating the Excel Workbook

Let’s use this boilerplate to do our Excel file manipulation:

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

final String excelFileName = "test.xlsx"

WorkbookFactory.create(new FileInputStream(excelFileName))
	.withCloseable { Workbook workbook ->
		deleteFromWorkbook(workbook);
		
		new File(excelFileName).withOutputStream { OutputStream outputStream -> 
			workbook.write(outputStream);
		}
	}
	
void deleteFromWorkbook(Workbook workbook) { 
	throw new cucumber.api.PendingException()
}

Deleting Row from the Sheet

Unfortunately, there is no method that will just delete the Row from the Sheet without leaving behind a blank row (Sheet.removeRow(Row row) will actually delete the row, but will leave behind blank row). You’ll have to shift the Rows up (that’s just how spreadsheets work…)

void deleteFromWorkbook(Workbook workbook) { 
	Sheet sheet = workbook.getSheetAt(0);
	
	final int rowNumberToDelete = 4; // TODO: replace with the row number that you're actually trying to delete
	
	if (rowNumberToDelete <= sheet.getLastRowNum()) {
		sheet.removeRow(sheet.getRow(sheet.getLastRowNum()))
		return;
	}
	
	sheet.shiftRows(rowNumberToDelete + 1, sheet.getLastRowNum(), -1)
}

Hope you only have one row to delete!! Here is more information on how Sheet.shiftRows() works

Hope this helps, and happy coding!

3

Hi Michael,

I try to deleting all Sheet’s in the excel file. Please advise me solution.

Thanks
Shiva

1 Like
4

WHY?!

Just delete the file itself and be done with it…!

5

I am generating data from genRocket and adding into Testdata file,
for adding test data file requires a manual steps : like Manage test data | Katalon Docs

to avoid these steps, Just I am deleting all sheets in the Excel file and re using the same Excel file.

our agenda is Integrate GenRocket test data into a Katalon Excel file and remove/delete Excel data after completion of test suite execution in Katalon Studio.

6

You’re going to have a bad time… Katalon Studio , just like any other program, maintains a lock on any Excel files that it controls… For example, any files it has to have open to access as test data files.

If you’re trying to sync it up as a test data file, it’s going to need to know the sheet and the spreadsheet that it’s in… You don’t want to delete that, and you literally can’t do it programmatically from Katalon Studio for the reason I mentioned earlier.

7

An alternative, is to write some type of record handler, that will access your sheets / spreadsheets, and that way, you can do this without having to lock certain sheets / spreadsheets in… I can help you with that, as I use that to maintain, for example, “backbone data” such as profiles

UPDATE: I read more into your request. In order to fulfill it, I need to know:

  • what does your genRocket tool require, to generate the spreadsheet full of data? Does the spreadsheet have to exist, and have sheets with headers?
  • can the genRocket tool append data to the Sheets?