Examples with NumberFormat jxl.write.NumberFormat used on opensource projects

Example 1 with NumberFormat

use of jxl.write.NumberFormat in project pentaho-kettle by pentaho.

the class ExcelOutput method writeField.

/**
 * Write a value to Excel, increasing data.positionX with one afterwards.
 *
 * @param v
 *          The value to write
 * @param vMeta
 *          The valueMeta to write
 * @param excelField
 *          the field information (if any, otherwise : null)
 * @param column
 *          the excel column for getting the template format
 * @param isHeader
 *          true if this is part of the header/footer
 * @return <code>true</code> if write succeeded
 */
private boolean writeField(Object v, ValueMetaInterface vMeta, ExcelField excelField, int column, boolean isHeader) {
    try {
        String hashName = vMeta.getName();
        if (isHeader) {
            // all strings, can map to the same format.
            hashName = "____header_field____";
        }
        WritableCellFormat cellFormat = data.formats.get(hashName);
        // when template is used, take over the column format
        if (cellFormat == null && meta.isTemplateEnabled() && !isHeader) {
            try {
                if (column < data.templateColumns) {
                    CellFormat format = data.sheet.getColumnView(column).getFormat();
                    if (format != null) {
                        cellFormat = new WritableCellFormat(format);
                        // save for next time around...
                        data.formats.put(hashName, cellFormat);
                    }
                }
            } catch (RuntimeException e) {
            // ignore if the column is not found, format as usual
            }
        }
        if (meta.isAutoSizeColumns()) {
            // prepare auto size columns
            int vlen = vMeta.getName().length();
            if (!isHeader && v != null) {
                vlen = v.toString().trim().length();
            }
            if (vlen > 0 && vlen > data.fieldsWidth[column]) {
                data.fieldsWidth[column] = vlen + 1;
            }
        }
        // Do we need to use a specific format to header?
        if (isHeader) {
            // Set font for header and footer+
            // row to write in
            int rowNumber = data.sheet.getColumn(data.positionX).length;
            data.sheet.addCell(new Label(data.positionX, rowNumber, vMeta.getName(), data.headerCellFormat));
            if (cellFormat == null) {
                // save for next time around...
                data.formats.put(hashName, data.headerCellFormat);
            }
        } else {
            // Will write new row after existing ones in current column
            data.positionY = data.sheet.getColumn(data.positionX).length;
            switch(vMeta.getType()) {
                case ValueMetaInterface.TYPE_DATE:
                    {
                        if (v != null && vMeta.getDate(v) != null) {
                            if (cellFormat == null) {
                                if (excelField != null && excelField.getFormat() != null) {
                                    DateFormat dateFormat = new DateFormat(excelField.getFormat());
                                    if (data.writableFont != null) {
                                        cellFormat = new WritableCellFormat(data.writableFont, dateFormat);
                                        if (data.rowFontBackgoundColour != null) {
                                            cellFormat.setBackground(data.rowFontBackgoundColour);
                                        }
                                    } else {
                                        cellFormat = new WritableCellFormat(dateFormat);
                                    }
                                } else {
                                    if (data.writableFont != null) {
                                        cellFormat = new WritableCellFormat(data.writableFont, DateFormats.FORMAT9);
                                        if (data.rowFontBackgoundColour != null) {
                                            cellFormat.setBackground(data.rowFontBackgoundColour);
                                        }
                                    } else {
                                        cellFormat = new WritableCellFormat(DateFormats.FORMAT9);
                                    }
                                }
                                // save for next time around...
                                data.formats.put(hashName, cellFormat);
                            }
                            DateTime dateTime = new DateTime(data.positionX, data.positionY, vMeta.getDate(v), cellFormat);
                            data.sheet.addCell(dateTime);
                        } else if (!meta.isNullBlank()) {
                            data.sheet.addCell(new Label(data.positionX, data.positionY, ""));
                        }
                        break;
                    }
                default:
                // Output the data value as a string
                case ValueMetaInterface.TYPE_STRING:
                case ValueMetaInterface.TYPE_BOOLEAN:
                case ValueMetaInterface.TYPE_BINARY:
                    {
                        if (cellFormat == null) {
                            cellFormat = new WritableCellFormat(data.writableFont);
                            if (data.rowFontBackgoundColour != null) {
                                cellFormat.setBackground(data.rowFontBackgoundColour);
                            }
                            data.formats.put(hashName, cellFormat);
                        }
                        if (v != null) {
                            Label label = new Label(data.positionX, data.positionY, vMeta.getString(v), cellFormat);
                            data.sheet.addCell(label);
                        } else if (!meta.isNullBlank()) {
                            data.sheet.addCell(new Label(data.positionX, data.positionY, ""));
                        }
                        break;
                    }
                case ValueMetaInterface.TYPE_NUMBER:
                case ValueMetaInterface.TYPE_BIGNUMBER:
                case ValueMetaInterface.TYPE_INTEGER:
                    {
                        if (v != null) {
                            if (cellFormat == null) {
                                String format;
                                if (excelField != null && excelField.getFormat() != null) {
                                    format = excelField.getFormat();
                                } else {
                                    format = "###,###.00";
                                }
                                NumberFormat numberFormat = new NumberFormat(format);
                                if (data.writableFont != null) {
                                    cellFormat = new WritableCellFormat(data.writableFont, numberFormat);
                                    if (data.rowFontBackgoundColour != null) {
                                        cellFormat.setBackground(data.rowFontBackgoundColour);
                                    }
                                } else {
                                    cellFormat = new WritableCellFormat(numberFormat);
                                }
                                // save for next time around...
                                data.formats.put(vMeta.getName(), cellFormat);
                            }
                            jxl.write.Number number = new jxl.write.Number(data.positionX, data.positionY, vMeta.getNumber(v), cellFormat);
                            data.sheet.addCell(number);
                        } else if (!meta.isNullBlank()) {
                            data.sheet.addCell(new Label(data.positionX, data.positionY, ""));
                        }
                        break;
                    }
            }
        }
    } catch (Exception e) {
        logError("Error writing field (" + data.positionX + "," + data.positionY + ") : " + e.toString());
        logError(Const.getStackTracker(e));
        return false;
    } finally {
        // always advance :-)
        data.positionX++;
    }
    return true;
}