web stats
Excel As Data Source - Mirth Community

Go Back   Mirth Community > Mirth Connect > Development

Reply
 
Thread Tools Display Modes
  #1  
Old 05-19-2011, 12:25 PM
marmanold marmanold is offline
Mirth Newb
 
Join Date: Feb 2011
Location: Franklin, TN
Posts: 9
marmanold is on a distinguished road
Lightbulb Excel As Data Source

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:

    Code:
    //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:

    Code:
    <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!
Attached Files
File Type: jar jxl.jar (708.7 KB, 327 views)

Last edited by marmanold; 05-25-2011 at 06:15 AM.
Reply With Quote
  #2  
Old 05-24-2011, 11:01 AM
jonduncan99 jonduncan99 is offline
Mirth Newb
 
Join Date: Sep 2009
Posts: 16
jonduncan99 is on a distinguished road
Default Excel writer

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.
Reply With Quote
  #3  
Old 05-24-2011, 11:11 AM
marmanold marmanold is offline
Mirth Newb
 
Join Date: Feb 2011
Location: Franklin, TN
Posts: 9
marmanold is on a distinguished road
Default Excel as File Destination

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.
__________________
Michael Arnold| Electronic Data Interchange Specialist

PlayMaker CRM
Sales Innovation for Home Care
A Division of The ADAM Group
115 Southeast Parkway Court | Franklin, TN 37064
866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com
Reply With Quote
  #4  
Old 05-24-2011, 04:29 PM
lmitrev lmitrev is offline
What's HL7?
 
Join Date: May 2011
Posts: 2
lmitrev is on a distinguished road
Default can you use this for sample patient data?

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
Reply With Quote
  #5  
Old 05-25-2011, 06:14 AM
marmanold marmanold is offline
Mirth Newb
 
Join Date: Feb 2011
Location: Franklin, TN
Posts: 9
marmanold is on a distinguished road
Default

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.
__________________
Michael Arnold| Electronic Data Interchange Specialist

PlayMaker CRM
Sales Innovation for Home Care
A Division of The ADAM Group
115 Southeast Parkway Court | Franklin, TN 37064
866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com
Reply With Quote
  #6  
Old 07-07-2011, 11:56 PM
bernd05 bernd05 is offline
What's HL7?
 
Join Date: Aug 2010
Posts: 2
bernd05 is on a distinguished road
Default Get an error when running the channel

Hi,

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

Code:
[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
Reply With Quote
  #7  
Old 07-08-2011, 05:43 AM
marmanold marmanold is offline
Mirth Newb
 
Join Date: Feb 2011
Location: Franklin, TN
Posts: 9
marmanold is on a distinguished road
Default

Quote:
Originally Posted by bernd05 View Post
Hi,

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

Code:
[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
Unfortunately Mirth errors are rather cryptic. The "function not an object" error is something I've seen from time to time working with JAVA classes from within Mirth. It usually either means the JAVA class isn't loaded (Mirth hasn't been restarted since the .jar file was installed) or that the method isn't being used correctly.

You could try instantiating the object before using it. Something like:
Code:
var excelBook = new Packages.jxl.Workbook;
var excel = excelBook.getWorkbook(excelFile);
I also just realized that I left out the part in the code where I import the JAVA package. I believe the following is what I do in my channel. (I have another client Mirth setup installed at the moment, so I can't verify.)
Code:
importPackage(Packages.jxl.Workbook);
That might fix the issue as well.

Viel Glück!
__________________
Michael Arnold| Electronic Data Interchange Specialist

PlayMaker CRM
Sales Innovation for Home Care
A Division of The ADAM Group
115 Southeast Parkway Court | Franklin, TN 37064
866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com
Reply With Quote
  #8  
Old 07-11-2011, 07:25 AM
bernd05 bernd05 is offline
What's HL7?
 
Join Date: Aug 2010
Posts: 2
bernd05 is on a distinguished road
Default

I've updated to the newest version of Mirth and now everything is fine.

Thanks for your reply.

Regards,
- Bernd
Reply With Quote
  #9  
Old 07-21-2011, 04:29 PM
DimitriA DimitriA is offline
Mirth Newb
 
Join Date: Jul 2011
Posts: 17
DimitriA is on a distinguished road
Default

Has anyone gotten this to work?

I am trying to write an excel file using this (from the tutorial on the API)

Code:
importPackage(Packages.jxl.Workbook);

WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls")); 
WritableSheet sheet = workbook.createSheet("First Sheet", 0); 

Label label = new Label(0, 0, "Name");
sheet.addCell(label);
Label label = new Label(1, 0, "Value");
sheet.addCell(label);

for(count=0; count<$('resultVal'); count++)
{

Label label = new Label(0, count, $('resultName')[count]);
sheet.addCell(label);

Number number = new Number(1, count, $('resultVal')[count]);
sheet.addCell(number); 
}
Any help?
Reply With Quote
  #10  
Old 07-22-2011, 05:39 AM
marmanold marmanold is offline
Mirth Newb
 
Join Date: Feb 2011
Location: Franklin, TN
Posts: 9
marmanold is on a distinguished road
Default

Your code is in Java. You will have to translate it to Javascript for it to work in Mirth.
__________________
Michael Arnold| Electronic Data Interchange Specialist

PlayMaker CRM
Sales Innovation for Home Care
A Division of The ADAM Group
115 Southeast Parkway Court | Franklin, TN 37064
866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com
Reply With Quote
Reply

Tags
excel, java, jexcel

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 12:21 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation