Hi Folks,

Today I’m gonna share a solution Mirth Custom Excel Reader which reads the excel file and get the contents in a string delimited text format.

For this we mainly using apache poi jar which is standard jar even used for java solutions for parsing excel files

Pre-requisites:

• MirthConnect : Above 3.3
• Custom Jars:
  • dom4j-1.6.jar
  • poi-3.9.jar
  • poi-ooxml-3.8.jar
  • poi-ooxml-schemas-3.7.jar
  • xmlbeans-2.5.0.jar

Solution:

function ExtractExcelContent(fileDirectory, originalFilename, billingEntity) {
	
	logger.debug("len:"+originalFilename.split("_").length);
	
    var path = fileDirectory + '/' + originalFilename;
    // Call file with path
    var fileF = new java.io.File(path);
    var inputstream = new java.io.FileInputStream(fileF);

    // String Builder for excelData
    var excelData = new java.lang.StringBuilder();
    var workBook = new org.apache.poi.ss.usermodel.WorkbookFactory.create(inputstream);
    var dataFormatter = new org.apache.poi.ss.usermodel.DataFormatter();
    // Create Empty String separator
    var sep = "";
    var sep2 = "";

    // Create ArrayList()
    var excelheaders = new java.util.ArrayList();
    var get_Entity = '';
    var get_Number = '';
    var exitExcelReader = '';
    var isEAIserviceNotification = false;

var r 	=  org.apache.poi.ss.usermodel.Row;
var cell  =  org.apache.poi.ss.usermodel.Cell;
var sheet = Packages.org.apache.poi.ss.usermodel.Sheet;

try {
	
        var evaluator = workBook.getCreationHelper().createFormulaEvaluator();
        var cr = "";
        // Loop Through the number of Excel Sheets available
        for (var i = 0; i < workBook.getNumberOfSheets(); i++) {

            // Call [poi.ss.usermodel.Sheet] class
          
            // Get the sheet starting from (0,1,2,3...)
            sheet = workBook.getSheetAt(i);
            
		var rowStart = Math.min(4, sheet.getFirstRowNum());
		var rowEnd = Math.max(1400, sheet.getLastRowNum());
for (var rowNum = rowStart; rowNum < rowEnd; rowNum++) {
	var j = 0;
    r = sheet.getRow(rowNum);
   if (r == null) {
      // This whole row is empty
      // Handle it as needed
      continue;
   }

   var lastColumn = Math.max(r.getLastCellNum(), 20);

   for (var cn = 0; cn 4  && r.getCell(3)!=null && r.getCell(3)>0) {
      	logger.debug("inside if");
         // The spreadsheet is empty in this cell
         /* if (cell.getRowIndex() == 4) {
                        excelheaders.add(cell.toString());
                    }*/
           			sep2 = "#:~";
                        // Append "" empty space
                        excelData.append(sep);
                        // Append column headers						
                        excelData.append(excelheaders.get(j));
                        excelData.append(sep2);
                        excelData.append('');
                        sep = "|";
                        
		 
      } else if(cell != null ) {
      	logger.debug("inside else");
         // Do something useful with the cell's contents
		                     // Start getting the content of the cell from 5th cell
                    preparedBy = sheet.getRow(5).getCell(0).toString();

                     //if (cell != null){

                    if (sheet.getRow(cell.getRowIndex()).getCell(11) != null && cell.getRowIndex() > 4) {
                        get_Entity = sheet.getRow(cell.getRowIndex()).getCell(11).toString();
                    }
					
                   

                    // Get the RowIndex and add that to cell
                    
                    if (cell.getRowIndex() == 4 ) {
                        excelheaders.add(cell.toString());
                    }



                    // check if the content is equal to the content in LINE:67											
                    if (cell.getRowIndex() > 4 && cell.getRow().getCell(3) > 0 &&
                        cell.getColumnIndex()  4) {
                        				get_Number = dataFormatter.formatCellValue(cell);
                        				
                   		 	}
                        } else if (cell != null){
                            cr = new org.apache.poi.ss.util.CellReference(cell.toString());
                            row = sheet.getRow(cr.getRow());

                            cell = row.getCell(cr.getCol());

                            excelData.append(CheckData(dataFormatter.formatCellValue(cell)));

                        }
                        sep = "|";
                        
                        if (cell.getColumnIndex() == 20) {
                            excelData.append("\n");
                            sep = "";
                        }

                    } else if (cell.getRowIndex() > 4 &&
                        cell.getRow().getCell(3) > 0 &&
                        cell.getColumnIndex() <= 20) {

                        exitExcelReader = true;
                        break;
                    }

                    
      }
      j++; // increment 'j' counter value
   }
}
}
 channelMap.put('Entity', get_Entity);
         channelMap.put('Number', get_Number);


        // File Archive Funtionality Start 
        var currentDate = com.mirth.connect.server.userutil.DateUtil.getCurrentDate('YYYYMMdd');
        var extension = originalFilename.split('//.')

        var archivePath  				= 'ArchivePath'//your path

        channelMap.put('archivePath', archivePath);

        var sourceFileWithPath = fileDirectory + "\\" + originalFilename;

        var imageBytes = FileUtil.readBytes(sourceFileWithPath);
        var encodedValue = FileUtil.encode(imageBytes);

        //Archive source message
        FileUtil.write(archivePath, false, imageBytes);
        logger.debug(archivePath + ' was backedup successfully ');

        // File Archive Funtionality End

        // Delete Source File Functionality Start 
        var del = new java.io.File(sourceFileWithPath);
        del['delete']();

        logger.debug(sourceFileWithPath + ' was deleted from source successfully ')
} catch (e) {
        logger.debug("catch Block : " + e);
       getErrorMailContent(" file did not process successfully");
    } finally {
        //workBook.close();
        inputstream.close();
        channelMap.put('isEAIServiceNotification', isEAIserviceNotification);
    }

   
function CheckData (StringValue)
{
	var d	=	StringValue;
	 
	if(StringValue!=undefined){
		
	     if(isNaN(StringValue)){	       	      	
	      	return d;	       
	      	
	     }else{	       
	       
	       var x = StringValue.toString().split('\\.');	       
		     if(x[1]=='0'){
		          d =  x[0];
			}	      
	     }
	      
	}
	
	return d;
}

Above code traverses through each cell and fetches data via row wise and convert them to a delimited text.

I hope this solution helps you guys if you ever get a excel file as a input.

Feel free to comment and explore the code