Example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat

List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat.

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

@Override
public void updateSuites(Sheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1);
    row.createCell(0, CellType.STRING).setCellValue("ID");
    row.createCell(1, CellType.STRING).setCellValue("Date");
    row.createCell(2, CellType.STRING).setCellValue("Name");
    row.createCell(3, CellType.STRING).setCellValue("Hostname");
    row.createCell(4, CellType.STRING).setCellValue("IP");
    row.createCell(5, CellType.STRING).setCellValue("Username");
    row.createCell(6, CellType.STRING).setCellValue("Profiles");
    row.createCell(7, CellType.STRING).setCellValue("Environment Variables");

    for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) {
        row = sheet.createRow(sheet.getLastRowNum() + 1);

        String id = mapEntry.getKey();
        row.createCell(0, CellType.STRING).setCellValue(id);

        JsonObject suite = mapEntry.getValue();
        JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp");
        Long timestamp = null == primitive ? null : primitive.getAsLong();

        Cell cell = row.createCell(1);/*from   w w  w.j  a v  a2  s  . c o m*/
        if (null != timestamp) {
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
            cell.setCellValue(new Date(timestamp));
            cell.setCellStyle(cellStyle);
        }

        cell = row.createCell(2);
        primitive = suite.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(3);
        JsonObject host = suite.getAsJsonObject("host");
        primitive = null == host ? null : host.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(4);
        primitive = null == host ? null : host.getAsJsonPrimitive("ip");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(5);
        primitive = null == host ? null : host.getAsJsonPrimitive("username");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(6);
        JsonArray array = suite.getAsJsonArray("profiles");
        List<String> profiles = Lists.newArrayList();
        if (null != array) {
            int size = array.size();
            for (int i = 0; i < size; i++) {
                JsonElement element = array.get(i);
                String profile = null == element ? null : element.getAsString();
                profiles.add(profile);
            }
            String profilesValue = Joiner.on('\n').skipNulls().join(profiles);
            cell.setCellValue(profilesValue);
        }

        cell = row.createCell(7);
        JsonObject environmentVariables = suite.getAsJsonObject("environment");
        Map<String, String> index = new TreeMap<>();
        if (null != environmentVariables) {
            Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet();
            for (Map.Entry<String, JsonElement> environmentEntry : entries) {
                String key = environmentEntry.getKey();
                JsonElement element = environmentEntry.getValue();
                String value = null == element ? "" : element.getAsString();
                index.put(key, value);
            }

            String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index);
            cell.setCellValue(variablesValue);
        }
    }

    for (int i = 0; i < 8; i++) {
        sheet.autoSizeColumn(i, false);
    }
}

From source file:Import.Utils.XSSFConvert.java

public void convert() throws IOException {
    Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
    for (int i = 0; i < wbs.length; i++) {
        Workbook wb = wbs[i];//from  w  ww.  j  a  v a2  s. c om
        CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        org.apache.poi.ss.usermodel.Sheet s = wb.createSheet();
        // declare a row object reference
        Row r = null;
        // declare a cell object reference
        Cell c = null;
        // create 2 cell styles
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();

        // create 2 fonts objects
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // Set font 1 to 12 point type, blue and bold
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set font 2 to 10 point type, red and bold
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set cell style and formatting
        cs.setFont(f);
        cs.setDataFormat(df.getFormat("#,##0.0"));

        // Set the other cell style and formatting
        cs2.setBorderBottom(cs2.BORDER_THIN);
        cs2.setDataFormat(df.getFormat("text"));
        cs2.setFont(f2);

        // Define a few rows
        for (int rownum = 0; rownum < 30; rownum++) {
            r = s.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum += 2) {
                c = r.createCell(cellnum);
                Cell c2 = r.createCell(cellnum + 1);

                c.setCellValue((double) rownum + (cellnum / 10));
                c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum));
            }
        }

        // Save
        String filename = "workbook.xls";
        if (wb instanceof XSSFWorkbook) {
            filename = filename + "x";
        }

        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filename);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex);
        }
        wb.write(out);
        out.close();
    }
}

From source file:it.cineca.pst.huborcid.web.rest.ReportatFileResource.java

License:Open Source License

/**
 * GET  /reportat -> get all the relPersonApplications.
 *//*from w w  w.  ja  v  a  2  s .co m*/
@RequestMapping(value = "/reportat/downloadExcel", method = RequestMethod.GET)
@Timed
public void getExcel(HttpServletResponse response) throws URISyntaxException {
    String currentLogin = SecurityUtils.getCurrentLogin();
    Application application = applicationRepository.findOneByApplicationID(currentLogin);
    Sort sort = new Sort(Sort.Direction.ASC, "person.localID");
    List<RelPersonApplication> listAccessToken = relPersonApplicationRepository
            .findAllByLastIsTrueAndApplicationIs(application, sort);

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Report Access Token");

    Object[] headerExcel = new Object[] { "LOCAL ID", "ORCID", "ORCID ASSOCIATION DATE", "ORCID ACCESS TOKEN",
            "ORCID ACCESS TOKEN RELEASED DATE" };
    Row rowHeader = sheet.createRow(0);
    int cellnumHeader = 0;
    //header
    for (Object obj : headerExcel) {
        Cell cell = rowHeader.createCell(cellnumHeader++);
        if (obj instanceof Date)
            cell.setCellValue((Date) obj);
        else if (obj instanceof Boolean)
            cell.setCellValue((Boolean) obj);
        else if (obj instanceof String)
            cell.setCellValue((String) obj);
        else if (obj instanceof Double)
            cell.setCellValue((Double) obj);
    }

    //data

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));

    int rownum = 1;
    for (int i = 0; i < listAccessToken.size(); i++) {
        RelPersonApplication relPerson = listAccessToken.get(i);
        Row rowData = sheet.createRow(rownum++);
        int cellnumData = 0;
        //localid
        Cell cellLocalId = rowData.createCell(cellnumData++);
        cellLocalId.setCellValue(relPerson.getPerson().getLocalID());

        //orcid
        Cell cellOrcid = rowData.createCell(cellnumData++);
        cellOrcid.setCellValue(relPerson.getPerson().getOrcid());

        //orcidCreated
        Cell cellOrcidCreated = rowData.createCell(cellnumData++);
        if (relPerson.getPerson().getOrcidReleaseDate() != null) {
            cellOrcidCreated.setCellValue(relPerson.getPerson().getOrcidReleaseDate().toDate());
            cellOrcidCreated.setCellStyle(cellStyle);
        }

        //orcid access token
        Cell callAccessToken = rowData.createCell(cellnumData++);
        if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) {
            callAccessToken.setCellValue(relPerson.getOauthAccessToken());
        } else {
            callAccessToken.setCellValue((String) null);
        }

        //access token Created
        Cell cellAccessTokenCreated = rowData.createCell(cellnumData++);
        if (relPerson.getDateReleased() != null) {
            if ((relPerson.getDenied() == null) || (relPerson.getDenied() == false)) {
                cellAccessTokenCreated.setCellValue(relPerson.getDateReleased().toDate());
                cellAccessTokenCreated.setCellStyle(cellStyle);
            } else {
                //cellAccessTokenCreated.setCellValue((Date)null);
                cellAccessTokenCreated.setCellStyle(cellStyle);
            }
        }

        //          //FIXME quando verr gestita la revoca
        //          //denied
        //          Cell cellDenied = rowData.createCell(cellnumData++);
        //          if(relPerson.getDenied()!=null)
        //             cellDenied.setCellValue(new Boolean(null));
    }

    //autosize
    for (int i = 0; i < headerExcel.length; i++) {
        sheet.autoSizeColumn(i);
    }

    try {
        workbook.write(response.getOutputStream());

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public CellStyle[] fillSheetHeader(Sheet sheet, Workbook wb, CreationHelper createHelper,
        int beginRowHeaderData, int beginColumnHeaderData) {
    CellStyle hCellStyle = buildHeaderCellStyle(sheet);

    int colnum = extractedFieldsMetaData.size();
    Row row = sheet.getRow(beginRowHeaderData);
    CellStyle[] cellTypes = new CellStyle[colnum]; // array for numbers patterns storage

    for (int j = 0; j < colnum; j++) {
        Cell cell = row.createCell(j + beginColumnHeaderData);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        IFieldMetaData fieldMetaData = extractedFieldsMetaData.get(j);
        String fieldName = fieldMetaData.getName();
        String format = (String) fieldMetaData.getProperty("format");
        String alias = (String) fieldMetaData.getAlias();
        Boolean visible = (Boolean) fieldMetaData.getProperty("visible");
        if (extractedFields != null && extractedFields.get(j) != null) {
            Object f = extractedFields.get(j);
            logger.debug("Extracted field " + fieldName + " is instance of " + f.getClass().getName());
            if (f instanceof Field) {
                Field field = (Field) f;
                fieldName = field.getName();
                if (field.getPattern() != null) {
                    format = field.getPattern();
                }//w  w w  .j  ava 2 s . com
            }
        }
        CellStyle aCellStyle = wb.createCellStyle();
        if (format != null) {
            short formatInt = HSSFDataFormat.getBuiltinFormat(format);
            aCellStyle.setDataFormat(formatInt);
            cellTypes[j] = aCellStyle;
        }
        if (visible != null && visible.booleanValue() == true) {
            if (alias != null && !alias.equals("")) {
                cell.setCellValue(createHelper.createRichTextString(alias));
            } else {
                cell.setCellValue(createHelper.createRichTextString(fieldName));
            }
            cell.setCellStyle(hCellStyle);
        }
    }
    return cellTypes;
}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes,
        int beginRowData, int beginColumnData) {

    CellStyle dCellStyle = buildDataCellStyle(sheet);

    int rownum = beginRowData;
    short formatIndexInt = HSSFDataFormat.getBuiltinFormat("#,##0");
    CellStyle cellStyleInt = wb.createCellStyle(); // cellStyleInt is the default cell style for integers
    cellStyleInt.cloneStyleFrom(dCellStyle);
    cellStyleInt.setDataFormat(formatIndexInt);

    short formatIndexDoub = HSSFDataFormat.getBuiltinFormat("#,##0.00");
    CellStyle cellStyleDoub = wb.createCellStyle(); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    cellStyleDoub.setDataFormat(formatIndexDoub);

    CellStyle cellStyleDate = wb.createCellStyle(); // cellStyleDate is the default cell style for dates
    cellStyleDate.cloneStyleFrom(dCellStyle);
    //cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm"));
    cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d"));

    for (int i = 0; i < numberOfRows; i++) {
        Row rowVal = sheet.getRow(rownum);
        IRecord record = (IRecord) dataStore.getRecordAt(i);
        List fields = record.getFields();
        int length = extractedFieldsMetaData.size();
        for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) {
            IFieldMetaData metaField = extractedFieldsMetaData.get(fieldIndex);
            IField f = (IField) record.getFieldAt((Integer) metaField.getProperty("index"));
            if (f != null && f.getValue() != null) {

                Boolean visible = (Boolean) metaField.getProperty("visible");
                if (visible) {
                    Class c = metaField.getType();
                    logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]");
                    if (rowVal == null) {
                        rowVal = sheet.createRow(rownum);
                    }//from w w  w.java2s.c o m
                    Cell cell = rowVal.createCell(fieldIndex + beginColumnData);
                    cell.setCellStyle(dCellStyle);
                    if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.intValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt);
                    } else if (Number.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.doubleValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleDoub);
                    } else if (String.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]");
                        String val = (String) f.getValue();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else if (Boolean.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]");
                        Boolean val = (Boolean) f.getValue();
                        cell.setCellValue(val.booleanValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                    } else if (Date.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]");
                        Date val = (Date) f.getValue();

                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        String dtString = df.format(val);
                        cell.setCellValue(dtString);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else {
                        logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]");
                        String val = f.getValue().toString();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    }
                }

            }
        }

        rownum++;
    }
}

From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java

License:Mozilla Public License

public CellStyle getNumberFormat(int j, Map<Integer, CellStyle> decimalFormats, Sheet sheet,
        CreationHelper createHelper, CellType celltype) {

    int mapPosition = j;

    if (celltype.equals(CellType.TOTAL)) {
        mapPosition = j + 90000;// w ww  .  j  a  v  a2s. co m
    } else if (celltype.equals(CellType.SUBTOTAL)) {
        mapPosition = j + 80000;
    } else if (celltype.equals(CellType.CF)) {
        mapPosition = j + 60000;
    }

    if (decimalFormats.get(mapPosition) != null)
        return decimalFormats.get(mapPosition);

    if (celltype.equals(CellType.CF)) {
        j = this.getCalculatedFieldDecimals();
    }

    String decimals = "";

    for (int i = 0; i < j; i++) {
        decimals += "0";
    }

    CellStyle cellStyle = this.buildDataCellStyle(sheet);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
        format += "." + decimals;
    }
    cellStyle.setDataFormat(df.getFormat(format));

    if (celltype.equals(CellType.TOTAL)) {
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    }
    if (celltype.equals(CellType.CF)) {
        cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
    }
    if (celltype.equals(CellType.SUBTOTAL)) {
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }

    decimalFormats.put(mapPosition, cellStyle);
    return cellStyle;
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

/**
 * /*from  w  ww.  ja v a2  s .com*/
 * @param sheet ...
 * @param workbook ...
 * @param createHelper ...
 * @param beginRowHeaderData header's vertical offset. Expressed in number of rows
 * @param beginColumnHeaderData header's horizontal offset. Expressed in number of columns
         
 * @return ...
 */
private CellStyle[] fillSheetHeader(Sheet sheet, Workbook workbook, CreationHelper createHelper,
        int beginRowHeaderData, int beginColumnHeaderData) {

    CellStyle[] cellTypes;

    logger.trace("IN");

    try {

        IMetaData dataStoreMetaData = dataStore.getMetaData();
        int colnumCount = dataStoreMetaData.getFieldCount();

        Row headerRow = sheet.getRow(beginRowHeaderData);
        CellStyle headerCellStyle = buildHeaderCellStyle(sheet);

        cellTypes = new CellStyle[colnumCount];
        for (int j = 0; j < colnumCount; j++) {
            Cell cell = headerRow.createCell(j + beginColumnHeaderData);
            cell.setCellType(getCellTypeString());
            String fieldName = dataStoreMetaData.getFieldAlias(j);
            IFieldMetaData fieldMetaData = dataStoreMetaData.getFieldMeta(j);
            String format = (String) fieldMetaData.getProperty("format");
            String alias = (String) fieldMetaData.getAlias();
            String scaleFactorHeader = (String) fieldMetaData.getProperty(
                    WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);

            String header;
            if (extractedFields != null && j < extractedFields.size() && extractedFields.get(j) != null) {
                Field field = (Field) extractedFields.get(j);
                fieldName = field.getAlias();
                if (field.getPattern() != null) {
                    format = field.getPattern();
                }
            }
            CellStyle aCellStyle = this.buildCellStyle(sheet);
            if (format != null) {
                short formatInt = this.getBuiltinFormat(format);
                aCellStyle.setDataFormat(formatInt);
                cellTypes[j] = aCellStyle;
            }

            if (alias != null && !alias.equals("")) {
                header = alias;
            } else {
                header = fieldName;
            }

            header = MeasureScaleFactorOption.getScaledName(header, scaleFactorHeader, locale);
            cell.setCellValue(createHelper.createRichTextString(header));

            cell.setCellStyle(headerCellStyle);

        }

    } catch (Throwable t) {
        throw new SpagoBIRuntimeException("An unexpected error occured while filling sheet header", t);
    } finally {
        logger.trace("OUT");
    }

    return cellTypes;
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes,
        int beginRowData, int beginColumnData) {
    CellStyle dCellStyle = this.buildCellStyle(sheet);
    Iterator it = dataStore.iterator();
    int rownum = beginRowData;
    short formatIndexInt = this.getBuiltinFormat("#,##0");
    CellStyle cellStyleInt = this.buildCellStyle(sheet); // cellStyleInt is the default cell style for integers
    cellStyleInt.cloneStyleFrom(dCellStyle);
    cellStyleInt.setDataFormat(formatIndexInt);

    CellStyle cellStyleDate = this.buildCellStyle(sheet); // cellStyleDate is the default cell style for dates
    cellStyleDate.cloneStyleFrom(dCellStyle);
    cellStyleDate.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));

    IMetaData d = dataStore.getMetaData();

    while (it.hasNext()) {
        Row rowVal = sheet.getRow(rownum);
        IRecord record = (IRecord) it.next();
        List fields = record.getFields();
        int length = fields.size();
        for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) {
            IField f = (IField) fields.get(fieldIndex);
            if (f != null && f.getValue() != null) {

                Class c = d.getFieldType(fieldIndex);
                logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]");
                if (rowVal == null) {
                    rowVal = sheet.createRow(rownum);
                }/*from   w  ww .  j av  a2  s  .  c  om*/
                Cell cell = rowVal.createCell(fieldIndex + beginColumnData);
                cell.setCellStyle(dCellStyle);
                if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]");
                    IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex);
                    String scaleFactor = (String) fieldMetaData.getProperty(
                            WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);
                    Number val = (Number) f.getValue();
                    Double doubleValue = MeasureScaleFactorOption.applyScaleFactor(val.doubleValue(),
                            scaleFactor);
                    cell.setCellValue(doubleValue);
                    cell.setCellType(this.getCellTypeNumeric());
                    cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt);
                } else if (Number.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]");
                    IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex);
                    String decimalPrecision = (String) fieldMetaData
                            .getProperty(IFieldMetaData.DECIMALPRECISION);
                    CellStyle cs;
                    if (decimalPrecision != null) {
                        cs = getDecimalNumberFormat(new Integer(decimalPrecision), sheet, createHelper,
                                dCellStyle);
                    } else {
                        cs = getDecimalNumberFormat(DEFAULT_DECIMAL_PRECISION, sheet, createHelper, dCellStyle);
                    }
                    Number val = (Number) f.getValue();
                    Double value = val.doubleValue();
                    String scaleFactor = (String) fieldMetaData.getProperty(
                            WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);
                    cell.setCellValue(MeasureScaleFactorOption.applyScaleFactor(value, scaleFactor));
                    cell.setCellType(this.getCellTypeNumeric());
                    cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cs);
                } else if (String.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]");
                    String val = (String) f.getValue();
                    cell.setCellValue(createHelper.createRichTextString(val));
                    cell.setCellType(this.getCellTypeString());
                } else if (Boolean.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]");
                    Boolean val = (Boolean) f.getValue();
                    cell.setCellValue(val.booleanValue());
                    cell.setCellType(this.getCellTypeBoolean());
                } else if (Date.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]");
                    Date val = (Date) f.getValue();
                    cell.setCellValue(val);
                    cell.setCellStyle(cellStyleDate);
                } else {
                    logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]");
                    String val = f.getValue().toString();
                    cell.setCellValue(createHelper.createRichTextString(val));
                    cell.setCellType(this.getCellTypeString());
                }
            }
        }
        rownum++;
    }
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper,
        CellStyle dCellStyle) {//from   www  .  j  a v a 2 s .c o  m

    if (decimalFormats.get(j) != null)
        return decimalFormats.get(j);
    String decimals = "";
    for (int i = 0; i < j; i++) {
        decimals += "0";
    }

    CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
        format += "." + decimals;
    }
    cellStyleDoub.setDataFormat(df.getFormat(format));

    decimalFormats.put(j, cellStyleDoub);
    return cellStyleDoub;
}

From source file:it.redev.parco.ext.ExportableModelEntityQuery.java

License:Open Source License

private void setCell(Cell cell, Object obj) {
    if (obj == null) {
        cell.setCellValue("");
    } else if (obj instanceof Date) {
        cell.setCellValue((Date) obj);
        CellStyle style = workbook.createCellStyle();
        CreationHelper helper = workbook.getCreationHelper();
        style.setDataFormat(helper.createDataFormat().getFormat(dateFormat));
        cell.setCellStyle(style);//from w w w.  j a v a  2  s  . c om
    } else if (obj instanceof Boolean) {
        cell.setCellValue((Boolean) obj);
    } else if (obj instanceof Integer) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("0"));
        cell.setCellStyle(style);
    } else if (obj instanceof Double) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("0.00"));
        cell.setCellStyle(style);
    } else if (obj instanceof BigDecimal) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("#,##0.0000"));
        cell.setCellStyle(style);
    } else {
        CreationHelper helper = workbook.getCreationHelper();
        cell.setCellValue(helper.createRichTextString(obj.toString()));
    }
}