Update Uber Jar with Service Pack SP20

[Sai1278]

We are experiencing runtime errors when importing Excel files in SP20, likely due to a different runtime version of the library. Can anyone provide input on the suitable version for SP20?

1. Apache POI version
2. Uber JAR version

Has anyone encountered a similar issue before?

error

java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;

<dependency>
    <groupId>com.adobe.aem</groupId>
    <artifactId>uber-jar</artifactId>
    <version>6.5.15</version>               
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

Thanks,
Sai

[h_kataria]

A. if you are using 6.5.20 then you should use the same Uber jar version in your pom as well

<dependency>
  <groupId>com.adobe.aem</groupId>
  <artifactId>uber-jar</artifactId>
  <version>6.5.20</version>
  <scope>provided</scope>
  </dependency>

https://experienceleague.adobe.com/en/docs/experience-manager-65/content/release-notes/service-pack/...
B. you can try to get the exact apache poi version from the dependency finder /system/console/depfinder . Just provide the package name that you are trying to use and you will get the bundle exporting it and the dependency which you can use in your pom. Below reference is from cloud SDK but probably it will be same for 6.5.20. You can cross check and use that on your instance and use that

<dependency>
	<artifactId>com.adobe.granite.poi</artifactId>
	<version>2.1.0</version>
	<groupId>com.adobe.granite</groupId>
	<scope>provided</scope>
</dependency>

[Sai1278]

Hi @h_kataria 
Thanks for the reply. I have already tried the solutions mentioned, but the issue has not been resolved. Could you please provide some more context?

[Lokesh_Vajrala]

@Sai1278  What version of org.apache.poi is exported through the bundle com.adobe.granite.poi ?  You can try using the version exported from the granite.poi bundle and check if the error is gone. 

[h_kataria]

Hi @Sai1278 
I tried checking the latest implementation and it seems getCellTypeEnum implementation has been removed from the latest versions which is probably why you are getting this error.
You can instead try using getCellType() method 

Also if you have imported the right dependencies in pom, then your IDE itself should show some error when you are trying to use the getCellTypeEnum method. Try removing the explicit org.apache.poi dependency which you have added. It might be overriding the actual version.You probably don't need it since already have the uber jar dependency.
Hope this helps.

[Sai1278]

Hi @h_kataria 
I tried but the issue is not resolved

[h_kataria]

What issue are you facing still ? Could you please provide share the error, the dependencies for apache poi which you have added in your pom currently and the depfinder result ?

[Sai1278]

I'm getting below error and try versions

java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType; 

[h_kataria]

This error 

java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;  suggests getCellTypeEnum is still being used in the code which will probably not work as it seems to have been removed in latest versions, can you try changing it to getCellType once https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html 

[Sai1278]

Hi @h_kataria 
I've updated code but when I tried to import the excel file getting error "no valid files found for import" and log's file this error not showing  {java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;}

private static String cellToString(Cell pCell) {
    String retval = null;
    if (pCell != null) {
        switch (pCell.getCellType()) {
            case BOOLEAN:
                retval = "" + pCell.getBooleanCellValue();
                break;
            case NUMERIC:
                retval = "" + pCell.getNumericCellValue();
                break;
            case STRING:
                retval = pCell.getStringCellValue();
                break;
            case FORMULA:
                switch (pCell.getCachedFormulaResultType()) {
                    case NUMERIC:
                        retval = "" + pCell.getNumericCellValue();
                        break;
                    case STRING:
                        retval = "" + pCell.getRichStringCellValue();
                        break;
                    default:
                        break;
                }
                break;
            default:
                break;
        }
    }
    return retval;
}

[h_kataria]

Since the API version is updated there might be some other changes as well which you might have to do. If possible, share the complete file. Otherwise, just check your logic where you are trying to import the excel, that is where I assume the problem lies now. 

[MukeshYadav_]

Hi @Sai1278 ,

Please try different version of org.apache.poi

https://github.com/dromara/hutool/issues/742

https://poi.apache.org/help/faq.html#faq-N10006

Note:-

You have older Apache POI jars on your classpath Reference:- https://stackoverflow.com/questions/50469433/java-lang-nosuchmethoderror-org-apache-poi-ss-usermodel...

Thanks

[Sai1278]

Hi @MukeshYadav_ 
I follow the which you mention steps but the issue is not resolved

[Raja_Reddy]

Hi @Sai1278 
are you following @h_kataria  mention steps? 
still you are facing issue. please try below version uber and poi versions

           <dependency>
                <groupId>com.adobe.aem</groupId>
                <artifactId>uber-jar</artifactId>
                <version>6.5.20</version>
                <scope>provided</scope>
            </dependency>


           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                 <version>5.2.5</version>
            </dependency>

[Sai1278]

Hi @Raja_Reddy 

I have used the which you mention version above, but the issue remains unresolved and errors are not being printed in the log files. If possible, could you please provide changes to the getCellType method?

[Raja_Reddy · Accepted solution]

Hi @Sai1278 
You can use below code. hope it's work

private static String cellToString(Cell pCell) {
        String retval = null;
        if (pCell != null) {
            switch (pCell.getCellType()) {
                case BOOLEAN:
                    retval = String.valueOf(pCell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(pCell)) {
                        retval = String.valueOf(pCell.getDateCellValue());
                    } else {
                        retval = String.valueOf(pCell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    retval = pCell.getStringCellValue();
                    break;
                case FORMULA:
                    switch (pCell.getCachedFormulaResultType()) {
                        case NUMERIC:
                            retval = String.valueOf(pCell.getNumericCellValue());
                            break;
                        case STRING:
                            retval = pCell.getRichStringCellValue().getString();
                            break;
                        default:
                            break;
                    }
                    break;
                default:
                    break;
            }
        }
        return retval;
    }

[Sai1278]

Hi @Raja_Reddy 
Everything is working fine. Thank you for your support.

@h_kataria , thank you for your support as well.