List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormat
public static String getBuiltinFormat(short index)
From source file:HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.// w ww . ja v a2 s .co m */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); }
From source file:XLS2CSVmra.java
License:Apache License
/** * Formats a number or date cell, be that a real number, or the * answer to a formula//from ww w . j a va2 s . c om */ private String formatNumberDateCell(CellValueRecordInterface cell, double value) { // Get the built in format, if there is one ExtendedFormatRecord xfr = (ExtendedFormatRecord) xfRecords.get(cell.getXFIndex()); if (xfr == null) { System.err.println("Cell " + cell.getRow() + "," + cell.getColumn() + " uses XF with index " + cell.getXFIndex() + ", but we don't have that"); return Double.toString(value); } else { int formatIndex = xfr.getFormatIndex(); String format; if (formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) { FormatRecord tfr = (FormatRecord) customFormatRecords.get(new Integer(formatIndex)); format = tfr.getFormatString(); } else { format = HSSFDataFormat.getBuiltinFormat(xfr.getFormatIndex()); } // Is it a date? if (HSSFDateUtil.isADateFormat(formatIndex, format) && HSSFDateUtil.isValidExcelDate(value)) { // Java wants M not m for month format = format.replace('m', 'M'); // Change \- into -, if it's there format = format.replaceAll("\\\\-", "-"); // Format as a date Date d = HSSFDateUtil.getJavaDate(value, false); DateFormat df = new SimpleDateFormat(format); return df.format(d); } else { if (format.toUpperCase().equals("GENERAL")) { // Some sort of wierd default return Double.toString(value); } // Format as a number DecimalFormat df = new DecimalFormat(format); return df.format(value); } } }
From source file:bean.BankProfileData.java
public void export0() { Integer columnNo;/* w ww . j a v a 2 s. c o m*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Bank SL Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getBankProfileSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++) .setCellValue(getBankProfileSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++) .setCellValue(getBankProfileSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getBankProfileSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getBankProfileSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getBankProfileSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Bank SL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("bankProfileData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassCaData.java
public void export0() { Integer columnNo;/* www . j a v a2 s.co m*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Advances Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "yyyy-MM-dd")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "yyyy-MM-dd")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue( "Account Created Date " + getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd") .concat("-") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "yyyy-MM-dd")) : getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassCaSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassCaSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassCaSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassCaSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassCaSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassCaSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Advances Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classCaData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassDmData0.java
public void export0() { Integer columnNo;/* w w w. ja v a2 s . c o m*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Planong Damayan Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassDmSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassDmSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassDmSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassDmSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassDmSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassDmSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Planong Damayan Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classDmData0().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassOpData.java
public void export0() { Integer columnNo;//from w w w. j av a2s .c o m HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Other Current Liabilities Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassOpSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassOpSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassOpSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassOpSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassOpSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassOpSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Other Current Liabilities Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classOpData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassOtData.java
public void export0() { Integer columnNo;//from w w w . ja v a 2s .c om HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Other Current Receivables Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassOtSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassOtSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassOtSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassOtSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassOtSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassOtSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Other Current Receivables Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classOtData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassRiData.java
public void export0() { Integer columnNo;/* w ww.ja v a 2 s . c om*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Rental Income SL Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassRiSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassRiSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassRiSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassRiSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Rental Income SL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classRiData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassScData.java
public void export0() { Integer columnNo;//from w ww. j av a2 s.c om HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Share Capital Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassScSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassScSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassScSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassScSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassScSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassScSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Share Capital Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classScData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }
From source file:bean.ClassSdData.java
public void export0() throws IOException { Integer columnNo;/*from w w w. j a v a 2 s . c om*/ HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Savings Deposit Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassSdSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassSdSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassSdSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassSdSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassSdSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassSdSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Savings Deposit Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classSdData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }