Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Development (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=8)
-   -   Excel As Data Source (http://www.mirthcorp.com/community/forums/showthread.php?t=5461)

marmanold 05-19-2011 12:25 PM

Excel As Data Source
 
1 Attachment(s)
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!

jonduncan99 05-24-2011 11:01 AM

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.

marmanold 05-24-2011 11:11 AM

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.

lmitrev 05-24-2011 04:29 PM

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

marmanold 05-25-2011 06:14 AM

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 07-07-2011 11:56 PM

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

marmanold 07-08-2011 05:43 AM

Quote:

Originally Posted by bernd05 (Post 19798)
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!

bernd05 07-11-2011 07:25 AM

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

Thanks for your reply.

Regards,
- Bernd

DimitriA 07-21-2011 04:29 PM

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?

marmanold 07-22-2011 05:39 AM

Your code is in Java. You will have to translate it to Javascript for it to work in Mirth.


All times are GMT -8. The time now is 08:09 AM.

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