-- page--
<af:button id="b7" icon="/images/excel.png" shortDesc="Export">
<af:fileDownloadActionListener contentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
filename="PickHaulProposals.xls"
method="#{WorkbenchBean.generateExcel}"/>
</af:button>
---bean------
public void generateExcel(FacesContext facesContext, OutputStream outputStream)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("PickHaulProposals");
DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("TWCPickHaulInvWorkbenchVO1Iterator");
HSSFRow excelrow = null;
RowSetIterator rsi = dcIteratorBindings.getViewObject().createRowSetIterator(null);
int i = 0;
excelrow = (HSSFRow) worksheet.createRow((short) i);
short j = 0;
HSSFCellStyle decimalStyle = workbook.createCellStyle();
HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
decimalStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00000"));
DecimalFormat df = new DecimalFormat("#.00000");
HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setColor((short) HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
HSSFCell cellA1 = excelrow.createCell(0);
cellA1.setCellValue("Select Flag");
cellA1 = excelrow.createCell(1);
cellA1.setCellValue("Invoice Type");
cellA1 = excelrow.createCell(2);
cellA1.setCellValue("Harvest Ticket Number");
cellA1 = excelrow.createCell(3);
cellA1.setCellValue("Invoice Number");
cellA1 = excelrow.createCell(4);
cellA1.setCellValue("Harvest Year");
cellA1 = excelrow.createCell(5);
cellA1.setCellValue("Region");
cellA1 = excelrow.createCell(6);
cellA1.setCellValue("Variety");
cellA1 = excelrow.createCell(7);
cellA1.setCellValue("Pool Number");
cellA1 = excelrow.createCell(8);
cellA1.setCellValue("Picked Date");
cellA1 = excelrow.createCell(9);
cellA1.setCellValue("Received date");
cellA1 = excelrow.createCell(10);
cellA1.setCellValue("Ranch Block");
cellA1 = excelrow.createCell(11);
cellA1.setCellValue("Ranch");
cellA1 = excelrow.createCell(12);
cellA1.setCellValue("Vendor");
cellA1 = excelrow.createCell(13);
cellA1.setCellValue("Vendor Site Code");
cellA1 = excelrow.createCell(14);
cellA1.setCellValue("UOM");
cellA1 = excelrow.createCell(15);
cellA1.setCellValue("Quantity Received");
cellA1 = excelrow.createCell(16);
cellA1.setCellValue("Rate");
cellA1 = excelrow.createCell(17);
cellA1.setCellValue("Amount Owed");
cellA1 = excelrow.createCell(18);
cellA1.setCellValue("Subsidy");
cellA1 = excelrow.createCell(19);
cellA1.setCellValue("Status");
cellA1 = excelrow.createCell(20);
cellA1.setCellValue("Accrual Status");
cellA1 = excelrow.createCell(21);
cellA1.setCellValue("Creation Date");
cellA1 = excelrow.createCell(22);
cellA1.setCellValue("Submitted Date");
cellA1 = excelrow.createCell(23);
cellA1.setCellValue("Paid Date");
cellA1 = excelrow.createCell(24);
cellA1.setCellValue("Last Updated By");
for (int k = 0; k <= 24; k++)
{
excelrow.getCell(k).setCellStyle(style);
}
Row worknechRows[]=rsi.getAllRowsInRange();
while(worknechRows!=null && worknechRows.length >0){
for(Row row : worknechRows)
//while (rsi.hasNext())
{
//Row row = rsi.next();
//print data from second row in excel
++i;
int colIndex = 0;
excelrow = worksheet.createRow((int) i);
for (String colName: row.getAttributeNames())
{
log.info("Columns Name " + colName);
log.info("Column data " + row.getAttribute(colName));
if (colName.equalsIgnoreCase("SelectCheck"))
{
HSSFCell cell = excelrow.createCell(0);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("InvType"))
{
HSSFCell cell = excelrow.createCell(1);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestTicketNum"))
{
HSSFCell cell = excelrow.createCell(2);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("InvNumber"))
{
HSSFCell cell = excelrow.createCell(3);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestYear"))
{
HSSFCell cell = excelrow.createCell(4);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestRegion"))
{
HSSFCell cell = excelrow.createCell(5);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Variety"))
{
HSSFCell cell = excelrow.createCell(6);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("PoolNumber"))
{
HSSFCell cell = excelrow.createCell(7);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("PickedDate"))
{
HSSFCell cell = excelrow.createCell(8);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("ReceivedDate"))
{
HSSFCell cell = excelrow.createCell(9);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("RanchBlockNumber"))
{
HSSFCell cell = excelrow.createCell(10);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("RanchBlockName"))
{
HSSFCell cell = excelrow.createCell(11);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("VendorName"))
{
HSSFCell cell = excelrow.createCell(12);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("VendorSiteCode"))
{
HSSFCell cell = excelrow.createCell(13);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Uom"))
{
HSSFCell cell = excelrow.createCell(14);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("QtyReceived"))
{
HSSFCell cell = excelrow.createCell(15);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Float.parseFloat(row.getAttribute(colName).toString()));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Rate"))
{
HSSFCell cell = excelrow.createCell(16);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Double.valueOf(df.format(Float.parseFloat(row.getAttribute(colName).toString()))));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cell.setCellStyle(decimalStyle);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("AmtOwed"))
{
HSSFCell cell = excelrow.createCell(17);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Double.valueOf(df.format(Float.parseFloat(row.getAttribute(colName).toString()))));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cell.setCellStyle(decimalStyle);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Subsidy"))
{
HSSFCell cell = excelrow.createCell(18);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Float.parseFloat(row.getAttribute(colName).toString()));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Status"))
{
HSSFCell cell = excelrow.createCell(19);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
}else if (colName.equalsIgnoreCase("Accrual"))
{
HSSFCell cell = excelrow.createCell(20);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
}else if (colName.equalsIgnoreCase("CreationDate")) {
final HSSFCell cell = excelrow.createCell(21);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("SubmittedDate")) {
final HSSFCell cell = excelrow.createCell(22);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("PaidDate")) {
final HSSFCell cell = excelrow.createCell(23);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("LastUpdatedBy")) {
final HSSFCell cell = excelrow.createCell(24);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
}
worksheet.createFreezePane(0, 1, 0, 1);
}
worknechRows=rsi.getNextRangeSet();
}
rsi.closeRowSetIterator();
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e)
{
e.printStackTrace();
}
}
--------------------------------------------
public void generateExcel(FacesContext facesContext, OutputStream outputStream)
{
try
{
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
Date date = new Date();
System.out.println("@@Starting:@@"+formatter.format(date));
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("PickHaulProposals");
DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("TWCPickHaulInvWorkbenchVO1Iterator");
HSSFRow excelrow = null;
RowSetIterator rsi = dcIteratorBindings.getViewObject().createRowSetIterator(null);
int i = 0;
excelrow = (HSSFRow) worksheet.createRow((short) i);
short j = 0;
HSSFCellStyle decimalStyle = workbook.createCellStyle();
HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
decimalStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00000"));
DecimalFormat df = new DecimalFormat("#.00000");
HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setColor((short) HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
HSSFCell cellA1 = excelrow.createCell(0);
cellA1.setCellValue("Select Flag");
cellA1 = excelrow.createCell(1);
cellA1.setCellValue("Invoice Type");
cellA1 = excelrow.createCell(2);
cellA1.setCellValue("Harvest Ticket Number");
cellA1 = excelrow.createCell(3);
cellA1.setCellValue("Invoice Number");
cellA1 = excelrow.createCell(4);
cellA1.setCellValue("Harvest Year");
cellA1 = excelrow.createCell(5);
cellA1.setCellValue("Region");
cellA1 = excelrow.createCell(6);
cellA1.setCellValue("Variety");
cellA1 = excelrow.createCell(7);
cellA1.setCellValue("Pool Number");
cellA1 = excelrow.createCell(8);
cellA1.setCellValue("Picked Date");
cellA1 = excelrow.createCell(9);
cellA1.setCellValue("Received date");
cellA1 = excelrow.createCell(10);
cellA1.setCellValue("Ranch Block");
cellA1 = excelrow.createCell(11);
cellA1.setCellValue("Ranch");
cellA1 = excelrow.createCell(12);
cellA1.setCellValue("Vendor");
cellA1 = excelrow.createCell(13);
cellA1.setCellValue("Vendor Site Code");
cellA1 = excelrow.createCell(14);
cellA1.setCellValue("UOM");
cellA1 = excelrow.createCell(15);
cellA1.setCellValue("Quantity Received");
cellA1 = excelrow.createCell(16);
cellA1.setCellValue("Rate");
cellA1 = excelrow.createCell(17);
cellA1.setCellValue("Amount Owed");
cellA1 = excelrow.createCell(18);
cellA1.setCellValue("Subsidy");
cellA1 = excelrow.createCell(19);
cellA1.setCellValue("Status");
cellA1 = excelrow.createCell(20);
cellA1.setCellValue("Accrual Status");
cellA1 = excelrow.createCell(21);
cellA1.setCellValue("Creation Date");
cellA1 = excelrow.createCell(22);
cellA1.setCellValue("Submitted Date");
cellA1 = excelrow.createCell(23);
cellA1.setCellValue("Paid Date");
cellA1 = excelrow.createCell(24);
cellA1.setCellValue("Last Updated By");
for (int k = 0; k <= 24; k++)
{
excelrow.getCell(k).setCellStyle(style);
}
Row worknechRows[]=rsi.getAllRowsInRange();
while(worknechRows!=null && worknechRows.length >0){
for(Row row : worknechRows)
//while (rsi.hasNext())
{
//Row row = rsi.next();
//print data from second row in excel
++i;
int colIndex = 0;
excelrow = worksheet.createRow((int) i);
for (String colName: row.getAttributeNames())
{
log.info("Columns Name " + colName);
log.info("Column data " + row.getAttribute(colName));
if (colName.equalsIgnoreCase("SelectCheck"))
{
HSSFCell cell = excelrow.createCell(0);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("InvType"))
{
HSSFCell cell = excelrow.createCell(1);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestTicketNum"))
{
HSSFCell cell = excelrow.createCell(2);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("InvNumber"))
{
HSSFCell cell = excelrow.createCell(3);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestYear"))
{
HSSFCell cell = excelrow.createCell(4);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("HarvestRegion"))
{
HSSFCell cell = excelrow.createCell(5);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Variety"))
{
HSSFCell cell = excelrow.createCell(6);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("PoolNumber"))
{
HSSFCell cell = excelrow.createCell(7);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("PickedDate"))
{
HSSFCell cell = excelrow.createCell(8);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("ReceivedDate"))
{
HSSFCell cell = excelrow.createCell(9);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("RanchBlockNumber"))
{
HSSFCell cell = excelrow.createCell(10);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("RanchBlockName"))
{
HSSFCell cell = excelrow.createCell(11);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("VendorName"))
{
HSSFCell cell = excelrow.createCell(12);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("VendorSiteCode"))
{
HSSFCell cell = excelrow.createCell(13);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Uom"))
{
HSSFCell cell = excelrow.createCell(14);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("QtyReceived"))
{
HSSFCell cell = excelrow.createCell(15);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Float.parseFloat(row.getAttribute(colName).toString()));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Rate"))
{
HSSFCell cell = excelrow.createCell(16);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Double.valueOf(df.format(Float.parseFloat(row.getAttribute(colName).toString()))));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cell.setCellStyle(decimalStyle);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("AmtOwed"))
{
HSSFCell cell = excelrow.createCell(17);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Double.valueOf(df.format(Float.parseFloat(row.getAttribute(colName).toString()))));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cell.setCellStyle(decimalStyle);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Subsidy"))
{
HSSFCell cell = excelrow.createCell(18);
if (null != row.getAttribute(colName))
{
cell.setCellValue(Float.parseFloat(row.getAttribute(colName).toString()));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else
{
cell.setCellValue("");
}
} else if (colName.equalsIgnoreCase("Status"))
{
HSSFCell cell = excelrow.createCell(19);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
}else if (colName.equalsIgnoreCase("Accrual"))
{
HSSFCell cell = excelrow.createCell(20);
if (null != row.getAttribute(colName))
{
cell.setCellValue(row.getAttribute(colName).toString());
} else
{
cell.setCellValue("");
}
}else if (colName.equalsIgnoreCase("CreationDate")) {
final HSSFCell cell = excelrow.createCell(21);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("SubmittedDate")) {
final HSSFCell cell = excelrow.createCell(22);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("PaidDate")) {
final HSSFCell cell = excelrow.createCell(23);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
else if (colName.equalsIgnoreCase("LastUpdatedBy")) {
final HSSFCell cell = excelrow.createCell(24);
if (null != row.getAttribute(colName)) {
cell.setCellValue(row.getAttribute(colName).toString());
}
else {
cell.setCellValue("");
}
}
}
worksheet.createFreezePane(0, 1, 0, 1);
}
worknechRows=rsi.getNextRangeSet();
}
rsi.closeRowSetIterator();
workbook.write(outputStream);
outputStream.flush();
// SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
Date date1 = new Date();
System.out.println("@@Ending:@@"+formatter.format(date1));
} catch (Exception e)
{
e.printStackTrace();
}
}
