Web Page to Excel Sheet

[Ankit Tripathi]

Hi all,
I have to read data from a web page and copy it to an Excel sheet 3 times in a day and that all should be done automatically at specified time daily.
On the part of writing data to sheet I am using Apache POI and for automating the process at specific time I am using Timer class and it's methods.
I have gone through documentation for Timer class and used some versions of schedule() method defined there but on initial testing with println method I was not able to get satisfactory results.also I am not getting how to automate the task many times in a single day at specified timings.
I am pasting initial testing code here.

URLReader.java

package com.ankit.javatoexcel1;



import java.util.*;

public class URLReader {
    public static void main(String[] args)  {
   //Creating instance of Timer class
	 Timer timer  = new Timer();
              //getting instance of Calendar
                      Calendar date = Calendar.getInstance();
           //setting the properties of calendar to run it 5 days in week
                                date.set(
                                     Calendar.DAY_OF_WEEK,
                                     Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, Calendar.THURSDAY, Calendar.FRIDAY
		  
                                             );
                //setting the specified time initially to fetch the log although I want that it should be done three times in a single day
                                                date.set(Calendar.HOUR, 5);
                                                date.set(Calendar.MINUTE, 18);
                                                 date.set(Calendar.SECOND, 0);
        //calling the schedule method on timer object to set the interval between two runs and total period for which task should run.
                                                          timer.schedule(
                                                               new MyTimer(),
                                                                    15,
                                                                    1000*365*24*7*60*60        
		                                                );
                                                             }

                                        }

MyTimer.java

package com.ankit.javatoexcel1;



import java.util.*;


 class MyTimer extends TimerTask {
	                    public void run()
	                       {
                            //Business Logic
		System.out.println("Hiiiiii");
	                              }
                                                    }

also suggest if there can be a better way in comparison of Timer and TimerTask using which I can interact with POI . I have heard about "Quartz",also Windows scheduling service or cronjobs should not be used acc. to my requirement.

[Ulf Dittmer]

If you want to schedule something 3 times a day, then the interval should be 8 hours (assuming an even spacing). What interval do you think "1000*365*24*7*60*60" represents? The presence of both "7" and "365" by itself should make you suspicious.

[Ankit Tripathi]

According to schedule() method defined in Timer class, "1000*365*24*7*60*60" is total timespan for which task should be run(As explaind by link and what I have got).

schedule(TimerTask task, long delay, long period)
Schedules the specified task for repeated fixed-delay execution, beginning after the specified delay.

Link

There are fair chances that I would have made a mistake in choosing the right version of schedule method because definitions given in link seem to be little bit confusing.

Also I want that business logic should be performed on fixed delay of 3 hours during my office hours starting at 10:15 AM 3 times in a day.

[Rob Spoor]

Ulf Dittmer wrote:What interval do you think "1000*365*24*7*60*60" represents? The presence of both "7" and "365" by itself should make you suspicious.

Another warning: that value is mathematically larger than Integer.MAX_VALUE so it will overflow. The result is probably even negative. The values should be turned into longs, or at least the first one: 1000L*365*24*7*60*60. Because the first one is a long the entire result will be a long and the value will not overflow.

[Ulf Dittmer]

According to schedule() method defined in Timer class, "1000*365*24*7*60*60" is total timespan for which task should be run

No. It's the period between Task invocations. (Repeated Tasks run forever, or until the web app is terminated, whichever comes first.)

Also I want that business logic should be performed on fixed delay of 3 hours during my office hours starting at 10:15 AM 3 times in a day.

One approach would be to schedule a Task to run at, say, 10 AM each day which then creates 3 worker Tasks to run in 0:15h, 3:15h and 6:15h for non-repeating execution. Another approach would be to schedule 3 worker Tasks that run at 10:15, 13:15 and 16:15 each day repeatedly.

[Ankit Tripathi]

Finally I was able to write business logic.Here is the code snippet given below.
But I am facing a new problem now.

import java.net.*;
import java.text.*;
import java.io.*;
import java.util.*;
import java.awt.Toolkit;
import java.util.ArrayList;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;
import org.apache.poi.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

public class TimerClass {

	Toolkit toolkit;

	Timer timer;

	void TimerClassDemo() {
		toolkit = Toolkit.getDefaultToolkit();
		timer = new Timer();
		long time = 15*60*60*1000;
		timer.schedule(new RemindTask(), 0, time);
	}

	public static void main(String args[]) {
		TimerClass tc = new TimerClass();
		tc.TimerClassDemo();
	}}

	class RemindTask extends TimerTask {

		public void run() {
			try
			{
	        Calendar cal = Calendar.getInstance();
	        String am_pm;
	        int hour = cal.get(Calendar.HOUR);
	        String h = Integer.toString(hour);
	        
	      
	        int minute = cal.get(Calendar.MINUTE);
	        String m = Integer.toString(minute);
	        
	      
	        int second = cal.get(Calendar.SECOND);
	        String s = Integer.toString(second);
	        
	        if(cal.get(Calendar.AM_PM) == 0)
            {
                am_pm = "AM";
            }
              else
              {
                am_pm = "PM";
              }
                
	      
	        
	        DateFormat formatter ;
	        Date date=new Date() ; 
	        formatter = new SimpleDateFormat("dd-MMM-yy");
            String d = formatter.format(date);
			String inputLine="";
			InputStream in = null;
			String str="";
			String key="";
			String value="";
			String freemem="";
			String totmem="";
			String maxmem="";
			String avpros="";
			List list=new ArrayList();
			
			URL url = new URL("http://www.elexonline.com/index.html");
			int x=3;
			
			
		in = url.openStream();
				BufferedReader is = new BufferedReader(new InputStreamReader(in));
				
				while (( inputLine=is.readLine()) != null)
				    {
					list.add(inputLine);
					}	
				
				is.close();
				
				for (int i=0; i<list.size();i++){
					
				if(list.get(i).toString().contains("marketing")){
				freemem = list.get(i+1).toString();	
				//System.out.println(freemem);
				}
				
				if(list.get(i).toString().contains("EMS")){
					totmem = list.get(i+1).toString();
					//System.out.println(totmem);
					}
				
				if(list.get(i).toString().contains("IT")){
					maxmem = list.get(i+1).toString();
					//System.out.println(maxmem);
					}
				
				if(list.get(i).toString().contains("Email")){
					avpros = list.get(i+1).toString();	
					//System.out.println(avpros);
					}
					}
			
				
				
		HSSFWorkbook wb = new HSSFWorkbook();
		CreationHelper createHelper = wb.getCreationHelper();
		FileOutputStream fos= new FileOutputStream("H:/WorkBook.xls");
		HSSFSheet sheet1 = wb.createSheet("MemoryLog");
		
		 sheet1 = wb.getSheetAt(0);
		 sheet1.autoSizeColumn(1);
		 sheet1.autoSizeColumn(2);
		 sheet1.autoSizeColumn(3);
		 sheet1.autoSizeColumn(4);
		 sheet1.autoSizeColumn(5);
		 sheet1.autoSizeColumn(6);
		 

		HSSFRow row = sheet1.createRow((short)1);
		HSSFCell cell = row.createCell(1);
		cell.setCellValue(createHelper.createRicAhTextString("Date"));
		HSSFCell cell1 = row.createCell(2);
		cell1.setCellValue(createHelper.createRichTextString("Time"));
		HSSFCell cell2 = row.createCell(3);
		cell2.setCellValue(createHelper.createRichTextString("Free Memory"));
		HSSFCell cell3 = row.createCell(4);
		
		cell3.setCellValue(createHelper.createRichTextString("Total Memory"));
		HSSFCell cell4 = row.createCell(5);
		cell4.setCellValue(createHelper.createRichTextString("Max Memory"));
		HSSFCell cell5 = row.createCell(6);
		cell5.setCellValue(createHelper.createRichTextString("No Of Processors"));

//code to dinamically increase the value of row no. means value of k,each time class loads or program runs
		for( int k=2;k<x;k++)
		   {
			HSSFRow row1 = sheet1.createRow((short)k); 
			HSSFCell cell11 = row1.createCell(1);
			cell11.setCellValue(createHelper.createRichTextString(d));
			HSSFCell cell12 = row1.createCell(2);
			cell12.setCellValue(createHelper.createRichTextString( h + ":" 
					+ m + ":" + s + " " + am_pm));
			HSSFCell cell13 = row1.createCell(3);
			cell13.setCellValue(createHelper.createRichTextString(freemem));
			HSSFCell cell14 = row1.createCell(4);
			
			cell14.setCellValue(createHelper.createRichTextString(totmem));
			HSSFCell cell15 = row1.createCell(5);
			cell15.setCellValue(createHelper.createRichTextString(maxmem));
			HSSFCell cell16 = row1.createCell(6);
			cell16.setCellValue(createHelper.createRichTextString(avpros));
		   }

		x++;
//
				
	    wb.write(fos);
	    fos.close();	 
	 
		}
		catch(Exception ex)
		{
		System.out.println("Exception occurred "+ex);	
		}
		   
	
	}
	}

Here I have hardcoded the value of k i.e. row no. but I am not getting how to move the cursor to new line of Excel sheet at each run of program means data should be inserted in a new row on each run of program such that value of k should be increased to one more on each run of program.

>

[Ankit Tripathi]

Anybody noticed this post?

[Aby Kuriakose]

Write a condition for checking the empty row before the for loop begins and assign the row value to intialize "k" in for loop.