Excel As Data Source

[marmanold]

For those interested it is possible via the JExcel Api to use an Excel file as a data source in Mirth.

1. Move attached jxl.jar to C:\Program Files\Mirth Connect\custom-lib\
2. Restart Mirth
3. Create a new channel with the follow as the JavaScript Reader data source:
   
   

   //List to store each Excel row in
   var excelData = new Packages.java.util.ArrayList();
   
   //Open an Excel file
   var excelFile = new Packages.java.io.File("C:/Users/your_user_name/Documents/name_of_file.xls");
   var excel = Packages.jxl.Workbook.getWorkbook(excelFile);
   
   //Read in the first sheet
   var sheet = excel.getSheet(0);
   
   //Variables for the current row and column
   var myRow = 1;
   var myCol = 0;
   
   //Variable for the last row or last column
   var rowStop = 0;
   var colStop = 0;
   
   while (rowStop == 0) {
       var msgString = '<result>';
       while (colStop == 0) {
           //See if this column is valid
           //If so, write the cell
           try {
               //logger.error(sheet.getCell(myCol, myRow).getContents());
               msgString = msgString + '<col' + myCol + '>' + sheet.getCell(myCol, myRow).getContents() + '</col' + myCol + '>';
           }
           //If not, stop processing at this column and move on to the next row.
           catch(ex) {
               colStop = 1;    
           }
           myCol++;
       }
       myRow++;
       myCol = 0;
       colStop = 0;
   
       //See if the next row is valid
       try {
           sheet.getCell(myCol, myRow);
       }
       //If not, stop processing the Excel file.
       catch(ex) {
           rowStop = 1;    
       }
   
       msgString = msgString + '</result>';
   
       //Clear out invalid XML characters
       msgString = msgString.replace(/&/g, '&amp;');
       msgString = msgString.replace(/\'/g, '&apos;');
       msgString = msgString.replace(/\"/g, '&quot;');
   
       //Add the message to the message list
       var msgXML = new XML(msgString.toString());
       excelData.add(msgXML.toString());
   }
   
   //Close the Excel file
   excel.close()
   
   return excelData;

4. Destinations, etc. will be done as normal. Your inbound message template should look something like the following:
   
   

   <result>
       <col0></col0>
       <col1></col1>
       <col2></col2>
       <col3></col3>
       <col4></col4>
       <col5></col5>
   </result>

This is all working for me on my Windows 7 64-bit machine. Your mileage may vary. Make sure you set the data types in the summary tab to XML!

[jonduncan99]

That looks pretty easy. I was looking at http://poi.apache.org/spreadsheet/index.html which looks like it can do 2007/2010 xml formats but I never figured out how to use it with Mirth. I need to be able to write Excel files.
I think it would be really nice if we could make that a file connector but I don't know enough about programming to be able to do that and give to the community.

[marmanold]

Having Excel as a file destination is definitely possible with this module. I had a small proof of concept somewhere, but I can't find it at the moment. Basically you'd just loop through each row and column of the message writing each data element to the coordinating cell in Excel.

[lmitrev]

Please bear with me - I'm a Mirth newbie.
What kind of data would you enter in your inbound Excel message template?
I'm interested in running some bench tests using sample patient data - lab results, notes, etc. but have no good data source to pass through Mirth.
Thx,
Lou

[marmanold]

The javascript is written to be generic. You can provide it with any Excel (.xls only) file and it will read in all the data on the first worksheet.

You shouldn't put any data in the inbound message template. It is simply the template used for the transformer in your destinations. You will put a <coln></coln> tag for each column used in your Excel report. Because this is read in as an array it's zero-based, so you start counting at zero. (E.g., column one is <col0></col0>, column two is <col1></col1>, etc.)

I can tell you that the Java library is a little memory/processor intensive. I was hitting 250MB memory used and 50% processor used while it was processing. I would say it took about five minutes or so to read my 319KB Excel file with about 500 rows.

[bernd05]

Hi,

I've tried to follow your instructions, but when I try to use the Channel I reveiced an error saying:

[2011-07-08 09:55:32,817]  
FATAL (org.mule.impl.DefaultComponentExceptionStrategy:84): The error is: null
java.lang.NullPointerException
	at com.webreach.mirth.connectors.js.JavaScriptMessageReceiver.poll(JavaScriptMessageReceiver.java:88)
	at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:90)
	at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
	at java.lang.Thread.run(Unknown Source)
[2011-07-08 09:54:52,754]  
FATAL 
(org.mule.impl.DefaultComponentExceptionStrategy:83): The Default Component Exception Strategy has been invoked but there is no current event on the context
[2011-07-08 09:51:07,408]  
ERROR 
(com.webreach.mirth.connectors.js.JavaScriptMessageReceiver:152): org.mozilla.javascript.EcmaError: TypeError: Cannot call property getWorkbook in object [JavaPackage jxl.Workbook]. It is not a function, it is "object". (62c52ab0-7111-48f0-bafc-db7bccfbb578#9)

Any hints for me?

Thanks, Regards,
- Bernd