Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setFont

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setFont

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setFont.

Prototype

public void setFont(HSSFFont font)

Source Link

Usage

From source file:gov.nih.nci.ncicb.cadsr.common.downloads.impl.GetExcelDownloadImpl.java

License:BSD License

private void generateExcelFile() throws Exception {
    Connection cn = null;//  ww w  .  j a va 2  s. c o  m
    Statement st = null;
    ResultSet rs = null;
    FileOutputStream fileOut = null;

    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        int rowNumber = 0;

        HSSFCellStyle boldCellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldCellStyle.setFont(font);
        boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);

        // Create a row and put the column header in it
        HSSFRow row = sheet.createRow(rowNumber++);
        short col = 0;
        List colInfo = this.initColumnInfo(source);

        for (int i = 0; i < colInfo.size(); i++) {
            ColumnInfo currCol = (ColumnInfo) colInfo.get(i);

            if (currCol.type.indexOf("Array") >= 0) {
                for (int nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) {
                    ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI);

                    HSSFCell cell = row.createCell(col++);
                    cell.setCellValue(currCol.displayName + nestedCol.displayName);
                    cell.setCellStyle(boldCellStyle);
                }
            } else {
                HSSFCell cell = row.createCell(col++);

                cell.setCellValue(currCol.displayName);
                cell.setCellStyle(boldCellStyle);
            }
        }

        String sqlStmt = "SELECT * FROM DE_EXCEL_GENERATOR_VIEW " + "WHERE DE_IDSEQ IN " + " ( " + where
                + " )  ";

        ConnectionHelper connHelper = new ConnectionHelper(jndiName);
        cn = connHelper.getConnection();

        if (cn == null) {
            throw new Exception("Cannot get the connection for the JNDI name [" + jndiName + "]");
        }

        st = cn.createStatement();
        rs = st.executeQuery(sqlStmt);
        generateDataRow(rowNumber, sheet, colInfo, rs);
        String filename = getFileName();
        fileOut = new FileOutputStream(filename);
        wb.write(fileOut);

    } catch (SQLException e) {
        log.warn("Database error ", e);
    } catch (Exception ex) {
        log.error("Exception caught in Generate Excel File", ex);
        throw ex;
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (cn != null) {
                cn.close();
            }
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (Exception e) {
            log.debug("Unable to perform clean up due to the following error ", e);
        }
    }

}

From source file:gov.va.med.pharmacy.peps.presentation.common.displaytag.DefaultHssfExportView.java

License:Artistic License

/**
 * doExport creates excel file for download
 * @param out OutputStream/*from  w  w  w .  ja v a2 s  . com*/
 * @see org.displaytag.export.BinaryExportView#doExport(java.io.OutputStream)
 * @throws IOException IOException
 * @throws JspException JspException
 */
public void doExport(OutputStream out) throws IOException, JspException {
    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        sheet = wb.createSheet("Table_Export");

        int rowNum = 0;
        int colNum = 0;

        //Create a header row
        HSSFRow xlsRow = sheet.createRow(rowNum++);

        HSSFCellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
        HSSFFont bold = wb.createFont();
        bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        bold.setColor(HSSFColor.WHITE.index);
        headerStyle.setFont(bold);

        Iterator iterator = this.model.getHeaderCellList().iterator();

        while (iterator.hasNext()) {
            HeaderCell headerCell = (HeaderCell) iterator.next();

            String columnHeader = headerCell.getTitle();

            if (columnHeader == null) {
                columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
            }

            HSSFCell cell = xlsRow.createCell(colNum++);
            cell.setCellValue(columnHeader);
            cell.setCellStyle(headerStyle);
        }

        RowIterator rowIterator = this.model.getRowIterator(this.exportFull);

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            xlsRow = sheet.createRow(rowNum++);
            colNum = 0;

            // iterate on columns
            ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());

            while (columnIterator.hasNext()) {
                Column column = columnIterator.nextColumn();

                Object value = column.getValue(this.decorated);

                HSSFCell cell = xlsRow.createCell(colNum++);

                if (value instanceof Number) {
                    Number num = (Number) value;
                    cell.setCellValue(num.doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                } else if (value instanceof Calendar) {
                    cell.setCellValue((Calendar) value);
                } else {
                    cell.setCellValue(escapeColumnValue(value));
                }

            }
        }

        wb.write(out);

        //            new HssfTableWriter(wb).writeTable(this.model, "-1");            
        //            wb.write(out);
    } catch (Exception e) {
        throw new HssfGenerationException(e);
    }
}

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 ww w. j ava 2 s  .co  m*/
    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

private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setVerticalAlignment((short) 3);
    // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
    // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    return cellStyle;
}

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // cellStyle.setVerticalAlignment((short)3);
    // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setFillForegroundColor(odd ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index);
    // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
    // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // cellStyle.setWrapText(true);

    return cellStyle;
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    font.setBold(true);//from   ww  w  .  j  av a  2s .  c o  m
    cellStyle.setFont(font);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_40_PERCENT.getIndex());
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    return cellStyle;
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    cellStyle.setFont(font);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    cellStyle.setFillForegroundColor(/*  w  w w . j  a  v a  2  s.  c o  m*/
            odd ? HSSFColorPredefined.WHITE.getIndex() : HSSFColorPredefined.GREY_25_PERCENT.getIndex());

    return cellStyle;
}

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);

        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);/*from  w w  w .ja v a2 s .  com*/

        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);/*from  ww w  .  j  a v a  2 s .  c  om*/
    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));
    }/*from   w ww. j a v  a 2  s . 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;
}