0 Replies Latest reply: Jul 23, 2010 2:16 PM by 843810 RSS

    Getting errors I can not understand

    843810
      Hello I am attempting to write a program for my boss and I keep getting errors that i cant figure out

      I am using the JexclAPI to help with the reading and writing of the excel files

      The program at the moment is supposed to print out
       ----jGRASP exec: java ReadExcel
      
      7 2010 
      -----------
      Useable Points: 1000, 4/2006
      -----------
      4 2006
      -----------
      2010 - 2006 = 4
      7 - 4 = 3
      Total time in months = 51
      -----------
      -----------
      Used Points: 500, 3/2007
      -----------
      3 2007
      -----------
      2010 - 2007 = 3
      7 - 3 = 4
      Total time in months = 40
      -----------
      -----------
      Useable Points: 1000, 4/2007
      Used Points: 500, 4/2007
      -----------
      4 2007
      -----------
      2010 - 2007 = 3
      7 - 4 = 3
      Total time in months = 39
      -----------
      -----------
      -----------
      8
      
       ----jGRASP: operation complete.
      and make a copy of an Excel sheet but its not letting me put in the close statement for the writable worksheet by saying
      ReadExcel.java:173: unreported exception jxl.write.WriteException; must be caught or declared to be thrown
           copy.close();
      and i tried putting the try and catch statements and it didnt work either

      also is not copying the Excel file but just making a new blank one

      nor is it letting me use my expire(time, row); method
      it keeps giving me
      Exception in thread "main" java.lang.NullPointerException
             at ReadExcel.expire(ReadExcel.java:140)
             at ReadExcel.read(ReadExcel.java:101)
             at ReadExcel.main(ReadExcel.java:150)
      does anybody have any ideas on how to fix this??


      import java.io.File;
      import java.util.Date;
      import jxl.*;
      import jxl.write.*;
      import java.io.IOException;
      
      import jxl.Cell;
      import jxl.CellType;
      import jxl.Sheet;
      import jxl.Workbook;
      import jxl.read.biff.BiffException;
      
      // Note: Save any changes on the spreadsheet before reading it in otherwise they will not be made
      
      /* when expire(time, row); is being used i get an error
      Exception in thread "main" java.lang.NullPointerException
             at ReadExcel.expire(ReadExcel.java:140)
             at ReadExcel.read(ReadExcel.java:101)
             at ReadExcel.main(ReadExcel.java:150)
      */
      
      
      
      public class ReadExcel {
      
       private String inputFile;
       String date[] = new String[2];
       
       UseablePoints[] useable;
       UsedPoints[] used;
      
      
      
       public void setInputFile(String inputFile) {
        this.inputFile = inputFile;
       }
      
      public String todayDate(Workbook w, Sheet sheet)
      {     
           Cell c1 = sheet.getCell(1,2); 
           Cell d1 = sheet.getCell(2,2);
           String tm = c1.getContents();
           String ty = d1.getContents();
           String today = tm + " " + ty;
           System.out.print(today);
           return today;
      }
      
      
      public String getData(Workbook w, Sheet sheet, int row)
      {
           Cell p = sheet.getCell(4,row - 1);
           Cell u = sheet.getCell(3,row - 1);
           String s = p.getContents();
           String t = u.getContents();
           int useda = Integer.parseInt(t);
           int a = Integer.parseInt(s);
           String trans = transactionDate(w, sheet, row);
           date = trans.split(" ");
           int b = Integer.parseInt(date[0]);
           int c = Integer.parseInt(date[1]);
           if(a != 0)
           {
                UseablePoints first = new UseablePoints(a, b, c);
                useable[row - 6] = first;
                System.out.println("Useable Points: " + first.getPoints() + ", " + first.getMonth() + "/" + first.getYear());
           
                }
           if(useda != 0)
           {
                UsedPoints ufirst = new UsedPoints(useda, b, c);
                used[row - 6] = ufirst;
                System.out.println("Used Points: " + ufirst.getPoints() + ", " + ufirst.getMonth() + "/" + ufirst.getYear());
      
           }
           System.out.println("-----------");
           System.out.println(trans);
           return trans;
      }
      
      public String transactionDate(Workbook w, Sheet sheet, int row)
      {
           Cell c3 = sheet.getCell(1,row - 1); 
           Cell d3 = sheet.getCell(2,row - 1);
           String am = c3.getContents();
           String ay = d3.getContents();
           String transaction = am + " " + ay;
           return transaction;
      }
      
      
       public void read() throws IOException  {
        File inputWorkbook = new File(inputFile);
        Workbook w;
        try {
         w = Workbook.getWorkbook(inputWorkbook);
           Sheet sheet = w.getSheet(0);
           int time;
           int rlength = sheet.getRows();
           useable = new UseablePoints[rlength - 5];
           used = new UsedPoints[rlength - 5];
           String t = todayDate(w, sheet);
           System.out.println(" ");
           System.out.println("-----------");
           
           for(int row = 6; row < rlength + 1; row++)
           {
                String trans = getData(w, sheet, row);
      
                time = timeBetween(t, trans);
      //          THIS LINE BELOW IS CAUSING A PROBLEM WHEN UNCOMMENTED
      //          expire(time, row);
                System.out.println("-----------");
                System.out.println("-----------");
                
           }
           System.out.println("-----------");
           System.out.println(rlength);
           writeCopy(w);
           w.close();
           } catch (BiffException e) {
         e.printStackTrace();
        }
      
       }
       
        
       public int timeBetween(String t, String trans)
       {
            int time = 0;
           String[] today = new String[2];
           today = t.split(" ");
           int tmonth = Integer.parseInt(today[0]);
           int tyear = Integer.parseInt(today[1]);
           String[] transaction = new String[2];
           transaction = trans.split(" ");
           int amonth = Integer.parseInt(transaction[0]);
           int ayear = Integer.parseInt(transaction[1]);
           int years = tyear - ayear;
           int myears = years * 12;
           int months = tmonth - amonth;
           time = myears + months;
           System.out.println("-----------");
           System.out.println(tyear + " - " +  ayear + " = " + years);
           System.out.println(tmonth + " - " +  amonth + " = " + months);
           System.out.println("Total time in months = " + time);
      
           return time;
       }
       
       public void expire(int time, int row)
       {
            if((time >= 36) && (useable[row - 6].getPoints() > 0))
           {
                useable[row - 6].expirePoints();
           }
           System.out.println("Points: " + useable[row-6].getPoints());
      }
      
      public static void writeCopy(Workbook w)
      {
           try {
           WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), w); 
           WritableSheet sheet = copy.getSheet(1);
           WritableCell cell = sheet.getWritableCell(1, 2); 
           if (cell.getType() == CellType.LABEL)
           {
                 Label l = (Label) cell;
                  l.setString("modified cell");
           } 
           copy.write();
           //THIS LINE IS CAUSING PROBLEMS
      //     copy.close();
           } catch (IOException e) {
         e.printStackTrace();
        }
        
      }
      
       public static void main(String[] args) throws IOException {
             ReadExcel test = new ReadExcel();
             test.setInputFile("/Users/Lauren/Documents/jexcelapi_2_6_12/Projects with JExcel/Points System/myfile.xls");
          test.read();
       }
      
      }