This can be done using a 3rd party jar. Apache POI is a set of open source libraries that will allow you to consume or create XLSX files using java. Information on this project can be found at https://poi.apache.org or you could find another library if needed. This example uses apache POI v3.9, it can be found here: https://archive.apache.org/dist/poi/release/bin/
NOTE: The latest version of apache POI has a conflict with the appache commons library that QIE uses natively. You will need to download apache POI v3.9 to be compatable. The archive copies can be found here: https://archive.apache.org/dist/poi/release/bin/
Information on installing the jar for use in QIE can be found here: https://www.qvera.com/kb/index.php/256
Once the jar files have been downloaded and installed, then you can use them like this:
// this formatter is used to get a string output from the cell regardless of the cell content
var oDataFormatter = new org.apache.poi.ss.usermodel.DataFormatter();
// convert message to input stream for POI to use
// if you need to read this directly from disk instead of the source
// you could do:
// var excelFile = new java.io.File("C:\\\\HL7\\Example.xlsx");
// var inputStream = new java.io.FileInputStream(excelFile);
var inputStream = new java.io.ByteArrayInputStream(source.getBytes());
// now we have POI read the excel file and convert it to a workbook object
var book = new org.apache.poi.xssf.usermodel.XSSFWorkbook(inputStream);
// next we extract the first sheet from the workbook
var sheet = book.getSheetAt(0);
// finally we cycle the rows of the sheet
var itterator = sheet.iterator();
var rowCount = 0;
// iterating over the file
while (itterator.hasNext()) {
var row = itterator.next();
// now itterate over each column in the row
var cellItterator = row.cellIterator();
while (cellItterator.hasNext()) {
var cell = cellItterator.next();
qie.debug('Cell Type: ' + cell.getCellType());
qie.debug('Cell Value: ' + oDataFormatter.formatCellValue(cell));
}
rowCount++;
}
qie.debug('Rows Processed: ' + rowCount);
The debug output of this sample would output all of the cells in the first sheet.
Here is a sample channel.
