Not able to print in cells of Excel. JAVA

Question

This is program for reading file and writing difference into each cell of excel.
I am facing an issue where the output in console shows the incremented value of i, but its not writing value to all index's, instead only the last index is written.
I am new to java and tried doing changes to code but nothing worked.

Below is my code:

FileInputStream fstream = new FileInputStream("C:\\Users\\Vishal\\workspace\\timestampAutomation\\bin\\com\\time\\output\\myoutput1.txt");
    DataInputStream in = new DataInputStream(fstream);
    BufferedReader br = new BufferedReader(new InputStreamReader(in));
    String strLine;
    String timestamp="";
    String value="";
    int count = 0;
    int i = 0;
     ArrayList words=new ArrayList<String>();
     Pattern p = Pattern.compile("\\bSYSTEM:TIMESTAMP\\b", Pattern.CASE_INSENSITIVE);
    while ((strLine = br.readLine()) != null)
    {
        String[] words1=strLine.split(",");
        words.addAll(Arrays.asList(words1));
    }

    System.out.println("WORDS LENGTH:"+words.size());
    for (String word : (ArrayList<String>)words) 
    {

        Matcher m=p.matcher(word);
        count++;

        if (m.find()) 
        {

            if(count<words.size()-1)
            {
                String tmp=(String)words.get(count);
                String[] tmpArr=tmp.split("=");
                timestamp=tmpArr[1];
                String val=(String)words.get(count+1);
                String[] valArr=val.split("=");
                value=valArr[1];
            }

                System.out.println("Timestamp:"+timestamp+"\tValue:"+value);
                //Splitting output into data format given
                //Splitting output into data format given
                String year=value.substring(0, 4);

                String mnt=value.substring(4, 6);

                String day=value.substring(6, 8);

                String hr=value.substring(8, 10);

                int hours=Integer.parseInt(hr)-2;

                String min=value.substring(10, 12);

                String sec=value.substring(12, 14);

                String valueCon=year+"/"+mnt+"/"+day+" "+String.valueOf(hours)+":"+min+":"+sec;

                long newtime= Long.parseLong(timestamp);
                Date currentDate = new Date(newtime - TimeUnit.MINUTES.toMillis(330));

                String timeStamp = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(currentDate);

                String dateStart = timeStamp;
                String dateStop = valueCon;

                SimpleDateFormat format = new SimpleDateFormat("yy/MM/dd HH:mm:ss");

                Date d1 = null;
                Date d2 = null;

                d1 = format.parse(dateStart);
                d2 = format.parse(dateStop);

                long duration  = d1.getTime() - d2.getTime();

                long diffInSeconds = TimeUnit.MILLISECONDS.toSeconds(duration);
                System.out.println("hbase Timestamp "+timeStamp);
                System.out.println("Value: "+valueCon); 
                System.out.println("Difference of Timestamp in Seconds:"+diffInSeconds);

                //printing values in excel
                Workbook wb = new HSSFWorkbook();
                Sheet sheet = wb.createSheet("sheet");
                Row row = sheet.createRow((short) 0);



                row.createCell(i).setCellValue(diffInSeconds);

                FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Vishal\\workspace\\timestampAutomation\\bin\\com\\time\\output\\helloworl.xls");
                wb.write(fileOut);
                fileOut.close();
                i++;
        }


    }
    }

output on console:

WORDS LENGTH:123
Timestamp:1504767614024 Value:20170907090000
hbase Timestamp 2017/09/07 07:00:14
Value: 2017/09/07 7:00:00
Difference of Timestamp in Seconds:14
current value of i 0    Timestamp:1504767614025 Value:20170907090000
hbase Timestamp 2017/09/07 07:00:14
Value: 2017/09/07 7:00:00
Difference of Timestamp in Seconds:14
current value of i 1    Timestamp:1504767614029 Value:20170907090000
hbase Timestamp 2017/09/07 07:00:14
Value: 2017/09/07 7:00:00
Difference of Timestamp in Seconds:14
current value of i 2    Timestamp:1504767614030 Value:20170907090000
hbase Timestamp 2017/09/07 07:00:14
Value: 2017/09/07 7:00:00
Difference of Timestamp in Seconds:14
current value of i 3

But it doesn't print on all four index(cells) in excel.
Am i missing something, please help me :

output in excel

Answer 1

There is a problem in your logic

//printing values in excel
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet("sheet");
  Row row = sheet.createRow((short) 0);
  row.createCell(i).setCellValue(diffInSeconds);

So, you are creating a workbook in loop , that means you are creating n number of workbooks then in each of those workbooks you are creating a sheet and then in each sheet one row at 0th index and then in this row you create a cell at ith index, so in each of these workbooks the cell is being created at previous workbooks's-->sheet's-->cell(i) plus 1. This makes no sense. A typical excel sheet should look like 1 workbook, 1 or more sheets and then each sheet containing 1 or more rows and each row containing one of more cells.

Move this before your for-loop

   Workbook wb = new HSSFWorkbook();
   Sheet sheet = wb.createSheet("sheet");

Create row in the for loop as per your need, from the given code , it looks like you only want 1 row. Then for that row create as many cells you wish, from given code 4 cells. Create those cells in a for-loop and then finally when you are done write the output to file.

Move this

 FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Vishal\\workspace\\timestampAutomation\\bin\\com\\time\\output\\helloworl.xls");
                wb.write(fileOut);
                fileOut.close();

After all the for-loops are closed.