web stats
Excel As Data Source - Mirth Community

Go Back   Mirth Community > Mirth Connect > Development

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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, 329 views)

Last edited by marmanold; 05-25-2011 at 06:15 AM.
Reply With Quote
 

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 03:52 PM.


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