Skip to main content

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) {
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()) {
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 =;
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());
} 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);
FileInputStream fileInputStream = new FileInputStream(fl);
OutputStream responseOutputStream = response.getOutputStream();
int bytes;
while ((bytes = != -1) {
} catch (FileNotFoundException e) {
logger.error(" File Not Found Exception in exportDetailSuccessReport " + e.getMessage());
} catch (IOException e) {
logger.error(" IO Exception in exportDetailSuccessReport " + e.getMessage());


Popular posts from this blog

Conversion from Clob To String in java and Vice versa

/***reading Clob data from oracle table and setting to Pojo**/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;


public class MessageDao {
private static String url = "jdbc:oracle:thin:@";
private static String username = "ABC123";
private static String password = "ABC123";

public  List readClobToBO() throws Exception {

Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement stmt = conn
ResultSet resultSet = stmt.executeQuery();
List msgList= new ArrayList();

while ( {

StagedMessage message = new StagedMessage();


Portable Java 8/ JDK8 setup without admin rights

Some time we got machine to work where we don't have access rights to install anything or if you do not want to install. You can get latest jdk running by this trick. Just follow step step & do comment if you like it.

Step by Step details to get running java from .exe setup without any admin rights. Get portable 7 zip from portableapps.comdownload java 8 from oracle 7 zip & extract jdk-8u102-windows-x64.exeyou will see below folders                .data                .pdata                .rdata                .reloc                .rsrc                .text                CERTIFICATE

          5.Go inside E:\jdk-8u102-windows-x64\.rsrc\1033\JAVA_CAB10you will find 111 file.

         6.extract it it some folder where you can find again extract that to some folder say Java & copy it to very first folder of setup along with .data,.rsrc etc.
         7.Go to folder where you have co…

GOF Design pattern with Java & Real World Examples

What are design patterns? 

So basically these are just collection of best practices to solve recurring problem by different kind of class arrangement. These arrangement could be based on creation of object or structural basis or on basis of required behavior.

Creational Design Pattern
Prototype Designpattern we will like to use this designpattern whenever creation of initial object & setting required default values is costly. So we just create 1 object & later on whenever more objects required we can provide initial object by just cloning of given object.Java Example is java.lang.Object#clone()
Builder DesignPattern When we jave object structure are complex and complicated we would go for this. we will be separating object creation from internal representation. With help of some simple interface we would create complicated Object.
Singleton When we need one instance per JVM. Example : Java Run time environment Java.lang.System.  java.lang.StringBuilder, Spring Beans are singleton …