2

Currenty I am trying to read an Excel file that is polled via Apache Camel (2.25.1). This means the method gets the file contents via a String:

@Handler
public void processFile(@Body String body) {

For reading the Excel file I use Apache POI and POI-ooxml (both 4.1.2).

However, using the String directly

WorkbookFactory.create(new ByteArrayInputStream(body.getBytes(Charset.forName("UTF-8"))))

throws an "java.io.IOException: ZIP entry size is too large or invalid".

Using the String with other encodings:

WorkbookFactory.create(new ByteArrayInputStream(body.getBytes()))

throw "org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file".

Besides, I tried:

File file = exchange.getIn().getBody(File.class);
Workbook workbook = new XSSFWorkbook(new FileInputStream(file));

Probably because the file is read from an FTP-server, a java.io.FileNotFoundException is thrown: Invalid file path

However, the next code does work:

URL url = new URL(fileFtpPath);
URLConnection urlc = url.openConnection();
InputStream ftpIs = urlc.getInputStream();
Workbook workbook = new XSSFWorkbook(ftpIs);

But I prefer not making a connection to the FTP server myself, since Camel has already read the file and the needed Excel contents are available (in String body). Is there any way to read the contents of the Excel file from the String with Apache POI?

user
  • 23
  • 2

2 Answers2

0

I have my routes in XML, so I use groovy to process excel files, perhaps you may find it helpful

import org.apache.poi.ss.usermodel.WorkbookFactory

def workbook = WorkbookFactory.create(request.getBody(File.class))
def sheet = workbook.getSheetAt(0)
...

There is another approach usually using for large excel files where we are dealing with a stream. To go this way we should implement XSSFSheetXMLHandler.SheetContentsHandler from org.apache.poi.xssf.eventusermodel
You could find a copy of the original POI example in this SO question, for some reason it was recently deleted from poi svn. If you are interested, my groovy version looks like this

import org.apache.poi.openxml4j.opc.OPCPackage
import org.apache.poi.ooxml.util.SAXHelper
import org.apache.poi.xssf.eventusermodel.XSSFReader
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable
import org.apache.poi.hssf.usermodel.HSSFDataFormatter
import org.xml.sax.InputSource

class MyHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    ...
}

def pkg = OPCPackage.open(request.getBody(InputStream.class))
def xssfReader = new XSSFReader(pkg)
def sheetParser = SAXHelper.newXMLReader()

def handler = new XSSFSheetXMLHandler(xssfReader.getStylesTable(), null, new ReadOnlySharedStringsTable(pkg), MyHandler, new HSSFDataFormatter(), false)
sheetParser.setContentHandler(handler)
sheetParser.parse(new InputSource(xssfReader.getSheetsData().next()))
Greenev
  • 871
  • 6
  • 23
  • Probably the frameworks (POI and Camel) are causing my problem, not the Java language. So this solution still throws FileNotFoundException. I think because of usage of File class with file on FTP server. The Java equivalent `WorkbookFactory.create(exchange.getIn().getBody(java.io.File.class))` still throws a FileNotFoundException. Anyway thanks for trying to help. – user Jul 28 '20 at 06:51
  • Solved it by using `InputStream is = exchange.getIn().getBody(InputStream.class);` Surprisingly since following didn't work: `WorkbookFactory.create(new ByteArrayInputStream(body.getBytes(Charset.forName("UTF-8"))))` Though I did not use the sheetParser, I did accept your answer since it gave me the idea of getBody(InputStream.class). – user Jul 28 '20 at 14:04
  • glad I could help – Greenev Jul 28 '20 at 15:11
0

You can directly convert the body into InputStream and pass this into XSSFWorkbook constructor

        Exchange exchange = consumerTemplate.receive("file://C:/ftp/?noop=true", pollCount);
        InputStream stream = exchange.getIn().getBody(InputStream.class);

        XSSFWorkbook workbook = new XSSFWorkbook(stream);
                
        XSSFSheet sheet = workbook.getSheetAt(0);
5huraif
  • 1
  • 3