Groovy Script is failing while same code is working in java

Question

I am trying to convert JSON string to excel using poi. same code works in java but when converted to groovy its failing with below error.

java exception: java.lang.Error: not implemented at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.setElementTextContent(PackagePropertiesMarshaller.java:150) at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.setElementTextContent(PackagePropertiesMarshaller.java:155) at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.addCreated(PackagePropertiesMarshaller.java:188) at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.marshall(PackagePropertiesMarshaller.java:112) at org.apache.poi.openxml4j.opc.internal.marshallers.ZipPackagePropertiesMarshaller.marshall(ZipPackagePropertiesMarshaller.java:53) at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:541) at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1505) at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:242) at org.apache.poi.ss.usermodel.Workbook$write$7.call(Unknown Source) at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:115) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:127) at Script141.writeObjects2ExcelFile(Script141.groovy:101) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:101)

My Groovy code is below

import java.io.FileOutputStream;
import java.io.IOException;
//import java.io.InputStream;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
   
def invoke(msg) {
// Get JSON String

    // Step 1: Read JSON File to List Objects
    String jsonStr = msg.get("jsonresponsebodyci") //system define method to read string

    List<Customer_info> customers_info = convertJsonString2Objects(jsonStr);
    
      // Step 2: Convert Java List Objects to JSON File
      writeObjects2ExcelFile(customers_info, "/app/ReportDaily.xlsx");
  }
  
  private static List<Customer_info> convertJsonString2Objects(String jsonString){
    List<Customer_info> customers_info = null;
    
    try {
      customers_info = new ObjectMapper().readValue(jsonString, new TypeReference<List<Customer_info>>(){});
    } catch (JsonParseException e) {
      e.printStackTrace();
    } catch (JsonMappingException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    
    return customers_info;
  }
  
    
  private static void writeObjects2ExcelFile(List<Customer_info> customers_info, String FilePath) throws IOException {
 String[] COLUMNs = ["Company Name", "UEN", "Login Id", "Application Name", "Created By", "Phone", "Email Id", "Created On", "status", "Enable", "API Name"];
    
    Workbook workbook = new XSSFWorkbook();
     
    CreationHelper createHelper = workbook.getCreationHelper();
 
    Sheet sheet = workbook.createSheet("Customers_Info");
    
 
    Font headerFont = workbook.createFont();
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.BLUE.getIndex());
 
    CellStyle headerCellStyle = workbook.createCellStyle();
    headerCellStyle.setFont(headerFont);
 
    // Row for Header
    Row headerRow = sheet.createRow(0);


    // Header
    for (int col = 0; col < COLUMNs.length; col++) {
      Cell cell = headerRow.createCell(col);
      cell.setCellValue(COLUMNs[col]);
      cell.setCellStyle(headerCellStyle);

    }

    int rowIdx = 1;

for (Customer_info customer_info : customers_info) 
{
      Row row = sheet.createRow(rowIdx++);
      row.createCell(0).setCellValue(customer_info.getcompanyname());
      row.createCell(1).setCellValue(customer_info.getuen());
      row.createCell(2).setCellValue(customer_info.getloginid());
    row.createCell(3).setCellValue(customer_info.getapplicationname());
      row.createCell(4).setCellValue(customer_info.getcreatedby());
      row.createCell(5).setCellValue(customer_info.getphone());
    row.createCell(6).setCellValue(customer_info.getemail());
      row.createCell(7).setCellValue(customer_info.getcreatedon());
      row.createCell(8).setCellValue(customer_info.getstatus());
    row.createCell(9).setCellValue(customer_info.getenabled());
      row.createCell(10).setCellValue(customer_info.getapiname());
 
    
    }
  
 
    FileOutputStream fileOut = new FileOutputStream(FilePath);
workbook.write(fileOut);
fileOut.close();
workbook.close();
    }

public class Customer_info {
      private String apiname;
      private String loginid;
      private String createdby;
      private String phone;
      private String companyname;
      private String applicationid;
      private String createdon;
      private String email;
      private String enabled;
      private String applicationname;
      private String status;
      private String uen;
     
      public Customer_info() {
      }
     
      public Customer_info(String apiname, String loginid, String createdby, String phone, String companyname, String applicationid, String createdon, String email, String enabled, String applicationname, String status, String UEN) {
        this.apiname = apiname;
            this.loginid = loginid;
            this.createdby = createdby;
            this.phone = phone;
            this.companyname = companyname;
        this.applicationid = applicationid;
        this.createdon = createdon;
            this.email = email;
            this.enabled = enabled;
            this.applicationname = applicationname;
        this.status = status;
        this.uen = uen;
      }
     
      public String getapiname() {
        return apiname;
      }
     
      public void setapiname(String apiname) {
        this.apiname = apiname;
      }
     
      public String getloginid() {
        return loginid;
      }
     
      public void setloginid(String loginid) {
        this.loginid = loginid;
      }
     
      public String getcreatedby() {
        return createdby;
      }
     
      public void setcreatedby(String createdby) {
        this.createdby = createdby;
      }
     
      public String getphone() {
        return phone;
      }
     
      public void setphone(int phone) {
        this.phone = phone;
      }
      
      public String getcompanyname() {
        return companyname;
      }
     
      public void setcompanyname(String companyname) {
        this.companyname = companyname;
      }
     
      public String getapplicationid() {
        return applicationid;
      }
     
      public void setapplicationid(String applicationid) {
        this.applicationid = applicationid;
      }
     
      public String getcreatedon() {
        return createdon;
      }
     
      public void setcreatedon(String createdon) {
        this.createdon = createdon;
      }
     
      public String getemail() {
        return email;
      }
     
      public void setemail(int email) {
        this.email = email;
      }
      
      public String getenabled() {
        return enabled;
      }
     
      public void setenabled(String enabled) {
        this.enabled = enabled;
      }
     
      public String getapplicationname() {
        return applicationname;
      }
     
      public void setapplicationname(String applicationname) {
        this.applicationname = applicationname;
      }
     
      public String getstatus() {
        return status;
      }
     
      public void setstatus(String status) {
        this.status = status;
      }
     
      public String getuen() {
        return uen;
      }
     
      public void setuen(int uen) {
        this.uen = uen;
      }
     
      @Override
      public String toString() {
       // return "Customer [apiname=" + apiname + ", apiname=" + name + ", address=" + address + ", age=" + age + "]";
          
          return "Customer_Info [apiname=" + apiname + ", createdby=" + createdby + ", phone=" + phone + ", companyname=" + companyname + ", applicationid=" + applicationid + ", createdon=" + createdon + ", email=" + email + ", enabled=" + enabled + ",applicationname=" + applicationname + ", status=" + status + ", uen=" + uen + ", loginid=" + loginid + "]";
      }
     
    }

Json File Below

[{"apiname":"APP","loginid":"dmin@localhost","createdby":"Admin","phone":"1606964791954","companyname":"comp","applicationid":"f1354c1e8363","createdon":"1606964791954","email":"[email protected]","enabled":"true","applicationname":"APP","status":"Approved","uen":"12"}]

After Troubleshooting found below line is causing this issue

customers_info = new ObjectMapper().readValue(jsonString, new TypeReference<List<Customer_info>>(){});

according to the stacktrace issue is here: workbook.write(fileOut);. what is the fersion of groovy, java, and POI ? — daggett

daggett daggett · Accepted Answer · 2020-12-05T17:48:10

that's not an answer to your question

i just took your code and refactored it a bit to a groovy style and it works

you can run it from groovy console

//get POI dependencies
@Grab(group='org.apache.poi', module='poi', version='4.1.2')
@Grab(group='org.apache.poi', module='poi-ooxml', version='4.1.2')

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


def jsonStr='''
[{"apiname":"APP","loginid":"dmin@localhost","createdby":"Admin","phone":"1606964791954","companyname":"comp","applicationid":"f1354c1e8363","createdon":"1606964791954","email":"[email protected]","enabled":"true","applicationname":"APP","status":"Approved","uen":"12"}]
'''
//contans column tilte and closure to get value from each row
def columns = [
    [title: "Company Name",     value:{it.companyname} ], 
    [title: "UEN",              value:{it.uen} ], 
    [title: "Login Id",         value:{it.loginid} ], 
    [title: "Application Name", value:{it.applicationname} ], 
    [title: "Created By",       value:{it.createdby} ],
    [title: "Phone",            value:{it.phone} ],
    [title: "Email Id",         value:{it.email} ], 
    [title: "Created On",       value:{new Date(it.createdon as Long).format('yyyy-MM-dd HH:mm:ss')} ], 
    [title: "status",           value:{it.status} ], 
    [title: "Enable",           value:{it.enabled} ], 
    [title: "API Name",         value:{it.apiname} ],
]

void writeObjects2ExcelFile(List<Map> customers_info, List columns,String filePath){
    println customers_info
    Workbook workbook = new XSSFWorkbook();
    CreationHelper createHelper = workbook.getCreationHelper();
    Sheet sheet = workbook.createSheet("Customers_Info");
    Font headerFont = workbook.createFont();
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.BLUE.getIndex());
 
    CellStyle headerCellStyle = workbook.createCellStyle();
    headerCellStyle.setFont(headerFont);
 
    // header row
    Row headerRow = sheet.createRow(0);
    columns.eachWithIndex{col,colIdx->
        Cell cell = headerRow.createCell(colIdx);
        cell.setCellValue(col.title);
        cell.setCellStyle(headerCellStyle);
    }
    // data rows
    customers_info.eachWithIndex{customer_info,rowIdx->
        Row row = sheet.createRow(rowIdx+1);
        columns.eachWithIndex{col,colIdx->
            row.createCell(colIdx).setCellValue( col.value(customer_info) )
        }
    }
    FileOutputStream fileOut = new FileOutputStream(filePath);
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();
}

def data = new groovy.json.JsonSlurper().parseText(jsonStr)
writeObjects2ExcelFile(data, columns, '/11/tmp/out.xlsx')

Groovy Script is failing while same code is working in java

1 Answers