I understood it, Excel itself does not allow entering validation range string more than 255 characters, this was not POI limitation. And now I’m using Named Ranges and Named Cells and it’s working properly for me. So I had to put my validation range tokens in another sheet(made hidden) and I referenced desired cell ranges … Read more
According to the Apache POI Quick Guide, the POIFSFileSystem (or similarly, NPOIFSFileSystem) is only used with .xls (Excel versions through 2003) documents. The equivalent for .xlsx documents (Excel 2007+) is OPCPackage. OPCPackage pkg = OPCPackage.open(new File(“file.xlsx”)); You can create an XSSFWorkbook from the OPCPackage: XSSFWorkbook wb = new XSSFWorkbook(pkg); Or you can just create it … Read more
If the cell, which is containing the date, is formatted as the default date format (Short Date), then only the format id 0xE (14) is stored in the file. See https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html. The *.xlsx file contains only <xf numFmtId=”14″ … applyNumberFormat=”1″/> in styles.xml. There is no special formatCode saved for this numFmtId. So how this will … Read more
Add commons-collections4-x.x.jar file in your build path and try it again. It will work. You can download it from https://mvnrepository.com/artifact/org.apache.commons/commons-collections4/4.0
Try using the event API. See Event API (HSSF only) and XSSF and SAX (Event API) in the POI documentation for details. A couple of quotes from that page: HSSF: The event API is newer than the User API. It is intended for intermediate developers who are willing to learn a little bit of the … Read more
I was in a similar situation with a webserver environment. The typical size of the uploads were ~150k rows and it wouldn’t have been good to consume a ton of memory from a single request. The Apache POI Streaming API works well for this, but it requires a total redesign of your read logic. I … Read more