to create excel file using java

Apache POI API has been used for this purpose. This is used for manipulating various microsoft files.

This is a function of a class which generate an excel file after executing query in the database. Executing query in the database is not concerned here. This code insists on the creating file using POI. The function return the file in byte array( i do it as one of my requirement).

public byte[] createExcell(String fileLoc, String query, String dbName) {

Comments:
fileLoc: provide the location where to keep the file after generation.
query: database query on which you want to create excel
dbname: if you work with multiple database in your project

try {
String file_name = fileLoc +"/"+ (new Date()).getTime() + ".xls";
FileOutputStream fileOut = new FileOutputStream(file_name);
File file = new File(file_name);
if (file.exists()) {
file.delete();
}

delete file if a file name generated before with same name.

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Candidate List");
byte[] bytes;

GatewayPersonalInfo gwPersonalInfo = new GatewayPersonalInfo();
List lstPerson;
lstPerson = gwPersonalInfo.getAllPersonalInfo(query, dbName);

Above three lines has been used to import data from databases in list.

HSSFRow rowNew;
HSSFCell cellA, cellB, cellC, cellD, cellE;
rowNew = worksheet.createRow(0);
cellA = rowNew.createCell(0);
cellA.setCellValue("Sl. No");
cellB = rowNew.createCell(1);
cellB.setCellValue("App Serial No");
cellC = rowNew.createCell(2);
cellC.setCellValue("Full name");
cellD = rowNew.createCell(3);
cellD.setCellValue("Contact No.");
cellE = rowNew.createCell(4);
cellE.setCellValue("Email ID");

Just to create header in the file

if (lstPerson != null) {
if (!lstPerson.isEmpty()) {
Iterator itr = lstPerson.iterator();
PersonalInfo person;
int rowno = 1;
while (itr.hasNext()) {

person = (PersonalInfo) itr.next();
rowNew = worksheet.createRow(rowno);
cellA = rowNew.createCell(0);
cellA.setCellValue(rowno);
cellB = rowNew.createCell(1);
cellB.setCellValue(person.getSerialNo());
cellC = rowNew.createCell(2);
cellC.setCellValue(person.getFullName());
cellD = rowNew.createCell(3);
cellD.setCellValue(person.getContactNo());
cellE = rowNew.createCell(4);
cellE.setCellValue(person.getEmail());
System.out.println(rowno + " row added.");
rowno = rowno + 1;
}
}
}
workbook.write(fileOut);

fileOut.flush();
fileOut.close();

FileInputStream fin = new FileInputStream(file);
bytes = new byte[(int) file.length()];
fin.read(bytes);
file.delete();

After taking in the byte array, file is being deleted. But it depends on your requirements. You can keep the file.

return bytes;

} catch (Exception ex) {
System.out.println(ex);
logger.error(ex);
} finally {
}
return null;
}

Kindly provide your suggestion for further modification.