List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:hr.restart.swing.raExtendedTable.java
License:Apache License
public void exportToXLS(File output) { String fname = output.getName(); if (!fname.endsWith("xls") && fname.indexOf('.') < 0) output = new File(output.getParentFile(), fname + ".xls"); System.out.println("exporting to XLS"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat df = wb.createDataFormat(); String fontFamily = frmParam.getParam("sisfun", "excelFont", "Arial", "Font za export u Excel", true); if (fontFamily == null || fontFamily.length() == 0) fontFamily = "Arial"; int fontSize = 10; String fontSizeTx = frmParam.getParam("sisfun", "excelFontSize", "10", "Veliina fonta za export u Excel, u tokama", true); if (fontSizeTx != null && Aus.getNumber(fontSizeTx) >= 6 && Aus.getNumber(fontSizeTx) <= 72) fontSize = Aus.getNumber(fontSizeTx); HSSFFont font = wb.createFont();//from w w w . j av a2 s. c om font.setFontName(fontFamily); font.setFontHeightInPoints((short) fontSize); HSSFFont fontTitle = wb.createFont(); fontTitle.setFontName(fontFamily); fontTitle.setFontHeightInPoints((short) (fontSize * 1.8)); HSSFFont fontSubtitle = wb.createFont(); fontSubtitle.setFontName(fontFamily); fontSubtitle.setFontHeightInPoints((short) (fontSize * 1.5)); HSSFCellStyle csHeader = wb.createCellStyle(); csHeader.setFont(font); csHeader.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); csHeader.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooter = wb.createCellStyle(); csFooter.setFont(font); csFooter.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooter.setAlignment(HSSFCellStyle.ALIGN_LEFT); csFooter.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooterNum2 = wb.createCellStyle(); csFooterNum2.setFont(font); csFooterNum2.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csFooterNum = wb.createCellStyle(); csFooterNum.setFont(font); csFooterNum.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum.setDataFormat(df.getFormat("#")); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setFont(font); csDate.setAlignment(HSSFCellStyle.ALIGN_CENTER); csDate.setDataFormat(df.getFormat("dd.mm.yyyy")); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setFont(fontTitle); csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csTitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csSubtitle = wb.createCellStyle(); csSubtitle.setFont(fontSubtitle); csSubtitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csSubtitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csNum2 = wb.createCellStyle(); csNum2.setFont(font); csNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csNum3 = wb.createCellStyle(); csNum3.setFont(font); csNum3.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum3.setDataFormat(df.getFormat("#,##0.000")); HSSFCellStyle csNum = wb.createCellStyle(); csNum.setFont(font); csNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum.setDataFormat(df.getFormat("#.#")); HSSFCellStyle csInt = wb.createCellStyle(); csInt.setFont(font); csInt.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csInt.setDataFormat(df.getFormat("#")); HSSFCellStyle csText = wb.createCellStyle(); csText.setFont(font); csText.setAlignment(HSSFCellStyle.ALIGN_LEFT); csText.setDataFormat(df.getFormat("text")); repDynamicProvider dp = repDynamicProvider.getInstance(); boolean sums = dp.hasSumRow(); int cols = getColumnModel().getColumnCount(); int rows = getRowCount() - (sums ? 1 : 0); HSSFSheet sheet = wb.createSheet(); HSSFRow row; HSSFCell cell; short cRow = 0; // header and title row = sheet.createRow(cRow = 0); cell = row.createCell((short) 0); cell.setCellStyle(csText); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(repMemo.getrepMemo().getOneLine()); row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csTitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); if (dp.getTitle().length() > 0) cell.setCellValue(dp.getTitle().substring(1)); else cell.setCellValue(""); if (dp.getSubtitle().length() > 0) { row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csSubtitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getSubtitle().substring(1)); } for (short c = 0; c < cols; c++) sheet.setColumnWidth(c, (short) (getColumnModel().getColumn(c).getWidth() * 40)); // sections row = sheet.createRow(++cRow); int secRow = 0, firstRow = 0; for (int r = 0; r < rows; r++) { if (r == 0) { row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); cell.setCellStyle(csHeader); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(getColumnModel().getColumn(c).getHeaderValue().toString()); } if (firstRow == 0) firstRow = cRow; secRow = cRow; } row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(r, c); if (o instanceof Number) { if (o instanceof BigDecimal) { BigDecimal bd = (BigDecimal) o; if (bd.scale() == 2) cell.setCellStyle(csNum2); else if (bd.scale() == 3) cell.setCellStyle(csNum3); else cell.setCellStyle(csNum); cell.setCellValue(bd.doubleValue()); } else { String t = dp.getValueAt(r, c); if (Aus.isDigit(t)) { cell.setCellStyle(csInt); cell.setCellValue(((Number) o).doubleValue()); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(t); } } } else if (o instanceof Date) { cell.setCellStyle(csDate); cell.setCellValue((Date) o); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getValueAt(r, c)); } } } System.out.println("sums " + sums); if (sums) { int non = 0; while (non < cols && dp.getValueAt(getRowCount() - 1, non).trim().length() == 0) ++non; if (non < cols) { System.out.println("creating row " + non); row = sheet.createRow(++cRow); if (non > 0) { cell = row.createCell((short) 0); cell.setCellStyle(csFooter); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue("U K U P N O"); if (non > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (non - 1))); } for (short c = (short) non; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(rows - 1, c); if ((o instanceof BigDecimal) && ((BigDecimal) o).scale() == 2) cell.setCellStyle(csFooterNum2); else cell.setCellStyle(csFooterNum); if (dp.getValueAt(getRowCount() - 1, c).trim().length() != 0) cell.setCellFormula("SUBTOTAL(9;" + xlsRange(firstRow + 1, cRow, c) + ")"); else cell.setCellValue(""); } } } FileOutputStream out = null; try { out = new FileOutputStream(output); wb.write(out); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) try { out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
/** * Ralise l'export des donnes de contenu et de la ligne d'en-tte. * * @param parameters Paramtre de cet export * @param workbook Document excel/* w w w . j a v a 2 s . c o m*/ * @param sheet Feuille Excel * @param forceLandscape Indique si le parametrage force un affichage en paysage */ private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet, final boolean forceLandscape) { initHssfStyle(workbook); // Column width final Map<Integer, Double> maxWidthPerColumn = new HashMap<>(); if (parameters.hasDtObject()) { exportObject(parameters, workbook, sheet, maxWidthPerColumn); } else { exportList(parameters, workbook, sheet, maxWidthPerColumn); } // On definit la largeur des colonnes: double totalWidth = 0; int cellIndex; for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) { cellIndex = entry.getKey(); final Double maxLength = entry.getValue(); final double usesMaxLength = Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH); sheet.setColumnWidth(cellIndex, Double.valueOf(usesMaxLength * 256).intValue()); totalWidth += usesMaxLength; } /** * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ? */ // note: il ne semble pas simple de mettre title et author dans les // proprits du document final String title = parameters.getTitle(); if (title != null) { final HSSFHeader header = sheet.getHeader(); header.setLeft(title); } sheet.setHorizontallyCenter(true); sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE); if (forceLandscape || totalWidth > 85) { sheet.getPrintSetup().setLandscape(true); } // On dfinit le footer final HSSFFooter footer = sheet.getFooter(); footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages()); }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
/** * Ralise l'export des donnes de contenu et de la ligne d'en-tte. * * @param parameters Paramtre de cet export * @param workbook Document excel/*from w w w. j a v a 2 s . c o m*/ * @param sheet Feuille Excel * @param forceLandscape Indique si le parametrage force un affichage en paysage */ private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet, final boolean forceLandscape) { // Column width final Map<Integer, Double> maxWidthPerColumn = new HashMap<>(); if (parameters.hasDtObject()) { exportObject(parameters, workbook, sheet, maxWidthPerColumn); } else { exportList(parameters, workbook, sheet, maxWidthPerColumn); } // On definit la largeur des colonnes: double totalWidth = 0; int cellIndex; for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) { cellIndex = entry.getKey(); final Double maxLength = entry.getValue(); final int usesMaxLength = Double.valueOf(Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH)) .intValue(); sheet.setColumnWidth(cellIndex, usesMaxLength * 256); totalWidth += usesMaxLength; } /** * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ? */ // note: il ne semble pas simple de mettre title et author dans les proprits du document final String title = parameters.getTitle(); if (title != null) { final HSSFHeader header = sheet.getHeader(); header.setLeft(title); } sheet.setHorizontallyCenter(true); sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE); if (forceLandscape || totalWidth > 85) { sheet.getPrintSetup().setLandscape(true); } // On dfinit le footer final HSSFFooter footer = sheet.getFooter(); footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages()); }
From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java
License:Open Source License
public MemoryFileBuffer writeXLS(IWContext iwc, Map paymentMethod, Map products) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); if (paymentMethod != null && products != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength( this.iwrb.getLocalizedString("accounting_statistics.statistics", "Statistics"), 30)); sheet.setColumnWidth((short) 0, (short) (30 * 256)); sheet.setColumnWidth((short) 1, (short) (14 * 256)); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font);/*from ww w . j a va 2 s. c o m*/ int cellRow = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.item", "Item")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.total", "Total")); cell.setCellStyle(style); Collection keys = paymentMethod.keySet(); Iterator iter = keys.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); String key = (String) iter.next(); int count = ((Collection) paymentMethod.get(key)).size(); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.payment_method." + key, key)); cell = row.createCell((short) 1); cell.setCellValue(String.valueOf(count)); } keys = products.keySet(); iter = keys.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); String key = (String) iter.next(); int count = ((Collection) products.get(key)).size(); cell = row.createCell((short) 0); cell.setCellValue( this.iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key)); cell = row.createCell((short) 1); cell.setCellValue(String.valueOf(count)); } keys = products.keySet(); iter = keys.iterator(); while (iter.hasNext()) { String key = (String) iter.next(); createNewSheet(iwc, wb, iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key), (List) products.get(key)); } wb.write(mos); } buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java
License:Open Source License
private void createNewSheet(IWContext iwc, HSSFWorkbook workbook, String sheetName, List entries) { Collections.sort(entries, new AccountingEntryComparator(iwc.getCurrentLocale())); NumberFormat format = NumberFormat.getInstance(); format.setMaximumFractionDigits(0);/* ww w . j a v a 2 s .c o m*/ format.setMinimumFractionDigits(0); format.setGroupingUsed(false); HSSFSheet sheet = workbook.createSheet(StringHandler.shortenToLength(sheetName, 30)); sheet.setColumnWidth((short) 0, (short) (30 * 256)); sheet.setColumnWidth((short) 1, (short) (14 * 256)); sheet.setColumnWidth((short) 2, (short) (14 * 256)); sheet.setColumnWidth((short) 3, (short) (14 * 256)); sheet.setColumnWidth((short) 4, (short) (14 * 256)); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); int cellRow = 0; short cellColumn = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.name", "Name")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_from", "Valid from")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_to", "Valid to")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.units", "Units")); cell.setCellStyle(style); Iterator iter = entries.iterator(); while (iter.hasNext()) { AccountingEntry entry = (AccountingEntry) iter.next(); row = sheet.createRow(cellRow++); cellColumn = 0; IWTimestamp startDate = new IWTimestamp(entry.getStartDate()); IWTimestamp endDate = null; if (entry.getEndDate() != null) { endDate = new IWTimestamp(entry.getEndDate()); } float units = 0; if (entry.getUnits() > 0) { units = entry.getUnits(); } else { units = entry.getAmount(); } cell = row.createCell(cellColumn++); cell.setCellValue(entry.getName()); cell = row.createCell(cellColumn++); cell.setCellValue(PersonalIDFormatter.format(entry.getPersonalId(), iwc.getCurrentLocale())); cell = row.createCell(cellColumn++); cell.setCellValue(startDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT)); cell = row.createCell(cellColumn++); if (endDate != null) { cell.setCellValue(endDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT)); } else { cell.setCellValue("-"); } cell = row.createCell(cellColumn++); cell.setCellValue(format.format(units)); } }
From source file:is.idega.idegaweb.egov.cases.business.CasesWriter.java
License:Open Source License
public MemoryFileBuffer writeXLS(IWContext iwc, Collection<Case> cases) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); HSSFWorkbook workbook = new HSSFWorkbook(); short cellColumn = 0; HSSFSheet sheet = workbook.createSheet(StringHandler .shortenToLength(iwrb.getLocalizedString("cases_fetcher.statistics", "Statistics"), 30)); sheet.setColumnWidth(cellColumn++, (short) (8 * 256)); sheet.setColumnWidth(cellColumn++, (short) (14 * 256)); sheet.setColumnWidth(cellColumn++, (short) (30 * 256)); sheet.setColumnWidth(cellColumn++, (short) (14 * 256)); sheet.setColumnWidth(cellColumn++, (short) (18 * 256)); if (getBusiness(iwc).useTypes()) { sheet.setColumnWidth(cellColumn++, (short) (14 * 256)); }/*w ww. j a va 2s. co m*/ sheet.setColumnWidth(cellColumn++, (short) (14 * 256)); sheet.setColumnWidth(cellColumn++, (short) (30 * 256)); sheet.setColumnWidth(cellColumn++, (short) (50 * 256)); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); HSSFCellStyle style2 = workbook.createCellStyle(); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); style2.setWrapText(true); int cellRow = 0; cellColumn = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("cases_fetcher.case_id", "Case ID")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("created_date", "Created date")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("case_category", "Case category")); cell.setCellStyle(style); if (getBusiness(iwc).useTypes()) { cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("case_type", "Case type")); cell.setCellStyle(style); } cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("reference", "Reference")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("status", "Status")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("regarding", "Regarding")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("message", "Message")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("reply", "Reply")); cell.setCellStyle(style); User currentUser = iwc.getCurrentUser(); for (Iterator<Case> iter = cases.iterator(); iter.hasNext();) { Case theCase = iter.next(); if (!(theCase instanceof GeneralCase)) { continue; } GeneralCase element = (GeneralCase) theCase; CaseCategory category = element.getCaseCategory(); if (category != null) { Group handlerGroup = category.getHandlerGroup(); if (handlerGroup != null && !currentUser.hasRelationTo(handlerGroup)) { continue; } } CaseType type = element.getCaseType(); CaseStatus status = element.getCaseStatus(); if (status != null && status.equals(getBusiness(iwc).getCaseStatusDeleted())) { continue; } User user = element.getOwner(); IWTimestamp created = new IWTimestamp(element.getCreated()); row = sheet.createRow(cellRow++); cellColumn = 0; cell = row.createCell(cellColumn++); cell.setCellValue(element.getPrimaryKey().toString()); cell.setCellStyle(style2); cell = row.createCell(cellColumn++); cell.setCellValue(created.getLocaleDateAndTime(locale, IWTimestamp.SHORT, IWTimestamp.SHORT)); cell.setCellStyle(style2); if (user != null) { Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()); cell = row.createCell(cellColumn++); cell.setCellValue(name.getName(locale)); cell.setCellStyle(style2); cell = row.createCell(cellColumn++); cell.setCellValue(PersonalIDFormatter.format(user.getPersonalID(), locale)); cell.setCellStyle(style2); } else { cell = row.createCell(cellColumn++); cell.setCellValue(""); cell = row.createCell(cellColumn++); cell.setCellValue(""); } cell = row.createCell(cellColumn++); cell.setCellValue(category == null ? CoreConstants.EMPTY : category.getLocalizedCategoryName(locale)); cell.setCellStyle(style2); if (type != null && getBusiness(iwc).useTypes()) { cell = row.createCell(cellColumn++); cell.setCellValue(type.getName()); cell.setCellStyle(style2); } cell = row.createCell(cellColumn++); cell.setCellValue(element.getReference() != null ? element.getReference() : ""); cell.setCellStyle(style2); cell = row.createCell(cellColumn++); cell.setCellValue(status == null ? CoreConstants.MINUS : getBusiness(iwc).getLocalizedCaseStatusDescription(element, status, locale)); cell.setCellStyle(style2); cell = row.createCell(cellColumn++); cell.setCellValue(element.getSubject() != null ? element.getSubject() : "-"); cell.setCellStyle(style2); cell = row.createCell(cellColumn++); cell.setCellValue(element.getMessage()); cell.setCellStyle(style2); Collection<CaseLog> logs = getBusiness(iwc).getCaseLogs(element); if (!logs.isEmpty()) { for (CaseLog log : logs) { cell = row.createCell(cellColumn++); cell.setCellValue(log.getComment()); cell.setCellStyle(style2); } } else if (element.getReply() != null) { cell = row.createCell(cellColumn++); cell.setCellValue(element.getReply()); cell.setCellStyle(style2); } } workbook.write(mos); buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.cases.business.CasesWriterExtended.java
License:Open Source License
@Override public MemoryFileBuffer writeXLS(IWContext iwc, Collection cases) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(StringHandler .shortenToLength(iwrb.getLocalizedString("cases_fetcher.statistics", "Statistics"), 30)); sheet.setColumnWidth((short) 0, (short) (38 * 256)); sheet.setColumnWidth((short) 1, (short) (85 * 256)); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font);/* w w w . j av a 2 s .c om*/ HSSFCellStyle style2 = workbook.createCellStyle(); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); style2.setWrapText(true); HSSFCellStyle style3 = workbook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setFont(font); User currentUser = iwc.getCurrentUser(); int cellRow = 0; Iterator iter = cases.iterator(); while (iter.hasNext()) { GeneralCase element = (GeneralCase) iter.next(); CaseCategory category = element.getCaseCategory(); Group handlerGroup = category.getHandlerGroup(); if (!currentUser.hasRelationTo(handlerGroup)) { continue; } CaseType type = element.getCaseType(); CaseStatus status = element.getCaseStatus(); if (status.equals(getBusiness(iwc).getCaseStatusDeleted())) { continue; } User user = element.getOwner(); IWTimestamp created = new IWTimestamp(element.getCreated()); HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("cases_fetcher.case_id", "Case ID")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(element.getPrimaryKey().toString()); row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("created_date", "Created date")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(created.getLocaleDateAndTime(locale, IWTimestamp.SHORT, IWTimestamp.SHORT)); if (user != null) { row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()); cell = row.createCell((short) 1); cell.setCellValue(name.getName(locale)); row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(PersonalIDFormatter.format(user.getPersonalID(), locale)); } row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("case_category", "Case category")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(category.getLocalizedCategoryName(locale)); if (getBusiness(iwc).useTypes()) { row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("case_type", "Case type")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(type.getName()); } if (element.getReference() != null) { row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("reference", "Reference")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(element.getReference()); } row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("status", "Status")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(getBusiness(iwc).getLocalizedCaseStatusDescription(element, status, locale)); row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("regarding", "Regarding")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(element.getSubject() != null ? element.getSubject() : "-"); row = sheet.createRow(cellRow++); row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("message", "Message")); cell.setCellStyle(style3); cell = row.createCell((short) 1); cell.setCellValue(element.getMessage()); cell.setCellStyle(style2); if (element.getReply() != null) { row = sheet.createRow(cellRow++); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("reply", "Reply")); cell.setCellStyle(style3); cell = row.createCell((short) 1); cell.setCellValue(element.getReply()); cell.setCellStyle(style2); } } workbook.write(mos); buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.course.business.CourseAttendanceWriter.java
License:Open Source License
public MemoryFileBuffer writeXLS(IWContext iwc, Collection choices) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30)); sheet.setColumnWidth((short) 0, (short) (30 * 256)); sheet.setColumnWidth((short) 1, (short) (14 * 256)); sheet.setColumnWidth((short) 2, (short) (10 * 256)); sheet.setColumnWidth((short) 3, (short) (10 * 256)); sheet.setColumnWidth((short) 4, (short) (10 * 256)); sheet.setColumnWidth((short) 5, (short) (14 * 256)); sheet.setColumnWidth((short) 6, (short) (14 * 256)); sheet.setColumnWidth((short) 7, (short) (30 * 256)); HSSFFont font = wb.createFont();//from w ww . ja v a 2 s. c om font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); HSSFFont bigFont = wb.createFont(); bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); bigFont.setFontHeightInPoints((short) 13); HSSFCellStyle bigStyle = wb.createCellStyle(); bigStyle.setFont(bigFont); int cellRow = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(this.courseName); cell.setCellStyle(bigStyle); cell = row.createCell((short) 1); row = sheet.createRow(cellRow++); short iCell = 0; row = sheet.createRow(cellRow++); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("pre_care", "Pre care")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("post_care", "Post care")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("picked_up", "Picked up")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child.growth_deviation", "Growth deviation")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child.allergies", "Allergies")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child.other_information", "Other information")); cell.setCellStyle(style); User user; User owner; CourseChoice choice; CourseApplication application; Iterator iter = choices.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); choice = (CourseChoice) iter.next(); application = choice.getApplication(); owner = application.getOwner(); user = choice.getUser(); Child child = this.userBusiness.getMemberFamilyLogic().getChild(user); boolean preCare = choice.getDayCare() == CourseConstants.DAY_CARE_PRE || choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST; boolean postCare = choice.getDayCare() == CourseConstants.DAY_CARE_POST || choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST; Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()); row.createCell((short) 0).setCellValue(name.getName(this.locale, true)); row.createCell((short) 1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale)); row.createCell((short) 2).setCellValue( preCare ? iwrb.getLocalizedString("yes", "Yes") : iwrb.getLocalizedString("no", "No")); row.createCell((short) 3).setCellValue( postCare ? iwrb.getLocalizedString("yes", "Yes") : iwrb.getLocalizedString("no", "No")); row.createCell((short) 4).setCellValue(choice.isPickedUp() ? iwrb.getLocalizedString("yes", "Yes") : iwrb.getLocalizedString("no", "No")); Boolean hasGrowthDeviation = child .hasGrowthDeviation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (hasGrowthDeviation == null) { hasGrowthDeviation = child.hasGrowthDeviation(CourseConstants.COURSE_PREFIX); } if (hasGrowthDeviation != null && hasGrowthDeviation.booleanValue()) { row.createCell((short) 5).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell((short) 5).setCellValue(this.iwrb.getLocalizedString("no", "No")); } Boolean hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (hasAllergies == null) { hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX); } if (hasAllergies != null && hasAllergies.booleanValue()) { row.createCell((short) 6).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell((short) 6).setCellValue(this.iwrb.getLocalizedString("no", "No")); } if (child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()) != null) { row.createCell((short) 7).setCellValue( child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey())); } else if (child.getOtherInformation(CourseConstants.COURSE_PREFIX) != null) { row.createCell((short) 7).setCellValue(child.getOtherInformation(CourseConstants.COURSE_PREFIX)); } } wb.write(mos); buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.course.business.CourseParticipantsWriter.java
License:Open Source License
public MemoryFileBuffer writeXLS(IWContext iwc, Collection choices) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30)); sheet.setColumnWidth(0, (30 * 256)); sheet.setColumnWidth(1, (14 * 256)); sheet.setColumnWidth(2, (30 * 256)); sheet.setColumnWidth(3, (14 * 256)); sheet.setColumnWidth(4, (14 * 256)); HSSFFont font = wb.createFont();//from www. jav a 2 s. c o m font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); HSSFFont bigFont = wb.createFont(); bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); bigFont.setFontHeightInPoints((short) 13); HSSFCellStyle bigStyle = wb.createCellStyle(); bigStyle.setFont(bigFont); int cellRow = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell(0); cell.setCellValue(this.courseName); cell.setCellStyle(bigStyle); row = sheet.createRow(cellRow++); boolean showAll = iwc.getApplicationSettings().getBoolean(CourseConstants.PROPERTY_USE_BIRTHYEARS, true); if (showAll) { row = sheet.createRow(cellRow++); cell = row.createCell(0); cell.setCellValue(this.iwrb.getLocalizedString("participant", "Participant")); cell.setCellStyle(bigStyle); cell = row.createCell(13); cell.setCellValue(this.iwrb.getLocalizedString("custodians", "Custodians")); cell.setCellStyle(bigStyle); cell = row.createCell(43); cell.setCellValue(this.iwrb.getLocalizedString("relatives", "Relatives")); cell.setCellStyle(bigStyle); } int iCell = 0; row = sheet.createRow(cellRow++); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("address", "Address")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("postal_code", "Postal code")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone")); cell.setCellStyle(style); if (showAll) { cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child_care.growth_deviation", "Growth deviation")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child_care.allergies", "Allergies")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("child.other_information", "Other information")); cell.setCellStyle(style); /* XXX Picked up */ cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("picked_up", "Picked up")); cell.setCellStyle(style); /* XXX Pre-care and post-care*/ String cellValue = this.iwrb.getLocalizedString("pre_care", "Has pre care"); cellValue = cellValue + CoreConstants.SLASH; cellValue = cellValue + this.iwrb.getLocalizedString("post_care", "Has post care"); cell = row.createCell(iCell++); cell.setCellValue(cellValue); cell.setCellStyle(style); for (int a = 1; a <= 3; a++) { cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("relation", "Relation")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("address", "Address")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("zip_code", "Zip code")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("marital_status", "Marital status")); cell.setCellStyle(style); } } else { cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("register_date", "Register date")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("application.payer_personal_id", "Payer personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("application.payer_name", "Payer name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("application.reference_number", "Reference number")); cell.setCellStyle(style); } if (showAll) { for (int a = 1; a <= 2; a++) { cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("relation", "Relation")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("work_phone", "Work phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("mobile_phone", "Mobile phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("email", "E-mail")); cell.setCellStyle(style); } } User user; User owner; Address address; PostalCode postalCode = null; Phone phone; CourseChoice choice; CourseApplication application; Iterator iter = choices.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); choice = (CourseChoice) iter.next(); application = choice.getApplication(); user = choice.getUser(); owner = application.getOwner(); Child child = this.userBusiness.getMemberFamilyLogic().getChild(user); address = this.userBusiness.getUsersMainAddress(user); if (address != null) { postalCode = address.getPostalCode(); } phone = this.userBusiness.getChildHomePhone(user); Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()); row.createCell(0).setCellValue(name.getName(this.locale, true)); row.createCell(1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale)); if (address != null) { row.createCell(2).setCellValue(address.getStreetAddress()); if (postalCode != null) { row.createCell(3).setCellValue(postalCode.getPostalAddress()); } } if (phone != null) { row.createCell(4).setCellValue(phone.getNumber()); } if (showAll) { Boolean hasGrowthDeviation = child .hasGrowthDeviation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (hasGrowthDeviation == null) { hasGrowthDeviation = child.hasGrowthDeviation(CourseConstants.COURSE_PREFIX); } if (hasGrowthDeviation != null && hasGrowthDeviation.booleanValue()) { row.createCell(5).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell(5).setCellValue(this.iwrb.getLocalizedString("no", "No")); } Boolean hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (hasAllergies == null) { hasAllergies = child.hasAllergies(CourseConstants.COURSE_PREFIX); } if (hasAllergies != null && hasAllergies.booleanValue()) { row.createCell(6).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell(6).setCellValue(this.iwrb.getLocalizedString("no", "No")); } if (child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()) != null) { row.createCell(7).setCellValue( child.getOtherInformation(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey())); } else if (child.getOtherInformation(CourseConstants.COURSE_PREFIX) != null) { row.createCell(7).setCellValue(child.getOtherInformation(CourseConstants.COURSE_PREFIX)); } /* Picked up */ if (choice.isPickedUp()) { row.createCell(8).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell(8).setCellValue(this.iwrb.getLocalizedString("no", "No")); } /* Pre-care and post-care*/ int dayCare = choice.getDayCare(); if (dayCare > 0) { row.createCell(9).setCellValue(this.iwrb.getLocalizedString("yes", "Yes")); } else { row.createCell(9).setCellValue(this.iwrb.getLocalizedString("no", "No")); } iCell = 10; Collection custodians = new ArrayList(); try { custodians = child.getCustodians(); } catch (Exception e) { } Custodian extraCustodian = child.getExtraCustodian(); if (extraCustodian != null) { custodians.add(extraCustodian); } Iterator iterator = custodians.iterator(); while (iterator.hasNext()) { Custodian element = (Custodian) iterator.next(); address = this.userBusiness.getUsersMainAddress(element); Phone work = null; Phone mobile = null; Email email = null; String relation = this.iwrb.getLocalizedString("relation." + child.getRelation(element), "relation." + child.getRelation(element)); String maritalStatus = this.iwrb.getLocalizedString( "marital_status." + element.getMaritalStatus(), "marital_status." + element.getMaritalStatus()); try { phone = this.userBusiness.getUsersHomePhone(element); } catch (NoPhoneFoundException npfe) { phone = null; } try { work = this.userBusiness.getUsersWorkPhone(element); } catch (NoPhoneFoundException npfe) { work = null; } try { mobile = this.userBusiness.getUsersMobilePhone(element); } catch (NoPhoneFoundException npfe) { mobile = null; } try { email = this.userBusiness.getUsersMainEmail(element); } catch (NoEmailFoundException nefe) { email = null; } name = new Name(element.getFirstName(), element.getMiddleName(), element.getLastName()); row.createCell(iCell++).setCellValue(relation); row.createCell(iCell++).setCellValue(name.getName(this.locale, true)); row.createCell(iCell++) .setCellValue(PersonalIDFormatter.format(element.getPersonalID(), this.locale)); if (address != null) { row.createCell(iCell++).setCellValue(address.getStreetAddress()); if (postalCode != null) { row.createCell(iCell++).setCellValue(postalCode.getPostalAddress()); } else { iCell++; } } else { iCell++; } if (phone != null) { row.createCell(iCell++).setCellValue(phone.getNumber()); } else { iCell++; } if (work != null) { row.createCell(iCell++).setCellValue(work.getNumber()); } else { iCell++; } if (mobile != null) { row.createCell(iCell++).setCellValue(mobile.getNumber()); } else { iCell++; } if (email != null) { row.createCell(iCell++).setCellValue(email.getEmailAddress()); } else { iCell++; } if (maritalStatus != null) { row.createCell(iCell++).setCellValue(maritalStatus); } else { iCell++; } } iCell = 40; List relatives = new ArrayList(); Relative mainRelative = child .getMainRelative(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (mainRelative == null) { mainRelative = child.getMainRelative(CourseConstants.COURSE_PREFIX); } if (mainRelative != null) { relatives.add(mainRelative); } Collection otherRelatives = child .getRelatives(CourseConstants.COURSE_PREFIX + owner.getPrimaryKey()); if (otherRelatives.isEmpty()) { otherRelatives = child.getRelatives(CourseConstants.COURSE_PREFIX); } relatives.addAll(otherRelatives); iterator = relatives.iterator(); while (iterator.hasNext()) { Relative element = (Relative) iterator.next(); String relation = this.iwrb.getLocalizedString("relation." + element.getRelation(), "relation." + element.getRelation()); row.createCell(iCell++).setCellValue(relation); row.createCell(iCell++).setCellValue(element.getName()); row.createCell(iCell++).setCellValue(element.getHomePhone()); row.createCell(iCell++).setCellValue(element.getWorkPhone()); row.createCell(iCell++).setCellValue(element.getMobilePhone()); row.createCell(iCell++).setCellValue(element.getEmail()); } } else { iCell = 5; Phone work = null; Phone mobile = null; Email email = null; String payerName = null; String payerPersonalID = null; if (application.getPayerPersonalID() != null) { payerPersonalID = PersonalIDFormatter.format(application.getPayerPersonalID(), locale); payerName = application.getPayerName(); } else { User payer = application.getOwner(); payerName = new Name(payer.getFirstName(), payer.getMiddleName(), payer.getLastName()) .getName(locale); payerPersonalID = PersonalIDFormatter.format(payer.getPersonalID(), locale); } try { work = this.userBusiness.getUsersWorkPhone(child); } catch (NoPhoneFoundException npfe) { work = null; } try { mobile = this.userBusiness.getUsersMobilePhone(child); } catch (NoPhoneFoundException npfe) { mobile = null; } try { email = this.userBusiness.getUsersMainEmail(child); } catch (NoEmailFoundException nefe) { email = null; } if (work != null) { row.createCell(iCell++).setCellValue(work.getNumber()); } else { iCell++; } if (mobile != null) { row.createCell(iCell++).setCellValue(mobile.getNumber()); } else { iCell++; } if (email != null) { row.createCell(iCell++).setCellValue(email.getEmailAddress()); } else { iCell++; } row.createCell(iCell++).setCellValue( new IWTimestamp(application.getCreated()).getLocaleDate(locale, IWTimestamp.SHORT)); row.createCell(iCell++).setCellValue(payerPersonalID); row.createCell(iCell++).setCellValue(payerName); if (application.getReferenceNumber() != null) { row.createCell(iCell++).setCellValue(application.getReferenceNumber()); } else { iCell++; } } } wb.write(mos); buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.course.business.CourseParticipantsWriter.java
License:Open Source License
public MemoryFileBuffer writeAccountingXLS(IWContext iwc, Collection choices) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength(this.courseName, 30)); sheet.setColumnWidth(0, (30 * 256)); sheet.setColumnWidth(1, (14 * 256)); sheet.setColumnWidth(2, (30 * 256)); sheet.setColumnWidth(3, (14 * 256)); sheet.setColumnWidth(4, (14 * 256)); sheet.setColumnWidth(4, (10 * 256)); HSSFFont font = wb.createFont();/*from w ww.j av a 2 s. c o m*/ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); HSSFFont bigFont = wb.createFont(); bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); bigFont.setFontHeightInPoints((short) 13); HSSFCellStyle bigStyle = wb.createCellStyle(); bigStyle.setFont(bigFont); int cellRow = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell(0); cell.setCellValue(this.courseName); cell.setCellStyle(bigStyle); row = sheet.createRow(cellRow++); int iCell = 0; row = sheet.createRow(cellRow++); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("name", "Name")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("address", "Address")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("postal_code", "Postal code")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("home_phone", "Home phone")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("price", "Price")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("payer_personal_id", "Payer personal ID")); cell.setCellStyle(style); cell = row.createCell(iCell++); cell.setCellValue(this.iwrb.getLocalizedString("payer_name", "Payer name")); cell.setCellStyle(style); User user; Address address; PostalCode postalCode = null; Phone phone; CourseChoice choice; CourseApplication application; Iterator iter = choices.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); choice = (CourseChoice) iter.next(); application = choice.getApplication(); user = choice.getUser(); address = this.userBusiness.getUsersMainAddress(user); if (address != null) { postalCode = address.getPostalCode(); } phone = this.userBusiness.getChildHomePhone(user); Course course = choice.getCourse(); User owner = application.getOwner(); if (application.getPayerPersonalID() != null) { User payer = getUserBusiness(iwc).getUser(application.getPayerPersonalID()); if (payer != null) { owner = payer; } } application = choice.getApplication(); float userPrice = 0; if (choice.isNoPayment()) { userPrice = 0; } else { Map applicationMap = getCourseBusiness(iwc).getApplicationMap(application, new Boolean(false)); SortedSet prices = getCourseBusiness(iwc).calculatePrices(applicationMap); Map discounts = getCourseBusiness(iwc).getDiscounts(prices, applicationMap); CoursePrice price = course.getPrice(); float coursePrice = (price != null ? price.getPrice() : course.getCoursePrice()) * (1 - ((PriceHolder) discounts.get(user)).getDiscount()); float carePrice = 0; if (choice.getDayCare() == CourseConstants.DAY_CARE_PRE) { carePrice = price.getPreCarePrice(); } else if (choice.getDayCare() == CourseConstants.DAY_CARE_POST) { carePrice = price.getPostCarePrice(); } else if (choice.getDayCare() == CourseConstants.DAY_CARE_PRE_AND_POST) { carePrice = price.getPreCarePrice() + price.getPostCarePrice(); } carePrice = carePrice * (1 - ((PriceHolder) discounts.get(user)).getDiscount()); userPrice = carePrice + coursePrice; } Name name = new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()); row.createCell(0).setCellValue(name.getName(this.locale, true)); row.createCell(1).setCellValue(PersonalIDFormatter.format(user.getPersonalID(), this.locale)); if (address != null) { row.createCell(2).setCellValue(address.getStreetAddress()); if (postalCode != null) { row.createCell(3).setCellValue(postalCode.getPostalAddress()); } } if (phone != null) { row.createCell(4).setCellValue(phone.getNumber()); } row.createCell(5).setCellValue(userPrice); if (owner != null) { row.createCell(6).setCellValue(owner.getPersonalID()); row.createCell(7) .setCellValue(new Name(user.getFirstName(), user.getMiddleName(), user.getLastName()) .getName(this.locale, true)); } } wb.write(mos); buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }