Excel Generator in Java using POI and Reflection

Excel Generator in JAVA using apache POI and Reflection


Code Snippet:


    @SuppressWarnings("unchecked")
    public HSSFWorkbook exportToCSV(Object srcDtos, String[] columnList,
            String[] valueIdList, String title) {
       
        try {
           
            List<Object> dtoList = null;
            HSSFRow reportRow = null;
            int rowNum = 1;
            int colIndex = 0;
            String reportRowValue = "";
           
            if (srcDtos == null){
                return null;
            } else {
                dtoList = (ArrayList<Object>)srcDtos;
            }
           
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(title);
                       
            /**
             * setting up the report header
             */
            HSSFRow reportHeader = sheet.createRow(0);

            for (String column : columnList) {
                reportHeader.createCell(colIndex++).setCellValue(column);
            }

            Method invokeMethod = null;
            Class<?> reqClass = null;
            // Loading methods parameters
            Class[] parameter = new Class[1];
            String getColumn = "";
            try { 
               
                for (Object srcClassObj : dtoList) {

                    /**
                     * creating the report row content
                     */
                    reportRow = sheet.createRow(rowNum++);
                    reportRowValue = "";
                    int rowColIndex = 0;
                   
                    Class dtoClzName =srcClassObj.getClass();
                   
                    reqClass = Class.forName(dtoClzName.getName());
               

                    for (String columnField : valueIdList) {
                         
                        getColumn = "get" + initCapString(columnField);

                        parameter[0] = String.class;

                        invokeMethod = reqClass.getMethod(getColumn);

                        Object tmpObj = invokeMethod.invoke(srcClassObj);

                        reportRowValue = (tmpObj == null ? "" : tmpObj
                                .toString());

                        reportRow.createCell(rowColIndex++).setCellValue(reportRowValue);

                    }

                }

            } catch (ClassNotFoundException e) {
                logger.error("Error during the CSV generation --->" +
                        e.getMessage());
            }
           
            return workbook;

        } catch (Exception e) {
            logger.error("Error during the CSV generation --->"
                    +   e.getMessage());
        }
        return null;
    }



    /**
     * method to init capitalize the given string(first letter as CAPITAL)
     * @param srcString
     * @return
     */
    public static String initCapString(String srcString) {
        String[] arr = srcString.split(" ");
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < arr.length; i++) {
            sb.append(Character.toUpperCase(arr[i].charAt(0)))
                    .append(arr[i].substring(1)).append(" ");
        }
        return sb.toString().trim();
    }



public class VelsErrorMsgDTO implements Serializable {

    private Long errorMsgID;
    private String institutionID;
    private String errorMsgTitle;
    private String errorMsgDescription;
    private String lastUpdtTime;

    public Long getErrorMsgID() {
        return errorMsgID;
    }


    public void setErrorMsgID(Long errorMsgID) {
        this.errorMsgID = errorMsgID;
    }


    public String getInstitutionID() {
        return institutionID;
    }


    public void setInstitutionID(String institutionID) {
        this.institutionID = institutionID;
    }


    public String getErrorMsgTitle() {
        return errorMsgTitle;
    }


    public void setErrorMsgTitle(String errorMsgTitle) {
        this.errorMsgTitle = errorMsgTitle;
    }


    public String getErrorMsgDescription() {
        return errorMsgDescription;
    }


    public void setErrorMsgDescription(String errorMsgDescription) {
        this.errorMsgDescription = errorMsgDescription;
    }


    public String getLastUpdtTime() {
        return lastUpdtTime;
    }


    public void setLastUpdtTime(String lastUpdtTime) {
        this.lastUpdtTime = lastUpdtTime;
    }
   
}


Here the arguements

srcDtos      ------> actual  java DTO objects which carries your data
columnList  ------> Column header list
valueIdList  ------> java property names need to be invoked from DTO object(getter method of the
                                given  java field(example userName , address ..) would be  invoked to get the actual
                                value  that need to be displayed in excel file)
 title ---------------> Report title




    public static void main(String[] args) {
        List<VelsErrorMsgDTO > dtoList = new ArrayList<VelsErrorMsgDTO >();
        VelsErrorMsgDTO dto = new VelsErrorMsgDTO ();
        dto.setErrorMsgID(1L);
        dto.setErrorMsgTitle("Test errror");
        dto.setErrorMsgDescription("Test errror");
        dto.setInstitutionID("101");
        dtoList.add(dto);
       
        dto = new CIErrorMsgDTO();
        dto.setErrorMsgID(2L);
        dto.setErrorMsgTitle("Test errror");
        dto.setErrorMsgDescription("Test errror");
        dto.setInstitutionID("608");
        dtoList.add(dto);
       
        String[] columnList={ "Error Msg","Description","Institution"};
        String[] valueIdList={ "errorMsgTitle","errorMsgDescription","institutionID"};
        try {
          
            FileOutputStream fileOut = new FileOutputStream("C:\\temp\\TestXcel1.xls");
            workbook.write(fileOut);
            fileOut.close();

           
        }catch (Exception e){
            e.printStackTrace();
        }
       
    }

How to Read Data from DB and write into Excel sheet using Apache POI

Read Data from DB and write into Excel sheet using Apache POI

...
method to read data from the "resource_rbac_status" table and write into the excel sheet using apache POI HSSF API
...

public static HSSFWorkbook generateExcel(){
Connection dbconn = null;
HSSFWorkbook wb = null;
try{
dbconn = RmsDBUtil.getDBConnection(); /// get the connection
Statement stmt= dbconn.createStatement();;
ResultSet rs= stmt.executeQuery ("SELECT * FROM resource_rbac_status");
ResultSetMetaData rsmd = rs.getMetaData ();

int columnCount = rsmd.getColumnCount ();

wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Excel Sheet");

HSSFRow rowhead = sheet.createRow((short) 0);

for (int j = 1; j <= columnCount; ++j) {
rowhead.createCell((short) ((short) j-1)).setCellValue(rsmd.getColumnLabel(j));
}

int index = 1;
while (rs.next()) {

HSSFRow row = sheet.createRow((short) index);
for (int j = 1; j <= columnCount; ++j){

String a=rsmd.getColumnTypeName(j);

if (a.equals("INTEGER")){

row.createCell((short) ((short) j-1)).setCellValue(rs.getInt(j));
} else {

row.createCell((short) ((short) j-1)).setCellValue(rs.getString(j));
}

}

index++;
}

System.out.println("Data is saved in excel file.Please check the excel file ");

//close the connection and statement

}
catch (Exception e) {
System.out.println(e);
}
finally{
// close the connection
}

return wb;

}
...
....
....

How to Read an Excel sheet using Apache POI HSSF

How to read an excel sheet using POI API(HSSF)

....
method to read the excel sheet and prepares the list(of Holiday DTOs) using apache POI API
.....

public static List parseHolidayExcel(String xlsPath) {

List holidayList = new ArrayList();
FileInputStream fis = null;
POIFSFileSystem fileSystem = null;

try {
fis = new FileInputStream(xlsPath);
fileSystem = new POIFSFileSystem(fis);

HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
HolidayListDTO dto = null;
while (rows.hasNext()) {
HSSFRow row = rows.next();

if (row.getRowNum() == 0)
continue;

// System.out.println("Row no:"+row.getRowNum());
dto = new HolidayListDTO();
dto.setSiteStatus(row.getCell((short) 0).getStringCellValue());
dto.setCountry(row.getCell((short) 1).getStringCellValue());
dto.setLocation(row.getCell((short) 2).getStringCellValue());
dto.setHoliday_date((Date) (row.getCell((short) 3)
.getDateCellValue()));
dto.setDay(row.getCell((short) 4).getStringCellValue());
dto.setOccasion(row.getCell((short) 5).getStringCellValue());

holidayList.add(dto);

}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}

return holidayList;
}


To read an Excel sheet using POI API(HSSF) : Data Holder to hold details about the holidays....

import java.io.Serializable;
import java.util.Date;

public class HolidayListDTO implements Serializable{

public String siteStatus;
public String country;
public String location;
public Date holiday_date;
public String day;
public String occasion;
public String getSiteStatus() {
return siteStatus;
}
public void setSiteStatus(String siteStatus) {
this.siteStatus = siteStatus;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public Date getHoliday_date() {
return holiday_date;
}
public void setHoliday_date(Date holidayDate) {
holiday_date = holidayDate;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getOccasion() {
return occasion;
}
public void setOccasion(String occasion) {
this.occasion = occasion;
}


}

Sample Excel file(sheet) to be read by the above code using POI HSSF API....

SiteStatus country location date day occasion
offsite india chennai 10/12/2010 Monday Ramzan
onsite us new jersy 11/12/2010 Tuesday Rangoli
offsite india delhi 9/11/2010 Wednesday deepavali