5 Replies Latest reply: May 4, 2012 6:27 AM by userN RSS

    Read Excel File Using FileAdapter in SOA 11g

    916888
      Hi,

      Anyone please help me I need to read the Excel file,so it is not possible in FileAdapter.How to convert Excel file in to csv/text file then how we read those files in SOA11g.


      Regards,
      sai
        • 1. Re: Read Excel File Using FileAdapter in SOA 11g
          user9285225
          Hello,

          The below link would be helpful for u.

          http://ofmxperts.blogspot.co.uk/2011/12/bpel-file-adapter-tutorial.html
          • 2. Re: Read Excel File Using FileAdapter in SOA 11g
            916888
            Hi,

            I created one Java code,it convert Excel file into text file.My requirement is read the 'Excel' file then convert it and write it has a 'text' file.How to call Java code in SOA11g.


            Regards,
            sai
            • 3. Re: Read Excel File Using FileAdapter in SOA 11g
              userN
              I believe the best solution in your case to tweak the design a bit. You are already writing a java class that reads the excel file. I suggest to convert this java code(or say java class) to a web service and call that web service from your bpel code. In short call the java web service and not the java class.
              • 4. Re: Read Excel File Using FileAdapter in SOA 11g
                916888
                Hello,

                My Java code contain static methods so it is not allowed to convert web service.Pls find my java code below.

                _________________________________

                import java.io.File;
                import java.io.FileInputStream;
                import java.io.FileNotFoundException;
                import java.io.IOException;
                import java.io.PrintStream;
                import java.util.ArrayList;
                import java.util.Iterator;
                import java.util.List;
                import org.apache.poi.hssf.usermodel.HSSFCell;
                import org.apache.poi.hssf.usermodel.HSSFRow;
                import org.apache.poi.hssf.usermodel.HSSFSheet;
                import org.apache.poi.hssf.usermodel.HSSFWorkbook;
                import org.apache.poi.poifs.filesystem.POIFSFileSystem;

                public class ExtractExcelToCSV {

                private static List<List<HSSFCell>> cellGrid;

                public static void convertExcelToCsv() throws IOException {
                try {
                cellGrid = new ArrayList<List<HSSFCell>>();
                FileInputStream myInput = new FileInputStream("C:/input.xls");
                POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                Iterator<?> rowIter = mySheet.rowIterator();

                while (rowIter.hasNext()) {
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<?> cellIter = myRow.cellIterator();
                List<HSSFCell> cellRowList = new ArrayList<HSSFCell>();
                while (cellIter.hasNext()) {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                cellRowList.add(myCell);
                }
                cellGrid.add(cellRowList);
                }
                } catch (FileNotFoundException e) {
                e.printStackTrace();
                }

                File file = new File("newFile.csv");
                PrintStream stream = new PrintStream(file);
                for (int i = 0; i < cellGrid.size(); i++) {
                List<HSSFCell> cellRowList = cellGrid.get(i);
                for (int j = 0; j < cellRowList.size(); j++) {
                HSSFCell myCell = (HSSFCell) cellRowList.get(j);
                String stringCellValue = myCell.toString();
                stream.print(stringCellValue + ";");
                }
                stream.println("");
                }
                }

                public static void main(String[] args) {
                try {
                convertExcelToCsv();
                } catch (IOException e) {
                e.printStackTrace();
                }
                }
                }
                ______________________________________________
                • 5. Re: Read Excel File Using FileAdapter in SOA 11g
                  userN
                  ok, in that case you can just remove static from your java method/variables. Pass the filename and other parameters as required to the method and that will become web service request parameters. Also, you will need the return type on this methods as that will the response to the web service.