web stats
Mirth Community - View Single Post - Excel As Data Source
View Single Post
  #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