Tuesday, December 21, 2010

How to Read Data from DB and write into Excel sheet using Apache POI

Read Data from DB and write into Excel sheet using Apache POI

...
method to read data from the "resource_rbac_status" table and write into the excel sheet using apache POI HSSF API
...

public static HSSFWorkbook generateExcel(){
Connection dbconn = null;
HSSFWorkbook wb = null;
try{
dbconn = RmsDBUtil.getDBConnection(); /// get the connection
Statement stmt= dbconn.createStatement();;
ResultSet rs= stmt.executeQuery ("SELECT * FROM resource_rbac_status");
ResultSetMetaData rsmd = rs.getMetaData ();

int columnCount = rsmd.getColumnCount ();

wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Excel Sheet");

HSSFRow rowhead = sheet.createRow((short) 0);

for (int j = 1; j <= columnCount; ++j) {
rowhead.createCell((short) ((short) j-1)).setCellValue(rsmd.getColumnLabel(j));
}

int index = 1;
while (rs.next()) {

HSSFRow row = sheet.createRow((short) index);
for (int j = 1; j <= columnCount; ++j){

String a=rsmd.getColumnTypeName(j);

if (a.equals("INTEGER")){

row.createCell((short) ((short) j-1)).setCellValue(rs.getInt(j));
} else {

row.createCell((short) ((short) j-1)).setCellValue(rs.getString(j));
}

}

index++;
}

System.out.println("Data is saved in excel file.Please check the excel file ");

//close the connection and statement

}
catch (Exception e) {
System.out.println(e);
}
finally{
// close the connection
}

return wb;

}
...
....
....

How to Read an Excel sheet using Apache POI HSSF

How to read an excel sheet using POI API(HSSF)

....
method to read the excel sheet and prepares the list(of Holiday DTOs) using apache POI API
.....

public static List parseHolidayExcel(String xlsPath) {

List holidayList = new ArrayList();
FileInputStream fis = null;
POIFSFileSystem fileSystem = null;

try {
fis = new FileInputStream(xlsPath);
fileSystem = new POIFSFileSystem(fis);

HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
HolidayListDTO dto = null;
while (rows.hasNext()) {
HSSFRow row = rows.next();

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

// System.out.println("Row no:"+row.getRowNum());
dto = new HolidayListDTO();
dto.setSiteStatus(row.getCell((short) 0).getStringCellValue());
dto.setCountry(row.getCell((short) 1).getStringCellValue());
dto.setLocation(row.getCell((short) 2).getStringCellValue());
dto.setHoliday_date((Date) (row.getCell((short) 3)
.getDateCellValue()));
dto.setDay(row.getCell((short) 4).getStringCellValue());
dto.setOccasion(row.getCell((short) 5).getStringCellValue());

holidayList.add(dto);

}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}

return holidayList;
}


To read an Excel sheet using POI API(HSSF) : Data Holder to hold details about the holidays....

import java.io.Serializable;
import java.util.Date;

public class HolidayListDTO implements Serializable{

public String siteStatus;
public String country;
public String location;
public Date holiday_date;
public String day;
public String occasion;
public String getSiteStatus() {
return siteStatus;
}
public void setSiteStatus(String siteStatus) {
this.siteStatus = siteStatus;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public Date getHoliday_date() {
return holiday_date;
}
public void setHoliday_date(Date holidayDate) {
holiday_date = holidayDate;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getOccasion() {
return occasion;
}
public void setOccasion(String occasion) {
this.occasion = occasion;
}


}

Sample Excel file(sheet) to be read by the above code using POI HSSF API....

SiteStatus country location date day occasion
offsite india chennai 10/12/2010 Monday Ramzan
onsite us new jersy 11/12/2010 Tuesday Rangoli
offsite india delhi 9/11/2010 Wednesday deepavali

Thursday, December 2, 2010

Find out Number of Holidays in a month or date range using Java

To findout the number of Holidays in a given date range or in a month

Calendar format should be year, month-1, day

Here is java class to findout the number of holidays of a given data range(two dates)

//HolidayFinder class

package com.hbd.rms.util;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
* @author velmurugan pousel
* created on Sep 2, 2010 for RMS
* WorkingDaysFinder.java on Sep 2, 2010
*/
public class HolidaysFinder {

/**
* holidaylist holds the list of national/local holidays for the date range
* the format of the calender is (year,month-1,date)
*/
static List holidayList = new ArrayList();

// holiday list
static {
//holidayList.add(new GregorianCalendar(2010, 8, 18));// holiday on the given month
holidayList.add(new GregorianCalendar(2010, 11, 24));// holiday on the given month
}

/**
* check the given date is on holiday list
* @param calendardate
* @return
*/
public static boolean isHoliday(Calendar cal) {
return holidayList.contains(cal);
}

/**
* method to find out number of holidays between the given date range
* @param first
* @param second
* @return
*/
public static int countNumberOfSaturdaysAndSundays(GregorianCalendar first,
GregorianCalendar second) {

int count = 0;
GregorianCalendar currentcalendarday = first;
GregorianCalendar lastcalendarday = second;

while (currentcalendarday.getTime().getTime() < lastcalendarday.getTime().getTime()) {
if (isOnSaturday(currentcalendarday)) {
count++;
} else if (isOnSunday(currentcalendarday)) {
count++;
} else if (isHoliday(currentcalendarday)) {
count++;
}
currentcalendarday.add(Calendar.DATE, 1);
}

if (isSameDay(currentcalendarday, lastcalendarday)) {

if (isOnSaturday(currentcalendarday)) {
count++;
} else if (isOnSunday(currentcalendarday)) {
count++;
} else if (isHoliday(currentcalendarday)) {
count++;
}
}
return count;
}


/**
* check the given date is saturday
* @param calendardate
* @return
*/
public static boolean isOnSaturday(Calendar calendardate) {

if (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is a SATURDAY.");
} else {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is not a SATURDAY.");
}

return (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY);
}


/**
* check the given date is sunday
* @param calendardate
* @return
*/
public static boolean isOnSunday(Calendar calendardate) {

if (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is a SUNDAY.");
} else {
System.out.println("Debug: "
+ String.valueOf(calendardate.get(Calendar.DATE)) + "-"
+ String.valueOf(calendardate.get(Calendar.MONTH) + 1)
+ "-" + String.valueOf(calendardate.get(Calendar.YEAR))
+ " is not a SUNDAY.");
}

return (calendardate.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY);
}


public static boolean isSameDay(Calendar cal1, Calendar cal2) {
if (cal1 == null || cal2 == null) {
throw new IllegalArgumentException("The date must not be null");
}
return (cal1.get(Calendar.ERA) == cal2.get(Calendar.ERA) &&
cal1.get(Calendar.YEAR) == cal2.get(Calendar.YEAR) &&
cal1.get(Calendar.DAY_OF_YEAR) == cal2.get(Calendar.DAY_OF_YEAR));
}

public static void main(String args[]) {
// to findout the number of holidays between these two dates
GregorianCalendar firstdate = new GregorianCalendar();
GregorianCalendar seconddate = new GregorianCalendar(2010, 11, 30);
System.out.println(" year..."+ seconddate.get(Calendar.YEAR));
System.out.println("holiday conunt = " + countNumberOfSaturdaysAndSundays(firstdate, seconddate));
}

}

Here is the code to test with two dates(date1,date2)...

To Find out the holidays between these dates

convert the dates into calendar

GregorianCalendar firstdate = new GregorianCalendar();
GregorianCalendar seconddate = new GregorianCalendar();
firstdate.settime(date1);
seconddate .settime(date2);
// call the method

countNumberOfSaturdaysAndSundays(firstdate, seconddate));
...
...