Trouble writing data into a xlsx file using Apache POI - Excel becomes corrupted afterwards

Question

I have removed a lot of code, mainly try-catch blocks and pasted here what I believe would be easy for you to look at and advise me on resolving the issue I am facing. I am running the below script in a Groovy Script step in SoapUI tool. Most of the variable datatypes are following the Groovy language syntax.

When I run this code, a xlsx file with file name mentioned in the destPath variable is created but with a size of 0 KB. When I try to open the file, I am seeing the message "Excel cannot open the file "filename" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." I could not see where I am making mistake.

def srcPath = "C:\\Test Data\\Test Data2.xlsx"
def destPath = "C:\\Test Data\\Results\\destSheet.xlsx"

def setData = new SetData(log,srcPath,destPath)

log.info setData.setCellData("Result",0,"Testing123")

class  SetData{
def log; //log variable to print values on the console
String srcPath; 
String destPath;
XSSFWorkbook workbook;
OPCPackage pkg;

SetData(log,srcPath,destPath){
this.log = log; 
this.srcPath =srcPath;
this.destPath = destPath}

public String setCellData(String colName,int rowNum, String data){

OPCPackage pkg = OPCPackage.open(srcPath);
Workbook workbook = WorkbookFactory.create(pkg);        
if(rowNum<0)
return "false"; 
int colNum=-1;
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
if(row.getCell(i).getStringCellValue().trim().equals(colName))
colNum=i;           
}
sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum+1);
log.info "Row data " + row  //log.info is equivalent to System.out.Println
in Java,to print values on the console.

XSSFCell cell = row.getCell(colNum);

// cell style
CellStyle cs = workbook.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);
log.info data; //prints Testing123
cell.setCellValue(data); // Set the cell data
log.info "raw cell Value" + "***" +  
cell.getRichStringCellValue().getString() //Prints "Testing123"
log.info "Column index "+ cell.getColumnIndex() // Prints 3
log.info cell.getRowIndex() // Prints 0
fileOut = new FileOutputStream(new File(destPath)); 
workbook.write(fileOut);
fileout.flush();
fileOut.close();
fileOut=null;
pkg.close()
return "true";
}

Answer 1

Looks like your mixing XSSFWorkbook and Workbook. You should also check to see if the rows and cells you're reading from your source file actually have those rows/cells and create them if they don't. Here's a simplified version of your code for creating an Excel workbook from another workbook and writing to it:

    String srcPath = "C:\\projects\\source.xlsx";
    String destPath = "C:\\projects\\destSheet.xlsx";

    XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new File(srcPath));        

    XSSFSheet sheet = workbook.getSheetAt(0);

    XSSFRow row = sheet.getRow(1);
    if(row == null) { row = sheet.createRow(1); }

    XSSFCell cell = row.getCell(0);
    if(cell == null) { cell = row.createCell(0); }

    cell.setCellValue("Testing123");

    FileOutputStream fileOut = new FileOutputStream(new File(destPath)); 
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();