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.