Forum Discussion

_ivanovich_'s avatar
_ivanovich_
Frequent Contributor
4 years ago

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-math3-3.0.jar

jxl-2.6.8.jar

poi-5.0.0.jar

poi-ooxml-5.0.0.jar

poi-ooxml-schemas-4.1.2.jar

stax-api-1.0-2.jar

xmlbeans-4.0.0.jar

 

My groovy code is:

import jxl.*
import jxl.write.*

 

WritableWorkbook workbook = Workbook.createWorkbook(new File("C:\\Users\\ivan\\temp\\test.xlsx"))
WritableSheet sheet = workbook.createSheet("Test1", 0)
//log.info(sheet1.isHidden())
Label label = new Label(0, 2, "Test, Rangee3"); //column=0=A,row=0=1
sheet.addCell(label);
Label label1 = new Label(2, 2, "Test2, Rangee3");
sheet.addCell(label1);
workbook.write()
workbook.close()

 

Result:

The test.xlsx file is created but i am not able to open it in microsoft excel 365

It says the format or extension is not valid.

 

I tried with java code and it says the same thing

i used this java code:

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
public class CreateExcelFileExample1
{
public static void main(String[] args) throws FileNotFoundException, IOException
{
//creating an instance of Workbook class
Workbook wb = new HSSFWorkbook();
//creates an excel file at the specified location
OutputStream fileOut = new FileOutputStream("C:\\Users\\dtq1482\\Test_Douane\\nonConfInterne.xlsx");
//System.out.println("Excel File has been created successfully.");
wb.write(fileOut);
}
}

 

 

  • _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....