8 Replies Latest reply: Aug 8, 2013 7:30 AM by Deepu Muraleedharan RSS

    Cannot read an Excel file and compare in openscript

    user541023 - oracle

      Hi

      Can anyone please help me how to read an excel file in openscript. I tried the following function ,but it's not working.(I included the apache poi)Please help

      public void finish() throws Exception {

        beginStep("[7] Compare Files");

        {

      datatable.importExcel("C:\\Users\\hnariset\\Downloads\\OATS Downloads\\Plan V Actual Summary - Platform-test0.xlsx", true);

        File file1 = new File("C:\\Users\\hnariset\\Downloads\\OATS Downloads\\Plan V Actual Summary - Platform-test0.xlsx");

          displayFromExcel(file1);

        }

      }

      Function:

      @SuppressWarnings("rawtypes") public void displayFromExcel (File file1)

        {

        InputStream inputStream = null;

          try

        {

        inputStream = new FileInputStream(file1);

        }

        catch (FileNotFoundException e)

        {

        System.out.println ("File not found in the specified path.");

        JOptionPane.showMessageDialog(null, "File not found in the specified path.");

        e.printStackTrace ();

        }

         POIFSFileSystem fileSystem = null;

        try

        {

        fileSystem = new POIFSFileSystem (inputStream);

         HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);

        HSSFSheet         sheet    = workBook.getSheetAt (0);

        Iterator rows     = sheet.rowIterator ();

           while (rows.hasNext ())

        {

        HSSFRow row = (HSSFRow) rows.next();

        JOptionPane.showMessageDialog(null, "Row No.: " + row.getRowNum ());

         Iterator cells = row.cellIterator ();

        

        while (cells.hasNext ())

        {

        HSSFCell cell = (HSSFCell) cells.next();

      JOptionPane.showMessageDialog(null, "Cell No.: " + cell.getCellNum ());

        

        switch (cell.getCellType ())

        {

           case HSSFCell.CELL_TYPE_NUMERIC :

           {JOptionPane.showMessageDialog(null, "Numeric value: " + cell.getNumericCellValue ()); break;

           }

        

           case HSSFCell.CELL_TYPE_STRING :

           {String richTextString = cell.getStringCellValue ();

           JOptionPane.showMessageDialog(null, "String value: " + richTextString.toString ());

           break;

           }

              default :

           {JOptionPane.showMessageDialog(null, "Type not supported.");

               break;

           }

        }

        }

        }

        }

        catch (IOException e)

        {

        e.printStackTrace ();

        }

        }

        • 1. Re: Cannot read an Excel file and compare in openscript
          Deepu Muraleedharan

          y dont you use datatable??

          Can u specify the error generated??

          • 2. Re: Cannot read an Excel file and compare in openscript
            user541023 - oracle

            Hi

            i

            I'm actually trying to compare two excel sheets using open-script. I wrote the following code for this

             

            File file1 = new File(

              "C:\\Users\\hnariset\\DDownloads\\OATS Downloads\\Plan_V_Actual_Summary_-_Platform.xls");

              File file2 = new File(

              "C:\\Users\\hnariset\\DDownloads\\OATS Downloads\\Plan_V_Actual_Summary_-_Platform(1).xls");

              boolean filesSame = FileUtils.contentEquals(file1, file2);

            if (filesSame) {

              JOptionPane.showMessageDialog(null, "Files have same contents.");

              System.exit(0);

              } else {

              JOptionPane.showMessageDialog(null,"Files have different contents.");

              System.exit(0);

              }

             

            But this is giving me (filesSame=) false always,even if the two files are same.(I made a copy of one sheet manually in the folder and compared the two).This is happening for every type of file.

             

            I tried to see the error and printed the differences.The problem,I found, is in reading the files.It's reading in some language I can't understand.

            So I want to read the excel files and try to print it in English.

            The error(the previous code) is JAVA.IO.EXCEPTION and java.IO.SOCKET EXCEPTION..

            How to rectify this error using the same function or write a new function to know exactly the error(read in normal language)??

             

            Regards

            Harsha

            • 4. Re: Cannot read an Excel file and compare in openscript
              Deepu Muraleedharan

              if you want to execute above code, add following jar files in assets of script:

               

              First download packages from Apache Download Mirrors

               

              Add following(extract from zip file downloaded) to assets

               

              poi-excelant.jar

              poi-ooxml-schemas.jar

              poi-ooxml.jar

              poi-scratchpad.jar

              poi.jar

              xmlbeans.jar

              dom4j.jar

               

              Then run your scripts

              • 5. Re: Cannot read an Excel file and compare in openscript
                Deepu Muraleedharan

                also change *.xlsx file to *.xls

                • 6. Re: Cannot read an Excel file and compare in openscript
                  user541023 - oracle

                  Hi

                  I added all those jar files now it's saying NULL pointer exception at "point0".how to rectify this??

                   

                   

                  public void finish() throws Exception {

                    beginStep("[7] Compare Files");

                    {

                    //File file1 = new File("Plan_V_Actual_Summary_-_Platform(1).xls");

                    displayFromExcel(new File("Plan_V_Actual_Summary_-_Platform(1).xls"));

                    JOptionPane.showMessageDialog(null, "Result");

                    }

                    }

                    @SuppressWarnings("deprecation") public void displayFromExcel (File file1)

                    {

                    InputStream inputStream = null;

                    try

                    {

                  JOptionPane.showMessageDialog(null, "point0");  --this is printed

                    inputStream = new FileInputStream(file1);              -- eror by null pointer exception

                    JOptionPane.showMessageDialog(null, "point1");

                    }

                    catch (FileNotFoundException e)

                    {

                    //System.out.println ("File not found in the specified path.");

                    JOptionPane.showMessageDialog(null, "File not found in the specified path.");

                    e.printStackTrace ();

                    }

                    

                    POIFSFileSystem fileSystem = null;

                    

                    try

                    {

                    fileSystem = new POIFSFileSystem (inputStream);

                    JOptionPane.showMessageDialog(null, "point2"); 

                    HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);

                    JOptionPane.showMessageDialog(null, "point3");

                    HSSFSheet         sheet    = workBook.getSheetAt (1);

                    JOptionPane.showMessageDialog(null, "point4");

                    Iterator rows     = sheet.rowIterator ();

                    JOptionPane.showMessageDialog(null, "point5");

                    

                    while (rows.hasNext ())

                    {

                    HSSFRow row = (HSSFRow) rows.next();

                    

                    // display row number in the console.

                    //System.out.println ("Row No.: " + row.getRowNum ());

                    JOptionPane.showMessageDialog(null, "Row No.: " + row.getRowNum ());

                    

                    // once get a row its time to iterate through cells.

                    Iterator cells = row.cellIterator ();

                    

                    while (cells.hasNext ())

                    {

                    HSSFCell cell = (HSSFCell) cells.next();

                    

                    //System.out.println ("Cell No.: " + cell.getCellNum ());

                    JOptionPane.showMessageDialog(null, "Cell No.: " + cell.getCellNum());

                    

                    switch (cell.getCellType ())

                    {

                       case HSSFCell.CELL_TYPE_NUMERIC :

                       { // cell type numeric.

                           //System.out.println ("Numeric value: " + cell.getNumericCellValue ());

                           JOptionPane.showMessageDialog(null, "Numeric value: " + cell.getNumericCellValue ());

                           break;

                       }

                    case HSSFCell.CELL_TYPE_STRING :

                       {// cell type string.

                           String richTextString = cell.getStringCellValue ();

                           //System.out.println ("String value: " + richTextString.toString ());

                           JOptionPane.showMessageDialog(null, "String value: " + richTextString.toString ());

                    

                           break;

                       }

                       default :

                       {// types other than String and Numeric.

                           //System.out.println ("Type not supported.");

                           JOptionPane.showMessageDialog(null, "Type not supported.");

                           break;

                       }

                    }

                    }

                    }

                    }

                    catch (IOException e)

                    {

                    e.printStackTrace ();

                    JOptionPane.showMessageDialog(null, "ERROR:");

                    }

                    }

                  • 7. Re: Cannot read an Excel file and compare in openscript
                    user541023 - oracle

                    Hi

                    I'm sorry.the last one is wrong  this is the code and it ends abruptly by the catch exception after "point1.1"

                    the code is:

                    public void finish() throws Exception {

                      beginStep("[7] Compare Files");

                      {

                      //File file1 = new File("Plan_V_Actual_Summary_-_Platform(1).xls");

                      displayFromExcel(new File("C:\\Users\\hnariset\\Downloads\\OATS Downloads\\Plan_V_Actual_Summary_-_Platform(1).xls"));

                      JOptionPane.showMessageDialog(null, "Result");

                      }

                      }

                      @SuppressWarnings({ "deprecation", "rawtypes" }) public void displayFromExcel (File file1)

                      {

                      InputStream inputStream = null;

                      try

                      {

                     

                      //JOptionPane.showMessageDialog(null, "point0"); 

                      inputStream = new FileInputStream(file1);

                      //JOptionPane.showMessageDialog(null, "point1");

                      }

                      catch (FileNotFoundException e)

                      {

                      //System.out.println ("File not found in the specified path.");

                      JOptionPane.showMessageDialog(null, "File not found in the specified path 1212");

                      e.printStackTrace ();

                      }

                      

                      POIFSFileSystem fileSystem = null;

                      JOptionPane.showMessageDialog(null, "point1");

                      try

                      {

                      JOptionPane.showMessageDialog(null, "point1.1"); --printed and the next one is "ERROR:" in the catch phrase

                      fileSystem = new POIFSFileSystem (inputStream);

                      JOptionPane.showMessageDialog(null, "point2"); 

                      HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);

                      JOptionPane.showMessageDialog(null, "point3");

                      HSSFSheet         sheet    = workBook.getSheetAt (1);

                      JOptionPane.showMessageDialog(null, "point4");

                      Iterator rows     = sheet.rowIterator ();

                      JOptionPane.showMessageDialog(null, "point5");

                      

                      while (rows.hasNext ())

                      {

                      HSSFRow row = (HSSFRow) rows.next();

                      

                      // display row number in the console.

                      //System.out.println ("Row No.: " + row.getRowNum ());

                      JOptionPane.showMessageDialog(null, "Row No.: " + row.getRowNum ());

                      

                      // once get a row its time to iterate through cells.

                      Iterator cells = row.cellIterator ();

                      

                      while (cells.hasNext ())

                      {

                      HSSFCell cell = (HSSFCell) cells.next();

                      

                      //System.out.println ("Cell No.: " + cell.getCellNum ());

                      JOptionPane.showMessageDialog(null, "Cell No.: " + cell.getCellNum());

                      

                      switch (cell.getCellType ())

                      {

                         case HSSFCell.CELL_TYPE_NUMERIC :

                         {

                      

                             // cell type numeric.

                             //System.out.println ("Numeric value: " + cell.getNumericCellValue ());

                             JOptionPane.showMessageDialog(null, "Numeric value: " + cell.getNumericCellValue ());

                      

                             break;

                         }

                      

                         case HSSFCell.CELL_TYPE_STRING :

                         {

                      

                             // cell type string.

                             String richTextString = cell.getStringCellValue ();

                      

                             //System.out.println ("String value: " + richTextString.toString ());

                             JOptionPane.showMessageDialog(null, "String value: " + richTextString.toString ());

                      

                             break;

                         }

                      

                         default :

                         {

                      

                             // types other than String and Numeric.

                             //System.out.println ("Type not supported.");

                             JOptionPane.showMessageDialog(null, "Type not supported.");

                             break;

                         }

                      }

                      }

                      }

                      }

                      catch (IOException e)

                      {

                      e.printStackTrace ();

                      JOptionPane.showMessageDialog(null, "ERROR2:");

                      }

                       }

                    • 8. Re: Cannot read an Excel file and compare in openscript
                      Deepu Muraleedharan

                      make sure headres you added are following

                       

                       

                       

                       

                      
                      import java.io.File;
                      import java.io.FileInputStream;
                      import java.io.FileNotFoundException;
                      import java.io.IOException;
                      import java.io.InputStream;
                      import java.util.Iterator;
                      import javax.swing.JOptionPane;
                      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;