Forum Discussion

_ivanovich_'s avatar
_ivanovich_
Frequent Contributor
4 years ago
Solved

I am not able to open xlsx file created with groovy code

Hi, i have soapui 5.6.0 version installed in my machine with windows 10. I am trying to create an xlsx file with groovy. I have these jar files in lib folder: commons-codec-1.15.jar commons-math...
_ivanovich_'s avatar
_ivanovich_
Frequent Contributor
4 years ago

It works if i create xls file but not xslx

So is there something im missing here or is it beacuse im using the soapui open version?

Do we have the same probleme with pro version?

Thank you

  • richie's avatar
    richie
    Community Hero
    4 years ago
    Hey _ivanovich_,

    I will try and reproduce with readyapi! when i finish work.

    However i seem to remember a time when readyapi! did NOT support .xlsx, but supported only .xls for certain things....i think the data driven functionality.. Perhaps this relates to your issue?

    I was wondering. Do you have to create an .xlsx/.xls? Reason i ask is thst i always find spreadsheets troublesome relative to csv's. I tend to use .csv's instead as you dont need all the extra import declarations, its less hassle, you don't have to worry about .xls/.xlsx versioning, and you can open .csv's in excel if you want to manually manipulate the testdata.

    Ta,

    Rich
  • ZDGN's avatar
    ZDGN
    Contributor
    4 years ago

    Hi _ivanovich_ 

     

    Think you should change HSSFWorkbook() to XSSFWorkbook().

    Here is an sample of a Java code I use to write to an XLSX file:

    XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("SheetName");

int rowNum = 0;
int colNum = 0;

Row row = sheet.createRow(rowNum);
row = sheet.createRow(rowNum++);
Cell cell = row.createCell(colNum++);
cell.setCellValue("First Value");
cell = row.createCell(colNum++);
cell.setCellValue("Second Value");
cell = row.createCell(colNum++);
cell.setCellValue("Third Value");

try {
    FileOutputStream outputStream = new FileOutputStream("<Path to the directory with filename>");
    workbook.write(outputStream);
    workbook.close();
} catch (IOException e) {
    e.printStackTrace();
}

     

    Don't hesitate to come back to me if you need further explaination.

    And let us know if it helps or solves your problem.

     

     

    David.

     

     

    • _ivanovich_'s avatar
      _ivanovich_
      Frequent Contributor
      4 years ago
       
       

      Hi 

      thank you for your reply.

      Im using soapui open source and it s not related to data driven test.

      i just need to generate an excel file xlsx for my test.

       

       

      Thank you for your code.

      I tried your code it returns:

       

      org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: General error during class generation: Type org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache not present java.lang.TypeNotPresentException:

      Type org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache not present at....

       

      • ZDGN's avatar
        ZDGN
        Contributor
        4 years ago

        Im using soapui open source and it s not related to data driven test.

        i just need to generate an excel file xlsx for my test.

        That's what it is all about.

        So here is a functionnal Groovy script:

        import org.apache.poi.ss.usermodel.Cell 
import org.apache.poi.ss.usermodel.Row 
import org.apache.poi.xssf.usermodel.XSSFSheet 
import org.apache.poi.xssf.usermodel.XSSFWorkbook 

import java.io.FileOutputStream 
import java.io.IOException 
import java.util.List 

XSSFWorkbook workbook = new XSSFWorkbook() 
XSSFSheet sheet = workbook.createSheet("SheetName") 

int rowNum = 0 
int colNum = 0 

headers = ["Col1", "Col2", "Col3"] 

Row row = sheet.createRow(rowNum) 
for(String header : headers) {
  Cell cell = row.createCell(colNum++) 
  cell.setCellValue(header) 
}

rowNum = 1
colNum = 0

row = sheet.createRow(rowNum++) 
Cell cell = row.createCell(colNum++) 
cell.setCellValue("First Value") 
cell = row.createCell(colNum++) 
cell.setCellValue("Second Value") 
cell = row.createCell(colNum++) 
cell.setCellValue("Third Value") 

try {
    FileOutputStream outputStream = new FileOutputStream("C:\\Users\\<userName>\\Documents\\ExcelFile.xlsx") 
    workbook.write(outputStream) 
    workbook.close() 
} catch (IOException e) {
    e.printStackTrace() 
}

         

        Please don't forget you will need apache poi jar files into your SoapUI bin/ext folder.
        Made this script with 3.1 version.

         

        Let me know if it's OK for you.

         

        David.