Excel Printing thru JAVA

Excel Printing thru JAVA

I would like to post the code which i have used to print the data's in Excel here..

Requirement :

HR dept would like to generate the XL about who are all taken the leave for the given period ( HR will enter the Month and Year. This should be the file name )

I have used the POI API to print the XL.

Here the code..

HTTPRequest comes from ReportGenerationInput.jsp to this Servlet. Request has two arguments one is month( "mid") and another one is year ( "yid" ). As i already said these two values are feed by HR/User.

package org.val.system.la.action;

import java.io.*;import java.util.ArrayList;
import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.*;import org.apache.struts.util.MessageResources;import org.apache.struts.util.PropertyMessageResources;import org.val.system.la.business.LASException;import org.val.system.la.business.service.form.LeaveVO;import org.val.system.la.controller.LASControllerFactory;

public class ExcelGeneratorServlet extends HttpServlet {

private MessageResources las = PropertyMessageResources.getMessageResources( "LAS" );

public void doGet( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException {
try{

String month = request.getParameter( "mid" );
String year = request.getParameter( "yid" );
String fileName = "LeaveReport_" + month + "_" + year + ".xls";

/* Here we are setting that the response should be open as attachment. The attachment file name is for example "LeaveReport_03_2007.xls" */

response.reset();
response.setContentType( "APPLICATION/DOWNLOAD" );
response.setHeader( "Content-disposition", "attachment; filename=" + fileName );


/* Here we are opening a new workbook of POI. This work book consist of only one work sheet and named as "Employee Leave Report" */

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet( "Excel" );
wb.setSheetName( 0, "Employee Leave Report" );

/* Here i am getting the collections of leave details that employees had taken during the given period */

ArrayList leaveList = ( ArrayList ) LASControllerFactory.getInstance().getController().getLeaveTransactionByMonth( month, year );

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat( HSSFDataFormat.getBuiltinFormat( "dd/MM/yyyy" ) );

int colSize = leaveList.size();
if ( colSize != 0 ){

/* Here i am printing the Columns name in the first Row in Excel. i.e. the Row named as A in XL */

short zerothRow = 0;
HSSFRow hssfRow = s.createRow( 0 );

HSSFCell cell = hssfRow.createCell( zerothRow );
cell.setCellValue( "Leave Id" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( "Employee Name" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( "Reporting Authority" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( "From Date" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( "Half Day" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( "Leave Status" );

cell = hssfRow.createCell( ++zerothRow );
cell.setCellValue( " Reason" );

/* After printing the headers for each column now we have to print the value for each records */

for ( int row = 0; row <>
short cellCount = 0;
hssfRow = s.createRow( row + 1 );
LeaveVO leave = ( LeaveVO ) leaveList.get( row );

cell = hssfRow.createCell( cellCount );
cell.setCellValue( leave.getLeaveId().toString() );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( leave.getEmployeeVO().getFirstName() );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( leave.getReportingTo() );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( leave.getFromDate() );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( leave.getFromDateHalf() );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( las.getMessage(leave.getLeaveStatus()) );

cell = hssfRow.createCell( ++cellCount );
cell.setCellValue( leave.getReason() );
}
}

/* Now writing the content of XL into temp XL file thru FileoutputStream */

FileOutputStream writer = new FileOutputStream( new File( "/GenerateReport.xls" ) );

wb.write( writer );
writer.close();

/* Now reading the content of temp XL and write into response to show the XL in browser as attachment as i already said */

FileInputStream fis = new FileInputStream( "/GenerateReport.xls" );
OutputStream out = response.getOutputStream();

int i;
while ( (i = fis.read()) != -1 ){
out.write( i );
}

/* we have to close the input stream as well as response writer */

fis.close();
out.close();

}

catch ( LASException la ){
la.printStackTrace();
}

}

/* Request can be post or get. */

public void doPost( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException {
doGet( request, response );
}
}

Now you got the XL as attachment in your browser window.

23.Towards a more American Conversation

Towards a more American Conversation

U don't open conversation (on telephone) with a “Hello” but with a “Hi”

The telephone is never “engaged”, it's always “busy”.

U don't “disconnect” a phone, U simply “hang-up”.

U never “mess-up” things, U only “screw them up”.

U never have a “residence” tel. no., U have a “home” no.

U don't stop at the “signals”, but halt at the “lights”.

U don't “accelerate”, U “step on the gas”.

Your tire never “punctures”, U may have a “flat”.

The trains have “coaches” or “bogies” no more but “carriages” or “boxes”.

There R no “petrol bunks or pumps”, but “gas stations”.

“I don't know nothing”, 2 negatives don't make a positive here.

U no longer meet a “wonderful” person, U meet a “cool” guy

U don't pull the “switch down ” to light a bulb, rather “flick it up”.

U don't “turn on the heat”, U “turn on the juice”.

There's no “Business Area” only “business districts”, and no “districts” but “counties”.

No one stays “a stone's throw away”, might “a few blocks away”.

There's no “Town Side”, it's “Down Town”.

In hotel U no longer ask for “bill” and pay by “cheque”, rather ask for “check” and pay with (Dollar) “bills”.

There R no “soft drinks”, only “sodas”.

Life's no longer “miserable” it “stinks”.

U don't have a “great” time, U have a “ball”.

U don't “sweat it out”, U “work U'r butt off”.

Never “post” a letter, always “mail” it and “glue” the stamps, don't “stick” them.

U no longer live in “flats” or “blocks”, find an “apartment”.

U don't stand in a “queue”, you are in a “line”.

U no longer “like” something, U “appreciate” it.

“#” is not “hash”, it's “pound”.

U R not “deaf”, U have “impaired hearing”.

U R not “lunatic”, U are just “mentally challenged”.

U R not “disgusting” U R “sick”.

U can't get “surprised” U get “zapped”.

U don't “schedule” a meeting, U “skejule” it.

U never “joke”, U just “kid”.

U never “increase” the pressure, U always “crank” it up.

U never ask for a pencil “rubber” U ask for an “eraser”. A “rubber” is a “condom”

U don't try to find a “lift”, U find an “elevator”.

U no more ask for a “route” but for a “RAUT”

U don't ask somebody “How r u ?”, U say “What's up dude?”

U never go to see a “game or a match”, U go to watch a “game”.

U never go to a “theatre” (theatre is a place for dramatic presentations), U go to the “movies”.

If U see “World” champions (or Series), read “USA” champions (or Series).

There's no “zero” but “o”, no “Z” but “zee”.

There's no “FULL STOP” after a statement, there's a “PERIOD”.

If someone gets “angry” at U, U get “flamed”.

You don't say “How do you do”, you say “How you doin”

U don't call UR boss “sir”, U call him by his “first name”.

In short, U don't speak “English”, U speak “American”.

22.How To Send Money ?

How To Send Money ?

Here are a few ways to send money to India:

Personal Check (drawn on a US Bank). A lot of letters wrote tome that though there is a delay involved (from deposit to encashability), Indian Banks do NOT charge a service fee.

SBI Chicago has a provision wherein they charge you $5.00 as commission and you can get a draft made IN INDIAN RUPEES right here in the US and sent to India. Takes 5-6 days. Their number is 1-800-334-6342.

Cashier's Check, Certified Check, or Money Order from a Local Bank. May involve service charge and delay in encashability status (in Rupees).

Bank Draft. Usually, a $10 service fee (in a US Bank) is involved.

Cable Transfer. Speediest (3-5 working days). However, as I have noted from my local bank, $25 service charge is normally involved.

American Express Money Orders (I HAVE USED THIS METHOD). You can get these from many stores, including 7-11s, for a service charge of 50 to 99 cents (depending where you are in the US). Most banks in India will pay U Rupees in minutes. Also, if it gets lost, full money (-$5) is absolutely recoverable. Extremely safe, inexpensive, and fast method.

Citibank Rupee Drafts. Service charge is $5. 1-800-CITINRI. Immediately encashable in India.
Open an NRI a/c with Citibank (Bombay, Cal, etc.). Leave the checkbook with your relatives. Arrange so that your folks in India could write checks and withdraw money. Takes about a week. Also, if you send a personal ($) check to Citibank at NY city, they transfer the money to your Indian a/c by telex (no charge), wherein Rupees are credited (current rate).

Cable Transfer through SBI Chicago ($16 charge). Takes about 3 days.

NRI a/c with SBI NY, or BOI San Francisco. You can have a predesignated beneficiary in India who could draw in Indian Rupees in India while U pump $ here in the USA.

Morgan's Trust/Hanover's Trust Cashiers Check/Demand Draft. You can get these in most US banks.

Chase Manhattan Drafts from Bank of Baroda. Fast. Service charge present.

SBI San Francisco Bank Draft. Service Charge $7. #(415) 956-6326.

Credit Card. Get a VISA card. Get an extra card for your relative in India. They can take cash advance and you can cover the charges from here in the USA.

PS: Personal Checks are a Debatable Issue. Some say Indian Banks charge a fee while they mention the opposite. However, DELAY is involved in all cases.

The problem is where is the Money???

21.AAA Membership

AAA Membership

We wanted to mention this specially 'cause its very helpful for a person who is new to U.S. AAA read as 'triple A' stands for "American Automobile Association" is an auto club, which provides you with all kinds of tour, and travel information. It provides you with all the maps and travel guides, which you will need. It also has services like towing, opening your car door lock in case you get locked out, emergency help and so on. Being a member of this club also gets you concession for motel booking and rental cars.

There is a yearly fee for all these services, which they provide, and if you believe us, "It's more than worth it".

So we think its a good idea to be a member of this club. It will help you in traveling, which of course you will do once you get here.

20.Income Tax In US

Income Tax In US

Income Tax return filing is very simple in US as compared to India. Here Tax forms are very simple and easy to fill up. One need not know all about Taxation laws, but still can very easily file tax return. Here in US there are two types of taxes. One is the FEDRAL Tax, which is something like our Income tax, and the other is the STATE Tax. Last day of filing the income tax return is 15th of April. Accounting year is considered from 1st of January till 31st of December. US has a Tax treaty with India. Under this Tax treaty one can file his tax return either in India or in US as per the convenience. But you need to show your income earned in either of the country.

Suppose you come to US in the middle of the year. You must be definitely having some income in India before you came to US in that particular financial year. Also some part of salary is paid to you even when you are in US. This income is taxable and hence you pay tax on that income. Generally you pay tax on this income in India as in most of the cases income tax is deducted at source. So one pays tax on the income in India. So now you are liable only for the tax on the income that you earn in US.

Here there are different types of forms for different category of people. For those who are US residents and who have status of Resident Aliens, they have to file the what is called 1040EZ form (form 1040 easy). Any person who is in the US for more than 183 days continuously, gains status of Resident Alien. Any person who is in US for less than 183 days in that financial year is considered as Non Resident Alien.

Non Resident Alien will not have some Tax benefits which any US citizen or Resident Alien will have. Here in particular, Non-resident Alien will not get Standard deduction on their income. Also they have to pay Tax as per the Flat rate. So basically when you file your Tax return as NR you land up paying more taxes. But you have to file 1040NR when you are having status as Non resident Alien.

For filing Tax return form one can get help from the government body, which is, know as IRS (Internal Revenue Services). This is same as Income Tax department in India. Also one has to pay tax to the state government. Each state has a different tax law for the State Tax. Booklet of information on this is available on request. Tax forms are available freely at the public places like Post office, Public library where one can get the form that he/she requires.

Also, government gives special services for the people who needs the guidance. One can get this information even on phone by calling them. One can even go in person to understand the form.
Apart from these facilities provided by the government one can even go to private Tax consultants. They charge somewhere between $30 to $60. They take care of every detail and one need not bother about anything. Along with the Tax you are supposed to send one form called "W2" form. The employer provides this form to all the employees. The form contains all the information based on the salary of the person. Also all the taxes paid by the employer to the State government as well as Federal government. Details about the social security taxes and the medical taxes paid by the employer are also included in this form.

There are three copies of this form. One copy is to be sent with the State Tax form, one with the Federal Tax form and one is for the employee's personal record. Before sending the W2 form one should check the validity of the information in it. Most important is the correct social security number. If there is any change in any of the information one should inform the employer ASAP. Employer is supposed to send the corrected information to IRS and also a corrected copy to the employee, which is called W2C form.

19.Doctors in U.S.

Doctors in U.S

God forbid, but if something happens to you, here is what you should know:

- The Medical Benefit Card, which is sent by the Medical Insurance Company, is an important card and should be acquired at the earliest. Fill up the Medical forms sent by DCI and return those immediately to DCI to ensure that you are registered with the Insurance Company after you land in the U.S.

- This card entitles you to coverage under certain medical categories only.

- There is a $100 deductible. Which means if you land into medical problems, you have to pay the first 100 dollars from your pocket. Thereafter the Insurance company takes over the payment depending on the coverage plan etc.

- There are two acceptable ways of payment for the Doctor's services which depends on the Doctor. The Doctor's receptionist is the best person to ask payment-related questions. Some Doctors ask you to pay them (by cash, check, credit card etc.) immediately after the service. The receptionist will then give you a formal bill, which you must send (by postal mail) to the Medical Insurance Company along with the claim form supplied by DCI (which comes in with the initial package soon after you land in the U.S.) The Medical Ins. Co. will then send you a check after verifying your claim. Other Doctors will note down the details of your Medical Insurance Company from your card. They will then send the bill to the Insurance Company and receive the payment directly. You need not pay the Doctor. But in both the above modes, remember that there are certain charges which the Insurance Company will not pay and which they don't cover, such as the first $100. So find out the details from the Insurance Co. about your respective coverage/medical problem etc.

- For certain kinds of medical problems, where you have to visit the Doctor repeatedly, only a fixed number of visits per year are paid by the Insurance Company and not all.

- How to decide on the Doctor to visit? Ask your colleagues or other Indian friends. Otherwise check up the yellow pages (phone directory). Look under the "Physicians" heading. Telephone the Doctor and fix up an appointment. Only in cases of emergency, do the Doctors see you immediately, otherwise it is always by appointment.

- You can claim the expenses for any Dr. prescribed medicine that you have to buy from the Drug (medical) Store.

NOTE

- Pain in the lower back and strain between the shoulder blades and a stiff neck are very common due to the kind of spring mattresses and soft foam pillows available in the U.S. and one just needs to get used to it.

- Beware of Dental problems since they are usually not fully covered under the Insurance Scheme.

19.Renting a Car

Renting a Car

Car is a necessity in most of the places here in U.S. Either you buy a car or you can rent a car. You can pay either a monthly rate or daily rent. Though it’s expensive to rent a car for a month or so, it might be required if you are going to stay at that place for a small period of time. Here is some information you will need OR should know when you want to rent a car.

- The driver/co-drivers need to be at least 25 years old.- Credit Card is required to rent a car. Else a deposit of about $300 etc...Most companies do not allow you to rent a car if you do not have a credit card.- You need a valid driver's license to drive a rental car.

- Well-known car renting companies are - Hertz, Avis, Alamo, Budget, Dollar, and Enterprise etc.

- Car Insurance has to be bought for the Rental car. It is a separate charge or is included in the total per day renting cost. Some credit card companies pay for the insurance if you use their card to book a car e.g. American Express Credit Card, in such a case you need not buy insurance from the car rental company.

- You have to register the other drivers too as co-drivers of the rental car while booking the car. They should have valid driver's licenses too. Nobody else should be allowed to drive the car other than those registered!- Unlimited mileage or fixed mileage is also a criteria, which decides the hiring charges of a rental car.

- You can make the booking over the phone. But you have to go personally to pick-up the car from the renting agency.

- Generally the gas (fuel) tank is full and you are supposed to return it with a full tank.- Some rental agencies do not allow you to rent their car for travel outside the state.

- There are various types of cars that can be booked. Generally depending on the seating capacity, such as small, medium, compact, large, full-size, premium, mini-van, van etc. One may even choose the make of the car if available. The rental agencies maintain the cars in good shape and generally have new cars.

- The renting charges vary depending on the season, weekend/weekday, for how many days you want the car, how much in advance you are booking, availability, type of car etc.

17.Buying a Car

Buying a Car

If you are going to stay at a particular place for quite some time say more than three months, then its better to buy a car rather than rent one. Buying a car involves quite some work. Its better to be cautious while buying a car, 'cause owning a car brings you freedom and you own a vehicle but you need to maintain the car and take care of any problems.

The main concern while buying a car is its maintenance cost should not be high, otherwise you will loose a lot. So here is what experience tells us.

Buying a car in America is very easy, there are lots of used cars for sale and there is never a shortage. It is just the question of deciding the budget and type of car to be bought.
Most people prefer Japanese cars over American makes simply because of the easy re-salability. The most popular Japanese car makes are Honda and Toyota.

Types of models? There are two categories of cars, 2-door, 4-door. The 2-d are cheaper. Then comes the type of transmission (gear), automatic or stick shift. Most people buy automatic transmission. Such cars are very easy to drive. (Almost all-Indian cars are of the stick-shift variety).

Where to look? The local newspapers carry a lot of car sale advertisement everyday. You should see the paper and decide on the ones you are interested in. Next call up the owner at the tel. no. given, as early in the day as possible and fix up an appointment to visit and see the car. Test-drive it, if you like it and the price is ok, tell the owner that you'd like to get it checked up by a mechanic. This is the normal practice. There is nothing to feel shy about asking the car to be checked up. This will save you a lot of money later after purchasing the car from unforeseen problems that some used cars land up into.

Look up the yellow pages in the local tel. directory to get the list of car mechanics or just ask colleagues to recommend one. If that doesn't work out, ask the local AAA (American Automobile Association) office to recommend someone to you. For a fee of about $25-30 the mechanic will check up the car and tell you the problems or possible problems if any.
Some people do it for free. It is good to tell the auto mechanic to check up the following things with additional care in addition to their regular: -

1) Brake cylinder, drum and worn out pads
2) Transmission. (This is the costliest part in an automatic car)
3) If it is a 4 speed never buy it. 5 speed is ok.
4) Check for rust in bottom, radiators, muffler and tailpipe.
5) Check for vibrations when you brake from 40mph, without downshifting. If there are vibrations, either break pads or steering pinion is in bad shape. Get 4 people to sit in the car and you can make out if the suspensions are OK, when you make a sharp turn.
6) Most important, check for oil leaks, and whether the car gives you power speed/pick-up when you try to accelerate. You can feel if it's OK.
7) Last, check for emission whether the smoke is black (thick), which means oil, is also burned with fuel => the car is beyond its life. The piston rings might be leaving a gap in the cylinders because of wear. In that case, don't buy the car, because you will have to change the cylinder, piston and rings which will come to double the cost.
8) Ask the owner to give the records of the maintenance he has done, like new tires etc. Also, make sure you don't go beyond 3rd hand purchase. There must be something seriously wrong with the car if people have changed owners drastically.
9) Air filter, valve etc. OK?

Ask the owner to discount the price of the car by the amount required for the repairs suggested by the mechanic. Or else ask him to get it repaired for you. The rates of the used cars have some standard values, which are generally not exceeded. These values can be checked up in something called "Blue Book" which is a text available in the public library or with car dealers. So car models classified as per Year of make have their value listed in the Blue B OK. There is a cost deduction to be applied for the mileage that the car has been driven for, which is mentioned in most Blue Books. That gives a fair idea about the standard cost of any used car. Moreover there is ample scope for price negotiation. Generally private owners who sell through ads can give better discounts than used car dealers. Generally car dealers stick to the Blue Book Price but usually ask more.

Mileage is also a consideration while purchasing a used car. Lesser the better. The ideal average is 10000 miles per year. So a car manufactured in 1987 may have a driven mileage of about 70000 miles in 1994 and not more than 80000. These are approx. figures.

After buying a car you need to get it insured. You cannot drive a car in the U.S. without proper Insurance. The car has to be insured against collisions etc. There are various vehicle Insurance companies having various deals. Look up the yellow pages and call up some of them and ask for their rates. Then decide on one. The Insurance coverage period and the type of Insurance chosen and your age (more for less than 25 yr.) decide the amount to be paid. The "comprehensive", "collision" are terms used by the Insurance companies to describe the type of insurance coverage. Get everything explained properly.

There is sometimes windscreen coverage too, for a nominal additional amount per month, this insures your windshield against damage (a windscreen replacement may cost about $200.00 approx. otherwise). You have to get the emission testing done on your car before it can be registered. In case the previous owner has got it done and the test is valid still, then there is no need to do it again for the specified period. The valid test papers are required for Registration.
The local vehicle registration office will issue a temporary registration for 45 days and meanwhile process and prepare the proper number plates. The documents required to get a registration are - Car ownership (title) papers, Emission Test papers, and Car Insurance papers.

After proper registration is done you can drive your car. However you should have a Driver's License. Actually each state in the U.S. has different laws. Most states allow you to drive with an I.D.P. (International Driving Permit) for a year or so. But others require that you get a local Driver's License. The driver's license can be obtained from the local DMV office after a written and driving test. Refer to the section on obtaining a driver's license for details.