0

I'm getting a NullPointerException and looked up the error and read about it, but still can't seem to fix the problem. My main method takes a list of File types and Strings and passes it to the Parse method to convert Excel Files to txt Files. I'm fairly new to java, any help would be appreciated. Thanks!

Exception in thread "main" java.lang.NullPointerException
    at transform.Parse.Transform(Parse.java:54)
    at transform.Input.main(Input.java:62)

And here is my code. I put two comments where the exceptions are shown:

package transform;

public class Input {

    private static final Object Face_value = null;

    public static void main( String args[] ) throws Exception {

        String str = " "; 
        Scanner sc = new Scanner(System.in);
        ArrayList<String> ExcelList = new ArrayList<String>();    
        System.out.println("Print .xlsx file names");
            while (sc.hasNextLine() && !(str = sc.nextLine()).equals("")) {
                ExcelList.add(str);
            } 

          for(int i=0; i<ExcelList.size(); i++) {
              System.out.println(ExcelList.get(i));
          }

        String str1 = " ";
        Scanner sc1 = new Scanner(System.in);
        ArrayList<String> txtList = new ArrayList<String>();
        System.out.println("Print desired .txt file names");  
        while(sc1.hasNextLine() && !(str1 = sc1.nextLine()).equals("")) {
            txtList.add(str1);
        }

        if(txtList.size() != ExcelList.size()) {
            System.out.println("The number of Excel files listed does not match the number of .txt files listed");
        }

        for(int i=0; i < txtList.size(); i++) {
            System.out.println(txtList.get(i));
        }

        //convert ArrayList of type String to type File
        for(int i=0; i < ExcelList.size(); i++) {
            File file = new File(ExcelList.get(i));
            Parse.Transform(file, txtList.get(i));
        }  
         ///////////////////////////////////////////////////////////////////   
         //ERROR ON THE LINE ABOVE(line 62) -- Parse.Transform(file, txtList.get(i));
         ///////////////////////////////////////////////////////////////////
         /////////////////////////////////////////////////////////////////// 
        System.out.println("\nDone");

}
 }




 package transform;

public class Parse{

public static void Transform (File ExcelFile, String TxtFile) {
Writer writer = null;

try {

        //read excel file and get the first sheet
        FileInputStream fis = new FileInputStream(ExcelFile);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);

        // set the name of the .txt file
        File file = new File(TxtFile);        
        writer = new BufferedWriter(new FileWriter(file));
        Iterator rows = sheet.rowIterator();
        writer.write("@SecurityDeal\r\n");

      while( rows.hasNext() ) {
         XSSFRow row = (XSSFRow) rows.next();

         if(row.getRowNum() == 0){
           continue;
         }

          Iterator cells = row.cellIterator();

          XSSFCell Face_Value = row.getCell(10); 
          XSSFCell Rate = row.getCell(8);
          XSSFCell DealDate = row.getCell(6);
          XSSFCell MaturityDate = row.getCell(7);
          XSSFCell CReference = row.getCell(5);
          XSSFCell Comments = row.getCell(4);

          XSSFCell Counterparty = row.getCell(3);
         //////////////////////ERROR ON BELOW LINE(Line 54) ///////////////////////
         ////////////////////////////////////////////////////////////////
         ////////////////////////////////////////////////////////////////
         String Cp = Counterparty.toString(); 

          //convert date to correct format
          Date s1 = MaturityDate.getDateCellValue();
          SimpleDateFormat sdf = new SimpleDateFormat("M/dd/yyyy");
          String MD = sdf.format(s1);

          Date d1 = DealDate.getDateCellValue();
          SimpleDateFormat sdf1 = new SimpleDateFormat("M/dd/yyyy");
          String dd = sdf1.format(d1);


          //convert cells to String
          String Comments_String = Comments.toString();
          String Face_Value_String = Face_Value.toString();

          //convert strings to double
          double Number_Comments = Double.parseDouble(Comments_String);
          double Number_Face_Value = Double.parseDouble(Face_Value_String);

          //get rid of exponential format
          String Comments1 = String.format("%.0f", Number_Comments);
          String Face_Value1 = String.format("%.0f", Number_Face_Value);

          //Mapping for counterparty
          if(Cp.equals("CITI")){
             Cp = "US-Citibank, US";
          }
          else if(Cp.equals("BAML")){
             Cp = "US-Bank Of America Merrill Lynch";
          } 
          else if (Cp.equals("MIZUHO")) {
             Cp = "US-Mizuho Ltd";
          }
          else if(Cp.equals("WELLS")) {
             Cp = "US-Wells Fargo";                  
          }


          writer.write("$NEW\r\n" + "Instrument=US-Commercial Paper\r\n" + "BorrowInvest=BORROWING\r\n"
          + "Counterparty=" + Cp + "\r\n" + "Entity=US-GMF\r\n" + "Currency=USD\r\n" + "FaceValue=" + Face_Value1 + "\r\n"
          + "BaseRate=" + Rate + "\r\n" + "DealDate=" + dd + "\r\n" + "MaturityDate=" + MD + "\r\n" + "Dealer=GM_Financi\r\n" + 
          "CounterpartyReference=" + CReference + "\r\n" + "Comments=" + Comments1 + "\r\n$INSERT\r\n");  

          System.out.println("\r\n");
      }
      System.out.println("Successfully Created File");  
} catch ( IOException ex ) {
    ex.printStackTrace();
    System.out.println("Check formatting of file names. Make sure they end in .txt or .xlsx");
    } finally {
        try 
            {if (writer != null) {writer.close();}} 
                catch (IOException e) {e.printStackTrace();}
   }
  }
 }
Warrior990
  • 59
  • 1
  • 5
  • which line is line 54 in Parse.java? – pvpkiran Jun 28 '18 at 19:13
  • I added a comment for line 54 and line 62 – Warrior990 Jun 28 '18 at 19:16
  • on line 61 where you call row.getCell(3). If you breakpoin there and step through is Counterparty getting a value or is it null? because that would cause the exception then on Counterparty.toString(); – Zannith Jun 28 '18 at 19:18
  • Wait Parse error was on 54 I thought – Zannith Jun 28 '18 at 19:22
  • Yes, Counterparty is getting a value – Warrior990 Jun 28 '18 at 19:29
  • At line 54, what are the values of `file` and `txtList.get(i)` ? – FredK Jun 28 '18 at 19:30
  • @Zannith Oops you were right, Parse error is on 54 and i edited it again to show it – Warrior990 Jun 28 '18 at 19:32
  • @FredK the value of file is: File (id=47) but if i expand it, the filePath is null. The value for TxtFile is whatever input i put in. – Warrior990 Jun 28 '18 at 19:35
  • If the NPE occurs at the line `String Cp = Counterparty.toString();` then at that moment `Counterparty` must be `null`. Because you execute this code in a loop it can be that this doesn't occur the first time through the loop, but at some later time. You can find out in which file and on which row by inserting `if (Counterparty == null) { System.out.println("F="+ExcelFile+", R="+row.getRowNum()); }` before `String Cp = Counterparty.toString();` – Thomas Kläger Jun 28 '18 at 19:43
  • @ThomasKläger Thanks!! I got it now – Warrior990 Jun 28 '18 at 19:59
  • @ThomasKläger So i thought it was working, but my program seems to be getting the same error for smaller excel files with fewer rows. It just seems very inconsistent on when im getting errors and on what files. All the files have mostly the same data, just varying in rows but I have no idea why some files fail and others go through – Warrior990 Jun 28 '18 at 21:41
  • 1
    One problem with Excel is: if a cell is empty, it is not stored in the Workbook. And then `row.getCell(x)` will return null. Your code has to be prepared this case, meaning that you have to check each `XSSFCell` variable whether it is null bofore calling any methods on it. – Thomas Kläger Jun 29 '18 at 09:23

0 Answers0