Announcement

Collapse
No announcement yet.

Need to read Excel(.xls) files!!!!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need to read Excel(.xls) files!!!!

    Hi All,

    I have to read Excel files(.xsl), does any have any idea if thats possible. Actually converting an excel file to csv and then read it through mirth is very easy but my problem is that first of all, I don't have the control on these excel files, second this, if I decide to do that, I have to find out how to automate this process as I can't do it for all the files are they are huge in number.

    Please Help!!!!
    Dharamveer.

  • #2
    The Java Excel API could be a possible solution: http://jexcelapi.sourceforge.net/.
    How to use additional Java classes in mirth is described here http://www.mirthcorp.com/community/w...+Mirth+Connect

    Comment


    • #3
      Not Familiar with plug-in usage!!!

      Hi Rauchj!!!

      I'm not so familiar with plug-ins, I'm going through the supporting link you provided and I also tried downloading the latest plug-in, this file has lot of other files and what I know is that I have to put the jar file in custom-lib but not sure how to proceed from there and make use of this jar file, could you please throw some more light on this.

      Thank you,
      Dharamveer.

      Comment


      • #4
        The only 2 free APIs I know of to process excel files is JExcelAPI (previously mentioned) and Apache POI.

        I have used both, JExcelAPI is easier to use (it does less) so if you are unfamiliar with APIs I would start there. However, last I checked JExcelAPI is not compatible with xlsx files.

        There is pretty good documentation on both.

        Good Luck.

        Comment


        • #5
          Issue Fixed

          Thanks to both Rauchj and SQLOwns,

          My issue is fixed, I used jxl.jar and wrote a script to extract data from excel.

          Thanks to both of you once again.

          Comment


          • #6
            would you mind sharing your solution?

            Comment


            • #7
              Excel Reader

              Hi Rauchj,

              I downloaded one file from http://sourceforge.net/projects/jexc...celapi/2.6.12/

              and then I made few changes to the following script according to the business requirements, but this script will straight away convert each row to one xml file:
              try {
              var excelFile = new Packages.java.io.File("C:\\mappings.xls");
              fileExists = excelFile.exists();
              } catch(exception){
              fileExists=false;
              }


              if(fileExists){
              logger.error("----in getting xls");
              var ws = new Packages.jxl.WorkbookSettings();
              ws.setEncoding("Cp1252");
              var excel = Packages.jxl.Workbook.getWorkbook(excelFile, ws);

              //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).getCellFormat().getFormat().getFormatString ());
              // logger.error(sheet.getCell(myCol, myRow).getCellFormat().getFormat().toString());
              // logger.error(sheet.getCell(myCol, myRow).getCellFormat().toString());
              // logger.error(sheet.getCell(myCol, myRow).getCellType());
              // logger.error(sheet.getCell(myCol, myRow).getCellType().toString());

              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 excelData = new Array();
              var msgXML = new XML(msgString.toString());
              logger.info(msgXML)
              //excelData.add(msgXML.toString());
              }

              //Close the Excel file
              excel.close()8:16 PM
              }

              Please let me know in case you have any more questions!!!
              Thank you,
              Dharamveer.

              Comment


              • #8
                Can someone please provide a sample channel for this requirement? Need to read Excel file and save the column data to DB? I have added jar file and tried the same code provided but nothing is happening..

                Comment


                • #9
                  The script added in the post above you should be placed in a JS reader source. Try that and see.
                  HL7v2.7 Certified Control Specialist!

                  Comment


                  • #10
                    I'd actually do it in the source transformer so that you can use your choice of source connectors to accept the file. Then convert to a easier to process type so that the destination doesn't need to know it was an excel file. The sample code provided converts to XML. You'd just need to set that as your outbound msg.

                    Read it as binary.
                    Set inbound type to Raw
                    Create a new ByteArrayInputStream with the base64 decoded message.
                    Use that in the Packages.jxl.Workbook.getWorkbook call instead of passing it a File.

                    Comment

                    Working...
                    X