Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 1 of 1

Thread: Java/Excel integration, Reading in Dates

  1. #1
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,600
    Thanks
    24
    Thanked 313 Times in 293 Posts

    Default Java/Excel integration, Reading in Dates

    For those who have attempted to intergrate Java and Excel for projects or work, this may or may not be useful for you.

    Depending on the library you use to read and minipulate the Excel documents, you may or may not have support for reading in dates from Excel. This is because Excel doesn't store the dates as dates, but rather as a counter for the number of days past January, 1, 1900. So, if you have a cell formatted for dates and you read in something like January 2003, you will get 37622. That is not helpful, and you can't just count up the years because you need to take account of leap years. So I wrote a method to figure this out for me (and used a second method I found online to figure out leap years). This was written for my needs, so it doesnt return the day of the month, but it will return the first three letters of the Month, a space, and every digit of the year. If you wanted, getting the day of the month would just be matter of adding to the output, as it's basically computed anyway.

    May not be the most efficient matter, but it gets the job done and I've checked it multiple times.
    public String getDate(int n)
    {
    	String output = "";
    	String[] arr1 = new String[]{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"};
    	String[] arr2 = new String[]{"31","28","31","30","31","30","31","31","30","31","30","31"	};
    	int year = 1900;
    	System.out.println(year);
    	while(n>366)
    	{
    		if(isLeapYear(year))
    		{
    			n-=366;
    			year++;
    		}
    		else
    		{
    			n-=365;
    			year++;
    		}
    		System.out.println(year);
    	}
    	if(!isLeapYear(year) && n>365)
    	{
    		System.out.println(year);
    		n-=365;
    		year++;
    	}
    	int i=0;
    	int tempN=Integer.parseInt(arr2[i+1]);
    	if(isLeapYear(year) && tempN==28)
    		tempN=29;
    	while(n>tempN)
    	{
    		int num = Integer.parseInt(arr2[i]);
    		if(i==1 && isLeapYear(year))
    			num++;
    		n-=num;
    		i++;
    		if(i!=11)
    		{
    			tempN = Integer.parseInt(arr2[i+1]);
    			if(isLeapYear(year) && tempN==28)
    				tempN=29;
    		}
    	}
    	output = arr1[i]+" "+year;
    	return output;
    }
    public boolean isLeapYear(int theYear)
    {
    	// Is theYear Divisible by 4?
    	if (theYear % 4 == 0)
    	{
    		    // Is theYear Divisible by 4 but not 100?
    		    if (theYear % 100 != 0)
    			return true;
    		    // Is theYear Divisible by 4 and 100 and 400?
    		    else if (theYear % 400 == 0)
    			return true;
    		    // It is Divisible by 4 and 100 but not 400!
    		    else
    			return false;
    	}
    	return false;
    }
    Last edited by Json; July 26th, 2010 at 02:41 AM.

  2. The Following 2 Users Say Thank You to aussiemcgr For This Useful Post:

    JavaPF (July 20th, 2010), Json (July 20th, 2010)


Similar Threads

  1. How to Connect to an Excel Spreadsheet using JDBC in Java
    By JavaPF in forum JDBC and Database Tutorials
    Replies: 14
    Last Post: August 27th, 2013, 11:57 AM
  2. UTC Dates
    By PedroCosta in forum Java Theory & Questions
    Replies: 4
    Last Post: April 1st, 2010, 11:39 AM
  3. conversions and excel
    By mkslt4 in forum Java Theory & Questions
    Replies: 2
    Last Post: February 10th, 2010, 12:54 AM
  4. Export to excel
    By ebosysindia in forum File I/O & Other I/O Streams
    Replies: 7
    Last Post: May 14th, 2009, 06:25 AM
  5. [SOLVED] Java program to sort arrays containing dates
    By scottyadam in forum Collections and Generics
    Replies: 1
    Last Post: March 9th, 2009, 06:08 PM