Apache Poi unable to read sheet names

user_1826247 0
Viewed 434

We are experiencing a strange bug regarding reading of Excel sheets via Apache Poi. We are using version 5.0.

This code previously worked, however it has now stopped working on all of our production environments. It still works when testing locally so this is proving quite difficult to debug.

The issue is that we are getting null sheet names returned, so are unable to correctly load the required sheet.

try (XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(venueListFile))) {

        LOGGER.info("Found {} sheets", wb.getNumberOfSheets());

        // First setup venues
        Sheet venueSetUpSheet = wb.getSheet("Store Set Up");
        List<String> sheetNames = new ArrayList<>();
        for (Iterator<Sheet> it = wb.sheetIterator(); it.hasNext(); ) {
            sheetNames.add(it.next().getSheetName());
        }

        if (venueSetUpSheet == null) {

            LOGGER.warn("Sheet 'Store Set Up' not found, available sheets: '" + String.join("','", sheetNames) + "'");
        } else {
            LOGGER.info("Found sheets: " + String.join("','", sheetNames) + "'");

Locally this returns:

Found 5 sheets
Found sheets: Store Set Up','Store Open Hours','Staff Setup','TV Configurations','Sheet3'

In production for the same Excel file it returns:

Found 5 sheets
Sheet 'Store Set Up' not found, available sheets: 'null','null','null','null','null'

It seems the file is read, and we have tested that the uploaded file on the server isn't corrupted. Is anyone aware of a known issue with Poi which would result in null sheet names?

apache-poi excel java
1 Answers

XSSFSheet bases on the underlying org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet. And XSSFSheet.getSheetName simply returns org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet.getName.

If that returns null instead of the name, then probably the wrong class org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet is used. For apache poi 5.0.0 it must be CTSheet from jar poi-ooxml-full-5.0.0.jar or poi-ooxml-lite-5.0.0.jar. It must not be from ooxml-schemas-*.jar or any poi-ooxml-schemas-*.jar like using lower versions of apache poi. Maybe some library in your productive environmwent ships or pulls any of the ooxml-schemas-*.jar? That is incompatible with apache poi 5.0.0 then.

You can ask the ClassLoader where a special class (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in your case) came from on run time:

...
  ClassLoader classloader = org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet.class.getClassLoader();
  java.net.URL res = classloader.getResource("org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet.class");
  String path = res.getPath();
  System.out.println("CTSheet came from " + path);
...

If that is not the poi-ooxml-full-5.0.0.jar or poi-ooxml-lite-5.0.0.jar, you might get at least an idea which other library ships or pulls that class too.

If this doesn't help, please also check where org.apache.xmlbeans.* classes come from. XmlBeans are needed for org.openxmlformats.schemas.* classes.

...
  classloader = org.apache.xmlbeans.XmlObject.class.getClassLoader();
  res = classloader.getResource("org/apache/xmlbeans/XmlObject.class");
  path = res.getPath();
  System.out.println("XmlObject came from " + path);     
...

Using apache poi 5.0.0, this class must be from xmlbeans-4.0.0.jar it must not be from any other version of XmlBeans.

If all not helps, You can also check the same way whether org.apache.poi.xssf.usermodel.XSSFSheet really comes from apache poi 5.0.0 (poi-ooxml-5.0.0.jar). Maybe there are multiple apache poi versions in your productive environment. That's also not supported:

...
  classloader = org.apache.poi.xssf.usermodel.XSSFSheet.class.getClassLoader();
  res = classloader.getResource("org/apache/poi/xssf/usermodel/XSSFSheet.class");
  path = res.getPath();
  System.out.println("XSSFSheet came from " + path);     
...

And at last, you could try getting the sheet name from underlying CTSheet object like so:

...
  try (Workbook wb = WorkbookFactory.create(new FileInputStream("./test.xlsx"));) {

   List<String> sheetNames = new ArrayList<>();
   for (Iterator<Sheet> it = wb.sheetIterator(); it.hasNext(); ) {
    Sheet sheet = it.next();
    if (sheet instanceof org.apache.poi.xssf.usermodel.XSSFSheet) {
     java.lang.reflect.Field _sheet = org.apache.poi.xssf.usermodel.XSSFSheet.class.getDeclaredField("sheet");
     _sheet.setAccessible(true);
     org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet ctSheet = 
      (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet)_sheet.get((org.apache.poi.xssf.usermodel.XSSFSheet)sheet);
     org.apache.xmlbeans.impl.values.TypeStore store = ((org.apache.xmlbeans.impl.values.XmlObjectBase)ctSheet).get_store();
     System.out.println(store);
     org.apache.xmlbeans.SimpleValue target = (org.apache.xmlbeans.SimpleValue)store.find_attribute_user(new javax.xml.namespace.QName("", "name"));
     System.out.println(target.getStringValue());
    }
    sheetNames.add(sheet.getSheetName());
   }
  
   System.out.println(sheetNames);
  }
...

Does that work? Or is that throwing exceptions? If so, which?

And of course, please check all error logs for errors. Since Office Open XML stores data in XML, maybe there are problems while parsing XML using Java? But that should throw exceptions which should be logged somewhere.

user_39154310
Related