Save jsp/list/hashmap/table data to Excel with help of apache poi

                                                        Sample Method to writing Excel with apache POI.


private void exportDetailSuccessReport(HttpServletRequest request, HttpServletResponse response,
List<HashMap<String, MasterListSuccessStoresDTO>> successStoreListOfList, ArrayList<FailedStoresDTO> lstScreen) {
response.setContentType("application/vnd.ms-excel");
HSSFWorkbook wb = new HSSFWorkbook();
ConfigurationBean configBean = (ConfigurationBean) BeanLocator.getServiceBean(ConfigurationBean.IMPORT_CONFIGURATION_BEAN);
String tempPath = configBean.getImportFilesPath();
File folder = new File(tempPath + File.separator + "Shoppers_Dashboard");
if (!folder.exists()) {
folder.mkdir();
}
File fl = new File(folder + File.separator + "Detail_report.xls");
List<HashMap<String, MasterListSuccessStoresDTO>> lstHmapOfMasterDto = successStoreListOfList;
if ((null != lstHmapOfMasterDto) && lstHmapOfMasterDto.size() > 0) {
HSSFSheet sheet = wb.createSheet("Success Data Export");
int sizeOfRecords = 0;
for (int i = 0; i < lstHmapOfMasterDto.size(); i++) {
HashMap<String, MasterListSuccessStoresDTO> hashMap = lstHmapOfMasterDto.get(i);
if ((null != hashMap) && hashMap.size() > 0) {
Collection<MasterListSuccessStoresDTO> mlist = (Collection<MasterListSuccessStoresDTO>) hashMap.values();
try {
if (mlist != null && mlist.size() > 0) {
HSSFRow rowhead = sheet.createRow((short) 0 + sizeOfRecords);
rowhead.createCell((short) 0).setCellValue("BUSINESS");
rowhead.createCell((short) 1).setCellValue("STORE ID");
rowhead.createCell((short) 2).setCellValue("START TIME");
rowhead.createCell((short) 3).setCellValue("END TIME");
rowhead.createCell((short) 4).setCellValue("TOTAL TIME");
rowhead.createCell((short) 5).setCellValue("START TIME");
rowhead.createCell((short) 6).setCellValue("END TIME");
rowhead.createCell((short) 7).setCellValue("TOTAL TIME");
rowhead.createCell((short) 8).setCellValue("START TIME");
rowhead.createCell((short) 9).setCellValue("END TIME");
rowhead.createCell((short) 10).setCellValue("TOTAL TIME");
Iterator<MasterListSuccessStoresDTO> itr = mlist.iterator();
int j = 0;
while (itr.hasNext()) {
MasterListSuccessStoresDTO successStrDto = itr.next();
HSSFRow row = sheet.createRow((short) j + 1 + sizeOfRecords);
row.createCell((short) 0).setCellValue(successStrDto.getBusinessUnitPull());
row.createCell((short) 1).setCellValue(successStrDto.getStoreIdPull());
row.createCell((short) 2).setCellValue(successStrDto.getStartTimePull());
row.createCell((short) 3).setCellValue(successStrDto.getEndTimePull());
row.createCell((short) 4).setCellValue(successStrDto.getTotalTimePull());
row.createCell((short) 5).setCellValue(successStrDto.getStartTimeProcess());
row.createCell((short) 6).setCellValue(successStrDto.getEndTimeProcess());
row.createCell((short) 7).setCellValue(successStrDto.getTotalTimeProcess());
row.createCell((short) 8).setCellValue(successStrDto.getStartTimeMMS());
row.createCell((short) 9).setCellValue(successStrDto.getEndTimeMMS());
row.createCell((short) 10).setCellValue(successStrDto.getTotalTimeMMS());
j++;
}
}
} catch (Exception ex) {
logger.error("Error in writing Excel for dashboard report" + ex.getMessage());
}
}
sizeOfRecords = sizeOfRecords + hashMap.size() + 4;
}
}
if (lstScreen != null && lstScreen.size() > 0) {
HSSFSheet sheet = wb.createSheet("Failed Data Export");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell((short) 0).setCellValue("STORE ID");
rowhead.createCell((short) 1).setCellValue("MMS STAGING");
rowhead.createCell((short) 2).setCellValue("PULL SUCCESS");
rowhead.createCell((short) 3).setCellValue("PULL FAIL");
rowhead.createCell((short) 4).setCellValue("PROCESS SUCCESS");
rowhead.createCell((short) 5).setCellValue("PROCESS FAIL");
for (int k = 0; k < lstScreen.size(); k++) {
FailedStoresDTO failedStrDto = lstScreen.get(k);
HSSFRow row = sheet.createRow((short) k + 1);
row.createCell((short) 0).setCellValue(failedStrDto.getStoreId());
row.createCell((short) 1).setCellValue(failedStrDto.getMmsStaging());
row.createCell((short) 2).setCellValue(failedStrDto.getPullPassed());
row.createCell((short) 3).setCellValue(failedStrDto.getPullFailed());
row.createCell((short) 4).setCellValue(failedStrDto.getPrcssdPassed());
row.createCell((short) 5).setCellValue(failedStrDto.getPrcssdFailed());
}
}
try {
FileOutputStream fileOut = new FileOutputStream(fl);
wb.write(fileOut);
fileOut.close();
FileInputStream fileInputStream = new FileInputStream(fl);
OutputStream responseOutputStream = response.getOutputStream();
int bytes;
while ((bytes = fileInputStream.read()) != -1) {
responseOutputStream.write(bytes);
}
fl.deleteOnExit();
} catch (FileNotFoundException e) {
logger.error(" File Not Found Exception in exportDetailSuccessReport " + e.getMessage());
} catch (IOException e) {
logger.error(" IO Exception in exportDetailSuccessReport " + e.getMessage());
}
}

Comments

Popular posts from this blog

Conversion from Clob To String in java and Vice versa

Portable Java 8/ JDK8 setup without admin rights

Multi Threading , Producer consumer problem in Java