Using a third party library results in InvocationTargetException

I’m trying to use this library which is a wrapper around Apache POI to read/write to excel file (.xlsm).

So the reason I’m not using the POI built-in API is because I’m dealing with an excel with 1 million rows and this wrapper library is simpler to be implemented than the Apache POI’s solutions to read big files.

Anyway I’ve tested this in eclipse first and it worked as it was supposed to but then I addeed the 17 jar files needed for this to work into the IS and my local project and everything seems to be fine, but when I run the java service it throws “InvocationTargetException”.

Here’s the exception error:

java.lang.reflect.InvocationTargetException: Could not initialize class org.apache.poi.POIXMLTypeLoader

Here’s is my code:

File is = new File(filename);
StreamingReader reader = StreamingReader.builder() //this is the wrapper to read the excel file partialy
	.rowCacheSize(10)    
	.bufferSize(1024)     
	.sheetName("SOURCE_1")  
	.read(is);

that StreamingReader is causing this to happen and I don’t really know what’s wrong with it. Any help is highly appreciated.

Thanks in advance

You better check which jar among those 17 causing the error, see the utility of that jar file into your application and take the decision of having or not. Also, raise a ticket with SAG if needed for any suggestions.

Thanks,

I am sceptical if SAG provides support to the 3rd party library but if you want to read/write excel files there is a GCS package available which can handle this “WxExcel.zip”.

Please note that this is not a part of webMethods Product Suite and it is maintained by SAG GCS team. Kindly check with your sales representative for more details…

MR as173d, the class that couldn’t be initialized is one of the apache poi classes and cannot be removed.

I don’t understand it works perfectly fine on my eclipse but when I move it to EAI java service it just doesn’t work.

I will try to contact SAG but I’m not sure if they are going to help or not.

thanks anyway

M@he$h Thanks for your reply. I already asked them and they don’t provide any, that’s why I wanted to use apache POI but the way they deal with large excel files is messy kind of code so I found that library which works fine on eclipse but not on WM java service and I can’t understand why…

but anyway, does this “WxExcel.zip” provide any api to load the large excel files partially? and is there any way that I can get my hands on it other than SAG? Is there any url or something for it ?

Thanks

Ok I managed to find out where exactly this exception happens… I decided to go with the Apache POI’s sample code, and while I was doing that I realized at this line it happens.


File xlsxFile = new File("E:\\bpc\\testdata01.xlsm");
try {
	OPCPackage xlsxPackage = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);
	ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
	XSSFReader xssfReader = new XSSFReader(xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable(); //happens here
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); // or happens here
}
//catch stuff

the moment I call any function of that XSSReader it throws that exception.

Here is the source code for XSSReader:


/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */
package org.apache.poi.xssf.eventusermodel;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.ThemesTable;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.xmlbeans.XmlException;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;

/**
 * This class makes it easy to get at individual parts
 *  of an OOXML .xlsx file, suitable for low memory sax
 *  parsing or similar.
 * It makes up the core part of the EventUserModel support
 *  for XSSF.
 */
public class XSSFReader {
    private OPCPackage pkg;
    private PackagePart workbookPart;

    /**
     * Creates a new XSSFReader, for the given package
     */
    public XSSFReader(OPCPackage pkg) throws IOException, OpenXML4JException {
        this.pkg = pkg;

        PackageRelationship coreDocRelationship = this.pkg.getRelationshipsByType(
                PackageRelationshipTypes.CORE_DOCUMENT).getRelationship(0);

        // Get the part that holds the workbook
        workbookPart = this.pkg.getPart(coreDocRelationship);
    }


    /**
     * Opens up the Shared Strings Table, parses it, and
     *  returns a handy object for working with
     *  shared strings.
     */
    public SharedStringsTable getSharedStringsTable() throws IOException, InvalidFormatException {
        ArrayList<PackagePart> parts = pkg.getPartsByContentType( XSSFRelation.SHARED_STRINGS.getContentType());
        return parts.size() == 0 ? null : new SharedStringsTable(parts.get(0), null);
    }

    /**
     * Opens up the Styles Table, parses it, and
     *  returns a handy object for working with cell styles
     */
    public StylesTable getStylesTable() throws IOException, InvalidFormatException {
        ArrayList<PackagePart> parts = pkg.getPartsByContentType( XSSFRelation.STYLES.getContentType());
        if(parts.size() == 0) return null;
        
        // Create the Styles Table, and associate the Themes if present
        StylesTable styles = new StylesTable(parts.get(0), null);
        parts = pkg.getPartsByContentType( XSSFRelation.THEME.getContentType());
        if(parts.size() != 0) {
           styles.setTheme(new ThemesTable(parts.get(0), null));
        }
        return styles;
    }



    /**
     * Returns an InputStream to read the contents of the
     *  shared strings table.
     */
    public InputStream getSharedStringsData() throws IOException, InvalidFormatException {
        return XSSFRelation.SHARED_STRINGS.getContents(workbookPart);
    }

    /**
     * Returns an InputStream to read the contents of the
     *  styles table.
     */
    public InputStream getStylesData() throws IOException, InvalidFormatException {
        return XSSFRelation.STYLES.getContents(workbookPart);
    }

    /**
     * Returns an InputStream to read the contents of the
     *  themes table.
     */
    public InputStream getThemesData() throws IOException, InvalidFormatException {
        return XSSFRelation.THEME.getContents(workbookPart);
    }

    /**
     * Returns an InputStream to read the contents of the
     *  main Workbook, which contains key overall data for
     *  the file, including sheet definitions.
     */
    public InputStream getWorkbookData() throws IOException, InvalidFormatException {
        return workbookPart.getInputStream();
    }

    /**
     * Returns an InputStream to read the contents of the
     *  specified Sheet.
     * @param relId The relationId of the sheet, from a r:id on the workbook
     */
    public InputStream getSheet(String relId) throws IOException, InvalidFormatException {
        PackageRelationship rel = workbookPart.getRelationship(relId);
        if(rel == null) {
            throw new IllegalArgumentException("No Sheet found with r:id " + relId);
        }

        PackagePartName relName = PackagingURIHelper.createPartName(rel.getTargetURI());
        PackagePart sheet = pkg.getPart(relName);
        if(sheet == null) {
            throw new IllegalArgumentException("No data found for Sheet with r:id " + relId);
        }
        return sheet.getInputStream();
    }

    /**
     * Returns an Iterator which will let you get at all the
     *  different Sheets in turn.
     * Each sheet's InputStream is only opened when fetched
     *  from the Iterator. It's up to you to close the
     *  InputStreams when done with each one.
     */
    public Iterator<InputStream> getSheetsData() throws IOException, InvalidFormatException {
        return new SheetIterator(workbookPart);
    }

    /**
     * Iterator over sheet data.
     */
    public static class SheetIterator implements Iterator<InputStream> {

        /**
         *  Maps relId and the corresponding PackagePart
         */
        private Map<String, PackagePart> sheetMap;

        /**
         * Current CTSheet bean
         */
        private CTSheet ctSheet;
        
        /**
         * Iterator over CTSheet objects, returns sheets in <tt>logical</tt> order.
         * We can't rely on the Ooxml4J's relationship iterator because it returns objects in physical order,
         * i.e. as they are stored in the underlying package
         */
        private Iterator<CTSheet> sheetIterator;

        /**
         * Construct a new SheetIterator
         *
         * @param wb package part holding workbook.xml
         */
        private SheetIterator(PackagePart wb) throws IOException {

            /**
             * The order of sheets is defined by the order of CTSheet elements in workbook.xml
             */
            try {
                //step 1. Map sheet's relationship Id and the corresponding PackagePart
                sheetMap = new HashMap<String, PackagePart>();
                for(PackageRelationship rel : wb.getRelationships()){
                    if(rel.getRelationshipType().equals(XSSFRelation.WORKSHEET.getRelation()) ||
                       rel.getRelationshipType().equals(XSSFRelation.CHARTSHEET.getRelation())){
                        PackagePartName relName = PackagingURIHelper.createPartName(rel.getTargetURI());
                        sheetMap.put(rel.getId(), wb.getPackage().getPart(relName));
                    }
                }
                //step 2. Read array of CTSheet elements, wrap it in a ArayList and construct an iterator
                //Note, using XMLBeans might be expensive, consider refactoring to use SAX or a plain regexp search
                CTWorkbook wbBean = WorkbookDocument.Factory.parse(wb.getInputStream()).getWorkbook();
                sheetIterator = wbBean.getSheets().getSheetList().iterator(); 
            } catch (InvalidFormatException e){
                throw new POIXMLException(e);
            } catch (XmlException e){
                throw new POIXMLException(e);
            }
        }

        /**
         * Returns <tt>true</tt> if the iteration has more elements.
         *
         * @return <tt>true</tt> if the iterator has more elements.
         */
        public boolean hasNext() {
            return sheetIterator.hasNext();
        }

        /**
         * Returns input stream of the next sheet in the iteration
         *
         * @return input stream of the next sheet in the iteration
         */
        public InputStream next() {
            ctSheet = sheetIterator.next();

            String sheetId = ctSheet.getId();
            try {
                PackagePart sheetPkg = sheetMap.get(sheetId);
                return sheetPkg.getInputStream();
            } catch(IOException e) {
                throw new POIXMLException(e);
            }
        }

        /**
         * Returns name of the current sheet
         *
         * @return name of the current sheet
         */
        public String getSheetName() {
            return ctSheet.getName();
        }
        
        /**
         * Returns the comments associated with this sheet,
         *  or null if there aren't any
         */
        public CommentsTable getSheetComments() {
           PackagePart sheetPkg = getSheetPart();
           
           // Do we have a comments relationship? (Only ever one if so)
           try {
              PackageRelationshipCollection commentsList = 
                   sheetPkg.getRelationshipsByType(XSSFRelation.SHEET_COMMENTS.getRelation());
              if(commentsList.size() > 0) {
                 PackageRelationship comments = commentsList.getRelationship(0);
                 PackagePartName commentsName = PackagingURIHelper.createPartName(comments.getTargetURI());
                 PackagePart commentsPart = sheetPkg.getPackage().getPart(commentsName);
                 return new CommentsTable(commentsPart, comments);
              }
           } catch (InvalidFormatException e) {
              return null;
           } catch (IOException e) {
              return null;
           }
           return null;
        }
        
        public PackagePart getSheetPart() {
           String sheetId = ctSheet.getId();
           return sheetMap.get(sheetId);
        }

        /**
         * We're read only, so remove isn't supported
         */
        public void remove() {
            throw new IllegalStateException("Not supported");
        }
    }
}

Thanks for your help guys. I’ll contact SAG consultant on Monday and will refer this post as well to them. Hopefully they can help me with it.

Thanks for your information. Update us once you hear back from SAG.

Thanks,

Ok so I contacted SAG team and they managed to fix this issue for me but before I go to the solution let me point out something about that “WxExcel.zip”.

That package M@he$h mentioned in his previous reply is maintained by SAG GCS but there’s no documentation for it therefore SAG won’t support it at all but you are free to use it but you still have to contact them to get your hands on it.
The other issue with that package is that the team who developed the package never tested it against large excel files so I assume the package doesn’t come with any good api to manage the memory but that’s just an assumption.

Now the solution to my issue was that I had to put all the jar files into the package’s jar file (code/jars) and add the following line to manifest.v3 file of that package:

<value name="classloader">package</value>

and then restart IS and viola. :slight_smile:

Apparently IS ignores the package classloader during compilation.

Here is the complete response from the SAG consultant:

Hopefully this will help some one else in future.

Guys thank you for your help :slight_smile:

WxExcel.zip cannot be used for any rich implementations related to excel. It is barely useful to create simple excel sheets (kind of document to excel style). Sheets with complex content, formulas and features like merge rows cannot be handled by this package. It is better to go with Apache POI library. I have used POI to create some extremely complex excel sheets and it works awesome.

I also have this issue, It’s so good,Thanks a lot!